Hello,
As I playing around with some MySQL settings I just did a "Repair and optimize database", but got this:
Optimizations have been completed with some errors.
Warning: [mysql error 1067] Invalid default value for 'date'
ALTER TABLE piwigo_comments ORDER BY id; in [path]/include/dblayer/functions_mysqli.inc.php on line 845
Any clue what to do next?
Any advise would be welcome ...
Thanks,
Matthijs
Environment
Piwigo 2.9.5
Operating system: Linux
PHP: 7.2.19-0ubuntu0.18.04.1 (Show info) [2019-07-08 16:14:38]
MySQL: 5.7.26-0ubuntu0.18.04.1-log [2019-07-08 16:14:38]
Graphics Library: External ImageMagick 6.9.7-4
Last edited by matthys (2019-07-08 16:20:19)
Offline
Pity ... no reaction yet ....
I just wonder if this has to do with the default value for date, which is "0000-00-00 00:00:00", and MySQL 5.7.
Offline
Check if MySQL has strict mode enabled, in which '0000-00-00' is not a valid date; without strict mode it should be accepted. See https://stackoverflow.com/questions/363 … r-datetime
Offline
Hmmm ... I just wonder if I should change the default value or removing the STRICT_TRANS_TABLES mode...
However this could cause problems in the future I think:
The default SQL mode in MySQL 5.7 includes these modes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.
I checked with my sever and is also the default:
mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
I think Piwigo should use a default which also would run on a 'strickt' configured database server, especially if more than one database is used. Just my thought ...
Somebody else advise me to use "1970-01-01 00:00:00" as default for example.
Offline