Textpattern CMS support forum

You are not logged in. Register | Login | Help

#11 2016-06-21 09:27:34

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

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

Thanks for the clarification.

Bloke wrote #299895:

See above post for details.

I guess those are just sql statements to run?

it can be toggled the other way in my.ini.

If I happen to find the my.ini file, what kind of “toggle” am I expecting to see? You mean some values need changed?

all hell breaks loose in date land^1^.

hehe

1 A fix… so hell remains locked away.


The text persuades, the *notes prove。

Offline

#12 2016-06-21 10:07:53

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

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

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

Checking the usual places:

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

I currently have no my.cnf (deleted an old offender yesterday) but I can create a new one and put something in it, if I knew what to add for this “toggle” action.


The text persuades, the *notes prove。

Offline

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

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

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

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.


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

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

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

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

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.


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

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

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

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

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)


The text persuades, the *notes prove。

Offline

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

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

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

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?


The text persuades, the *notes prove。

Offline

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

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

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

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.


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

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

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

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

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?


The text persuades, the *notes prove。

Offline

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

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

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

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)


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

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

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

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

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)


The text persuades, the *notes prove。

Offline

Board footer

Powered by FluxBB