Jump to content

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


felic
 Share

Recommended Posts

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-changeshttps://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

Link to comment
Share on other sites

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 |                |
+-------------------+------------------+------+-----+---------------------+----------------+
Link to comment
Share on other sites

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

literally the same. Hm...

Link to comment
Share on other sites

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  :P  

sql errors - check (mysql 5.6.15):
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
remove STRICT_TRANS_TABLES
  • Like 10
Link to comment
Share on other sites

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
+-----------------------------------------------------------------------------------------+
Link to comment
Share on other sites

  • 3 months later...
  • 10 months later...

@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...

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...