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

MySQL 5.7, change default datetime values #617

New Issue
Closed
opened 2026-10-05 00:07:36 +02:00 by adam · 7 comments
adam commented 2026-10-05 00:07:36 +02:00
Owner
Copy link
Copy source

I don't understand how this user http://piwigo.org/forum/viewtopic.php?id=27923 ended up in this situation but he had MySQL 5.7 and default datetime values at "0000-00-00 00:00:00". We should fix that with an upgrade script.

ALTER TABLE `piwigo_comments` CHANGE `date` `date` datetime NOT NULL default '1970-01-01 00:00:00';
ALTER TABLE `piwigo_history` CHANGE `date` `date` date NOT NULL default '1970-01-01';
ALTER TABLE `piwigo_images` CHANGE `date_available` `date_available` datetime NOT NULL default '1970-01-01 00:00:00';
ALTER TABLE `piwigo_old_permalinks` CHANGE  `date_deleted` `date_deleted` datetime NOT NULL default '1970-01-01 00:00:00';
ALTER TABLE `piwigo_rate` CHANGE `date` `date` date NOT NULL default '1970-01-01';
ALTER TABLE `piwigo_sessions` CHANGE `expiration` `expiration` datetime NOT NULL default '1970-01-01 00:00:00';
ALTER TABLE `piwigo_upgrade` CHANGE `applied` `applied` datetime NOT NULL default '1970-01-01 00:00:00';
ALTER TABLE `piwigo_user_infos` CHANGE `registration_date` `registration_date` datetime NOT NULL default '1970-01-01 00:00:00';
I don't understand how this user http://piwigo.org/forum/viewtopic.php?id=27923 ended up in this situation but he had MySQL 5.7 and default datetime values at "0000-00-00 00:00:00". We should fix that with an upgrade script. ``` ALTER TABLE `piwigo_comments` CHANGE `date` `date` datetime NOT NULL default '1970-01-01 00:00:00'; ALTER TABLE `piwigo_history` CHANGE `date` `date` date NOT NULL default '1970-01-01'; ALTER TABLE `piwigo_images` CHANGE `date_available` `date_available` datetime NOT NULL default '1970-01-01 00:00:00'; ALTER TABLE `piwigo_old_permalinks` CHANGE `date_deleted` `date_deleted` datetime NOT NULL default '1970-01-01 00:00:00'; ALTER TABLE `piwigo_rate` CHANGE `date` `date` date NOT NULL default '1970-01-01'; ALTER TABLE `piwigo_sessions` CHANGE `expiration` `expiration` datetime NOT NULL default '1970-01-01 00:00:00'; ALTER TABLE `piwigo_upgrade` CHANGE `applied` `applied` datetime NOT NULL default '1970-01-01 00:00:00'; ALTER TABLE `piwigo_user_infos` CHANGE `registration_date` `registration_date` datetime NOT NULL default '1970-01-01 00:00:00'; ``` <!-- github-sync-id:226258174 -->
adam closed this issue 2026-10-05 00:07:36 +02:00
adam commented 2026-10-05 00:07:37 +02:00
Author
Owner
Copy link
Copy source

Hello,

I ended up with the same situation. I performed the SQL statements manually one by one after seeing each individual error on the top of the Admin pages in my browser when I run "Tools->Maintainance->Repair and optimise database".

I'm running Piwigo on this environment:
Piwigo 2.9.0
Operating system: Linux
PHP: 7.0.15-0ubuntu0.16.04.4 (Show info) [2017-05-05 00:02:46]
MySQL: 5.7.18-0ubuntu0.16.04.1 [2017-05-05 00:02:46]
Graphics Library: GD 2.1.1

Atleast I'm not the only one .... :-)

Kind regards

Jens

Hello, I ended up with the same situation. I performed the SQL statements manually one by one after seeing each individual error on the top of the Admin pages in my browser when I run "Tools->Maintainance->Repair and optimise database". I'm running Piwigo on this environment: Piwigo 2.9.0 Operating system: Linux PHP: 7.0.15-0ubuntu0.16.04.4 (Show info) [2017-05-05 00:02:46] MySQL: 5.7.18-0ubuntu0.16.04.1 [2017-05-05 00:02:46] Graphics Library: GD 2.1.1 Atleast I'm not the only one .... :-) Kind regards Jens <!-- github-comment-id:299322006 -->
adam commented 2026-10-05 00:07:37 +02:00
Author
Owner
Copy link
Copy source

Hi, this also affects the update script "149-database.php" as it contains an incompatible ALTER statement - a dateime column cannot have the default NULL anymore ;-)

Hi, this also affects the update script ["149-database.php"](https://github.com/Piwigo/Piwigo/blob/master/install/db/149-database.php#L34) as it contains an incompatible ALTER statement - a dateime column cannot have the default NULL anymore ;-) <!-- github-comment-id:305809556 -->
adam commented 2026-10-05 00:07:38 +02:00
Author
Owner
Copy link
Copy source

@madmas are you really sure of that?

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.18-0ubuntu0.16.04.1 |
+-------------------------+

mysql> create table test_table (name varchar(10) not null, nulldate datetime default NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> desc test_table;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(10) | NO   |     | NULL    |       |
| nulldate | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test_table set name="hello", nulldate=now();
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_table set name="goodbye";
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_table;
+---------+---------------------+
| name    | nulldate            |
+---------+---------------------+
| hello   | 2017-06-02 18:02:12 |
| goodbye | NULL                |
+---------+---------------------+
2 rows in set (0.00 sec)
@madmas are you really sure of that? ``` mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 5.7.18-0ubuntu0.16.04.1 | +-------------------------+ mysql> create table test_table (name varchar(10) not null, nulldate datetime default NULL); Query OK, 0 rows affected (0.04 sec) mysql> desc test_table; +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | name | varchar(10) | NO | | NULL | | | nulldate | datetime | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into test_table set name="hello", nulldate=now(); Query OK, 1 row affected (0.00 sec) mysql> insert into test_table set name="goodbye"; Query OK, 1 row affected (0.00 sec) mysql> select * from test_table; +---------+---------------------+ | name | nulldate | +---------+---------------------+ | hello | 2017-06-02 18:02:12 | | goodbye | NULL | +---------+---------------------+ 2 rows in set (0.00 sec) ``` <!-- github-comment-id:305832478 -->
adam commented 2026-10-05 00:07:39 +02:00
Author
Owner
Copy link
Copy source

@plegall You're right, I might have had a incomplete understanding.
The issue is, when updating from ealier versions of MySQL where '0000-00-00' was a valid default for datetime, many databases run into trouble.
In fact, on updating Piwigo, I hat the issue taht the DB update script did not run through, the last_visit column was not added.
I found that this NULL default value caused the trouble but with you example that might not be the actual reason. I'll try to re-verify the cause.

@plegall You're right, I might have had a incomplete understanding. The issue is, when updating from ealier versions of MySQL where '0000-00-00' was a valid default for datetime, many databases run into trouble. In fact, on updating Piwigo, I hat the issue taht the DB update script did not run through, the last_visit column was not added. I found that this NULL default value caused the trouble but with you example that might not be the actual reason. I'll try to re-verify the cause. <!-- github-comment-id:307059945 -->
adam commented 2026-10-05 00:07:40 +02:00
Author
Owner
Copy link
Copy source

When attempting a metadata synchronization in version 2.9.1, I got an error that I'm pretty sure is related to this issue:

Warning:  [mysql error 1292] Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_creation' at row 1085

INSERT  INTO piwigo_images_1505742726092187
  (id,filesize,width,height,date_creation,latitude,longitude,date_metadata_update)
  VALUES('1','4664','3888','2592','2012-02-17 17:11:05',NULL,NULL,'2017-09-18 09:51:53')
  , ('2','4688','3888','2592','2012-02-17 17:11:05',NULL,NULL,'2017-09-18 09:51:53')
  , ('3','4683','3888','2592','2012-02-17 17:11:06',NULL,NULL,'2017-09-18 09:51:53')
  , ('4','4692','3888','2592','2012-02-17 17:11:59',NULL,NULL,'2017-09-18 09:51:53')
  , ('5','4760','3888','2592','2012-02-17 17:12:00',NULL,NULL,'2017-09-18 09:51:53')
  , ('6','4632','3888','2592','2012-02-17 17:12:00',NULL,NULL,'2017-09-18 09:51:53')
  , ('7','4755','3888','2592','2012-02-17 17:12:40',NULL,NULL,'2017-09-18 09:51:53')
  , ('8','4730','3888','2592','2012-02-17 17:12:41',NULL,NULL,'2017-09-18 09:51:53')
  , ('9','4592','3888','2592','2012-02-17 17:23:05',NULL,NULL,'2017-09-18 09:51:53')
  , ('10','4611','388 in /var/www/piwigo/include/dblayer/functions_mysqli.inc.php on line 845

My environment:

  • Piwigo 2.9.1
  • Operating system: Linux
  • PHP: 7.0.22-0ubuntu0.16.04.1 (Show info) [2017-09-18 09:57:48]
  • MySQL: 5.7.19-0ubuntu0.16.04.1 [2017-09-18 09:57:48]
  • Graphics Library: External ImageMagick 6.8.9-9
When attempting a metadata synchronization in version 2.9.1, I got an error that I'm pretty sure is related to this issue: ``` Warning: [mysql error 1292] Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_creation' at row 1085 INSERT INTO piwigo_images_1505742726092187 (id,filesize,width,height,date_creation,latitude,longitude,date_metadata_update) VALUES('1','4664','3888','2592','2012-02-17 17:11:05',NULL,NULL,'2017-09-18 09:51:53') , ('2','4688','3888','2592','2012-02-17 17:11:05',NULL,NULL,'2017-09-18 09:51:53') , ('3','4683','3888','2592','2012-02-17 17:11:06',NULL,NULL,'2017-09-18 09:51:53') , ('4','4692','3888','2592','2012-02-17 17:11:59',NULL,NULL,'2017-09-18 09:51:53') , ('5','4760','3888','2592','2012-02-17 17:12:00',NULL,NULL,'2017-09-18 09:51:53') , ('6','4632','3888','2592','2012-02-17 17:12:00',NULL,NULL,'2017-09-18 09:51:53') , ('7','4755','3888','2592','2012-02-17 17:12:40',NULL,NULL,'2017-09-18 09:51:53') , ('8','4730','3888','2592','2012-02-17 17:12:41',NULL,NULL,'2017-09-18 09:51:53') , ('9','4592','3888','2592','2012-02-17 17:23:05',NULL,NULL,'2017-09-18 09:51:53') , ('10','4611','388 in /var/www/piwigo/include/dblayer/functions_mysqli.inc.php on line 845 ``` My environment: * Piwigo 2.9.1 * Operating system: Linux * PHP: 7.0.22-0ubuntu0.16.04.1 (Show info) [2017-09-18 09:57:48] * MySQL: 5.7.19-0ubuntu0.16.04.1 [2017-09-18 09:57:48] * Graphics Library: External ImageMagick 6.8.9-9 <!-- github-comment-id:330230803 -->
adam commented 2026-10-05 00:07:41 +02:00
Author
Owner
Copy link
Copy source

Similar on fedora 29 :

Warning:  [mysql error 1292] Incorrect datetime value: '-0001-11-30 00:00:00' for column `piwigo`.`piwigo_images`.`date_creation` at row 1
UPDATE piwigo_images SET `filesize`='73168', `width`='1280', `height`='720', `date_creation`='-0001-11-30 00:00:00', `date_metadata_update`=CURDATE() WHERE `id`=12906; in /home/httpd/piwigo/include/dblayer/functions_mysqli.inc.php on line 845

For some reason the date_creation default value is NULL. Not sure where Piwigo is getting the crazy string from.

Would the SQL be better using CURDATE() for date_creation anyway ?

Similar on fedora 29 : ``` Warning: [mysql error 1292] Incorrect datetime value: '-0001-11-30 00:00:00' for column `piwigo`.`piwigo_images`.`date_creation` at row 1 UPDATE piwigo_images SET `filesize`='73168', `width`='1280', `height`='720', `date_creation`='-0001-11-30 00:00:00', `date_metadata_update`=CURDATE() WHERE `id`=12906; in /home/httpd/piwigo/include/dblayer/functions_mysqli.inc.php on line 845 ``` For some reason the date_creation default value is NULL. Not sure where Piwigo is getting the crazy string from. Would the SQL be better using CURDATE() for date_creation anyway ? <!-- github-comment-id:469423068 -->
adam commented 2026-10-05 00:07:41 +02:00
Author
Owner
Copy link
Copy source

I have similar problem loading my site. And is not that invalid creation date.

Warning: array_merge(): Argument #1 is not an array in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 392

Warning: Invalid argument supplied for foreach() in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 394

Notice: Undefined index: id in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 420

Notice: Undefined index: status in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 420

Warning: [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

SELECT cat_id
FROM renzogro_user_access
WHERE user_id =
; in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/dblayer/functions_mysqli.inc.php on line 845

Fatal error: Call to a member function fetch_assoc() on boolean in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/dblayer/functions_mysqli.inc.php on line 906

I have similar problem loading my site. And is not that invalid creation date. Warning: array_merge(): Argument #1 is not an array in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 392 Warning: Invalid argument supplied for foreach() in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 394 Notice: Undefined index: id in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 420 Notice: Undefined index: status in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/functions_user.inc.php on line 420 Warning: [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 SELECT cat_id FROM renzogro_user_access WHERE user_id = ; in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/dblayer/functions_mysqli.inc.php on line 845 Fatal error: Call to a member function fetch_assoc() on boolean in /var/www/vhosts/renzogrosso.com/public_html/galeria/include/dblayer/functions_mysqli.inc.php on line 906 <!-- github-comment-id:509845355 -->
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#617