Bug #24762 | Failure to import data (timestamp) | ||
---|---|---|---|
Submitted: | 1 Dec 2006 21:41 | Modified: | 24 Oct 2009 2:15 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 4.1.20,5.0.60,5.1.38,5.1.40 | OS: | Linux (CentOS,Mac OSX) |
Assigned to: | CPU Architecture: | Any |
[1 Dec 2006 21:41]
[ name withheld ]
[1 Dec 2006 21:42]
[ name withheld ]
MySQL Dump
Attachment: duplicate_entry.sql (application/octet-stream, text), 2.30 KiB.
[3 Dec 2006 10:08]
Valeriy Kravchuk
Thank you for a problem report. I was not able to repeat with latest 4.1.23-BK on Linux - dump was loaded without any errors. So, please, try with a newer version, 4.1.22.
[3 Dec 2006 22:32]
Scott Noyes
I can duplicate the error when strict mode is off. With strict mode on, the rows in question fail with "ERROR 1292 (22007): Incorrect datetime value: '2006-04-02 02:01:15' for column 'when' at row 1". Note that these times don't exist on these dates, because of DST. mysql> CREATE TABLE `user_actions` ( -> `id` int(11) NOT NULL default '0', -> `when` timestamp NOT NULL default CURRENT_TIMESTAMP, -> PRIMARY KEY (`id`,`when`) -> ); Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO `user_actions` VALUES (1, '2006-04-02 02:01:15'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> INSERT INTO `user_actions` VALUES (1, '2006-04-02 02:01:20'); ERROR 1062 (23000): Duplicate entry '1-2006-04-02 03:00:00' for key 1 mysql> show warnings; +---------+------+---------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------+ | Warning | 1299 | Invalid TIMESTAMP value in column 'when' at row 1 | | Error | 1062 | Duplicate entry '1-2006-04-02 03:00:00' for key 1 | +---------+------+---------------------------------------------------+ 2 rows in set (0.02 sec) mysql> show variables like 'version%'; +-------------------------+-------------------------------+ | Variable_name | Value | +-------------------------+-------------------------------+ | version | 5.0.27-community-nt | | version_comment | MySQL Community Edition (GPL) | | version_compile_machine | ia32 | | version_compile_os | Win32 | +-------------------------+-------------------------------+ 4 rows in set (0.00 sec)
[6 Dec 2006 10:55]
Sergei Golubchik
What was the server timezone when you generated the dump ?
[6 Dec 2006 11:27]
[ name withheld ]
1. Upgrading to MySQL 4.1.22 (from MySQL's provided RPMs) worked. (so unfortunately I can't help you any further in reproducing this problem :/ ) 2. Both servers are GMT (G'Wich Mean Time).
[27 Dec 2006 14:40]
Valeriy Kravchuk
Looks like the bug is fixed in 4.1.22 already.
[24 Oct 2009 2:15]
Donna Harmon
I have verified Duplicate key errors in multiple versions. When importing data that contains invalid DST values for timestamp fields, according to the system time zone used, the time will be changed to the next valid DST hour value. For example: The system will convert timestamps of 2009-03-08 02:00:00 through 02:59:59 to 03:00:00 as 2am is an invalid DST for most of North America. Workaround 1: Change the values being imported to valid DST values Workaround 2: Change the system time zone to be a non-DST time zone if appropriate.
[27 Oct 2009 19:36]
Peter Gulutzan
I didn't see a clear 'how to repeat', so this is my 'how to repeat': I loaded the mysql.time_zone_name table using the instructions in http://dev.mysql.com/doc/refman/5.4/en/time-zone-support.html I started the server with mysqld --default-time-zone=Canada/Mountain (not 'gmt' despite earlier comments). On a client I said: CREATE TABLE `user_actions` ( `id` int(11) NOT NULL default '0', `when` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`when`) ); INSERT INTO `user_actions` VALUES (1, '2006-04-02 02:01:15'); Result: warning. SELECT * FROM user_actions; Result: `when` column has: '2006-04-02 03:00:00'. If I say sql_mode=traditional, I get an error not a warning. But this is typical MySQL. With sql_mode='', if I try to insert 256 in a TINYINT, I get 127. If I try to insert 'abc' in a CHAR(2), I get 'ab'. The manual says: "If you are not using strict mode ... MySQL inserts adjusted values for invalid or missing values and produces warnings." So one of two things has happened: () I have guessed incorrectly about the 'how to repeat'. or () It's not a bug.
[29 Oct 2009 21:25]
Peter Gulutzan
> The issue in http://bugs.mysql.com/bug.php?id=24762 is that the > 'adjusted' value is conflicting with another value causing a duplicate > key error (regardless of the sql mode. Regardless of sql_mode? I don't see that. I get an error if sql_mode=strict_all_tables: " mysql> set @@sql_mode=strict_all_tables; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `user_actions` VALUES (1, '2006-04-02 02:01:15'); ERROR 1292 (22007): Incorrect datetime value: '2006-04-02 02:01:15' for column 'when' at row 1 " ... and, again, that's typical MySQL. > Is it still not a bug? I suggested that if my "how to repeat" guesses are correct, then it is not a bug. Nobody has said my guesses are wrong. So it's not a bug. > If it is not, do you agree that a feature request to have the value stay > as is with out adjusting it is acceptable? Well, anybody can write a feature request, but if you mean "will we do it?" I don't know about that.