SystemUpdater #12 Error, PW incompatible with mysql 5.7.x ?

[felic]

Hi,

since i updated my local mysql to v5.7.9, the Processwire SystemUpdater fails (on all local PW installations) with the following message:

SystemUpdater: ERROR: Update #12 ERROR: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'created'

I suppose PW is not prepared for this mysql version.

Sources: https://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html#upgrade-system-table-changes, https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode

As my database knowledge is limited,- can someone confirm this? 

regards

Olaf

[justb3a]

I use mysql 5.7.9 – without any problems so far. (PW 2.6 and 2.7). Where do you get this error messages exactly? Maybe I can reproduce this behaviour..

[felic]

@justb3a Thx for you reply. With fresh installs or/and upgrading?

[justb3a]

Fresh install 2.7.1 and upgraded to 2.7.2

2015-11-28 19:04:38Update #14: Initializing update
2015-11-28 19:04:38Detected core version change 2.7.1 => 2.7.2
2015-11-28 19:04:39Update #14: Completed!

Edit: I just upgraded an "old" PW 2.6.20 installation without any problems:

2015-12-01 14:39:13Update #14: Initializing update
2015-12-01 14:39:13Detected core version change 2.6.20 => 2.7.2
2015-12-01 14:39:14Update #14: Completed!

MySQL

❯ mysql --version
mysql  Ver 14.14 Distrib 5.7.9, for osx10.11 (x86_64) using  EditLine wrapper 

Have a look at your database. What is the default for column created (table pages)?

+-------------------+------------------+------+-----+---------------------+----------------+
| Field             | Type             | Null | Key | Default             | Extra          |
+-------------------+------------------+------+-----+---------------------+----------------+
| created           | timestamp        | NO   | MUL | 0000-00-00 00:00:00 |                |
+-------------------+------------------+------+-----+---------------------+----------------+

[BitPoet]

Have a look if your mysql configuration has strict_trans_tables or strict_all_tables set. This prevents zero dates (since 5.7.4) from working.

See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes for details.

[felic]

+-------------------+------------------+------+-----+---------------------+----------------+
| Field             | Type             | Null | Key | Default             | Extra          |
+-------------------+------------------+------+-----+---------------------+----------------+
| created           | timestamp        | NO   | MUL | 0000-00-00 00:00:00 |                |
+-------------------+------------------+------+-----+---------------------+----------------+

literally the same. Hm...

[justb3a]

Have a look if your mysql configuration has strict_trans_tables or strict_all_tables set. This prevents zero dates (since 5.7.4) from working.

See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes for details.

That's a good point. I remember that I changed something like that a long time ago. I found my snippet    

sql errors - check (mysql 5.6.15):

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

remove STRICT_TRANS_TABLES

[felic]

Ah, that's it. Got to remove some... Thanks to both of you, @justb3a, @BitPoet!

mysql> select @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------------+
| @@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
+-----------------------------------------------------------------------------------------+

[Svet]

justb3a thank you so much!

[gebeer]

@justb3a

 Did you change the settings in the my.cnf file or through phpmyadmin? 

I read that in order to make the changes persist across DB server restarts, we have to change my.cnf.

What for shared hosting where we only have access through phpmyadmin?

 

[felic]

As far as i remember i did on the console (os x) because this occurs on my local stuff. This might be possible through the SQL Console within phpmyadmin (?) 

[gebeer]

@felic Yes, it is possible on the console and through phpadmin. But the changes can only be made by a user with root privileges and they will not persist. So after a restart of  mysql they will be lost.

In most shared hosting environments we are not allowed to do these changes ourselves...

[justb3a]

@gebeer I changed it in the `my.cnf` file directly but it only occurred (as well^^) in my local environment.

[gebeer]

For hosting environments where you cannot change my.cnf and make settings persistent, you can do this: https://github.com/processwire/processwire-issues/issues/28#issuecomment-252870084

I just implemented it and it is working fine.