adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-10-30 21:52:00 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

[installation & upgrade] Installation Failure For MySQL > 5.7.4 with default mysql settings #305

New Issue
Closed
opened 2026-10-04 23:51:25 +02:00 by adam · 5 comments
adam commented 2026-10-04 23:51:25 +02:00
Owner
Copy link
Copy source

Reported by hamiltont on 16 Feb 2015 19:54

Version: 2.7.3

As of mysql 5.7.4 it appears that mysql turns strict mode by default - see https://dev.mysql.com/doc/refman/5.7/en … ode-strict

This causes the piwigo installation to fail when creating numerous tables.
I can confirm that disabling STRICT_TRANS_TABLES allows piwigo to
install properly. Helpful stackoverflow post on this topic is at
http://stackoverflow.com/a/25351733

Errors, so that they can hopefully be cleaned up and Piwigo can work with default MySQL

[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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, PRIMARY KEY (id), KEY history_i1 (summarized) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US
[Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.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 830, referer: http://<removed>/install.php?language=en_US

Steps to reproduce: Install mysql 5.7.4 with default settings, run piwigo installation. Multiple database tables are not created. If you watch the mysql error log you can see error output indicating the problems

Piwigo Bugtracker #3205

**Reported by hamiltont on 16 Feb 2015 19:54** **Version:** 2.7.3 As of mysql 5.7.4 it appears that mysql turns strict mode by default - see https://dev.mysql.com/doc/refman/5.7/en … ode-strict This causes the piwigo installation to fail when creating numerous tables. I can confirm that disabling STRICT_TRANS_TABLES allows piwigo to install properly. Helpful stackoverflow post on this topic is at http://stackoverflow.com/a/25351733 Errors, so that they can hopefully be cleaned up and Piwigo can work with default MySQL [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;date&#039;\nCREATE TABLE `piwigo_comments` ( `id` int(11) unsigned NOT NULL auto_increment, `image_id` mediumint(8) unsigned NOT NULL default &#039;0&#039;, `date` datetime NOT NULL default &#039;0000-00-00 00:00:00&#039;, `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(&#039;true&#039;,&#039;false&#039;) NOT NULL default &#039;false&#039;, `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 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;date&#039;\nCREATE TABLE `piwigo_history` ( `id` int(10) unsigned NOT NULL auto_increment, `date` date NOT NULL default &#039;0000-00-00&#039;, `time` time NOT NULL default &#039;00:00:00&#039;, `user_id` mediumint(8) unsigned NOT NULL default &#039;0&#039;, `IP` varchar(15) NOT NULL default &#039;&#039;, `section` enum(&#039;categories&#039;,&#039;tags&#039;,&#039;search&#039;,&#039;list&#039;,&#039;favorites&#039;,&#039;most_visited&#039;,&#039;best_rated&#039;,&#039;recent_pics&#039;,&#039;recent_cats&#039;) default NULL, `category_id` smallint(5) default NULL, `tag_ids` varchar(50) default NULL, `image_id` mediumint(8) default NULL, `summarized` enum(&#039;true&#039;,&#039;false&#039;) default &#039;false&#039;, `image_type` enum(&#039;picture&#039;,&#039;high&#039;,&#039;other&#039;) 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 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;date_available&#039;\nCREATE TABLE `piwigo_images` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `file` varchar(255) binary NOT NULL default &#039;&#039;, `date_available` datetime NOT NULL default &#039;0000-00-00 00:00:00&#039;, `date_creation` datetime default NULL, `name` varchar(255) default NULL, `comment` text, `author` varchar(255) default NULL, `hit` int(10) unsigned NOT NULL default &#039;0&#039;, `filesize` mediumint(9) unsigned default NULL, `width` smallint(9) unsigned default NULL, `height` smallint(9) unsigned default NULL, `coi` char(4) default NULL COMMENT &#039;center of interest&#039;, `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 &#039;&#039;, `storage_category_id` smallint(5) unsigned default NULL, `level` tinyint unsigned NOT NULL default &#039;0&#039;, `md5sum` char(32) default NULL, `added_by` mediumint(8) unsigned NOT NULL default &#039;0&#039;, `rotation` tinyint unsigned default in /var/www/include/dblayer/functions_mysqli.inc.php on line 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;date_deleted&#039;\nCREATE TABLE `piwigo_old_permalinks` ( `cat_id` smallint(5) unsigned NOT NULL default &#039;0&#039;, `permalink` varchar(64) binary NOT NULL default &#039;&#039;, `date_deleted` datetime NOT NULL default &#039;0000-00-00 00:00:00&#039;, `last_hit` datetime default NULL, `hit` int(10) unsigned NOT NULL default &#039;0&#039;, PRIMARY KEY (`permalink`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;date&#039;\nCREATE TABLE `piwigo_rate` ( `user_id` mediumint(8) unsigned NOT NULL default &#039;0&#039;, `element_id` mediumint(8) unsigned NOT NULL default &#039;0&#039;, `anonymous_id` varchar(45) NOT NULL default &#039;&#039;, `rate` tinyint(2) unsigned NOT NULL default &#039;0&#039;, `date` date NOT NULL default &#039;0000-00-00&#039;, 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 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;expiration&#039;\nCREATE TABLE `piwigo_sessions` ( `id` varchar(255) binary NOT NULL default &#039;&#039;, `data` mediumtext NOT NULL, `expiration` datetime NOT NULL default &#039;0000-00-00 00:00:00&#039;, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARACTER SET utf8; in /var/www/include/dblayer/functions_mysqli.inc.php on line 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;applied&#039;\nCREATE TABLE `piwigo_upgrade` ( `id` varchar(20) NOT NULL default &#039;&#039;, `applied` datetime NOT NULL default &#039;0000-00-00 00:00:00&#039;, `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 830, referer: http://&lt;removed&gt;/install.php?language=en_US [Mon Feb 09 17:13:15 2015] [error] [client 172.17.42.1] PHP Warning: [mysql error 1067] Invalid default value for &#039;registration_date&#039;\nCREATE TABLE `piwigo_user_infos` ( `user_id` mediumint(8) unsigned NOT NULL default &#039;0&#039;, `nb_image_page` smallint(3) unsigned NOT NULL default &#039;15&#039;, `status` enum(&#039;webmaster&#039;,&#039;admin&#039;,&#039;normal&#039;,&#039;generic&#039;,&#039;guest&#039;) NOT NULL default &#039;guest&#039;, `language` varchar(50) NOT NULL default &#039;en_UK&#039;, `expand` enum(&#039;true&#039;,&#039;false&#039;) NOT NULL default &#039;false&#039;, `show_nb_comments` enum(&#039;true&#039;,&#039;false&#039;) NOT NULL default &#039;false&#039;, `show_nb_hits` enum(&#039;true&#039;,&#039;false&#039;) NOT NULL default &#039;false&#039;, `recent_period` tinyint(3) unsigned NOT NULL default &#039;7&#039;, `theme` varchar(255) NOT NULL default &#039;elegant&#039;, `registration_date` datetime NOT NULL default &#039;0000-00-00 00:00:00&#039;, `enabled_high` enum(&#039;true&#039;,&#039;false&#039;) NOT NULL default &#039;true&#039;, `level` tinyint unsigned NOT NULL default &#039;0&#039;, `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 830, referer: http://&lt;removed&gt;/install.php?language=en_US **Steps to reproduce:** Install mysql 5.7.4 with default settings, run piwigo installation. Multiple database tables are not created. If you watch the mysql error log you can see error output indicating the problems [Piwigo Bugtracker #3205](http://piwigo.org/bugs/view.php?id=3205) <!-- github-sync-id:97227074 -->
adam closed this issue 2026-10-04 23:51:25 +02:00
adam commented 2026-10-04 23:51:26 +02:00
Author
Owner
Copy link
Copy source

OK, so I suppose we can fix it by removing all:
default '0000-00-00 00:00:00''
from install/piwigo_structure-mysql.sql (and check the install/db/*.php too)

Unfortunately, I don't have MySQL 5.7.4 to test that :-/

OK, so I suppose we can fix it by removing all: `default '0000-00-00 00:00:00''` from install/piwigo_structure-mysql.sql (and check the install/db/*.php too) Unfortunately, I don't have MySQL 5.7.4 to test that :-/ <!-- github-comment-id:184342863 -->
adam commented 2026-10-04 23:51:27 +02:00
Author
Owner
Copy link
Copy source

I postpone to 2.9 because I have no MySQL 5.7 to test :-/ (and I don't really want to blind test)

I postpone to 2.9 because I have no MySQL 5.7 to test :-/ (and I don't really want to blind test) <!-- github-comment-id:184344585 -->
adam commented 2026-10-04 23:51:28 +02:00
Author
Owner
Copy link
Copy source

this workaround works:

sed -i -e 's/0000-00-00 00:00:00/1970-01-01 00:00:01/g' \
  -e 's/0000-00-00/1970-01-01/g' ./install/piwigo_structure-mysql.sql

If you need a mysql 5.7, you can use docker, ie:

docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD="your_password" -d mysql:5.7

this workaround works: ``` sed -i -e 's/0000-00-00 00:00:00/1970-01-01 00:00:01/g' \ -e 's/0000-00-00/1970-01-01/g' ./install/piwigo_structure-mysql.sql ``` If you need a mysql 5.7, you can use docker, ie: `docker run --name mysql5.7 -p 3306:3306 -e MYSQL_ROOT_PASSWORD="your_password" -d mysql:5.7` <!-- github-comment-id:197731432 -->
adam commented 2026-10-04 23:51:28 +02:00
Author
Owner
Copy link
Copy source

I have no MySQL 5.7 to test

Here are the columns with a default of zero.

| piwigo_comments                     | date                 | datetime  | 0000-00-00 00:00:00 |
| piwigo_history                      | date                 | date      | 0000-00-00          |
| piwigo_images                       | date_available       | datetime  | 0000-00-00 00:00:00 |
| piwigo_old_permalinks               | date_deleted         | datetime  | 0000-00-00 00:00:00 |
| piwigo_rate                         | date                 | date      | 0000-00-00          |
| piwigo_sessions                     | expiration           | datetime  | 0000-00-00 00:00:00 |
| piwigo_upgrade                      | applied              | datetime  | 0000-00-00 00:00:00 |
| piwigo_user_infos                   | registration_date    | datetime  | 0000-00-00 00:00:00 |

Do all the INSERT statements set a value for these columns?
As far as I can tell, the answer is yes. They all use NOW() or $dbnow.

Therefore the default is not needed. Simply remove it.

This change would work for all versions of MySQL.

> I have no MySQL 5.7 to test Here are the columns with a default of zero. ``` | piwigo_comments | date | datetime | 0000-00-00 00:00:00 | | piwigo_history | date | date | 0000-00-00 | | piwigo_images | date_available | datetime | 0000-00-00 00:00:00 | | piwigo_old_permalinks | date_deleted | datetime | 0000-00-00 00:00:00 | | piwigo_rate | date | date | 0000-00-00 | | piwigo_sessions | expiration | datetime | 0000-00-00 00:00:00 | | piwigo_upgrade | applied | datetime | 0000-00-00 00:00:00 | | piwigo_user_infos | registration_date | datetime | 0000-00-00 00:00:00 | ``` Do all the `INSERT` statements set a value for these columns? As far as I can tell, the answer is yes. They all use `NOW()` or `$dbnow`. Therefore the default is not needed. Simply remove it. This change would work for all versions of MySQL. <!-- github-comment-id:206773545 -->
adam commented 2026-10-04 23:51:29 +02:00
Author
Owner
Copy link
Copy source

Fixed in Piwigo 2.8.1, see #443

Fixed in Piwigo 2.8.1, see #443 <!-- github-comment-id:262950811 -->
Sign in to join this conversation.
No Branch/Tag Specified
Labels
No items
No labels
Milestone
No items
No Milestone
Projects
Clear projects
No projects
Assignees
Clear assignees
adam (Adam Wojcieszonek)
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: adam/Piwigo#305