Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
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
Offline
#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
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
Offline
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
Offline
Offline
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
Offline
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
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
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 toNULL
? 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
Offline
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
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