Announcement

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

tomass
Member
New Zealand
2015-06-16
21

[resolved] Errors after upgrade to 2.9

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

Offline

 

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

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13306

Re: [resolved] Errors after upgrade to 2.9

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

Offline

 

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

tomass
Member
New Zealand
2015-06-16
21

Re: [resolved] Errors after upgrade to 2.9

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)

Offline

 

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

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13306

Re: [resolved] Errors after upgrade to 2.9

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.

Offline

 

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

tomass
Member
New Zealand
2015-06-16
21

Re: [resolved] Errors after upgrade to 2.9

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.

Offline

 

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

tomass
Member
New Zealand
2015-06-16
21

Re: [resolved] Errors after upgrade to 2.9

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

Offline

 

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

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13306

Re: [resolved] Errors after upgrade to 2.9

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

Offline

 

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

tomass
Member
New Zealand
2015-06-16
21

Re: [resolved] Errors after upgrade to 2.9

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

Offline

 

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

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13306

Re: [resolved] Errors after upgrade to 2.9

tomass wrote:

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

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

Offline

 

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

adkisson
Member
2017-05-09
3

Re: [resolved] Errors after upgrade to 2.9

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

Offline

 

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

rbr28
Member
Bloomington, IN USA
2010-07-13
36

Re: [resolved] Errors after upgrade to 2.9

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)

Offline

 

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

adkisson
Member
2017-05-09
3

Re: [resolved] Errors after upgrade to 2.9

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.

Offline

 

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

zork
Member
uk
2003-02-20
24

Re: [resolved] Errors after upgrade to 2.9

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

Offline

 

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2020 · Contact