I started getting this error: Incorrect date value: '2019-02-30' for column... after switch to MySql 8

Question

I know this is an invalid date.  A user entered this date on a system that only does basic range checks on the date parts.  We use QIE to track invalid dates entered to trigger an investigation to correct the date.

I have been using a database connection to save this to MySql database.  I previously used MySql 5.7 and these dates were successfully inserted into the database without errors.  After upgrading to MySql 8 I started getting this error:

Incorrect date value: '2019-02-30' for column 'dateCol' at row 1

What changed?

Answer 1

The reason this fails is due to additional date validation added to MySql 8.

The new behavior and a possible solution can be found in the bullet list "Be aware of certain properties of date value interpretation in MySQL:" found in this link: here: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

"The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31,

     respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a

     warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable

     ALLOW_INVALID_DATES. See Section 5.1.11, “Server SQL Modes”, for more information."

This link also references the change: https://stackoverflow.com/questions/58431136/how-to-fix-mysql-8-error-codes-1525-and-1292

It has a nice demo as well: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f48ec79ab2e127a795942b1c2dc25cab

So, to allow this type of bad dates to be saved without errors or without being transformed by the database you can try adding ALLOW_INVALID_DATES to the sqlMode.  You can do this on the server as in the demo linked above or via the sql_mode JDBC connection url property or by setting the sql_mode in the configuration file on the server.

Examples:

via connection url:

    jdbc:mysql://localhost/test?user=sqluser&password=sqluserpw&sessionVariables=sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES'

via configuration file:

    sessionVariables=sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES'

Note: There are other changes to date data types.  If you need to preserver invalid dates entered by users you might try storing it as a string rather than a date.  That would isolate your process from other dates validation changes not discussed above.

Resources:

    https://stackoverflow.com/questions/58431136/how-to-fix-mysql-8-error-codes-1525-and-1292

    https://severalnines.com/database-blog/moving-mysql-57-mysql-80-what-you-should-know

    https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html

    https://dev.mysql.com/doc/refman/8.0/en/datetime.html

    https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f48ec79ab2e127a795942b1c2dc25cab