Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 5 down vote accepted

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.