Invalid default value for 'WoStatusChanged'

[HugoFara]

A bug originally signaled by @jzohrab on Discord.

When installing the default demo database, there is a possibility f it failing with a message ERROR 1067 (42000): Invalid default value for 'WoStatusChanged'. This is appends when NO_ZERO_DATE mode is disabled in MySQL configuration.

Since this mode is deprecated according to Server SQL Modes#NO_ZERO_DATE, a default date of 1970-01-01 00:00:01 should be enough.

[HugoFara]

Finally a default date of 0000-00-00 00:00:01 may do the trick, since 0000-00-00 00:00:00 is the timestamp 1970-01-01 00:00:01.

[juliandoerner]

This error is still present when using docker compose. I don't really understand the bug-fix. Do you have a glue why this happens?

[jzohrab]

00000…1 isn’t a valid time stamp. Per https://dev.mysql.com/doc/refman/8.0/en/datetime.html:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Depending on your system, MySQL sometimes accepts invalid time stamps, but since docker is always the same system, it probably needs to be changed to the correct value.

[juliandoerner]

Undoing the changes from a325106 did the trick to me.

Here is the diff

diff --git a/inc/database_connect.php b/inc/database_connect.php
index f1ce5c1..132d2b0 100644
--- a/inc/database_connect.php
+++ b/inc/database_connect.php
@@ -1965,7 +1965,7 @@ function check_update_db($debug, $tbpref, $dbname): void
                 WoSentence varchar(1000) DEFAULT NULL, 
                 WoWordCount tinyint(3) unsigned NOT NULL DEFAULT 0, 
                 WoCreated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
-                WoStatusChanged timestamp NOT NULL DEFAULT '0000-00-00 00:00:01', 
+                WoStatusChanged timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', 
                 WoTodayScore double NOT NULL DEFAULT '0', 
                 WoTomorrowScore double NOT NULL DEFAULT '0', 
                 WoRandom double NOT NULL DEFAULT '0', 

This however might be not satisfying but I don't have any knowledge about mysql and php..

[HugoFara]

I'm investigating the issue. From what I have tested, a default date of 1970-01-01 12:00:00 works, while dates like 1970-01-01 00:00:00 doesn't. Could you confirm it is the same for you?

This issue is a bit confusing because the official doc does not say timestamps such as 1970-01-01 00:00:00 should work, but don't from what I've tested.

[juliandoerner]

According to the documentation as mentioned by jzohrab, 1970-01-01 00:00:00 is outside of the range.