Piwigo.org

tomass · 2017-05-03 12:34:09

Hi,
I had an error during upgrade from 2.8.6 to 2.9.0 but unfortunately didn't record the details. It was something about permissions, 2 lines of errors. However on second attempt it seemingly worked. I was locked on upgrade page anyway so didn't have much choice.

Now I can see on all album pages at the bottom:

Warning: [mysql error 1054] Unknown column 'last_visit' in 'field list'

UPDATE piwigo_user_infos
SET last_visit = NOW(),
lastmodified = lastmodified
WHERE user_id = 1
in /var/www/html/photos/include/dblayer/functions_mysqli.inc.php on line 845

If I go to
Tools > Maintenance > Repair and optimise database

Optimisations have been completed with some errors.

Warning: [mysql error 1067] Invalid default value for 'date'
ALTER TABLE piwigo_comments ORDER BY id; in /var/www/html/photos/include/dblayer/functions_mysqli.inc.php on line 845

I assume something went wrong with the database upgrade. I did overwrite all code with fresh 2.9 files (extracted from zip, overwrite with cp -r). It didn't make a difference. Disabling all plugins made no difference either (I have re-enabled them now).

The instance is available here: https://tomassobekphotography.co.nz/photos/
Would someone be able to help troubleshoot this, please.
Cheers, Tomas

plg · 2017-05-03 13:21:34

Hi tomass,

Obviously the database upgrade did not apply correctly.

What does this SQL query returns on your database?

Code:

select * from piwigo_upgrade order by applied desc limit 10;

and this one:

Code:

select * from piwigo_config where param like '%version%';

tomass · 2017-05-04 08:12:08

Here is the output:

Code:

mysql> select * from piwigo_upgrade order by applied desc limit 10;
+-----+---------------------+------------------------------------------------------------------------------------------------------------+
| id  | applied             | description                                                                                                |
+-----+---------------------+------------------------------------------------------------------------------------------------------------+
| 151 | 2017-05-03 09:53:23 | [migration from 2.8.0 to 2.9.0, 0.002 s] add "picture_sizes_icon" and "index_sizes_icon" parameters        |
| 152 | 2017-05-03 09:53:23 | [migration from 2.8.0 to 2.9.0, 0.004 s] add 5 parameters to show/hide icons (edit/caddie/repressentative) |
| 150 | 2017-05-03 09:53:23 | [migration from 2.8.0 to 2.9.0, 20.141 s] add history_id_from+history_id_to in history_summary table       |
| 149 | 2017-05-03 09:53:03 | [migration from 2.8.0 to 2.9.0, 0.001 s] add last_visit+last_visit_from_history in user_infos table        |
| 148 | 2016-03-09 02:14:07 | [migration from 2.7.0 to 2.8.0, 22.195 s] add auth_key_id in history table                                 |
| 147 | 2016-03-09 02:13:45 | [migration from 2.7.0 to 2.8.0, 0.004 s] add user authentication keys table                                |
| 146 | 2016-03-09 02:13:45 | [migration from 2.7.0 to 2.8.0, 21.993 s] add format_id in history table                                   |
| 145 | 2016-03-09 02:13:23 | [migration from 2.7.0 to 2.8.0, 0.006 s] add image formats table                                           |
| 101 | 2015-05-24 07:34:58 | upgrade included in installation                                                                           |
| 104 | 2015-05-24 07:34:58 | upgrade included in installation                                                                           |
+-----+---------------------+------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

Code:

mysql> select * from piwigo_config where param like '%version%';
+-------------------+-------+---------+
| param             | value | comment |
+-------------------+-------+---------+
| piwigo_db_version | 2.9   | NULL    |
+-------------------+-------+---------+
1 row in set (0.00 sec)

plg · 2017-05-04 10:46:28

What is your version of MySQL ?

Code:

select version();

Can you check the table piwigo_user_infos and tell us if you have the 2 columns "last_visit" and "last_visit_from_history"? I expect the answer to be "no".

If the answer is "no", then can you try to execute this SQL query:

Code:

ALTER TABLE `piwigo_user_infos`
  ADD COLUMN `last_visit` datetime default NULL,
  ADD COLUMN `last_visit_from_history` enum('true','false') NOT NULL default 'false'
;

I think this query won't execute correctly, and I need to know what's the error message.

tomass · 2017-05-04 11:00:12

Code:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+

Code:

mysql> describe piwigo_user_infos;
+-----------------------+------------------------------------------------------+------+-----+---------------------+-----------------------------+
| Field                 | Type                                                 | Null | Key | Default             | Extra                       |
+-----------------------+------------------------------------------------------+------+-----+---------------------+-----------------------------+
| user_id               | mediumint(8) unsigned                                | NO   | PRI | 0                   |                             |
| nb_image_page         | smallint(3) unsigned                                 | NO   |     | 15                  |                             |
| status                | enum('webmaster','admin','normal','generic','guest') | NO   |     | guest               |                             |
| language              | varchar(50)                                          | NO   |     | en_UK               |                             |
| expand                | enum('true','false')                                 | NO   |     | false               |                             |
| show_nb_comments      | enum('true','false')                                 | NO   |     | false               |                             |
| show_nb_hits          | enum('true','false')                                 | NO   |     | false               |                             |
| recent_period         | tinyint(3) unsigned                                  | NO   |     | 7                   |                             |
| theme                 | varchar(255)                                         | NO   |     | elegant             |                             |
| registration_date     | datetime                                             | NO   |     | 0000-00-00 00:00:00 |                             |
| enabled_high          | enum('true','false')                                 | NO   |     | true                |                             |
| level                 | tinyint(3) unsigned                                  | NO   |     | 0                   |                             |
| activation_key        | varchar(255)                                         | YES  |     | NULL                |                             |
| activation_key_expire | datetime                                             | YES  |     | NULL                |                             |
| lastmodified          | timestamp                                            | YES  | MUL | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+-----------------------+------------------------------------------------------+------+-----+---------------------+-----------------------------+

Code:

mysql> ALTER TABLE `piwigo_user_infos`
    ->   ADD COLUMN `last_visit` datetime default NULL,
    ->   ADD COLUMN `last_visit_from_history` enum('true','false') NOT NULL default 'false'
    -> ;
ERROR 1067 (42000): Invalid default value for 'registration_date'

Looks like it doesn't like the value 0000-00-00 00:00:00 as a default for column registration_date.

tomass · 2017-05-04 12:24:44

Hi Pierrick,
I will need to finish for tonight (NZ timezone) and will be away during the weekend. For now I have put back a pre-upgrade backup. I am able to either swap back to the upgraded broken version, or try the upgrade again from 2.8.6.

Thank you for your help and Piwigo in general, it is much appreciated.
Cheers, Tomas

plg · 2017-05-04 12:30:10

The situation is very unexpected (for me, at least). How can you be with MySQL 5.7 and have an invalid default datetime value?!? Here is a list of SQL queries to fix this situation first:

Code:

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';

Then you can run again:

Code:

ALTER TABLE `piwigo_user_infos`
  ADD COLUMN `last_visit` datetime default NULL,
  ADD COLUMN `last_visit_from_history` enum('true','false') NOT NULL default 'false'
;

tomass · 2017-05-04 12:46:36

Hi Pierrick,
I saw your response and was still able to do it straight away. It worked fine and the error message is not appearing on my photo gallery any more.

Should I be considering a clean re-install and migration of content, if such a thing is possible? (And is there a documentation how to do it? I haven't found one, but I didn't search very hard.)
Thanks again,
Tomas

plg · 2017-05-04 12:48:23

tomass wrote:
Should I be considering a clean re-install and migration of content

No, you should be fine with your current state :-)

adkisson · 2017-05-09 23:24:46

I saw the same errors as the original. The included fixes worked, except now I get the following when I try to repair and optimize the database:

Warning: [mysql error 1292] Incorrect datetime value: '0000-00-00 00:00:00' for column 'lastmodified' at row 1
ALTER TABLE piwigo_groups ORDER BY id; in /usr/share/piwigo/include/dblayer/functions_mysqli.inc.php on line 845

Will the following resolve this (based on the other ALTER TABLE suggestions in this thread)?

Code:

ALTER TABLE `piwigo_groups` CHANGE `date` `date` datetime NOT NULL default '1970-01-01 00:00:00';

rbr28 · 2017-05-10 02:06:33

Just wanted to add that I had the same problem and the fix below did solve it for me. Thanks for the quick response as usual.

I also get the same error when trying to repair/optimize the database though.

plg wrote:

The situation is very unexpected (for me, at least). How can you be with MySQL 5.7 and have an invalid default datetime value?!? Here is a list of SQL queries to fix this situation first:

Code:

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';

Then you can run again:

Code:

ALTER TABLE `piwigo_user_infos`
  ADD COLUMN `last_visit` datetime default NULL,
  ADD COLUMN `last_visit_from_history` enum('true','false') NOT NULL default 'false'
;

Last edited by rbr28 (2017-05-10 02:11:51)

adkisson · 2017-10-06 16:41:40

I'm still getting an error when trying to do repair/optimize database.

Warning: [mysql error 1292] Incorrect datetime value: '0000-00-00 00:00:00' for column 'lastmodified' at row 1
ALTER TABLE piwigo_groups ORDER BY id; in /usr/share/piwigo/include/dblayer/functions_mysqli.inc.php on line 845

I don't know enough mysql and or piwigo database structure to fix this. I've upgraded to 2.92 as of this morning, but no change to the error.

zork · 2018-08-27 00:06:32

Warning: [mysql error 1292] Incorrect date value: '-0001-11-30 00:00:00' for column 'date_creation' at row 1

Anyone got an idea how to stop this from displaying while running sync on videojs. its great that the pictures is created but this is just plain anoying and ive tried numorous option in mysql to rid this message but not having much luck

Piwigo.org

Announcement

#1 2017-05-03 12:34:09

[resolved] Errors after upgrade to 2.9

#2 2017-05-03 13:21:34

Re: [resolved] Errors after upgrade to 2.9

Code:

Code:

#3 2017-05-04 08:12:08

Re: [resolved] Errors after upgrade to 2.9

Code:

Code:

#4 2017-05-04 10:46:28

Re: [resolved] Errors after upgrade to 2.9

Code:

Code:

#5 2017-05-04 11:00:12

Re: [resolved] Errors after upgrade to 2.9

Code:

Code:

Code:

#6 2017-05-04 12:24:44

Re: [resolved] Errors after upgrade to 2.9

#7 2017-05-04 12:30:10

Re: [resolved] Errors after upgrade to 2.9

Code:

Code:

#8 2017-05-04 12:46:36

Re: [resolved] Errors after upgrade to 2.9

#9 2017-05-04 12:48:23

Re: [resolved] Errors after upgrade to 2.9

tomass wrote:

#10 2017-05-09 23:24:46

Re: [resolved] Errors after upgrade to 2.9

Code:

#11 2017-05-10 02:06:33

Re: [resolved] Errors after upgrade to 2.9

plg wrote:

Code:

Code:

#12 2017-10-06 16:41:40

Re: [resolved] Errors after upgrade to 2.9

#13 2018-08-27 00:06:32

Re: [resolved] Errors after upgrade to 2.9

Board footer