WordPress.org

Make WordPress Core

Opened 12 years ago

Closed 2 weeks ago

Last modified 2 weeks ago

#8857 closed enhancement (wontfix)

Make WP MySQL strict mode compliant

Reported by: ghostks Owned by:
Milestone: Priority: lowest
Severity: minor Version: 2.7
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 (22)

#1 @ryan
12 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
12 years ago

  • Milestone changed from 2.7.1 to 2.8

#3 @Denis-de-Bernardy
12 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
12 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
10 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
10 years ago

  • Milestone set to Future Release

#8 in reply to: ↑ 6 ; follow-up: @Ramoonus
9 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
9 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
8 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
8 years ago

  • Milestone Future Release deleted

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

#13 @felixq
7 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
3 years ago

#41785 was marked as a duplicate.

#16 @dd32
3 years ago

#41785 was marked as a duplicate.

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


2 weeks ago

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


2 weeks ago

#19 @nsinelnikov
2 weeks 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
2 weeks 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
2 weeks 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
2 weeks 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.

Note: See TracTickets for help on using tickets.