Textpattern CMS support forum

You are not logged in. Register | Login | Help

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,620
Website

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

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…


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

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

Destry
Moderator
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,172
Website

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

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.


The text persuades, the *notes prove。

Offline

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

Destry
Moderator
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,172
Website

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

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.


The text persuades, the *notes prove。

Offline

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

Destry
Moderator
From: Haut-Rhin
Registered: 2004-08-04
Posts: 4,172
Website

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

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.


The text persuades, the *notes prove。

Offline

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

sacripant
Plugin Author
From: Rhône — France
Registered: 2008-06-01
Posts: 478
Website

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

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.* ?

Offline

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

Bloke
Developer
From: Leeds, UK
Registered: 2006-01-29
Posts: 8,620
Website

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

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?


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

#37 2017-02-17 09:44:55

sacripant
Plugin Author
From: Rhône — France
Registered: 2008-06-01
Posts: 478
Website

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

I also suffered to succeed in upgrading some Txp 4.5.* with mySQL 5.7.*
I tryed all it’s explain in this thread, also modify mysql ‘my.conf’ file as explained by planeth.

Sorry, I can’t help you to find a clean patch.
But now, I know that is not easy to upgrade a old Txp Website with MySQL 5.7.*. And it will surely happen to other Txp users.

A official help text in Troubleshooting docs is a good starting point, waiting for you to find a patch.

Offline

Board footer

Powered by FluxBB