Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,373

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

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.

Offline

#2 2020-06-30 06:34:44

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,456
Website GitHub

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

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.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#3 2020-06-30 08:21:21

gaekwad
Server grease monkey
From: People's Republic of Cornwall
Registered: 2005-11-19
Posts: 4,271
GitHub

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

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

Offline

#4 2020-06-30 08:43:37

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 11,456
Website GitHub

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

Fixed. Thank you so much.


The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.

Txp Builders – finely-crafted code, design and Txp

Offline

#5 2020-06-30 08:53:55

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,373

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

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!

Offline

#6 2020-06-30 22:07:09

etc
Developer
Registered: 2010-11-11
Posts: 5,212
Website GitHub

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

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

Offline

#7 2020-07-02 09:21:12

whocarez
Plugin Author
From: Germany/Ukraine
Registered: 2007-10-08
Posts: 305
Website GitHub Twitter

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

Bloke wrote #324115:

Fixed. Thank you so much.

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

Offline

#8 2020-07-02 10:34:56

etc
Developer
Registered: 2010-11-11
Posts: 5,212
Website GitHub

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

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.

Offline

Board footer

Powered by FluxBB