Bug #25693 Duplicate entry 'XXXX' for key 'PRIMARY'

• Submitted: 18 Jan 2007 13:06
• Modified: 1 Feb 2007 12:05
• Reporter: Cyril SCETBON
• Status: Can't repeat
• Category: MySQL Server: Replication
• Severity: S2 (Serious)
• Version: 5.1.12-beta-log
• CPU Architecture: Any

[18 Jan 2007 13:06] Cyril SCETBON

Description:
When replicating a table with an auto_increment field (int(11)), on the slave the field jump from value 3 to 1124 ! But I also get value 5 after 1124 !

How to repeat:
On the master :

CREATE TABLE `table_siteA` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `big_one` longtext,
    `sequence` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    KEY `sequence` (`sequence`)
);

 insert into table_siteA(big_one) values('AAAAAAAAAAAAAAAAAAAAAAAA');
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into table_siteA(big_one) select big_one from table_siteA;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into table_siteA(big_one) select big_one from table_siteA;
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into table_siteA(big_one) select big_one from table_siteA;
Query OK, 4 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

on the slave :

show slave status\G

Slave_IO_Running: Yes
          Slave_SQL_Running: No
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table: test.table_siteA
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 1062
                 Last_Error: Error 'Duplicate entry '1124' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into table_siteA(big_one) select big_one from table_siteA'
               Skip_Counter: 0
        Exec_Master_Log_Pos: 301233
            Relay_Log_Space: 2265

select id from table_siteA;
+------+
| id   |
+------+
|    1 | 
|    2 | 
|    3 | 
|    5 | 
| 1124 | 
+------+
5 rows in set (0.00 sec)

[18 Jan 2007 13:30] Valeriy Kravchuk

Thank you for a problem report. Please, try to repeat with a newer version, 5.1.14, and inform about the results. In case of the same problem, inform about OS and send SHOW CREATE TABLE and SHOW TABLE STATUS results for test.table_sizeA from both master and slave.

[1 Feb 2007 10:31] Cyril SCETBON

Tested with 5.1.14-beta-log and no more duplicate entry error.

thanks.

[1 Feb 2007 12:05] Valeriy Kravchuk

So, I am closing this report as "Can't repeat" on 5.1.14.