Make WordPress Core

Opened 15 years ago

Last modified 5 months ago

#8857 reopened enhancement

Make WP MySQL strict mode compliant

Reported by: ghostks's profile ghostks Owned by:
Milestone: Priority: lowest
Severity: normal Version: 6.4.2
Component: Database Keywords:
Focuses: Cc:

Description (last modified by helen)

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

Change History (23)

#1 @ryan
15 years ago

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'."

#2 @ryan
15 years ago

  • Milestone changed from 2.7.1 to 2.8

#3 @Denis-de-Bernardy
15 years ago

  • Keywords needs-patch added
  • Milestone changed from 2.8 to Future Release
  • Priority changed from normal to lowest
  • Severity changed from normal to minor
  • Summary changed from Incorrect Mysql scheme to Make WP MySQL strict mode compliant

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.

#5 @Denis-de-Bernardy
15 years ago

  • Milestone Future Release deleted
  • Resolution set to wontfix
  • Status changed from new to closed

Please re-open when we drop MySQL 4 support

#6 follow-up: @Elpie
13 years ago

  • Cc Elpie added
  • Component changed from General to Database
  • Resolution wontfix deleted
  • Status changed from closed to reopened
  • Type changed from defect (bug) to enhancement

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

#7 @scribu
13 years ago

  • Milestone set to Future Release

#8 in reply to: ↑ 6 ; follow-up: @Ramoonus
13 years ago

Replying to Elpie:

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

you mean 3.3?

#9 in reply to: ↑ 8 @Elpie
13 years ago

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.

#11 @pento
12 years ago

  • Keywords needs-patch removed
  • Resolution set to wontfix
  • Status changed from reopened to closed

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.

#12 @nacin
12 years ago

  • Milestone Future Release deleted

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

#13 @felixq
10 years ago

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.

#15 @dd32
7 years ago

#41785 was marked as a duplicate.

#16 @dd32
7 years ago

#41785 was marked as a duplicate.

This ticket was mentioned in Slack in #core by helen. View the logs.


4 years ago

This ticket was mentioned in Slack in #docs by themiked. View the logs.


4 years ago

#19 @nsinelnikov
4 years ago

  • Resolution wontfix deleted
  • Status changed from closed to reopened

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.

#20 @helen
4 years ago

  • Description modified (diff)
  • Resolution set to wontfix
  • Status changed from reopened to closed

@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.

#21 @nsinelnikov
4 years ago

@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 =)

#22 @helen
4 years ago

@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.

#23 @pounibi
5 months ago

  • Resolution wontfix deleted
  • Severity changed from minor to normal
  • Status changed from closed to reopened
  • Version changed from 2.7 to 6.4.2

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.

Note: See TracTickets for help on using tickets.