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

Bloke · 2016-06-28 22:36:36

planeth wrote #300019:

I decided to set the sql_mode.

Simplest option all round under the circumstances. Thanks for the clarification that it worked. If anybody has this option in their hosting environment, take it.

Destry · 2016-06-29 19:15:05

I guess I don’t have that option using Homebrew’ s mysql. It installs mysql at /usr/local/Cellar/mysql/

Within that I have one item only, a directory: 5.7.13

Within that are a number of other directories and files, but nothing resembling what Planeth mentioned:

COPYING				homebrew.mxcl.mysql.plist	scripts
INSTALL_RECEIPT.json		include				share
README				lib				support-files

I’ve dove into each directory (scripts, include, lib, etc) and still don’t find anything similarly named as mysql.conf.d/mysqld.cnf or my.cnf.

I’m not worried about it since I have things working now the hard way, but I just thought I’d mention it for anyone also using Homebrew’s version on Mac. But then maybe you know better than me what else I should be looking for.

Destry · 2016-09-17 10:13:10

Updated all Txp sites to stable 4.6. :)

I had to modify the date values in the _to_4.6.0.php file, as described here, every single time.

Is that because of new mysql configurations? This stuff? Am I going to have to manually mod that Txp file now on Txp updates forevermore unless I mod the appropriate database file instead?

I just want to do what needs done to solve this once and for all… Like it used to be. :)

Bloke · 2016-09-17 21:37:28

Destry wrote #301580:

Am I going to have to manually mod that Txp file now on Txp updates forevermore unless I mod the appropriate database file instead?

Sadly yes, unless we come up with a fix that works under all versions of MySQL 5 and (therefore by association) MariaDB/Percona/etc. It’s on my priority list for 4.6.1.

Bloke · 2016-09-18 09:10:46

Bloke wrote #301594:

[fixing 4.6.0 date issue is] on my priority list for 4.6.1.

Okay, I just modded _to_4.6.0.php as depicted here and upgraded from 4.5.7 to the latest version. Under MySQL 5.5.42 the upgrade went smoothly. My articles that had 0000-00-00 00:00:00 expiry dates were correctly altered to NULL.

So, under this cursory test at least, it seems the solution works for MySQL. And, as far as I know, it works under MariaDB and Percona. The acid test is if it works for other versions of MySQL, both earlier and later. I don’t have any installations that run any other version of MySQL, but if anyone does, it would be really helpful for field reports of upgrades. Note that it won’t be a successful test for installations from scratch, it must be an upgrade. The upgrade scripts don’t run on brand new installations any more.

To make this as simple as possible for people to test I’ve uploaded a modified copy of _to_4.6.0.php to my server for download. It also incorporates one fix for Txp 4.6.1, btw, so you won’t get errors about missing files during the upgrade. Diagnostics will therefore report this file is ‘wrong’, but don’t worry about it.

Here are the steps to test this:

Install or make available a fully-functioning 4.5.7. If it’s already populated with articles, all the better.
Ensure, at minimum, you have some articles in this installation with no Expiry date set, or users you’ve created that have never logged in. You can verify this by looking at your tables in phpMyAdmin. You should see the relevant columns having values 0000-00-00 00:00:00.
Log out of Txp.
Copy the files from a stock 4.6.0 download over your 4.5.7.
Download my patched _to_4.6.0.php, remove the .txt extension and overwrite your textpattern/update/_to_4.6.0.php file with it.
Log into Txp again to allow the upgrade script to run (you may see the ‘unsupported theme’ message if you previously had Classic as your theme, but just refresh to get the Hive login screen up).
Report any successes or upgrade errors here, including your version of MySQL/MariaDB/Percona and PHP tested.
Check your tables in phpMyAdmin. Any columns that previously had 0000-00-00 00:00:00 or 0000-00-00 should now be NULL or some other sensible value. If not, please report.

Thank you in advance for any feedback.

testdeputy · 2016-09-20 11:15:06

Here’s my report/feedback:
1. upgraded working 4.5.7 version with Hive theme
2. PHP version: 5.5.35
3. MySQL: 10.0.20-MariaDB
4. patched version: _to_4.6.0.php

a. no errors
b. txp_users column last_access + textpattern column Expires set to NULL

Bloke · 2016-09-20 11:27:58

testdeputy wrote #301633:

Here’s my report/feedback no errors

Brilliant, thanks for confirming. I was half expecting MariaDB to throw another spanner in the works, so it’s good to know it behaves itself.

Just need some more MySQL field reports now before I’m happy this is a permanent fix…

Destry · 2016-09-20 13:07:34

I’m already past the point of being able to help you with a test per your instructions, Bloke, but my DB is mysql 5.7.13 — applicable to my adventurous history in this thread. ;)

In any case, I don’t mind editing the my.cnf file. Up until now I coudn’t find it, was the problem in my Homebrew install on Mac. It turns out I didn’t have one installed and working anywhere, so MySQL was just running on defaults, I guess.

But I just learned homebrew keeps a derelict .cnf file under the version install location — e.g. ../Cellar/mysql/5.7.13/support-files/my-default.cnf — which can be copied into a working location:

cp $(brew --prefix mysql)/support-files/my-default.cnf /usr/local/etc/my.cnf

Comparing the existing line planeth indicated…

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

with the same line already in the template file…

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Gives a difference of…

ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER

I’m guessing it’s the ERROR_FOR_DIVISION_BY_ZERO that is key here.

I don’t know if that will handle not having to mod the _to_4.6.0.php file now, but I think there’s one more Txp 4.5.7 sitting around locally somewhere I can check with, maybe.

Destry · 2016-09-20 13:22:51

Just a thought… Would it be possible to show where a database config file was located in the simple diagnostics readout? Maybe that’s too difficult, considering one could be using different brands of database server. But I’d imagine the readout would give the path of the working file, or just say “none found” if one was missing. Something like that.

Destry · 2016-09-20 15:51:55

Nope. Didn’t have another 4.5.7 install to try anything on. Sorry.

Also, not that it has any relevance to you, nothing I said above about the my.cnf file in Homebrew worked. I had all kinds of errors regarding PID file paths, and Crom knows what else — nothing I could figure. I deleted the my.cnf I created to get back to normalcy. Seems okay now in that respect.

So, looks like I’m reliably updating the _to_4.6.0.php files, at the very least.

Sorry I couldn’t be more help.

sacripant · 2017-02-16 14:10:39

Hello,

I’ve changed my computer. I migrate form a old white macbook to a Xubuntu 16.04 laptop.
On mac I used a old MAMP with PHP 5.4 an Mysql 5.5.42
Now, In Xubuntu, I installed PHP 5.6 and mysql 5.7.*

When I migrated some localhost Textpattern Website and try a upgrade to Txp 4.6.2 or 4.5.7, I encountered some problems with 0000-00-00 dates on Mysql.

My feedback :

Since Mysql 5.5.x if you upgrade a Txp website from TXP 4.5.* to Txp 4.6.2, upgrade works. After that, if you move your website to a Mysql 5.7 server. No problem. All work fine.
Since Mysql 5.7.x, if you import a Txp 4.5.* website and you try to upgrade to Txp 4.6.2. Ugrade fails. It’s necessary to patch manually your database.
Since Mysql 5.7.x, if you import a old Txp smaller than 4.5.7 and you want udpate to 4.5.7, Upgrade fails. It’s necessary to patch manually your database.

Conclusion: it’s necessary to make all updates <= txp 4.6.2 with mysql 5.5.*. If it’s not possible for you, you need to patch manually your database before make a update.

someone can confirm my feedback ?

If my feedback is true. It would maybe interesting to have on the textpattern github repository an official .sql patch file for Txp 4.5.* BDD and MySQL 5.7.* ?

Bloke · 2017-02-16 14:44:46

sacripant wrote #304054:

It would maybe interesting to have on the textpattern github repository an official .sql patch file for Txp 4.5.* BDD and MySQL 5.7.* ?

That’s probably a good idea, actually. I had to go through the pain the other day again (because I was upgrading from Txp 4.5.7 on MySQL 5.4 to Txp 4.6.2 using a Percona DB). What is bizarre though is that I found this in my testing:

You sometimes can’t ALTER parts of a table, e.g. you can’t just ALTER textpattern.expires because it grumbles that you haven’t changed feed_time as well. You need to alter all date/datetime instances in a table at once.
When updating existing rows of data, sometimes you can’t do it using a subselect.

Both of the above make writing an official resource guide tricky, but we could certainly put some ‘try these’ steps together in a document and put it somewhere. Where’s best to store it do you think? Troubleshooting docs?

Textpattern CMS

Textpattern CMS support forum

#25 2016-06-28 22:36:36

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

planeth wrote #300019:

#26 2016-06-29 19:15:05

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

#27 2016-09-17 10:13:10

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

#28 2016-09-17 21:37:28

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

Destry wrote #301580:

#29 2016-09-18 09:10:46

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

Bloke wrote #301594:

#30 2016-09-20 11:15:06

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

#31 2016-09-20 11:27:58

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

testdeputy wrote #301633:

#32 2016-09-20 13:07:34

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

#33 2016-09-20 13:22:51

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

#34 2016-09-20 15:51:55

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

#35 2017-02-16 14:10:39

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

#36 2017-02-16 14:44:46

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

sacripant wrote #304054:

Board footer