Mysql – datetime format changed between thesql 5.0 and 5.1

databaseMySQLreplicationtime

I've upgraded a mysql slave from mysql 5.0 to mysql 5.1. during the catch up, the replication failed on a query with incorrect datetime value:

110919 13:56:18 [ERROR] Slave SQL: Error 'Incorrect datetime value: '2010-03-14 02:35:34.0' for column 'creation_date' at row 1' on query.

I've tried to re-insert the query, and it failed again, then I fixed the value to:

2010-03-14 02:35:34

and the query accepted.

How can I set mysql to approve such datetime values in order to prevent future problems?

My guess will be that it was set on the table description, and was reset during mysql_upgrade.

Best Answer

Mysql 5.1 manipulates microseconds as the same way mysql 5.0 do, however microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded as per Mysql Doc: http://dev.mysql.com/doc/refman/5.1/en/datetime.html. However, since you are getting error with this, you can enable allow invalid dates using the sql mode setting in mysql.

sql-mode=allow_invalid_dates

in my.cnf or my.ini (depending on your OS).

http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_allow_invalid_dates

PS: It is surprising that the date & time you provided in your question is actually a non-existent time in DST (14th March 2010):)

Related Topic