MySQL Strict Issues
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:
- GEDCOM import failure with dates like "1891-00-00
- GEDCOM import no longer works
- Table Restore Failure
- date issue when adding a person
References
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
