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:
None 
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 ]
Description:
Hi,

I seem to be unable to import a certain MySQL file made using mysqldump from a MySQL 4.1.15 server. It _seems_ related to the timezone functions, but I have ensured the destination timezone matches the source server TZ.

The error is:-
--
ERROR 1062 (23000) at line 37: Duplicate entry '5483-3-2006-04-02 03:00:00' for key 1
ERROR 1062 (23000) at line 45: Duplicate entry '5483-155-2006-04-02 03:00:00' for key 1
--

As far as I can tell, there is _no_ duplicate entries.

Thanks

How to repeat:
Import the attached MySQL dump file into a 4.1.20 server.
[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.