Announcement

#1 2016-02-12 21:09:37

Zentalquabula
Member
2014-05-10
217

2.8RC2 DB errors

Errors when upgrading and using 2.8RC2:

=== upgrade 146

Piwigo encountered a non recoverable error


[mysql error 1067] Invalid default value for 'date'

ALTER TABLE `pi23_history`
  ADD COLUMN `format_id` int(11) unsigned DEFAULT NULL
;

#1    my_error /include/dblayer/functions_mysqli.inc.php(134)
#2    pwg_query /install/db/146-database.php(35)
#3    include /upgrade_feed.php(103)

http://dev.mysql.com/doc/refman/5.7/en/datetime.html

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.


It does not support '0000-00-00'

Temporary solution: manually alter table's default value to '1000-00-00'

Stable solution: change it when upgrading


====

Piwigo encountered an irrecoverable error

[mysql error 3065] Expression #1 of ORDER BY clause is not in SELECT list, references column 'pi23_images.date_creation' which is not in SELECT list; this is incompatible with DISTINCT

SELECT DISTINCT(image_id)
  FROM pi23_image_category
    INNER JOIN pi23_images ON id = image_id
  WHERE
    category_id = 1

  ORDER BY date_creation ASC, file ASC, id ASC
;

#1    my_error /include/dblayer/functions_mysqli.inc.php(134)
#2    pwg_query /include/dblayer/functions_mysqli.inc.php(873)
#3    query2array /include/section_init.inc.php(318)
#4    include /index.php(27)

===

Same error 3065 appears in plugin AdditionalPages

Temporary solution: set sql-mode="" in /etc/my.cnf

Stable solution: fix code to adher to mysql standards

Offline

 

#2 2016-02-13 14:14:18

rvelices
Former Piwigo Team
2005-12-29
1960

Re: 2.8RC2 DB errors

did you upgrade mysql to 7 ? they changed the default checks I think.

Offline

 

#3 2016-02-13 14:33:31

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

Re: 2.8RC2 DB errors

rvelices wrote:

did you upgrade mysql to 7 ? they changed the default checks I think.

To 5.7 I suppose you mean. It looks a lot like [Github] Piwigo issue #305

Offline

 

#4 2016-02-13 17:42:43

Zentalquabula
Member
2014-05-10
217

Re: 2.8RC2 DB errors

I had sql_mode="TRADITIONAL,NO_AUTO_CREATE_USER" in /etc/my.cnf, something I put there a long time ago to avoid similar errors.

This time I had to mute it to sql_mode="".

Offline

 

#5 2016-02-15 19:49:21

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

Re: 2.8RC2 DB errors

OK, so I suppose we can fix it by removing all:

Code:

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 :-/

For the second problem, with DISTINCT and ORDER BY, it sounds a bit complicated to do anything "without being able to test" :-/

MySQL 5.7 is not even available on Debian/Ubuntu yet. If I read correctly https://wordpress.org/about/stats/ MySQL 5.7 is on less than 0.2% of installations of Wordpress (and I consider Piwigo is on the same type of servers).

Offline

 

#6 2016-02-15 22:13:38

Zentalquabula
Member
2014-05-10
217

Re: 2.8RC2 DB errors

Granted, we are relatively few that use PPAs of PHP and MySQL. But we do discover problems first, problems that will hit hard once new versions of Ubuntu/Debian and so on are released.

Last edited by Zentalquabula (2016-02-15 22:40:53)

Offline

 

#7 2016-02-16 12:05:49

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

Re: 2.8RC2 DB errors

Zentalquabula wrote:

Granted, we are relatively few that use PPAs of PHP and MySQL. But we do discover problems first, problems that will hit hard once new versions of Ubuntu/Debian and so on are released.

I didn't want to mean Piwigo won't try to be compatible with MySQL 5.7.4+, just that I have no environment to test and I don't want to commit changes that I can't test :-/

If you help us, with local changes and pull-request maybe, we will be able to fix that :-)

Offline

 

Board footer

Powered by FluxBB

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