Issues upgrading from 4.5.7 - Invalid default value for 'Posted'

gomedia · 2020-06-30 06:25:40

Something for the collective knowledge base …

Went to upgrade a local (Mac hosted) website from 4.5.7 to 4.7.3 and bang! A thousand errors.

Reverted back to 4.5.7, this time upgraded to 4.6.2 and one of the old chestnuts went pop:

User_Error "Invalid default value for 'Posted'"
in /Users/adi/Sites/vanilla/textpattern/lib/txplib_db.php at line 409.
textpattern/lib/txplib_misc.php:1676 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:409 trigger_error()
textpattern/lib/txplib_db.php:536 safe_query()
textpattern/update/_to_4.6.0.php:28 safe_alter()
textpattern/update/_update.php:90 include()
textpattern/index.php:180 include()

Tried Update checks for zeroed dates cause admin area to break – didn’t fix it.

Tried Zero dates fix attempt (which is designed to temporarily switch off MySQL STRICT mode) – didn’t fix it.

Head in hands time … but after hacking around I realised that the tweak in the above “Zero dates fix attempt” wasn’t actually changing the MySQL mode.

What I found is that when MySQL mode is set to TRADITIONAL – it has TRADITIONAL in the list:

SELECT @@GLOBAL.sql_mode;
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                                    |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Consequently, if you try to change the mode thus:

SET GLOBAL sql_mode = "STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Nothing actually changes!

You need to lose “TRADITIONAL” from the list.

Amended the first part of the above “Zero dates fix attempt” workaround to:

if ($sql_mode = getThing('SELECT @@SESSION.sql_mode')) {
	$tmp_mode = implode(',', array_diff(
		do_list_unique($sql_mode),
		array('NO_ZERO_IN_DATE', 'NO_ZERO_DATE’, ‘TRADITIONAL’)
		));
	safe_query("SET SESSION sql_mode = '".doSlash($tmp_mode)."'");
}

… and bingo! Upgrade to 4.6.2 completed successfully & for that matter a subsequent hop to 4.7.2 was error free also.

I would imagine going straight from 4.5.7 to 4.7.3 or beyond would be fine with the above tweak also.

Bloke · 2020-06-30 06:34:44

Ooh, that’s interesting. Thank you for your investigative powers and diligence to find a solution. Never seen TRADITIONAL in use before. Will patch core today as you suggest. Thank you.

gaekwad · 2020-06-30 08:21:21

This is the top quality content I come here for. Excellent!

Bloke · 2020-06-30 08:43:37

Fixed. Thank you so much.

gomedia · 2020-06-30 08:53:55

Bloke wrote #324115:

Fixed. Thank you so much.

No probs – if there’s a hole to fall in, I’ll find it. The least I can do is put up a sign!

etc · 2020-06-30 22:07:09

Nice fix, thanks Adi! I hope this crazy issue is definitely solved now.

whocarez · 2020-07-02 09:21:12

Bloke wrote #324115:

Fixed. Thank you so much.

Isn’t that the same problem described here with MariaDB?

etc · 2020-07-02 10:34:56

whocarez wrote #324188:

Isn’t that the same problem described here with MariaDB?

Maybe, but I’m not sure totally dumping sql_mode is safe.

Textpattern CMS

Textpattern CMS support forum

#1 2020-06-30 06:25:40