Bug #6287 Slave skips auto_increment values in Replication with InnoDB
Submitted: 27 Oct 2004 14:41 Modified: 21 Dec 2004 17:55
Reporter: no forms Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:4.0.21-log OS:Linux (Linux)
Assigned to: Heikki Tuuri CPU Architecture:Any

[27 Oct 2004 14:41] no forms
Description:
On a master->slave setup, the slave seems to skip, or jump over, some values in an auto_increment column when doing a INSERT IGNORE ... SELECT statement into the following table:

CREATE TABLE `angebot` (
  `angebot_id` int(10) unsigned NOT NULL auto_increment,
  `haendler_produkt_liste` varchar(255) NOT NULL default '',
  `haendler_id` int(10) unsigned NOT NULL default '0',
  `produkt_anzahl` mediumint(8) unsigned NOT NULL default '0',
  `produkt_liste` varchar(255) NOT NULL default '',
  `timestamp` timestamp(14) NOT NULL,
  `gueltig_bis` varchar(255) NOT NULL default '',
  `deeplink` text NOT NULL,
  `preis` float NOT NULL default '0',
  `waehrung` enum('EUR','USD') NOT NULL default 'EUR',
  `bild` enum('nein','ja') NOT NULL default 'nein',
  `versandkosten` varchar(255) NOT NULL default '',
  `lieferzeit` varchar(255) NOT NULL default '',
  `beschreibung` mediumtext NOT NULL,
  `aktiv` enum('nein','ja') NOT NULL default 'ja',
  `aktiv_haendler` enum('nein','ja') NOT NULL default 'nein',
  `anzeigen` enum('nein','ja') NOT NULL default 'ja',
  `prio` mediumint(9) NOT NULL default '0',
  PRIMARY KEY  (`angebot_id`),
  UNIQUE KEY `haendler_angebot_identifikation` (`haendler_id`,`haendler_produkt_liste`),
  KEY `gueltig_bis` (`gueltig_bis`),
  KEY `aktiv` (`aktiv`),
  KEY `anzeigen` (`anzeigen`),
  KEY `prio` (`prio`),
  KEY `produkt_liste` (`produkt_liste`(10)),
  KEY `produkt_anzahl` (`produkt_anzahl`),
  KEY `constraint_haendler` (`haendler_id`,`aktiv_haendler`),
  KEY `haendler_id` (`haendler_id`),
  CONSTRAINT `0_1700` FOREIGN KEY (`haendler_id`, `aktiv_haendler`) REFERENCES `haendler` (`ha
endler_id`, `aktiv`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `angebot_ibfk_1` FOREIGN KEY (`haendler_id`) REFERENCES `haendler` (`haendler_id`
) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB

I can see that the table contains the same number of rows and contents on master and slave - except that the slave sometimes skips an `angebot_id` value. It looks very similar to me like Bug #4787, except we use 4.0.21-log (master and slave) and InnoDB.

This affects replication because a foreign key constraint in a later insert in another table references to auto_increment-ID. The INSERT statement executes fine on the master but fails on the slave. This shouldn't happen because the INSERT_ID is part of the binlog.

How to repeat:
This is the INSERT statement:

      INSERT IGNORE INTO   angebot (haendler_produkt_liste,
                             haendler_id,
                                 produkt_anzahl,
                                    gueltig_bis,
                                    deeplink,
                                    preis,
                                    waehrung,
                                    bild,
                                    versandkosten,
                                    lieferzeit,
                                    beschreibung,
                                    aktiv_haendler)
        SELECT tmp.*,
                 old.aktiv
        FROM angebot_tmp as tmp
        LEFT JOIN haendler as old
        ON tmp.haendler_id=old.haendler_id
        ORDER BY tmp.haendler_produkt_liste;

(the ORDER BY is definite/determined here as it is part of a unique key with the other part being constant)

Suggested fix:
So far I have tried:
- reset slave, set up replication from scratch: Some hours, then it will stop again.
- find the highest auto-ID+1 on the master, insert a dummy-row with the auto_increment-value defined. Again this works fine on the master, but replication on the slave will stop when the slave's auto-inc-counter is higher than the master's:
Error 'Duplicate entry '286656' for key 1' on query. Default database: 'me2'. Query: 'INSERT INTO angebot VALUES (286656, 'DUMMY_286656', 1, 0, '', NOW(), '', '', 0, 'EUR', 'nein',  '', '', '-- DUMMY BEI ID 286656, HAENDLER 35 --',  'nein', 'nein', 'nein', 0)'
- checked all buffer sizes in the my.conf to be similar on master and slave.

All of the above did not work. The only idea I have left so far to eliminate the auto_increment column and implement a similar mechanism.
[27 Oct 2004 14:51] no forms
some additional information:
the table angebot_tmp contains several thousand rows, but only few of them are actually inserted (due to the IGNORE). This is repeated approx. 50 times/hour, so lots of data before the issue occurs.
[27 Oct 2004 14:55] Heikki Tuuri
Hi!

Yes, it is well possible that there is a bug here. In the master, InnoDB assigns the auto-inc values from its internal counter. But on the slave, MySQL first runs SET INSERT_ID=..., and InnoDB assigns the auto-inc values from that. There may be some subtle behavioral difference which causes the values to differ with INSERT IGNORE.

I have assigned this bug to myself.

Regards,

Heikki
[16 Nov 2004 19:08] Heikki Tuuri
Hi!

This patch fixes the bug in 4.0.23 and 4.1.8.

Thank you,

Heikki

-------------------------------------------------
ha_innodb.cc:
  Fix InnoDB bug #6287: if one uses INSERT IGNORE to insert several rows at a ti
me, and the first inserts are ignored because of a duplicate key collision, then
 InnoDB in a replication slave assigns AUTO_INCREMENT values 1 bigger than in th
e master
-------------------------------------------------