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.
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.