MySQL Strict Issues

From TNG_Wiki
Jump to navigation Jump to search

Several issues have been reported on the TNG Community Forum where MySQL Strict mode had to be disabled.

Date Issues

NO_ZERO_DATE

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date, but its effect is highly dependent on whether or not strict mode is enabled. If NO_ZERO_DATE is set while not in strict mode, '0000-00-00' is permitted but a warning is produced. [1]

NO_ZERO_IN_DATE

The NO_ZERO_IN_DATE mode affects whether the server permits dates in which the year part is non-zero but the month or day part is 0. Hence, this mode affects dates such as '2014-00-01' or '2014-01-00', but not '0000-00-00'. The effect of NO_ZERO_IN_DATE also depends on whether strict mode is enabled. [1]

As of MySQL 5.7.4, the NO_ZERO_DATE and NO_ZERO_IN_DATE mode do nothing and have been rolled into strict SQL mode.

  • Strict mode affects whether the server permits '0000-00-00' as a valid date [2]
  • Strict mode affects whether the server permits dates in which the year part is nonzero but the month or day part is 0 (dates such as '2010-00-01' or '2010-01-00') [2]

TNG uses a lot of yyyy-00-00 dates when only the year is known and thus is not compatible with MySQL Strict mode, so you may have to ask your host to disable strict mode, unless you are running TNG v11.0.2 where the code was changed to disable MySQL Strict Mode for the session. [3] Alternatively, if you are using TNG 10.1 through TNG 11.0,1, you can install the Disable MySQL Strict Mode mod which will also provide the actual SQL query failure whenever possible

Data Truncation Issues

If strict SQL mode is not enabled and you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit and a warning is generated.

You can disable STRICT_TRANS_TABLES and STRICT_ALL_TABLES. This allows the automatic truncation of the inserted string.

TNG does not prevent the import of data that exceeds the database definitions, so you may need to ask your host to disable strict mode [3], or use a GEDCOM validation program to find the errors so you can correct them, or you could increase the size of the database field. Unless you are running TNG v11.0.2 where the code was changed to disable MySQL Strict Mode for the session. [3] Alternatively, if you are using TNG 10.1 through TNG 11.0,1, you can install the Disable MySQL Strict Mode mod which will also provide the actual SQL query failure whenever possible


TNG Reported Issues

The following are TNG reported issues:

References

  1. 1.0 1.1 MySQL Date Gotchas
  2. 2.0 2.1 MySQL Strict Mode Date Issue
  3. 3.0 3.1 3.2 Disable Strict Mode

Related Links

Disable MySQL Strict Mode mod if not running TNG 11.0.2

Proposal to Simplify SQL Modes

Making Strict SQL Mode the Default

Improvements in MySQL Strict Mode

How to Prepare for MySQL 5.7

MySQL Date Gotchas

Strict SQL Mode

Comparison of the IGNORE Keyword and Strict SQL Mode

Setting SQL Mode

Disable SQL mode for session