Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

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

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

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.


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

Online

#2 2016-04-26 02:48:47

GugUser
Member
From: Quito (Ecuador)
Registered: 2007-12-16
Posts: 1,473

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

Bloke wrote #298825:

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

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

Offline

#3 2016-04-26 08:03:35

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

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

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.


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

Online

#4 2016-04-26 08:14:27

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

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

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.


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

Online

#5 2016-04-26 11:13:20

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

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

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

Offline

#6 2016-04-26 17:14:07

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

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.

Offline

#7 2016-04-26 17:18:27

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

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

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?

Offline

#8 2016-04-27 08:46:05

ruud
Developer Emeritus
From: a galaxy far far away
Registered: 2006-06-04
Posts: 5,068
Website

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

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.

Offline

#9 2016-06-21 06:58:53

Destry
Member
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,909
Website

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

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?

Offline

#10 2016-06-21 09:13:10

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

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

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 land1. 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.

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


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

Online

#11 2016-06-21 09:27:34

Destry
Member
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,909
Website

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

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

1 A fix… so hell remains locked away.

Offline

#12 2016-06-21 10:07:53

Destry
Member
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,909
Website

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

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.

Offline

Board footer

Powered by FluxBB