Go to main content

Textpattern CMS support forum

You are not logged in. Register | Login | Help

#1 2016-06-16 03:55:41

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,373

Textpattern and MySQL 5.7

I need to upgrade my local version of MySQL …

Please can someone confirm that this is the current status with TXP & MySQL 5.7:

  • TXP 4.5 has to be patched according to this
  • TXP 4.6 will work straight out of the box

Thanks.

Offline

#2 2016-06-16 08:40:51

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

Re: Textpattern and MySQL 5.7

gomedia wrote #299767:

TXP 4.5 has to be patched

Yes.

TXP 4.6 will work straight out of the box

Uhhh, probably. I think it might depend on the sql_mode setting. If it’s set to strict and/or has NO_ZERO_DATE then it may fall over.

I’m slightly nervous about this as I’m not convinced my experiences in this area are entirely confined to the fact I’m using a different DB. But I can’t prove it, because my local XAMPP now uses MariaDB by default, and I’ve chosen Percona on my server.

I’d far rather we find a default value that works for both MySQL and its derivatives, since the installed base of MySQL forks appears to be on the increase in industry12345. It’s only a matter of time before that demand filters down to the CMS world, so we might as well be ready for it.

At the moment, every time I install a fresh Txp I’m having to jump through the copious hoops I listed in my post above (more here). Upgrades need even more massaging to remove any rows that have ’0000-00-00’-style dates in the database already. I can deal with it, but it’s non-trivial and a potential usage barrier for non-server-admin folk, imo.

But unless we can come up with a value that satisfies both camps, I guess we just cross our fingers that most of the people running recent MySQL versions don’t have strict mode on.

Perhaps you could try it out, pretty please? If you can, run MySQL 5.7 in strict mode and/or set NO_ZERO_DATE on and see what happens a) on new 4.6.0 installs, and b) by copying an existing DB to that environment. I’d love to find out, because then we can make a decision right now if this is likely to be an issue or not. I suspect Ruud is right and it works fine, but there’s this niggling thing in the back of my mind that says, even if it works now, things might start to break in the very near future.

1 www.itwire.com/business-it-news/open-source/60292-red-hat-ditches-mysql-switches-to-mariadb

2 thevarguy.com/cloud-computing-services-and-business-solutions/072814/rackspace-adds-support-mariadb-and-percona-open-sour

3 www.infoworld.com/article/2614268/open-source-software/wikipedia-dumps-mysql—hooks-up-with-mariadb.html

4 www.zdnet.com/article/google-quietly-dumps-oracle-mysql-for-mariadb/

5 www.suse.com/releasenotes/x86_64/SUSE-SLES/12/#fate-316482


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

#3 2016-06-17 00:50:53

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,373

Re: Textpattern and MySQL 5.7

Hi Stef, thanks for the info.

Rather than upgrade my can’t-afford-to-break-it local development environment, I’m going to set up a completely new install on a different Mac.

I’ll report back.

Offline

#4 2016-06-17 13:41:34

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,373

Re: Textpattern and MySQL 5.7

This is what I’ve got installed:

  • Apache 2.4.18
  • PHP 5.6.21
  • MySQL 5.7.13

And, if it helps:

--------------
select @@sql_mode
--------------

+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+

I’ve tried three scenarios, none of which are pretty.

1) Install standard (i.e unpatched) TXP 4.5.7. During setup phase the following errors generated (as documented here):

1. Invalid default value for 'Posted'
2. Table 'txp457.textpattern' doesn't exist
3. Invalid default value for 'posted'
4. Table 'txp457.txp_discuss' doesn't exist
5. Invalid default value for 'date_banned'
6. Invalid default value for 'issue_time'
7. Invalid default value for 'date'
8. Invalid default value for 'date'
9. Table 'txp457.txp_link' doesn't exist
10. Table 'txp457.txp_link' doesn't exist
11. Table 'txp457.txp_link' doesn't exist
12. Table 'txp457.txp_link' doesn't exist
13. Table 'txp457.txp_link' doesn't exist
14. Table 'txp457.txp_link' doesn't exist
15. Invalid default value for 'time'
16. Invalid default value for 'last_access'

2) Start from scratch, patch TXP 4.5.7 according to this (edit files textpattern/lib/txplib_db.php & textpattern/setup/txpsql.php) and retry.

Setup completes successfully, however at first login to TXP admin:

Internal error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ignore table `textpattern` add index section_status_idx (Section,Status)' at line 1".

It’s mentioned here but can’t get past this point.

On the public side home page I see:

General error Textpattern Error: Unknown column 'load_order' in 'order clause' on line 98
Tag error: <txp:article limit="5" /> ->  Textpattern Error: Unknown column 'Expires' in 'where clause' while parsing form None on page default
Tag error: <txp:article limit="5" /> ->  Textpattern Error: Unknown column 'Expires' in 'field list' while parsing form None on page default

3) Install TXP 4.6.0beta. Setup completes without errors but after initial login (at the Languages screen):

User_Error "Truncated incorrect date value: '0000-00-00'"
in /Users/adi/Sites/textpattern-4.6.0-beta/textpattern/lib/txplib_db.php at line 405.
textpattern/lib/txplib_misc.php:1677 adminErrorHandler()
updateErrorHandler()

textpattern/lib/txplib_db.php:405 trigger_error()
textpattern/lib/txplib_db.php:433 safe_query()
textpattern/update/_to_4.6.0.php:253 safe_delete()
textpattern/update/_update.php:84 include()
textpattern/index.php:180 include()

There’re no apparent errors on the public side.

So … with MySQL 5.7 and brand new TXP installs, I’ve got an unusable TXP 4.5.7 and a TXP 4.6.0 with errors. Help!

Offline

#5 2016-06-17 17:33:31

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

Re: Textpattern and MySQL 5.7

Eek. Well scenarios 1 and 2 we can’t do much about. Once 4.6 is out, 4.5 is essentially unsupported. So we need to make sure that the new version works with as many plugins as possible. Including mlp. That gives fewer reasons for people not to upgrade.

As for the third issue it’s sadly what I feared. There must be a date left in the default content somewhere, or a bad value leftover in the code. Humph.

This is going to hit upgrades as well so we’re gonna need to do some research.

Firstly, if you try changing the lines in _to_4.6.0.php that I mentioned in the issue linked above, does install go smoothly? And if you have a dB you can copy over to simulate an upgrade, see if that works too?

I suspect 0000-00-00 dates in the dB (e.g. expiry column in textpattern table) will make it fall over. In that case, maybe we need to find a way to nullify them properly during the upgrade. A possible solution is in my “pesky zero dates” thread, but it didn’t behave consistently when I tried it :-(

Sorry to burden you with all this. But any info you can shed on this will help us put a fix together. Thanks.


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

#6 2016-06-17 19:32:53

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

Re: Textpattern and MySQL 5.7

gomedia wrote #299806:

3) Install TXP 4.6.0beta. Setup completes without errors but after initial login (at the Languages screen):

User_Error "Truncated incorrect date value: '0000-00-00'"
in /Users/adi/Sites/textpattern-4.6.0-beta/textpattern/lib/txplib_db.php at line 405.
textpattern/update/_to_4.6.0.php:253 safe_delete()

This won’t happen in the 4.6.0 final release, because on a fresh 4.6 install the upgrade scripts won’t run.
What might be problematic are installs where MySQL is upgraded to a higher version, before TXP is upgraded to 4.6.

Offline

#7 2016-06-18 05:39:17

colak
Admin
From: Cyprus
Registered: 2004-11-20
Posts: 9,007
Website GitHub Mastodon Twitter

Re: Textpattern and MySQL 5.7

ruud wrote #299819:

User_Error "Truncated incorrect date value: '0000-00-00'"...

What might be problematic are installs where MySQL is upgraded to a higher version, before TXP is upgraded to 4.6.

which is very possible for a lot of users.


Yiannis
——————————
NeMe | hblack.art | EMAP | A Sea change | Toolkit of Care
I do my best editing after I click on the submit button.

Offline

#8 2016-06-19 12:49:22

gomedia
Plugin Author
Registered: 2008-06-01
Posts: 1,373

Re: Textpattern and MySQL 5.7

Bloke wrote #299817:

Firstly, if you try changing the lines in _to_4.6.0.php that I mentioned in the issue linked above, does install go smoothly?

I’m a bit confused & I suspect that I might’ve missed out a crucial part of the story … step 3 was a completely brand new install. So would the upgrade scripts still come into play?

Sorry to burden you with all this. But any info you can shed on this will help us put a fix together. Thanks.

No problem. Happy to help. The TXP 4.6.0beta test is still sitting at the Language screen if there’s any info that can be gathered there.

Offline

#9 2016-06-19 15:27:53

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

Re: Textpattern and MySQL 5.7

gomedia wrote #299841:

I’m a bit confused & I suspect that I might’ve missed out a crucial part of the story … step 3 was a completely brand new install. So would the upgrade scripts still come into play?

I’m guessing the beta differs from the final 4.6 version there, in that it does run the _to_4.6.0 update script.

Offline

#10 2016-06-19 18:17:50

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

Re: Textpattern and MySQL 5.7

ruud wrote #299819:

User_Error "Truncated incorrect date value: '0000-00-00'"... This won’t happen in the 4.6.0 final release, because on a fresh 4.6 install the upgrade scripts won’t run.

True. Final releases only run on upgrade. The reason it shows up on upgrade here is because the txp_uses_svn flag is still set, which forces the update scripts to run — it’s development code. We can’t very well knock this flag false without incurring problems when beta testers move to final. Umm, can we?

What might be problematic are installs where MySQL is upgraded to a higher version, before TXP is upgraded to 4.6.

Yes, and it’s these I’d like to figure out if there’s anything we can do to mitigate, or at least minimise, disruption.


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

Board footer

Powered by FluxBB