Failure to create tables with MySQL 5.7

[cstroe]

I'm running the 2.8.0 in docker. I have a remote MySQL server running in a separate container, and the Piwigo container communicates with it just fine.

Upon first connection to Piwigo, I get the install page. I fill out the appropriate information and after clicking the Start Installation button, I get a blank white page.

The error.log file reveals the following:

[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'date'\nCREATE TABLE `piwigo_comments` ( `id` int(11) unsigned NOT NULL auto_increment, `image_id` mediumint(8) unsigned NOT NULL default '0', `date` datetime NOT NULL default '0000-00-00 00:00:00', `author` varchar(255) default NULL, `email` varchar(255) default NULL, `author_id` mediumint(8) unsigned DEFAULT NULL, `anonymous_id` varchar(45) NOT NULL, `website_url` varchar(255) DEFAULT NULL, `content` longtext, `validated` enum('true','false') NOT NULL default 'false', `validation_date` datetime default NULL, PRIMARY KEY  (`id`), KEY `comments_i2` (`validation_date`), KEY `comments_i1` (`image_id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'date'\nCREATE TABLE `piwigo_history` ( `id` int(10) unsigned NOT NULL auto_increment, `date` date NOT NULL default '0000-00-00', `time` time NOT NULL default '00:00:00', `user_id` mediumint(8) unsigned NOT NULL default '0', `IP` varchar(15) NOT NULL default '', `section` enum('categories','tags','search','list','favorites','most_visited','best_rated','recent_pics','recent_cats') default NULL, `category_id` smallint(5) default NULL, `tag_ids` varchar(50) default NULL, `image_id` mediumint(8) default NULL, `summarized` enum('true','false') default 'false', `image_type` enum('picture','high','other') default NULL, `format_id` int(11) unsigned default NULL, `auth_key_id` int(11) unsigned DEFAULT NULL, PRIMARY KEY  (`id`), KEY `history_i1` (`summarized`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'date_available'\nCREATE TABLE `piwigo_images` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `file` varchar(255) binary NOT NULL default '', `date_available` datetime NOT NULL default '0000-00-00 00:00:00', `date_creation` datetime default NULL, `name` varchar(255) default NULL, `comment` text, `author` varchar(255) default NULL, `hit` int(10) unsigned NOT NULL default '0', `filesize` mediumint(9) unsigned default NULL, `width` smallint(9) unsigned default NULL, `height` smallint(9) unsigned default NULL, `coi` char(4) default NULL COMMENT 'center of interest', `representative_ext` varchar(4) default NULL, `date_metadata_update` date default NULL, `rating_score` float(5,2) unsigned default NULL, `path` varchar(255) NOT NULL default '', `storage_category_id` smallint(5) unsigned default NULL, `level` tinyint unsigned NOT NULL default '0', `md5sum` char(32) default NULL, `added_by` mediumint(8) unsigned NOT NULL default '0', `rotation` tinyint unsigned default in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'date_deleted'\nCREATE TABLE `piwigo_old_permalinks` ( `cat_id` smallint(5) unsigned NOT NULL default '0', `permalink` varchar(64) binary NOT NULL default '', `date_deleted` datetime NOT NULL default '0000-00-00 00:00:00', `last_hit` datetime default NULL, `hit` int(10) unsigned NOT NULL default '0', PRIMARY KEY  (`permalink`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'date'\nCREATE TABLE `piwigo_rate` ( `user_id` mediumint(8) unsigned NOT NULL default '0', `element_id` mediumint(8) unsigned NOT NULL default '0', `anonymous_id` varchar(45) NOT NULL default '', `rate` tinyint(2) unsigned NOT NULL default '0', `date` date NOT NULL default '0000-00-00', PRIMARY KEY  (`element_id`,`user_id`,`anonymous_id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'expiration'\nCREATE TABLE `piwigo_sessions` ( `id` varchar(255) binary NOT NULL default '', `data` mediumtext NOT NULL, `expiration` datetime NOT NULL default '0000-00-00 00:00:00', PRIMARY KEY  (`id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:35 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'applied'\nCREATE TABLE `piwigo_upgrade` ( `id` varchar(20) NOT NULL default '', `applied` datetime NOT NULL default '0000-00-00 00:00:00', `description` varchar(255) default NULL, PRIMARY KEY  (`id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1067] Invalid default value for 'registration_date'\nCREATE TABLE `piwigo_user_infos` ( `user_id` mediumint(8) unsigned NOT NULL default '0', `nb_image_page` smallint(3) unsigned NOT NULL default '15', `status` enum('webmaster','admin','normal','generic','guest') NOT NULL default 'guest', `language` varchar(50) NOT NULL default 'en_UK', `expand` enum('true','false') NOT NULL default 'false', `show_nb_comments` enum('true','false') NOT NULL default 'false', `show_nb_hits` enum('true','false') NOT NULL default 'false', `recent_period` tinyint(3) unsigned NOT NULL default '7', `theme` varchar(255) NOT NULL default 'elegant', `registration_date` datetime NOT NULL default '0000-00-00 00:00:00', `enabled_high` enum('true','false') NOT NULL default 'true', `level` tinyint unsigned NOT NULL default '0', `activation_key` varchar(255) default NULL, `activation_key_expire` datetime default NULL, `lastmodified` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), KEY `last in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US

So several tables didn't get created because of invalid default date values.

That leads to these errors when trying to load any page:

[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1146] Table 'piwigo_db.piwigo_user_infos' doesn't exist\n\nSELECT *\n  FROM piwigo_user_infos\n  WHERE user_id = 2\n; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Notice:  Trying to get property of non-object in /var/www/include/dblayer/functions_mysqli.inc.php on line 203, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1146] Table 'piwigo_db.piwigo_user_infos' doesn't exist\n\nINSERT  INTO piwigo_user_infos\n  (language,user_id,status,registration_date,level)\n  VALUES('en_US','1','webmaster','2016-03-21 07:34:36','8')\n  , ('en_US','2','guest','2016-03-21 07:34:36','0') in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1146] Table 'piwigo_db.piwigo_upgrade' doesn't exist\n\nINSERT  INTO piwigo_upgrade\n  (id,applied,description)\n  VALUES('61','2016-03-21 07:34:36','upgrade included in installation')\n  , ('62','2016-03-21 07:34:36','upgrade included in installation')\n  , ('63','2016-03-21 07:34:36','upgrade included in installation')\n  , ('64','2016-03-21 07:34:36','upgrade included in installation')\n  , ('65','2016-03-21 07:34:36','upgrade included in installation')\n  , ('66','2016-03-21 07:34:36','upgrade included in installation')\n  , ('67','2016-03-21 07:34:36','upgrade included in installation')\n  , ('68','2016-03-21 07:34:36','upgrade included in installation')\n  , ('69','2016-03-21 07:34:36','upgrade included in installation')\n  , ('70','2016-03-21 07:34:36','upgrade included in installation')\n  , ('71','2016-03-21 07:34:36','upgrade included in installation')\n  , ('72','2016-03-21 07:34:36','upgrade included in installation')\n  , ('73','2016-03-21 07:34:36','upgrade included in installation')\n  , ('74','201 in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Warning:  [mysql error 1146] Table 'piwigo_db.piwigo_user_infos' doesn't exist\n\nSELECT\n    ui.*,\n    uc.*,\n    t.name AS theme_name\n  FROM piwigo_user_infos AS ui\n    LEFT JOIN piwigo_user_cache AS uc ON ui.user_id = uc.user_id\n    LEFT JOIN piwigo_themes AS t ON t.id = ui.theme\n  WHERE ui.user_id = 1\n; in /var/www/include/dblayer/functions_mysqli.inc.php on line 832, referer: http://172.17.0.3/install.php?language=en_US
[Mon Mar 21 07:34:36 2016] [error] [client 172.17.0.1] PHP Fatal error:  Call to a member function fetch_assoc() on a non-object in /var/www/include/dblayer/functions_mysqli.inc.php on line 213, referer: http://172.17.0.3/install.php?language=en_US

Not sure what to do about this.

[cstroe]

Looking at piwigo_structure-mysql.sql and replacing the defaults for

• datetime from '0000-00-00 00:00:00' with '1970-01-01 00:00:00'
• date from '0000-00-00' with '1970-01-01'

makes the queries work. Manually tested.

[cstroe]

This is probably relevant:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.11                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+
8 rows in set (0.01 sec)

[cstroe]

Opened a PR that fixed the issue for me: #444

[mistic100]

Why are you using RC2 ? The final version has been released 2-3 weeks ago.

[cstroe]

Ah, sorry. It's 2.8.0 (via http://piwigo.org/download/dlcounter.php?code=latest). I got confused by the GitHub releases page.

[cstroe]

Actually, using MySQL 5.5, I don't get the date error. The installation works fine.

So I guess Piwigo is not yet ready to run with MySQL 5.7.

[plegall]

That would be a first step to fix compatibility with MySQL 5.7 (with default settings). But there are more issues, described on the forum http://piwigo.org/forum/viewtopic.php?pid=162338#p162338

[cstroe]

I see. Ok, good to know.