Update checks for zeroed dates cause admin area to break

[DarkerStar]

Just installed the most recent textpattern (so commit 87aff51) on an Ubuntu 16.04 server - PHP 7.0.6, MySQL 5.7.12 - and the admin area was unusable, stuck on the Admin -> Languages page (though the site itself seems to work).

The traceback I got was:

User_Error "Truncated incorrect date value: '0000-00-00'"
in /srv/www/textpattern/lib/txplib_db.php at line 405.

textpattern/lib/txplib_misc.php:1677 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:405 trigger_error()
textpattern/lib/txplib_db.php:433 safe_query()
textpattern/update/_to_4.6.0.php:254 safe_delete()
textpattern/update/_update.php:84 include()
textpattern/index.php:180 include()

The offending line in _to_4.6.0.php is:

safe_delete('txp_discuss_nonce', "DATE(issue_time) = '0000-00-00'");

And there are a bunch of other lines that check for "0000-00-00" dates, I presume to handle updates and fix issue #591.

When I comment out all 12 safe_delete and safe_update lines that check for "0000-00-00" dates, the admin area appears to work fine.

[Bloke]

I get this exact issue on my server, but I'm running Percona which has NO_ZERO_DATE set, it seems, internally by default and I can't find a way to change it.

Presumably, your MySQL setup has that setting applied somewhere in its config files, which is why you're seeing the issue.

In my case, I "fixed" it by modding _to_4.6.0.php as follows:

// Remove logs and nonces with zero dates.
safe_delete('txp_discuss_nonce', "DATE(issue_time) = 0");
safe_delete('txp_log',           "DATE(time)       = 0");
// Replace zero dates (which shouldn't exist, really) with somewhat sensible values.
safe_update('textpattern', "Posted      = NOW()",   "DATE(Posted)      = 0");
safe_update('textpattern', "Expires     = NULL",    "DATE(Expires)     = 0");
safe_update('textpattern', "LastMod     = Posted",  "DATE(LastMod)     = 0");
safe_update('txp_discuss', "posted      = NOW()",   "DATE(posted)      = 0");
safe_update('txp_file',    "created     = NOW()",   "DATE(created)     = 0");
safe_update('txp_file',    "modified    = created", "DATE(modified)    = 0");
safe_update('txp_image',   "date        = NOW()",   "DATE(date)        = 0");
safe_update('txp_link',    "date        = NOW()",   "DATE(date)        = 0");
safe_update('txp_users',   "last_access = NULL",    "DATE(last_access) = 0");
safe_update('textpattern', "feed_time   = DATE(Posted)", "feed_time    = 0");

I have no idea if that works under other MySQL clients that don't have that setting applied. If it does, then maybe it's a good enough patch to help both camps of people. Anyone care to test it and report back?

Oh, and a related topic: http://forum.textpattern.com/viewtopic.php?id=46973

[DarkerStar]

I don't think the NO_ZERO_DATE option exists any more. I think its effect is included when MySQL is in strict mode - which it is by default in recent Ubuntus at least. (The same might be true in Percona, which is you can't find the actual setting. You'd probably have to turn off strict mode altogether.)

At any rate, your suggested fix works for me, at least in the sense that the admin area is functional, without errors.

Whether it actually selects zero-valued dates in strict mode - or even whether it is possible to have legacy zero-valued dates in strict mode (for example, by having an installation where it was turned off in the past, had some zero-value dates inserted, then turned on) - I have no idea.

But at least this works in a fresh install on current MySQL versions with their default settings.

[wion]

I "fixed" it by modding _to_4.6.0.php

For me, running:

• mysql 5.7.13
• php 7.0.7

Your mods fixed the problem for the one site where I had the same error that @DarkerStar was getting, and no further errors appeared.

But in another local site just updated to 4.6 beta, I have a different error which the mods did NOT fix. Traceback is:

User_Error "Invalid default value for 'Posted'"
in /Users/me/Sites/mine/textpattern/lib/txplib_db.php at line 405.

textpattern/lib/txplib_misc.php:1677 adminErrorHandler()
updateErrorHandler()
textpattern/lib/txplib_db.php:405 trigger_error()
textpattern/lib/txplib_db.php:532 safe_query()
textpattern/update/_to_4.6.0.php:28 safe_alter()
textpattern/update/_update.php:84 include()
textpattern/index.php:180 include()

The default values for all date/time records in my textpattern table are 0000-00-00 00:00:00. Are those supposed to be showing as something else?

[wion]

It looks like the answer to my question is yes, the default values should be empty fields, according to the other site's textpattern table that your mods fixed.

However, Sequal Pro is not letting me edit the default values. Any suggestions?

[jwoldan]

@wion I ran into the same issue after upgrading to Ubuntu 16.04/MySQL 5.7.13, then upgrading Textpattern 4.5.7 to 4.6.0-beta2. I used the MySQL command line client to drop the default values as follows:

ALTER TABLE <table> ALTER COLUMN <column> DROP DEFAULT;

Some tables (including the textpattern table) required doing this for multiple columns together, for this you can use commas to include multiple ALTER COLUMN statements:

ALTER TABLE <table> ALTER COLUMN <column1> DROP DEFAULT, ALTER COLUMN <column2> DROP DEFAULT;

This results in the default value being NULL, which at least so far seems to work, though I can't say for sure whether this might cause other problems in the future. If the default value should be set to something else, let me know!

[jwoldan]

Oh, also had an issue with the textpattern.Expires column, which I ended up modifying to allow NULL values, then set that column to NULL in each row.

[wion]

@jwoldan, thanks. I got the database wrangled a while back, the hard way. :{

I just updated to beta2, though and get the truncation errors again inside the admin-side, so I think I need to do the file edit thing again that Bloke mentions above. Hopefully that's all I'm facing here.

I'm guessing this is the one bugaboo that is holding up the 4.6 release, but can't say for sure. I haven't dared begun to worry about the production server yet, which is style idling unhappy with 4.5.7.

[wion]

Confirming... editing the _to_4.6.0.php file restored life support.

[philwareham]

Is this still an issue, or has it been resolved now?

[Bloke]

Still an issue on some MySQLs or clones thereof. I have a patched file that I use for my Percona install which uses = 0 instead of = '0000-00-00 00:00:00' in the safe_update() calls of _to_4.6.0.php. Works for me, but I have yet to verify if it works on MySQL 5.7 and earlier. If anyone can prove it works, we can patch the update file.

[bloatware]

On MySQL 5.7, UNIX_TIMESTAMP is 0 for both NULL and 0000-00-00 00:00:00, fwiw.

But it is NULL on MariaDB, damn.

[petecooper]

Bumping this for clarity - @bloatware @Bloke: could we spin this out into a docs site article with the appropriate workaround code?

[morgant]

@Bloke @bloatware I was running into this NO_ZERO_DATE issue with MySQL 5.7.x and upgrading to Textpattern 4.7.1 as well. In my case, I happened to be upgrading from Txp 4.5.4, so I had to pull in @bloatware's commit in Txp 4.7.2 which temporarily disables NO_ZERO_DATE in textpattern/update/_to_4.6.0.php and apply the same fix to _to_4.5.7.php since that also modifies rows which might have zero dates.

[bloatware]

Thanks, done in 4.7.2.