Make WP MySQL strict mode compliant

[ghostks]

Incorrect Mysql scheme during WordPress installation on DBMS with strict date/datetime format settings will result in errors and will be unable to finish succesfully. Existing default date and datetime values are '0000-00-00' and '0000-00-00 00:00:00' which are not supported any more. Please see official Mysql dev documentation for more information:

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

​http://dev.mysql.com/doc/refman/5.0/en/datetime.html

[ryan]

Other parts of that page seem to suggest the 0 values are still valid. "As of MySQL 5.0.2, MySQL does not accept timestamp values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special value '0000-00-00 00:00:00'."

[Denis-de-Bernardy]

the reported bug is valid if and only if you switch mysql's strict mode off. but when that is on, essentially nothing works in WP anyway.

this is one of those huge changes, like getting rid of magic quotes at some point. we'd need to do things such as:

• use null values for dates and datetimes rather than the 0000-00-00 00:00:00 equivalents
• not use single quotes on integer values
• make sure data will fit in the columns
• the list goes on and on

if you're in for a good laugh, hop to:

​http://sql-info.de/mysql/gotchas.html

suggesting wontfix, myself. else needs a big patch and lots of testing, because quite a few plugins have come to expect null dates to return 0000-00-00 00:00:00.

[Denis-de-Bernardy]

my favorite one is:

​http://sql-info.de/en/mysql/referential-integrity.html#3_5

[Denis-de-Bernardy]

Please re-open when we drop MySQL 4 support

[Elpie]

With WP 3.2 under development this should be looked into more closely.

[Ramoonus]

Replying to Elpie:

With WP 3.2 under development this should be looked into more closely.

you mean 3.3?

[Elpie]

Replying to Ramoonus:

Replying to Elpie:

With WP 3.2 under development this should be looked into more closely.

you mean 3.3?

It was 3.2 when I wrote that and now, of course, MySQL5 is the minimum requirement. Two years ago, when this ticket was opened, it was fair enough to wait. Now that MySQL4 support has been dropped this will need to be addressed.

[eddiemoya]

Relevant: ​http://wordpress.org/extend/plugins/incorrect-datetime-bug-plugin-fix/

[pento]

This is not viable to fix - there are thousands of plugins that expect the zero date format, which will all break horribly if we switch to NULL dates.

[nacin]

Yeah, WordPress just pretty simply does not support strict mode.

[felixq]

Changelog MySQL 5.6.17:

Incompatible Change: The deprecated ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE SQL modes now do nothing. Instead, their previous effects are included in the effects of strict SQL mode (STRICT_ALL_TABLES or STRICT_TRANS_TABLES). In other words, strict mode now means the same thing as the previous meaning of strict mode plus the ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, and NO_ZERO_IN_DATE modes. This change reduces the number of SQL modes with an effect dependent on strict mode and makes them part of strict mode itself.

[SergeyBiryukov]

Follow-up: #26847

[dd32]

#41785 was marked as a duplicate.

[dd32]

#41785 was marked as a duplicate.

[nsinelnikov]

Basically, I have found this issue on this page (​https://codex.wordpress.org/Creating_Tables_with_Plugins)

The DateTime value shown in the example is 0000-00-00 00:00:00 which is out-of-range. According to information found around the net:

​https://mysqlconnector.net/troubleshooting/datetime-storage/
​https://dev.mysql.com/doc/refman/8.0/en/datetime.html
​https://www.mysqltutorial.org/mysql-datetime/

The minimum supported value for a DATETIME column is 1000-01-01 00:00:00

A @johnbillion pointed out, this value for the date range is in core: ​https://github.com/johnbillion/wordpress-develop/blob/3f9172b5e141f30a78cffde1c1f384b34cea3864/src/wp-admin/includes/schema.php#L162-L175

This can be a very deep core question and dbDelta WP installation queries as well.

[helen]

@nsinelnikov As noted in the discussion in Slack, this is valid in non-strict mode, which we have to keep for what essentially boils down to back-compat reasons. If documentation needs to be updated, that is fine, we just don't need this ticket open because there is nothing actionable for core.

[nsinelnikov]

@helen
Could you clarify, the user can independently choose in which mode to use the database?

What happens if he uses it in strict mode? It's just that I, as the author of the plugin, received an error report that the table was not created by dbDelta due to the fact that the default value is out of scope.

What standard value should I use for myself? Which supports the core or refer to the MySQL documentation.

Previously I thought that '0000-00-00 00:00:00' is the correct value =)

[helen]

@nsinelnikov I suppose a user could create a database with an incompatible mode before installing WordPress, although WordPress itself attempts to set modes accordingly so it will function. This is not at all my area of expertise, only historical knowledge - I don't know if there's something you're doing that causes this or if it's something we should prevent, but that would be a separate ticket IMO.

I do not remember off the top of my head what the symptoms are if somehow you've ended up in strict mode but my recollection is that from a user perspective there will be errors ranging from visual to very functional anywhere core is relying on a zero date - if you search core for "0000-00-00" you'll see the beginning of the scope of its impact. And again, WordPress sets the current SQL mode, so perhaps it's an issue of being outside of that context by either doing direct file inclusions or running before the mode is set.

[pounibi]

THX for setting minimal MySQL version to 5.5.5 (which is outdated for several years). But now even 5.7 is out of support. I tried the update advisor from mysqlsh: util.checkForServerUpgrade();. It threw several "warnings", all about the illegal datetime default setting "0000-00-00".

Here's what the advisor said:

It is strongly advised to replace zero values with valid ones, as they may not work correctly in the future.

So as the warning appeared in 5.7.8 it's still ignored (or set to wontfix) currently because of breaking ancient plugins.

It's a very BAD idea to have old MySQL releases with - partially massive - security flaws in production use and IMHO a not so good idea to to ignore the positive impact on security of the strict mode.

So PLEASE tell the community and the developers that there's a not so far date in the future, where the faulty timestamp won't be accepted any longer. The sentence "it will break several plugins" reminds me about: all this damn compatibility stuff in Windoze - just to run ancient buggy insecure old stuff.

With regards,
Peter.