Textpattern CMS support forum

You are not logged in. Register | Login | Help

#21 2016-06-21 11:45:38

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

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

Destry wrote #299907:

Tested… Incorrect datetime value

Nuts. Was a long shot.

What if you try the second format, with the subquery:

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'
   );

And repeat the command for the other column, replacing ‘LastMod’ for ‘Expiry’ in all places.

Failing that, you can do it in two steps:

SELECT ID FROM textpattern
      WHERE LastMod = '0000-00-00 00:00:00';

That’ll give you a list of numbers back, which you can then paste into the following command:

UPDATE textpattern
   SET LastMod = '1970-01-01 00:00:00'
   WHERE ID IN (comma, separated, list, of, IDs, here);

And, again, repeat for the Expiry column. No idea why it sometimes needs that, nor under what circumstances it fails or succeeds. Some tables it’ll do it fine, others it won’t. Bizarre.


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

#22 2016-06-21 11:47:41

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

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

documented in the sprawling MySQL quagmire of doc-molasses.

Haha…


The text persuades, the *notes prove。

Offline

#23 2016-06-21 14:09:24

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

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

I have it working now. Errors gone.

For others in my shoes following along, here’s the fat skinny…

I found it helpful to use Sequal Pro and the Terminal in tandem. Sequal Pro (SP) to look at DB structure/content, and Terminal to run the changes. (Maybe you could substitute SP for phpMyAdmin, or just do it all from the command-line if your a mysql shell wiz.

First I opened SP, chose the desired database, and clicked on the first Txp table in the list. Then I clicked the Structure table view, showing the columns names and default values for that table.1 (There’s also the important Content table view, showing the values for all records under Structure columns, I’ll come back to that.)

For the most part, I only needed to change the the default values for all instances of DATE/TIME or DATE in Txp tables. So with the SP in Structure view context, you can click through the Txp tables and see where the column names exist and the offending ’0000-00-00 00:00:00’ default values. Those are what you need to change in every Txp table that has any. The errors you see in the admin-side, Languages panel, will change as you fix these one at a time; it keeps bumping into a given table’s column where you haven’t corrected the default value yet.

To change the offending values for all of those instances, I worked from the Terminal. So you first get into the mysql shell, for example:

mysql -uroot

(Or whatever user you use.)

and then into context of the database:

use db_name;

This is the command pattern used to correct the default values:

mysql> alter table [table_name]
    -> alter column [column_name] drop default,
    -> alter column [column_name] drop default,
    -> etc;

For example, using the textpattern table, the full set of commands needed (to get the Posted, Expires, LastMod, and feed_time columns) is:

mysql> alter table textpattern
    -> alter column Posted drop default,
    -> alter column Expires drop default,
    -> alter column LastMod drop default,
    -> alter column feed_time drop default;

Run through that process for each Txp table and the associated DATETIME and/or DATE column having a default value of ’0000-00-00 00:00:00’ or ’0000-00-00’. For me this took care of most of the problem spots.

The only other place that needed fixed was back in the textpattern table again, but this time for all the values in the ‘Expires’ column. To verify in your own case, go back to SP and click the Content view context. Check all the records (a quick eyeball scan under the four columns noted above).

If you’re really, really lucky, nothing will be amiss in any column and you might be done. If you’re really lucky, like me, you’ll only have problems in one column and not have a gazillion records to deal with. And if you’re shit out of luck, you’ll have the opposite of the latter. Because…

If you were following along with Bloke’s things to try in previous posts here, the only one that worked for me was the most cumbersome of all; the triple-step dance of first running:

mysql> select id from textpattern
    -> where Expires = '0000-00-00 00:00:00';

which gives you the id numbers of all offending records, which in my case was the entire ‘Expires’ column. And this is why I was lucky, because I didn’t have thousands of article posts (there’s at least one good reason for being a lazy blogger). But I still had a few — 109 — to manually process into a comma-separated list, the second step of the dance.

The final step is then take the processed list of IDs and use them in this series of commands (pasted into the parentheses):

mysql> update textpattern
    -> set Expires = '1970-01-01 00:00:00'
    -> where id in (2,3,7,8,94,164,160,119,117,64,27,98,124,125,157,115,95,97,99,59,100,32,30,14,16,29,17,62,23,24,25,122,123,126,128,34,35,50,38,129,49,42,44,45,46,47,54,51,130,53,55,158,61,57,63,66,101,102,103,104,67,79,78,73,75,96,76,92,121,106,120,127,137,136,118,111,112,132,135,140,138,139,141,142,145,148,150,151,152,163,153,146,147,154,143,149,155,156,159,161,162,165,166,169,167,168,170,172,171);
Query OK, 105 rows affected (0.05 sec)
Rows matched: 109  Changed: 109  Warnings: 0

And that was the only series of commands that would work. (But we gave it a good try, Bloke.)

When returning to my admin-side and refreshed … all was well again. Or so it seems so far.

Rinse and repeat for every install you have. Woo-hoo!

Finally, when get through all that and see an error about a “Truncated” date value, the you likely need to do what Bloke says to do here, which I’ve had to nearly every time.

1 The Structure view remains in context as you click through the Txp tables until you click a different SP view.

Last edited by Destry (2016-06-21 18:58:26)


The text persuades, the *notes prove。

Offline

#24 2016-06-28 13:58:13

planeth
Plugin Author
From: Nantes, France
Registered: 2009-03-19
Posts: 195
Website

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

Thanks, for all your explanations.

For later reference, this is what I did (running Ubuntu 16, mysql5.7) :

According to the mysql doc dev.mysql.com/doc/refman/5.7/en/sql-mode.html, I decided to set the sql_mode.
Checked inside /etc/mysql/mysql.conf.d/mysqld.cnf :

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,

Added

[mysqld]
sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

at the end of /etc/mysql/my.cnf

It worked.

Offline

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

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

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

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.


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

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

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

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

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.


The text persuades, the *notes prove。

Offline

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

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

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

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. :)


The text persuades, the *notes prove。

Offline

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

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

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

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.


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

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

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

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

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:

  1. Install or make available a fully-functioning 4.5.7. If it’s already populated with articles, all the better.
  2. 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.
  3. Log out of Txp.
  4. Copy the files from a stock 4.6.0 download over your 4.5.7.
  5. 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.
  6. 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).
  7. Report any successes or upgrade errors here, including your version of MySQL/MariaDB/Percona and PHP tested.
  8. 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.


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

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

testdeputy
Member
Registered: 2011-05-29
Posts: 27

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

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

Offline

Board footer

Powered by FluxBB