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

Bloke · 2016-06-21 10:08:42

Destry wrote #299898:

I guess those are just sql statements to run?

Yep. Note that if you’re changing a table’s values using DROP DEFAULT you can’t always (for reasons I cannot fathom) do them piecemeal. You may need to alter all values in one statement. Hence the first example in my post above is a comma-separated list of alterations to make to the textpattern table. If I tried to just change, for example, the expiry column alone, it complained about the other columns containing bad values. *shrug*

The easiest way to get a list of all columns that need changing is to use the DESCRIBE command:

DESCRIBE textpattern;

That SQL statement will show you the table definition along with any default values that are set, so you can nuke ‘em.

If I happen to find the my.ini file, what kind of “toggle” am I expecting to see?

Something to do with strict mode (turn that setting off) or date-handling (like no_zero_dates). Again, turn that off if you can. The settings might actually be stored in the database. In that case, use the SQL command SHOW VARIABLES; to see them all, or something like:

SHOW VARIABLES WHERE Variable_name LIKE '%date%';

to filter them. If you find a variable you want to alter, you can then use:

SET variablename=value;

e.g.:

SET NO_ZERO_DATES=false;

Hope that helps. Again, any info you can feed back here on fixes and things might help nudge us towards a more concrete fix.

Bloke · 2016-06-21 10:09:44

Destry wrote #299899:

Just learned that my.ini is the Windows version of my.cnf. No wonder I couldn’t find it. ;)

My bad. I was mixing up php.ini and my.cnf, sorry.

Destry · 2016-06-21 10:23:53

mysql> DESCRIBE textpattern;
+-----------------+--------------+------+-----+---------------------+----------------+
| Field           | Type         | Null | Key | Default             | Extra          |
+-----------------+--------------+------+-----+---------------------+----------------+
| ID              | int(11)      | NO   | PRI | NULL                | auto_increment |
| Posted          | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                |
| Expires         | datetime     | NO   | MUL | 0000-00-00 00:00:00 |                |
| AuthorID        | varchar(64)  | NO   | MUL |                     |                |
| LastMod         | datetime     | NO   |     | 0000-00-00 00:00:00 |                |
| LastModID       | varchar(64)  | NO   |     |                     |                |
| Title           | varchar(255) | NO   | MUL |                     |                |
| Title_html      | varchar(255) | NO   |     |                     |                |
| Body            | mediumtext   | NO   |     | NULL                |                |
| Body_html       | mediumtext   | NO   |     | NULL                |                |
| Excerpt         | text         | NO   |     | NULL                |                |
| Excerpt_html    | mediumtext   | NO   |     | NULL                |                |
| Image           | varchar(255) | NO   |     |                     |                |
| Category1       | varchar(128) | NO   | MUL |                     |                |
| Category2       | varchar(128) | NO   |     |                     |                |
| Annotate        | int(2)       | NO   |     | 0                   |                |
| AnnotateInvite  | varchar(255) | NO   |     |                     |                |
| comments_count  | int(8)       | NO   |     | 0                   |                |
| Status          | int(2)       | NO   |     | 4                   |                |
| textile_body    | varchar(32)  | NO   |     | 1                   |                |
| textile_excerpt | varchar(32)  | NO   |     | 1                   |                |
| Section         | varchar(128) | NO   | MUL |                     |                |
| override_form   | varchar(255) | NO   |     |                     |                |
| Keywords        | varchar(255) | NO   |     |                     |                |
| url_title       | varchar(255) | NO   | MUL |                     |                |
| custom_1        | varchar(255) | NO   |     |                     |                |
| custom_2        | varchar(255) | NO   |     |                     |                |
| custom_3        | varchar(255) | NO   |     |                     |                |
| custom_4        | varchar(255) | NO   |     |                     |                |
| custom_5        | varchar(255) | NO   |     |                     |                |
| custom_6        | varchar(255) | NO   |     |                     |                |
| custom_7        | varchar(255) | NO   |     |                     |                |
| custom_8        | varchar(255) | NO   |     |                     |                |
| custom_9        | varchar(255) | NO   |     |                     |                |
| custom_10       | varchar(255) | NO   |     |                     |                |
| uid             | varchar(32)  | NO   |     |                     |                |
| feed_time       | date         | NO   |     | 0000-00-00          |                |
+-----------------+--------------+------+-----+---------------------+----------------+
37 rows in set (0.01 sec)

mysql> SHOW VARIABLES WHERE Variable_name like '%date%';
+-----------------------------------------+-------------------+
| Variable_name                           | Value             |
+-----------------------------------------+-------------------+
| binlog_direct_non_transactional_updates | OFF               |
| date_format                             | %Y-%m-%d          |
| datetime_format                         | %Y-%m-%d %H:%i:%s |
| log_slave_updates                       | OFF               |
| low_priority_updates                    | OFF               |
| query_cache_wlock_invalidate            | OFF               |
| sql_safe_updates                        | OFF               |
+-----------------------------------------+-------------------+
7 rows in set (0.06 sec)

The variables clearly seem matched to the defaults have “0000-00-00” in them. But should I be turning OFF the date_format and datetime_format variables? I don’t want to explode the bomb by clipping the red wire.

Also, noob question… I ran the SHOW VARIABLES WHERE command after getting in context of a particular table (USE DBname;), so is that showing only the variables for that particular database, or for all databases in mysql server currently?

Last edited by Destry (2016-06-21 10:32:57)

Destry · 2016-06-21 11:06:16

mysql> alter table textpattern
    -> ALTER COLUMN Posted DROP DEFAULT,
    -> alter column Expires drop default,
    -> alter column feed_time drop default;
ERROR 1067 (42000): Invalid default value for 'LastMod'
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;
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

As you can see, it wouldn’t let me run the statement if I didn’t include dropping default value for ‘LastMod’. So I included that on the next try and it successfully made them all ‘NULL’.

But then trying your second statement didn’t work…

UPDATE textpattern
    -> SET LastMod = '1970-01-01 00:00:00'
    -> WHERE LastMod = '0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'LastMod' at row 1

Out of curiosity I checked the admin-side at this point and the error changed:

User_Error "Invalid default value for 'modified'" in /Users/me/Sites/mine/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:532 safe_query()
textpattern/update/_to_4.6.0.php:81 safe_alter()
textpattern/update/_update.php:84 include()
textpattern/index.php:180 include()

But having a look at the database’s Content (vs. Structure) I see that all values for ‘Posted’ and ‘LastMod’ are fine (all have good date/time values). However, the entire ‘Expires’ column is full of ’0000-00-00 00:00:00’ values.

Should I now be running this?

update textpattern
    -> set Expires = '1970-01-01 00:00:00'
    -> where Expires = '0000-00-00 00:00:00';

O wizard?

Bloke · 2016-06-21 11:27:39

Destry wrote #299903:

should I be turning OFF the date_format and datetime_format variables?

No. Don’t touch those. That’s just for your locale, how you want dates to display in general (date-first, month-first, etc). Nothing to do with zero-dates. I just wondered if maybe it was a setting in the DB, but it appears not :-(

As you can see, it wouldn’t let me run the statement if I didn’t include dropping default value for ‘LastMod’. So I included that on the next try and it successfully made them all ‘NULL’.

Cool.

Incorrect datetime value: ’0000-00-00 00:00:00’

Out of curiosity does it work if you do both columns at once?

UPDATE textpattern
    SET LastMod = '1970-01-01 00:00:00',
        Expires = '1970-01-01 00:00:00'
    WHERE LastMod = '0000-00-00 00:00:00';

??

I ran the SHOW VARIABLES WHERE command after getting in context of a particular table (USE DBname;), so is that showing only the variables for that particular database, or for all databases in mysql server currently?

Depends. Some variables are global, some are specific to a DB. I believe globals can be identified by putting two ‘@’ signs in front of their name, but I’m a bit fuzzy on this, and I don’t know how to determine which are which. Probably some command to only show globals or something, but the MySQL docs are largely incomprehensible.

Destry · 2016-06-21 11:28:39

As mentioned, I don’t currently have a my.cnf file. When using this:

mysql --help | grep "Default options" -A 1

I’m informed that:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

The old one I removed yesterday was at /etc/my.cnf. But it only had two items in it, neither had anything to do with dates. None of the other locations have a file.

There’s a template my-default.cnf in the Homebrew install of mysql (the active install). It’s meant to be copied, moved, and lines uncommented as needed. It’s contents is:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M 

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

By not having a my.cnf file at all, does that default to having some files (such as data stuff) turned ON? If so, then maybe I do need to create one and override defaults to turn stuff OFF?

Bloke · 2016-06-21 11:32:49

Destry wrote #299905:

sql_mode=

That’s the setting that governs how MySQL behaves and is the one you want to set if things aren’t to your liking. You can specify strict settings and such like there.

There’s some info here if you can decipher it!

By not having a my.cnf file at all, does that default to having some files (such as data stuff) turned ON?

EDIT: Yes, it depends on the version of MySQL. Each one has a different set of defaults, again, documented in the sprawling MySQL quagmire of doc-molasses.

Last edited by Bloke (2016-06-21 11:34:24)

Destry · 2016-06-21 11:38:31

Tested…

mysql> UPDATE textpattern
    -> SET LastMod = '1970-01-01 00:00:00',
    -> Expires = '1970-01-01 00:00:00'
    -> WHERE LastMod = '0000-00-00 00:00:00';
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'LastMod' at row 1

But remember, ‘LastMod” doesn’t have ’0000-00-00 00:00:00’ values, only the Expires column does. That’s why I’m wondering if I should just run…

update textpattern
    -> set Expires = '1970-01-01 00:00:00'
    -> where Expires = '0000-00-00 00:00:00';

?

That wouldn’t explain the error in admin-side, though, I guess: “Invalid default value for ‘modified’”

Last edited by Destry (2016-06-21 11:45:51)

Bloke · 2016-06-21 11:45:38

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.

Destry · 2016-06-21 11:47:41

documented in the sprawling MySQL quagmire of doc-molasses.

Haha…

Destry · 2016-06-21 14:09:24

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.¹ (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.

¹ 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)

planeth · 2016-06-28 13:58:13

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.

Textpattern CMS

Textpattern CMS support forum

#13 2016-06-21 10:08:42

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

Destry wrote #299898:

#14 2016-06-21 10:09:44

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

Destry wrote #299899:

#15 2016-06-21 10:23:53

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

#16 2016-06-21 11:06:16

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

#17 2016-06-21 11:27:39

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

Destry wrote #299903:

#18 2016-06-21 11:28:39

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

#19 2016-06-21 11:32:49

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

Destry wrote #299905:

#20 2016-06-21 11:38:31

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

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

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

Destry wrote #299907:

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

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

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

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

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

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

Board footer