datetime format changed between mysql 5.0 and 5.1?

Question

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.

score 6 · Accepted Answer · answered Sep 20 '11 at 13:52

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):)

score 0 · Answer 2 · answered Sep 20 '11 at 19:31

You may have to set the datetime_format in my.cnf

Here is what is very, very unusual. The variables date_format and datetime_format are present in MySQL 5.x:

mysql> select version();
+----------------------+
| version()            |
+----------------------+
| 5.0.81-community-log |
+----------------------+
1 row in set (0.00 sec)

mysql> show variables like 'date%format';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| date_format     | %Y-%m-%d          |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)

If you look them up in the MySQL Documentation, it says they are unused. Yet, to my surprise, you can set them in your session. Here is how:

mysql> set date_format = '%Y%m%d';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'date%format';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| date_format     | %Y%m%d            |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)

Question: Can you set it globally ??? (This example is MySQL 5.5.12 in Windows)

mysql> set global date_format = '%Y%m%d';
ERROR 1238 (HY000): Variable 'date_format' is a read only variable

At least you cannot on the command line. What about in my.cnf (my.ini) ???

I added this to my.ini on my PC

[mysqld]
date_format="%Y%m%d"

restarted mysql and got this:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.12-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like 'date%format';
+-----------------+-------------------+
| Variable_name   | Value             |
+-----------------+-------------------+
| date_format     | %Y%m%d            |
| datetime_format | %Y-%m-%d %H:%i:%s |
+-----------------+-------------------+
2 rows in set (0.00 sec)

Although it is officially undocumented, you can play with the format at your discretion.

Give it a Try !!!

DISCLAIMER : Should any of your datetime data be caught or killed, I will disavow any knowledge of your actions.

datetime format changed between mysql 5.0 and 5.1?

2 Answers2