Pesky zero dates 0000-00-00 00:00:00

Bloke · 2016-04-25 21:47:28

This is part how-to, part how-do-I…

I’ve switched over to using Percona as my database of choice, which in its 5.7.x form seems to have, by default, NO_ZERO_DATES set. Not that I can find anywhere in any configuration files where that’s actually set, but certainly trying to run a stock Txp 4.5.7 site using the <txp:article /> tag, I got errors in the log files about invalid null dates.

I thought maybe it was to do with innodb_strict_mode but flipping that switch on and off made no difference. I tried to set NO_ZERO_DATES to, ummm, false (?) and 0 and '' all to no avail. Each time I restarted mysql there was no change. So in the end I hacked Txp.

Since all that Percona is complaining about is the fact there’s no year 0000 and no month 00 and no day 00, I edited textpattern/lib/constants.php and altered the NULLDATETIME value as follows:

define('NULLDATETIME', '\'1970-01-01 00:00:00\'');

I don’t advocate this mod, but it seems to work and, given that 4.6 is just around the corner, I kind of feel it’s justified this once to get the site running again. If anybody has any better solutions, please shout.

GugUser · 2016-04-26 02:48:47

Bloke wrote #298825:

(…) and, given that 4.6 is just around the corner,…

Oh, surprise – how far away is this corner? ;-)

Bloke · 2016-04-26 08:03:35

GugUser wrote #298827:

Oh, surprise – how far away is this corner? ;-)

The beta was meant to be out a week or so ago, but a few last minute bugs have delayed things. Think we’re back on track now. The breather has given some kind members of the community time to get some more translation work done though, which is nice.

Bloke · 2016-04-26 08:14:27

I should qualify my OP with some more info. I had already converted my tables from MyISAM to InnoDB and gone through the conversion pain. This involved various permutations of SQL statements such as the following to remove references to default 0000-style dates, and to change any fields that had already used them:

ALTER TABLE textpattern
   ALTER COLUMN Posted DROP DEFAULT,
   ALTER COLUMN LastMod DROP DEFAULT,
   ... ;

and then:

UPDATE textpattern
   SET LastMod = '1970-01-01 00:00:00'
   WHERE LastMod = '0000-00-00 00:00:00';

For each table that potentially had zero dates.

For some reason, the latter query sometimes didn’t work and I needed to do it as a subselect:

UPDATE textpattern
   SET LastMod = '1970-01-01 00:00:00'
   WHERE ID IN (
      SELECT ID FROM textpattern
      WHERE LastMod = '0000-00-00 00:00:00'
   );

*shrug* SQL, you strange rabbit.

etc · 2016-04-26 11:13:20

Brilliantly simple! There is yet some room between 4.5.7 and 4.6.. 4.5.8? The issue is becoming rather common.

ruud · 2016-04-26 17:14:07

Perhaps good to point out that unless you should NOT do this unless your choice of database forces you to, because you’ll have to manually run some update queries after upgrading to 4.6.

etc · 2016-04-26 17:18:27

ruud wrote #298834:

you’ll have to manually run some update queries after upgrading to 4.6.

Can’t update script do it? Isn’t it converting 0000-00-00 etc into NULL anyway?

ruud · 2016-04-27 08:46:05

No, the update scripts are meant for standard installs, not for installs where the user made all kinds of unexpected changes to the core code and database content.

But those that perform such changes are also qualified (one would hope) to manually run update queries once they’ve updated to 4.6. The only update queries they’ll have to run manually are those that convert the alternative zero dates to actual NULL values.

Destry · 2016-06-21 06:58:53

Sorry, I’m not so “qualified”, but does this mean if I see 0000-00-00 00:00:00 in the textpattern table as the default values for:

Posted
Expires
LastMod
feed_time

I should change those values to NULL? Or is this conversation only for people converting to Percona?

Bloke · 2016-06-21 09:13:10

Destry wrote #299886:

if I see 0000-00-00 00:00:00 in the textpattern table as the default values, I should change those values to NULL? Or is this conversation only for people converting to Percona?

It’s not solely for Percona, it just happens that Percona removed NO_ZERO_DATE or set strict mode by default (I’m not sure which) and it appears difficult / impossible to change.

As far as I can make out, MySQL 5.7.x does something similar, although it can be toggled the other way in my.ini.

Either way, it’s only a problem — at the moment — if you run an upgrade and all hell breaks loose in date land¹. Then you have to either toggle the relevant my.ini settings (if you can) or jump through the hoops to:

a) DROP DEFAULT all columns that have a default value containing some semblance of 0000-00-00.
b) edit any existing rows in your database that have 0000-00-00-style dates already stored. This usually entails setting them to 1970-01-01 00:00:00 instead.

See above post for details.

¹ A fix would be lovely to cater for either system so that hell remains locked away.

Destry · 2016-06-21 09:27:34

Thanks for the clarification.

Bloke wrote #299895:

See above post for details.

I guess those are just sql statements to run?

it can be toggled the other way in my.ini.

If I happen to find the my.ini file, what kind of “toggle” am I expecting to see? You mean some values need changed?

all hell breaks loose in date land^1^.

hehe

¹ A fix… so hell remains locked away.

Destry · 2016-06-21 10:07:53

Just learned that my.ini is the Windows version of my.cnf. No wonder I couldn’t find it. ;)

Checking the usual places:

mysql --help | grep "Default options" -A 1

I currently have no my.cnf (deleted an old offender yesterday) but I can create a new one and put something in it, if I knew what to add for this “toggle” action.

Textpattern CMS

Textpattern CMS support forum

#1 2016-04-25 21:47:28