MySQL Bugs: #48608: mysql replication slave trigger auto

Bug #48608	mysql replication slave trigger auto_increment columns
Submitted:	6 Nov 2009 19:20	Modified:	24 Feb 2010 20:58
Reporter:	Ben R	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.40, 5.0, 5.1, next-mr bzr	OS:	Linux
Assigned to:	Alfranio Junior	CPU Architecture:	Any
Tags:	auto_increment replication

Description:
auto_inc values on a slave table that is inserted into by a trigger use the wrong insert_id.

How to repeat:
Setup:
*set up master-slave replication with 1 master and 1 slave.

*on master
CREATE TABLE `table1` (
  `some_value` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
for this example I used (any non-default 1,1 values will work):
auto_increment_increment = 5
auto_increment_offset = 2

*on slave
(table1 should already exist if replicated properly)
CREATE TABLE `table2` (
  `table2_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `some_value` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`table2_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

*run a few insert into table1 (some_value) VALUES (1) from the master

*Resulting Problem: Rows inserted into table2 will have incorrect auto_inc values

Suggested fix:
Make it so that tables inserted into on slave triggers use the right auto_increment_increment

I should have added as clarification: the reason this all occurs is because in the bin logs, when the server is "changed", the statement is used in the bin logs

SET @@session.auto_increment_increment=5, @@session.auto_increment_offset=2/*!*/;

This also causes additional weirdness when you have master-master replication where one of the masters also has a slave. Because the auto_increment_increment only changed when the server_id changes in the binlogs, the auto_inc ids of table2 in the example above have values of both master's auto_increment_offset values.

I forgot to include the trigger. You probably could have just guessed this, but here is an example:

DROP TRIGGER /*!50114 IF EXISTS */ table1_insert;
DELIMITER //
CREATE TRIGGER table1_insert
AFTER INSERT ON table1
FOR EACH ROW
BEGIN

INSERT IGNORE INTO table2
SET some_value = 2;

END //
DELIMITER ;

Thank you for the report.

Verified as described. Probably documentation issue really.

Test case for MTR:

--source include/master-slave.inc

CREATE TABLE `table1` (
  `some_value` int(11) NOT NULL DEFAULT '0'
) DEFAULT CHARSET=latin1;

set auto_increment_increment = 5;
set auto_increment_offset = 2;

connection slave;

sleep 1;

CREATE TABLE `table2` (
  `table2_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `some_value` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`table2_id`)
) AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
;

DELIMITER //;
CREATE TRIGGER table1_insert
AFTER INSERT ON table1
FOR EACH ROW
BEGIN

INSERT IGNORE INTO table2
SET some_value = 2;

END //
DELIMITER ;//

connection master;

insert into table1 (some_value) VALUES (1);
insert into table1 (some_value) VALUES (1);
insert into table1 (some_value) VALUES (1);
insert into table1 (some_value) VALUES (1);

connection slave;

sleep 1;

select * from table2;

Workaround: reset auto_increment_increment and auto_increment_offset in TRIGGER

Zhenxing, Alfranio and Sven discussed this on IRC. And propose the following fix:

(1) Create a new event type that holds (1) the table name; (2) the insert_id for that table.

(2) Create a new variable, say INSERT_ID_LIST, that holds a list of table names and insert_id's. Example: INSERT_ID_LIST=t1:47,t2:11. This should be cleared after each top-level statement. This should fix most problems with autoinc when binlog_format=STATEMENT.

(3) Mark all statements that insert into autoinc columns as unsafe. Autoinc will never work for sure when binlog_format=STATEMENT, because the optimizer may re-order operations so that two rows in the same table are inserted in a different order on master and slave.

See thread "To discuss on replication call 090916: BUG#45677 and INSERT_ID".

See also BUG##45677.

The bug issue is actually in many ways a dup of Bug #45677.
The essence is in that stored routines can not have even just one auto-inc field
w/o sacrificing replication consistency.
Possibility to refine Bug #45677 fixes to provide unlimited support for auto-inc is viewed to me as rather theoretic.

Please, ignore the comment "[21 Dec 2009 10:40] Alfranio Correia".

After analyzing the problems related to auto_increment columns and discussing
with the replication team, we realized that the solution proposed in "[21 Dec 2009 10:40] Alfranio Correia" does not fix all problems and may be infeasible
to implement.

See also bugs: BUG#45677 and BUG#50440.

This is not a bug as the slave is just trying to mimic the master's behavior.
Unfortunately, this is a limitation of the server. If we had created the same table (i.e. `table2`) on the master, the result would be the same:

select * from table2;
table2_id       some_value
2       2
7       2
12      2
17      2

"This happens because is not possible to confine the effects of these two
variables to a single table, and thus they do not take the place of the
sequences offered by some other database management systems; these
variables control the behavior of all AUTO_INCREMENT columns in all tables
on the MySQL server. If the global value of either variable is set, its
effects persist until the global value is changed or overridden by setting
the session value, or until mysqld is restarted. If the local value is set,
the new value affects AUTO_INCREMENT columns for all tables into which new
rows are inserted by the current user for the duration of the session,
unless the values are changed during that session."

See http://tinyurl.com/y9vquwp for further information.

So, in order to get the following result

select * from table2;
table2_id       some_value
1       2
2       2
3       2
4       2

we need to change the trigger as follows

DELIMITER //;
CREATE TRIGGER table1_insert
AFTER INSERT ON table1
FOR EACH ROW
BEGIN

set auto_increment_increment = 1;
set auto_increment_offset = 1;

INSERT IGNORE INTO table2
SET some_value = 2;

END //
DELIMITER ;//

See bug #50440 | bug #53079 | bug #45677 | bug #42415