Duplication values in autoincremented field on replication
I have a strange issue with MASTER-SLAVE replication.
First of all, I understand that's not a good solution, but it's a temporary solution. However, it's required.
There are source MySQL DB (MASTER) and its replic DB (SLAVE). It's a statement-bases replication, because I need triggers to be run on the SLAVE side.
1. The original triggers were replaced by new ones on the SLAVE.
2. Every table has 3 triggers: on INSERT, on UPDATE and on DELETE.
3. Triggers were generated by a single pattern and differ only by params.
4. Every trigger does a single INSERT query to a table (CHANGES) on the SLAVE.
5. This table is not replicated and exists only on the SLAVE.
6. There is an auto-incremented column (ID - bigint) in this table.
7. None of the triggers set or modify values of the column ID. The DB sets a default (auto-incremented) values for it.
8. It's about 20 inserts executed on CHANGES per minute.
9. I see errors with duplicated values of the column ID.
How it is possible?
Let's again:
1. An INSERT / UPDATE / DELETE query is executed on MASTER.
2. This change is replicated to SLAVE.
3. A trigger is called and inserts a row to the CHANGES.
4. Duplicated values error is generated.
And as I said before, none of the triggers set or modify value of the auto-incremented field (ID). And only triggers work with table CHANGES.
I understand that two or more triggers can be called together, and try to do INSERT together, but I think DB should easy solve this.
Table schema:
CREATE TABLE `CHANGES` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`field1` ENUM(...) NOT NULL,
`field2` BOOL NOT NULL DEFAULT FALSE,
`field3` VARCHAR(64) NOT NULL,
`field4` VARCHAR(255) NOT NULL,
`field5` TEXT,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
Trigger sample:
CREATE TRIGGER `tr_TABLE_insert` AFTER INSERT ON `TABLE`
FOR EACH ROW BEGIN
INSERT INTO `CHANGES` (`field1`, `field3`, `field4`, `field5`)
VALUES ("value1", "value3", "value4", "value5");
END
MySQL info:
MASTER: Server version: 5.5.43-37.2-log Percona Server (GPL), Release 37.2, Revision 6202fee
SLAVE: Server version: 5.5.55-38.8-log Percona Server (GPL), Release 38.8, Revision 11f5bbd