Revolution: Error 500 + installer fails when MYSQL Strict SQL Mode is ON

[beau-gosse]

Summary

MODX installer fails and produces a 500 error when STRICT_TRANS_TABLES is enabled in MYSQL. This issue was previously discussed here: #2085.

Step to reproduce

Enable Strict SQL Mode in MYSQL's /etc/my.cnf file => global sqlmode='STRICT_TRANS_TABLES' . OS X users may also need to check /usr/local/mysql/my.cnf and /usr/local/Cellar/mysql/5.x.xx/my.cnf (if installed with Homebrew).

Observed behaviour

On localhost, the installer stops immediately and outputs : Fatal error: Class 'xPDODriver_' not found in <core_path>/xpdo/xpdo.class.php on line 1880. On a Virtual Host, the installer proceeds until the summary step and produces a 500 error on the next. In the latter case, some tables are created and others are not.

A few errors found in the installer logs:

[2015-12-31 03:51:27] (ERROR @ /setup/index.php) Could not create table `modx_system_settings`
SQL: CREATE TABLE `modx_system_settings` (`key` VARCHAR(50) NOT NULL DEFAULT '', `value` TEXT NOT NULL, `xtype` VARCHAR(75) NOT NULL DEFAULT 'textfield', `namespace` VARCHAR(40) NOT NULL DEFAULT 'core', `area` VARCHAR(255) NOT NULL DEFAULT '', `editedon` TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`key`)) ENGINE=MyISAM
ERROR: Array
(
    [0] => 42000
    [1] => 1067
    [2] => Invalid default value for 'editedon'
)

[2015-12-31 03:51:28] (ERROR @ /setup/index.php) Error 42S02 executing statement: 
Array
(
    [0] => 42S02
    [1] => 1146
    [2] => Table 'modx.modx_system_settings' doesn't exist
)

[2015-12-31 03:51:28] (ERROR @ /setup/index.php) Error saving vehicle object of class modSystemSetting; criteria: Array
(
    [key] => access_context_enabled
)
...

A few errors found in the server log:

[Wed Dec 30 12:28:47.365884 2015] [:error] [pid 24378] [client 127.0.0.1:53329] PHP   7. xPDOManager_mysql->createObjectContainer() <modx_path>/setup/includes/tables_create.php:116, referer: http://modx.kleverr.dev/setup/index.php?action=summary

[Thu Dec 31 03:51:27.227113 2015] [:error] [pid 32129] [client 127.0.0.1:63055] PHP Notice:  Undefined index: primary in /<core_path>/xpdo/om/mysql/xpdomanager.class.php on line 462, referer: http://modx.kleverr.dev/setup/index.php?action=summary

[Thu Dec 31 03:51:27.232336 2015] [:error] [pid 32129] [client 127.0.0.1:63055] PHP Notice:  Undefined index: unique in <core_path>/xpdo/om/mysql/xpdomanager.class.php on line 462, referer: http://modx.kleverr.dev/setup/index.php?action=summary

Expected behaviour

MODX should be installable when MYSQL Strict SQL Mode is ON

Environment

MODX 2.4.x, Apache 2.4.16, PHP 5.5.X, 5.6.16, MYSQL 5.7.10 Homebrew, OS X El Capitan

[Ibochkarev]

when you install and get the error:
PHP Notice: Undefined index: primary in /var/www/consalger/www/core/xpdo/om/mysql/xpdomanager.class.php on line 462 PHP Notice: Undefined index: unique in /var/www/consalger/www/core/xpdo/om/mysql/xpdomanager.class.php on line 462 Installation finished in 3.0856 s

[sottwell]

Didn't notice that #12987 was closed. Pull request #12990 for fix and comments applied to #12987, sorry.

[sottwell]

Did a pull request #12990, needs testing by somebody with STRICT_TRANS_TABLES enabled.

[Jako]

12990 was merged. Could someone test this commit https://github.com/modxcms/revolution/commit/ec222bad0a094fa4d526088593fb79c50e352180 with STRICT_TRANS_TABLES enabled?

[thewhiterabbit]

I'll test it now. How do I download this commit? I'll do it now.

[OptimusCrime]

That would be:

git clone [email protected]:modxcms/revolution.git
git checkout -b 2.5.x
git pull origin 2.5.x

[thewhiterabbit]

I suppose I don't have access.
:(
fatal: Could not read from remote repository.

Please make sure you have the correct access rights
and the repository exists.

[thewhiterabbit]

I know how to clone the repository, but how do I get access to it? I should have made that clearer, sorry.

[Omeryl]

Hey @thewhiterabbit, more than likely you just didn't have an SSH key added to GitHub. So do this instead:

git clone https://github.com/modxcms/revolution.git
git checkout -b 2.5.x
git pull origin 2.5.x

[thewhiterabbit]

It's been a long day. Thank you for pointing that out @Omeryl, I appreciate it!

As soon as the clone finishes I'll do my testing!

[thewhiterabbit]

OK, this is really weird. The installer isn't allowing me to tick the option for "Core Package has been manually unpacked" nor the option for "Files are already in-place".

I would assume that because I cloned the git repo, those options should be ticked. I must be doing something wrong. It fails install because it can't extract the core.transport.zip.

Have I completely done it wrong?

[thewhiterabbit]

Also, upon further examination, the tables in question:

modx_context_setting
modx_lexicon_entries
modx_system_settings
modx_user_group_settings
modx_user_settings

are still failing to be created.

[thewhiterabbit]

Just to be absolutely sure that I did it correctly, I copied the repo again, wiped the database, deleted the cache folder, then went to the setup page. This is what it looks like. The option "Core Package has been manually unpacked" is still disabled. So, I just continued with the install to test the table creation again. Still failing to create the aforementioned tables, and failing to extract the transport package for core.

[sottwell]

You need to build it. http://modxcookbook.com/installation/files/build.html

[Jako]

See the discussion on #12990

[thewhiterabbit]

Thank you for pointing that out @sottwell! I've eliminated the error regarding the transport, but the tables in question are still not being created. Using mySQL v5.7.10. I'm still getting the 500 error at the end of the installation and I've looked to verify that the missing tables are the cause.

[OptimusCrime]

What are the errors?

[thewhiterabbit]

@OptimusCrime see comment 216715450

[thewhiterabbit]

I just initiated a pull request for the true fix after much research, editing, and testing. Let me know if I can ever help with anything else! Turns out that the best practice is using NULL default values for any time based fields in mySQL.

[Jako]

Thanks a lot.

[thewhiterabbit]

You guys and gals are all very welcome! Glad to help.

[Jako]

13001 is merged, hope the issue is gone now. Thanks for all the comments, tests etc.