cacti and cacti-spine are not compatible with MySQL 5.7 default sql_mode

Status changed to 'Confirmed' because the bug affects multiple users.

Hi Hans,

Thanks for reporting this issue.

On 04-05-16 12:23, Hans Rönnbäck wrote:
> Cacti 0.8.8f which is the default version for Ubuntu 16.04 uses invalid
> datetime values ("0000-00-00 00:00:00"). MySQL 5.7 does not support
> zero-dates,

Are you sure?

Reading the upstream upgrade manual¹, it seems to only do this when you
ask it to (see below). I can't test right now, but isn't this sql_mode
something that you set somewhere? I.e. isn't this something that you
could avoid?

"""
Incompatible change: It is possible for a column DEFAULT value to be
valid for the sql_mode value at table-creation time but invalid for the
sql_mode value when rows are inserted or updated. Example:

SET sql_mode = '';
CREATE TABLE t (d DATE DEFAULT 0);
SET sql_mode = 'NO_ZERO_DATE,STRICT_ALL_TABLES';
INSERT INTO t (d) VALUES(DEFAULT);

In this case, 0 should be accepted for the CREATE TABLE but rejected for
the INSERT. However, previously the server did not evaluate DEFAULT
values used for inserts or updates against the current sql_mode. In the
example, the INSERT succeeds and inserts '0000-00-00' into the DATE column.

As of MySQL 5.7.2, the server applies the proper sql_mode checks to
generate a warning or error at insert or update time.
"""

Paul

¹ http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

Hi Hans (and all others that experience this issue),

Can you log into your MySQL server (preferably with the same credentials as cacti) and run the following command and report the outcome here:
mysql> SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;

On my system, this returns:
mysql> SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
+-------------------+--------------------+------------+
| @@global.sql_mode | @@session.sql_mode | @@sql_mode |
+-------------------+--------------------+------------+
| | | |
+-------------------+--------------------+------------+
1 row in set (0.06 sec)

I suspect that you have somewhere sql_mode NO_ZERO_DATE defined. If that is true, I think what I need to request from upstream is that they are robust against that option by clearing that mode in their connection.

Also, I like to know if this was a fresh install (because I expect this issue would cause the install to fail on your system).

And just so we have all the required info, please run:
apport-collect 1578144

Paul

Hi Paul. I'm the "other" who has reported this problem.

Here's your requested output (tidied up a tiny bit):

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

You are indeed correct that NO_ZERO_IN_DATE is in there.

This is not a fresh install on my system. It's been running for a while, originally installed with Ubuntu 14.10, then upgraded to 15.04, and finally now to 16.04 LTS, which is when the reported problem appeared.

I have not run apport-collect, as I am not the original reporter of this problem, but I can certainly provide any additional information from my system if you need it.

-andrew

apport information

apport information

apport information

Ran the commands, which returned this;

@@global.sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@session.sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

And these values seem to be the default as of MySQL 5.7

I've scanned the cacti forums, where its repeatedly stated that cacti is incompatible with "strict mode" (which implies NO_ZERO_IN_DATE/NO_ZERO_DATE). I've also tried to modify the cacti php files to clear the sql_mode, but it doesn't seem to work (still get errors in logs about invalid dates)

And my upgrade path is similar to Andrews, i.e. started out on Ubuntu 13.10, upgraded to 14.04 followed by 15.10 then went to 16.04 (last step introduced the problem)

Did the apport-collect as asked (it stated that my cacti cron-job was modified, but I have no memory of ever manipulating that file)

I've just installed 16.04 server in a VM, updated, then installed mysql-server mysql-client.
I ran the command request by Paul as the root user. I also cleaned up the output.

@@global.sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@session.sql_mode =
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

@@sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

The attached debdiff seems to do what is required to fix this bug.

I am going to do some more checking and will probably build using my PPA as well for testing by people interested in this bug before asking for a SRU.

People really wanting a solution may want to check if the one line change (in lib/database.php) works for them.

I tried manually applying the supplied patch and restarted cacti (re-enabled cron job), and its still consuming alot of CPU. Top gives me this;

  PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
  931 mysql 20 0 2249948 184144 2136 S 33.6 18.1 17:25.45 mysqld
11522 www-data 20 0 295704 31288 17844 S 15.6 3.1 0:27.59 php

The process list on MySQL have about 10 idle connections by the cacti user. And one is more or less constantly running the query:

SELECT count(*) FROM poller_time WHERE poller_id=0 AND end_time>'0000-00-00 00:00:00'
(followed by 2-3 other queries, all seem to return zero results)

Ah, checking the cacti log, it still emits a number of date-related errors like:

ERROR: SQL Failed! Error:'1292', Message:'Incorrect datetime value: '0000-00-00 00:00:00' for column 'end_time' at row 1', SQL Fragment:'INSERT INTO poller_time (poller_id, pid, start_time, end_time) VALUES (0, 12814, NOW(), '0000-00-00 00:00:00')'

Tried expanding the patch to remove the NO_ZERO_IN_DATE as well which still emits the same error.

Paul's patch appears to have solved the problem for me.

Hans,

On 13-05-16 17:23, Hans Rönnbäck wrote:
> Ah, checking the cacti log, it still emits a number of date-related
> errors like:
>
> ERROR: SQL Failed! Error:'1292', Message:'Incorrect datetime value:
> '0000-00-00 00:00:00' for column 'end_time' at row 1', SQL
> Fragment:'INSERT INTO poller_time (poller_id, pid, start_time, end_time)
> VALUES (0, 12814, NOW(), '0000-00-00 00:00:00')'
>
> Tried expanding the patch to remove the NO_ZERO_IN_DATE as well which
> still emits the same error.

Are you by any chance using spine as the poller?

Paul

And Hans, if you are using spine, does the problem go away with the change and using cmd.php as poller?

Actually, I was using spine (as a desperate attempt to reduce CPU load) and forgot about it. Switching back to cmd.php and using the supplied patch and all is good. CPU usage back to normal levels again.

I confirm that spine has the issue. Trying to fix that in a similar way.

New and verified debdiff for cacti.

Fixed type mismatch in previous debdiff for cacti-spine

I build (pending publication) the updated packages in my PPA. I would appreciate it when people would test these packages and give feedback before I upload to xenial-proposed.

https://launchpad.net/~paul-climbing/+archive/ubuntu/cacti

tested and working, thanks!

Paul, thank you for taking care of this. If I can help, please let me know.

Hi Robie,

On 19-05-16 12:22, Robie Basak wrote:
> Paul, thank you for taking care of this. If I can help, please let me
> know.

Well, help judging. I think my debdiffs can be uploaded (maybe fix the
version to not conflict with the logical version number for yakkety).
However, I am delaying now because of the requirement¹ that the fix
should also be available in yakkety.

My sync of cacti² failed because of a dependency on libphp-adodb which
is behind in Ubuntu with respect to Debian, but Ubuntu stripped more
than required (I think) so either it needs every time a manual action,
or that diff should be ignored. I don't have the time to investigate
what the proper solution is. I could take the Ubuntu version in Debian,
but honestly, I don't like to upload in Debian, just to fix it in Ubuntu.

I updated cacti-spine in Debian yesterday, but because of the mysql
changes, that also needs an Ubuntu change (applying the
libmysql-client_r patch). I just haven't gotten around yet to do it.

So, if the fix is considered urgent (I sure do think we could press),
you could just sponsor my debdiffs. Otherwise, please help decide what
to do and/or making sure cacti and cacti-spine are quickly synced/merged
from Debian.

Paul

¹ https://wiki.ubuntu.com/StableReleaseUpdates#Procedure
² see
http://people.canonical.com/~ubuntu-archive/proposed-migration/update_excuses.html

I uploaded fixed packages for cacti in yakkety and xenial. They are now in the process.

Unfortunately, I don't have upload rights for cacti-spine, so somebody needs to sponsor those for me, once I created a yakkety package (hopefully today).

This bug was fixed in the package cacti - 0.8.8h+ds1-1ubuntu1

---------------
cacti (0.8.8h+ds1-1ubuntu1) yakkety; urgency=medium

  * Lower versioned depends of libphp-adodb as Ubuntu fixed it earlier

 -- Paul Gevers <email address hidden> Fri, 20 May 2016 09:54:29 +0200

I created the merge debdiff for cacti-spine. It works in my lxc. Attached, both the debdiff against the current 0.8.8h-1 in Debian and the version in Ubuntu.

Forgot to mention explicitly, this merge needs sponsorship.

Paul, this looks good, thanks. Note that the Ubuntu change (build against libmysqlclient, not libmysqlclient_r) is actually applicable to Debian too, even against MySQL 5.6. I haven't yet sent all of these up to Debian/upstreams (I'm tracking in bug 1566364). libmysqlclient_r is deprecated in 5.6 but still exists as a symlink which is gone in 5.7. So the patch should work against MySQL 5.6 in Debian though I've not tested it.

So do you want to apply that in Debian and then I can sync cacti-spine in Yakkety? Or if that will take you some time, I'll be happy to upload your merge now.

Robie,

On 20-05-16 15:30, Robie Basak wrote:
> So do you want to apply that in Debian and then I can sync cacti-spine
> in Yakkety? Or if that will take you some time, I'll be happy to upload
> your merge now.

Good to know, but please proceed with the merge, so we can get this bug
out of the way.

Paul

OK, sponsored. Thanks!

Please note that I modified debian/changelog to merge the old Ubuntu entry. This is convention in Ubuntu for merge uploads where an old Ubuntu delta is brought forward (but dropped on a sync). You can do it manually using dpkg-mergechangelogs, or Merge-o-Matic does it automatically (when it works).

This bug was fixed in the package cacti-spine - 0.8.8h-1ubuntu1

---------------
cacti-spine (0.8.8h-1ubuntu1) yakkety; urgency=medium

  * Sync from Debian
    Remaining change:
    - d/p/libmysqlclient_r: build against libmysqlclient, not _r (LP:
      #1564871).

 -- Paul Gevers <email address hidden> Fri, 20 May 2016 15:11:04 +0200

@Robie, did you also upload cacti-spine?

@all, please check that the current state of this bug is according to the procedures for SRU. (at least cacti is all ready as far as I can tell.

On 21-05-16 08:49, Paul Gevers wrote:
> @Robie, did you also upload cacti-spine?

... to xenial (I meant to add that, but my brain was faster than my
fingers).

Paul

On Sat, May 21, 2016 at 06:49:27AM -0000, Paul Gevers wrote:
> @Robie, did you also upload cacti-spine [to Xenial]?

I did not, sorry. Thank you for checking. I wasn't following this bug
closely enough, hadn't realised you had provided an upload for Xenial
that needed sponsoring and hadn't scrolled up far enough.

I've uploaded your debdiff just now, so it's now awaiting SRU team
review. Please note that I made a couple of minor tweaks. 1) I changed
the version to ubuntu1.1 following the guidance linked from the SRU
process page. ubuntu2 would have been technically fine too as Yakkety
has a newer version, but I hear that 1.1 is preferred to make it clearer
that it is an SRU from the version number alone. 2) I appreciate the
dep3 header (I usually insist on these) but s/Ubuntu-bug/Bug-Ubuntu/ to
match the spec.

I trust those tweaks were OK with your name on it - I didn't think it
was worth another round trip to check.

Thank you again for looking after this for us in Ubuntu! Have you
considered asking for upload access for cacti-spine? As you maintain it
in Debian and are already an Ubuntu uploader this should be a
no-brainer I think.

On 21-05-16 12:19, Robie Basak wrote:
> I trust those tweaks were OK with your name on it - I didn't think it
> was worth another round trip to check.

You judged correctly.

> Thank you again for looking after this for us in Ubuntu! Have you
> considered asking for upload access for cacti-spine? As you maintain it
> in Debian and are already an Ubuntu uploader this should be a
> no-brainer I think.

Well, I hadn't used my Ubuntu upload rights in a long time, so I haven't
taken any action lately. However, multiple people already suggested me
to apply for MOTU rights, which I am considering.

Paul

Hello Hans, or anyone else affected,

Accepted cacti-spine into xenial-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/cacti-spine/0.8.8b-1ubuntu1.1 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-needed to verification-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

Hello Hans, or anyone else affected,

Accepted cacti into xenial-proposed. The package will build now and be available at https://launchpad.net/ubuntu/+source/cacti/0.8.8f+ds1-4ubuntu4.16.04 in a few hours, and then in the -proposed repository.

Please help us by testing this new package. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Your feedback will aid us getting this update out to other Ubuntu users.

If this package fixes the bug for you, please add a comment to this bug, mentioning the version of the package you tested, and change the tag from verification-needed to verification-done. If it does not fix the bug for you, please add a comment stating that, and change the tag to verification-failed. In either case, details of your testing will help us make a better decision.

Further information regarding the verification process can be found at https://wiki.ubuntu.com/QATeam/PerformingSRUVerification . Thank you in advance!

Hi there,

I can confirm, the patch in proposed works for me.
Thanks.

Lygie

Both updates fixed the issue in my xenial lxc. Replacing the verification tag appropriately.

Hi,
I found another issue.
Some selects fail because of MYSQLs "ONLY_FULL_GROUP_BY" mode. Think we habe to add something like

$cnn_id->EXECUTE("SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY', ''))");

to database.php also.

Lygie

Hi Lygie,

On 28-05-16 10:23, lygie wrote:
> I found another issue.

Thanks for reporting this.

> Some selects fail because of MYSQLs "ONLY_FULL_GROUP_BY" mode.

I decided to run my new cacti autopkgtest on the xenial version (why
didn't I do that before, I wonder), and found some more... The annoying
thing is, the test passes, but there are errors in the cacti.log.

The following error numbers show up:
Error:'1054'
Error:'1055' <- the one found by you
Error:'1062'
Error:'1064'

I'll dive into it, but I don't mind advice from readers here. I read
some of the documentation with respect to what ONLY_FULL_GROUP_BY is
protecting us from and I will try to figure out if it isn't pointing at
a bugs in cacti. Probably the same goes for the other errors.

Paul

Note to self: let the autopkgtest also check the log for errors, just
filed as Debian bug 825644¹.

¹ https://bugs.debian.org/825644

Hmm, at least those other errors are also there with sql_mode=''. So it looks like only those 1055 errors are new and they completely disappear with removal of ONLY_FULL_GROUP_BY. So indeed, I will only check those.

With respect to those 1055 errors, I have looked at a couple and without digging deep, I can't judge how severe they are. Several of those appear in places where I expect issues would have turned up already. For at least one case, upstream already said:

"user management, JOIN between usear_auth and user_log where in user_log entries for certain users may be missing due to the fact that they did not yet login"

Although I don't fully follow, it appears to me that they judged "I know what I am doing".

So I suggest we indeed add the line in comment #41.

This bug was fixed in the package cacti - 0.8.8f+ds1-4ubuntu4.16.04

---------------
cacti (0.8.8f+ds1-4ubuntu4.16.04) xenial; urgency=medium

  * Add make_cacti_sql_mode-strict_compatible.patch to enable cacti to
    work with the default settings of MySQL 5.7 (LP: #1578144)

 -- Paul Gevers <email address hidden> Sat, 14 May 2016 21:00:40 +0200

The verification of the Stable Release Update for cacti has completed successfully and the package has now been released to -updates. Subsequently, the Ubuntu Stable Release Updates Team is being unsubscribed and will not receive messages about this bug report. In the event that you encounter a regression using the package from -updates please report a new bug using ubuntu-bug and tag the bug report regression-update so we can easily find any regressions.

This bug was fixed in the package cacti-spine - 0.8.8b-1ubuntu1.1

---------------
cacti-spine (0.8.8b-1ubuntu1.1) xenial; urgency=medium

  * Add make_spine_sql_mode-strict_compatible.patch to enable cacti-spine
    to work with the default settings of MySQL 5.7 (LP: #1578144)

 -- Paul Gevers <email address hidden> Sat, 14 May 2016 21:24:30 +0200