Piwik compatible with old Mysql 4.1 (cannot connect to DB since 2.15.0)

[mtsluft]

Hello,

I run Piwik since 2008 and today I got the first error after the auto-update to the latest version (released today):

An error occurred

Cannot connect to the database:

SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_ZERO_IN_DATE'

This may be a temporary issue, try refreshing the page. If the problem persists please contact your Piwik administrator.

Can you help me? sad smiley

[Joey3000]

@mtsluft Hi, I'm just another user. Here is what you could try as a temporary patch, until the devs are back from the weekend: In https://github.com/piwik/piwik/blob/2.15.0/core/Db.php#L36 (i.e. the piwik/core/Db.php file, line 36 in your installation), delete the ,NO_ZERO_IN_DATE at the end of the constant.

But to help the devs to find the root cause, following details would be useful:

• Did the above patch make the issue disappear?
• PHP and MySQL version used
• Which DB engine do you use? Please run SHOW ENGINES (either in the command line, if you have access, or in phpMyAdmin otherwise) and post the output. (It will be either InnoDB, if available, or the default one otherwise.)

Hope this helps.

[mtsluft]

Hi Joey3000,

tried your suggestion, did not work :( Same error is showing, which you can see on http://iluft.com/piwik/

Thanks

[Joey3000]

@mtsluft It's not the same error. :) It now refers to a different value:
SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_ZERO_DATE'

So, could you please, as a test, also try removing the ,NO_ZERO_DATE from the same constant?

Also, the MySQL version used and result of SHOW ENGINES would still be great to know. (PHP is reported as 5.6.2.)

I assume that the last working Piwik version was 2.14.3 (i.e. the previous official release), right?

[mtsluft]

Removed also ,NO_ZERO_DATE, now another new error ...

MySQL Version is 4.1.22

and the result of SHOW ENGINES:

Engine Support Comment
MyISAM DEFAULT Default engine as of MySQL 3.23 with great perform...
HEAP YES Alias for MEMORY
MEMORY YES Hash based, stored in memory, useful for temporary...
MERGE YES Collection of identical MyISAM tables
MRG_MYISAM YES Alias for MERGE
ISAM NO Obsolete storage engine, now replaced by MyISAM
MRG_ISAM NO Obsolete storage engine, now replaced by MERGE
InnoDB YES Supports transactions, row-level locking, and fore...
INNOBASE YES Alias for INNODB
BDB NO Supports transactions and page-level locking
BERKELEYDB NO Alias for BDB
NDBCLUSTER NO Clustered, fault-tolerant, memory-based tables
NDB NO Alias for NDBCLUSTER
EXAMPLE NO Example storage engine
ARCHIVE NO Archive storage engine
CSV NO CSV storage engine
BLACKHOLE NO Storage engine designed to act as null storage

Yes, everything worked for years until the new update...

[mattab]

What's the new error that you get?

[mtsluft]

You can See the whole error at http://iLuft.com/piwik

[Joey3000]

@mattab It's
SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
now.
According to https://dev.mysql.com/doc/refman/5.0/en/sql-mode.html, NO_ZERO_IN_DATE, NO_ZERO_DATE, NO_AUTO_CREATE_USER and ERROR_FOR_DIVISION_BY_ZERO were introduced in MySQL 5.0.2. (But not NO_AUTO_VALUE_ON_ZERO, which was introduced in MySQL 4.1.1, as can be seen in https://web.archive.org/web/20051104064136/http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html. The original MySQL 4.1 manual is no longer online.)

It looks like all except NO_AUTO_VALUE_ON_ZERO needs to be removed from https://github.com/piwik/piwik/blob/2.15.0/core/Db.php#L36, if Piwik is to support "MySQL version 4.1 or greater" (see https://piwik.org/docs/requirements/).

@mtsluft Could you try removing all except NO_AUTO_VALUE_ON_ZERO from that constant and see if the issue disappears?
P.S.:

Yes, everything worked for years until the new update...

Those values were first introduced for Piwik 2.15.0 in #8589.

[Joey3000]

@mattab I thought for a moment that I made a mistake in that if Piwik is to support "MySQL version 4.1 or greater", then NO_AUTO_VALUE_ON_ZERO can't be supported either, as it was not available in MySQL 4.1.0, but only in the later 4.1.1. But according to https://web.archive.org/web/20051126090508/http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html, the first production release of the 4.1.x series was 4.1.7. (While 4.1.0 - 4.1.2 were alphas.) So, it's all good with NO_AUTO_VALUE_ON_ZERO and "MySQL version 4.1 or greater". A bit of a history research here. :)

[mattab]

Thanks @Joey3000 for your research here.

we can discuss increasing minimum required Mysql version to 5.x in Piwik 3.0: are there some benefits? comment in #9107

In the meantime, we will in 2.15.1 make Piwik 2.X compatible with Mysql 4.1.x as it used to

[Triplethread]

»In the meantime, we will in 2.15.1 make Piwik 2.X compatible with Mysql 4.1.x as it used to«
->> Yippee, my Server Version: 4.1.22

[mtsluft]

Hi,

removed all other things except NO_AUTO_VALUE_ON_ZERO and now Piwik works again. Thanks!!
Another question: My MySQL Version is 4.1.22 and Piwik runs quite fast. Would it make sense to transfer it to a newer version MySQL?

[mattab]

Would it make sense to transfer it to a newer version MySQL?

Yes, Mysql 5.5 is much faster than Mysql 4.1 👍