adam/Piwigo
1
0 Fork 0
mirror of https://github.com/Piwigo/Piwigo.git synced 2026-11-03 07:22:22 +01:00
Code Issues 657 Packages Projects Releases Wiki Activity

upgrade mysql to 5.7 database errors #707

New Issue
Open
opened 2026-10-05 00:12:54 +02:00 by adam · 0 comments
adam commented 2026-10-05 00:12:54 +02:00
Owner
Copy link
Copy source

upgraded from an older version of piwigo after upgrading my system. had to fix the piwigo database to finish the upgrade.

Piwigo 2.9.1
Operating system: Linux
PHP: 7.0.22-0ubuntu0.16.04.1 (Show info) [2017-09-16 10:08:43]
MySQL: 5.7.19-0ubuntu0.16.04.1 [2017-09-16 10:08:43]

i can see the create database script contains the updated default date syntax but upgrade scripts do not address columns with the null date issue. basic approach was to write queries to identify tables and columns that are having this migration issue. then apply those results.

create alter for datetime and date columns where the column default is like 0000-00-00 00:00:00 or 0000-00-00

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ALTER COLUMN ', COLUMN_NAME, ' SET DEFAULT ''1970-01-01 00:00:00'';') AS datetime_alter  FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'datetime' and COLUMN_DEFAULT='0000-00-00 00:00:00' and TABLE_NAME like 'piwigo_%';

here are the alter statements produced. two separate ones for datetime and date.

ALTER TABLE piwigo_comments ALTER COLUMN date SET DEFAULT '1970-01-01 00:00:00';
ALTER TABLE piwigo_images ALTER COLUMN date_available SET DEFAULT '1970-01-01 00:00:00';
ALTER TABLE piwigo_old_permalinks ALTER COLUMN date_deleted SET DEFAULT '1970-01-01 00:00:00';
ALTER TABLE piwigo_sessions ALTER COLUMN expiration SET DEFAULT '1970-01-01 00:00:00';
ALTER TABLE piwigo_upgrade ALTER COLUMN applied SET DEFAULT '1970-01-01 00:00:00';
ALTER TABLE piwigo_user_infos ALTER COLUMN registration_date SET DEFAULT '1970-01-01 00:00:00';

SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ALTER COLUMN ', COLUMN_NAME, ' SET DEFAULT ''1970-01-01'';') AS date_alter  FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'date' and COLUMN_DEFAULT='0000-00-00' and TABLE_NAME like 'piwigo_%';

ALTER TABLE piwigo_history ALTER COLUMN date SET DEFAULT '1970-01-01';
ALTER TABLE piwigo_rate ALTER COLUMN date SET DEFAULT '1970-01-01';

getting this error during a maintenance pass after fixing these tables. tried it out in console with same result.

mysql> ALTER TABLE piwigo_images ORDER BY id;
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_creation' at row 1056

for piwigo tables where there are values like 0000-00-00 00:00:00 update to null. so if column is nullable and date/datetime then value cannot be 0000-00-00 00:00:00

select date_creation from piwigo_images where date_creation = '0000-00-00 00:00:00';

update piwigo_images set date_creation = NULL where CAST(date_creation AS CHAR(20)) = '0000-00-00 00:00:00';

trying to identify other piwigo tables that have these zero date default values. didn't find anything to fix here.

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT  FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('datetime', 'date') and IS_NULLABLE = 'yes' and TABLE_NAME like 'piwigo_%

SELECT CONCAT('SELECT ', COLUMN_NAME, ' FROM ', TABLE_NAME, ' WHERE ', COLUMN_NAME, ' IN (''0000-00-00 00:00:00'',''0000-00-00'');') AS stuff  FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('datetime', 'date') and IS_NULLABLE = 'yes' and TABLE_NAME like 'piwigo_%';`

and the result selects.

SELECT validation_date FROM piwigo_comments WHERE validation_date IN ('0000-00-00 00:00:00','0000-00-00'); 
SELECT date_creation FROM piwigo_images WHERE date_creation IN ('0000-00-00 00:00:00','0000-00-00');  
SELECT date_metadata_update FROM piwigo_images WHERE date_metadata_update IN ('0000-00-00 00:00:00','0000-00-00');
SELECT last_hit FROM piwigo_old_permalinks WHERE last_hit IN ('0000-00-00 00:00:00','0000-00-00');
SELECT last_seen FROM piwigo_search WHERE last_seen IN ('0000-00-00 00:00:00','0000-00-00');                               
SELECT last_photo_date FROM piwigo_user_cache WHERE last_photo_date IN ('0000-00-00 00:00:00','0000-00-00');
SELECT date_last FROM piwigo_user_cache_categories WHERE date_last IN ('0000-00-00 00:00:00','0000-00-00');
SELECT max_date_last FROM piwigo_user_cache_categories WHERE max_date_last IN ('0000-00-00 00:00:00','0000-00-00');
SELECT last_check FROM piwigo_user_feed WHERE last_check IN ('0000-00-00 00:00:00','0000-00-00');
SELECT activation_key_expire FROM piwigo_user_infos WHERE activation_key_expire IN ('0000-00-00 00:00:00','0000-00-00');
SELECT last_visit FROM piwigo_user_infos WHERE last_visit IN ('0000-00-00 00:00:00','0000-00-00');
SELECT last_send FROM piwigo_user_mail_notification WHERE last_send IN ('0000-00-00 00:00:00','0000-00-00');
upgraded from an older version of piwigo after upgrading my system. had to fix the piwigo database to finish the upgrade. Piwigo 2.9.1 Operating system: Linux PHP: 7.0.22-0ubuntu0.16.04.1 (Show info) [2017-09-16 10:08:43] MySQL: 5.7.19-0ubuntu0.16.04.1 [2017-09-16 10:08:43] i can see the create database script contains the updated default date syntax but upgrade scripts do not address columns with the null date issue. basic approach was to write queries to identify tables and columns that are having this migration issue. then apply those results. create alter for datetime and date columns where the column default is like 0000-00-00 00:00:00 or 0000-00-00 ``` SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ALTER COLUMN ', COLUMN_NAME, ' SET DEFAULT ''1970-01-01 00:00:00'';') AS datetime_alter FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'datetime' and COLUMN_DEFAULT='0000-00-00 00:00:00' and TABLE_NAME like 'piwigo_%'; ``` here are the alter statements produced. two separate ones for datetime and date. ``` ALTER TABLE piwigo_comments ALTER COLUMN date SET DEFAULT '1970-01-01 00:00:00'; ALTER TABLE piwigo_images ALTER COLUMN date_available SET DEFAULT '1970-01-01 00:00:00'; ALTER TABLE piwigo_old_permalinks ALTER COLUMN date_deleted SET DEFAULT '1970-01-01 00:00:00'; ALTER TABLE piwigo_sessions ALTER COLUMN expiration SET DEFAULT '1970-01-01 00:00:00'; ALTER TABLE piwigo_upgrade ALTER COLUMN applied SET DEFAULT '1970-01-01 00:00:00'; ALTER TABLE piwigo_user_infos ALTER COLUMN registration_date SET DEFAULT '1970-01-01 00:00:00'; SELECT CONCAT('ALTER TABLE ', TABLE_NAME, ' ALTER COLUMN ', COLUMN_NAME, ' SET DEFAULT ''1970-01-01'';') AS date_alter FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'date' and COLUMN_DEFAULT='0000-00-00' and TABLE_NAME like 'piwigo_%'; ALTER TABLE piwigo_history ALTER COLUMN date SET DEFAULT '1970-01-01'; ALTER TABLE piwigo_rate ALTER COLUMN date SET DEFAULT '1970-01-01'; ``` getting this error during a maintenance pass after fixing these tables. tried it out in console with same result. ``` mysql> ALTER TABLE piwigo_images ORDER BY id; ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_creation' at row 1056 ``` for piwigo tables where there are values like 0000-00-00 00:00:00 update to null. so if column is nullable and date/datetime then value cannot be 0000-00-00 00:00:00 ``` select date_creation from piwigo_images where date_creation = '0000-00-00 00:00:00'; update piwigo_images set date_creation = NULL where CAST(date_creation AS CHAR(20)) = '0000-00-00 00:00:00'; ``` trying to identify other piwigo tables that have these zero date default values. didn't find anything to fix here. ``` SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('datetime', 'date') and IS_NULLABLE = 'yes' and TABLE_NAME like 'piwigo_% SELECT CONCAT('SELECT ', COLUMN_NAME, ' FROM ', TABLE_NAME, ' WHERE ', COLUMN_NAME, ' IN (''0000-00-00 00:00:00'',''0000-00-00'');') AS stuff FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('datetime', 'date') and IS_NULLABLE = 'yes' and TABLE_NAME like 'piwigo_%';` ``` and the result selects. ``` SELECT validation_date FROM piwigo_comments WHERE validation_date IN ('0000-00-00 00:00:00','0000-00-00'); SELECT date_creation FROM piwigo_images WHERE date_creation IN ('0000-00-00 00:00:00','0000-00-00'); SELECT date_metadata_update FROM piwigo_images WHERE date_metadata_update IN ('0000-00-00 00:00:00','0000-00-00'); SELECT last_hit FROM piwigo_old_permalinks WHERE last_hit IN ('0000-00-00 00:00:00','0000-00-00'); SELECT last_seen FROM piwigo_search WHERE last_seen IN ('0000-00-00 00:00:00','0000-00-00'); SELECT last_photo_date FROM piwigo_user_cache WHERE last_photo_date IN ('0000-00-00 00:00:00','0000-00-00'); SELECT date_last FROM piwigo_user_cache_categories WHERE date_last IN ('0000-00-00 00:00:00','0000-00-00'); SELECT max_date_last FROM piwigo_user_cache_categories WHERE max_date_last IN ('0000-00-00 00:00:00','0000-00-00'); SELECT last_check FROM piwigo_user_feed WHERE last_check IN ('0000-00-00 00:00:00','0000-00-00'); SELECT activation_key_expire FROM piwigo_user_infos WHERE activation_key_expire IN ('0000-00-00 00:00:00','0000-00-00'); SELECT last_visit FROM piwigo_user_infos WHERE last_visit IN ('0000-00-00 00:00:00','0000-00-00'); SELECT last_send FROM piwigo_user_mail_notification WHERE last_send IN ('0000-00-00 00:00:00','0000-00-00'); ``` <!-- github-sync-id:258246668 -->
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#707