Description:
I can see unsafe conditions for SBR in detail in this page.
http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html
But the page is for Mixed format, so I should add the link to this page from the SBR manual page at first ? Especial this case don't generate a warning, so it is very difficult to find this condition is unsafe.
"Prior to MySQL 5.1.40, when two or more tables with AUTO_INCREMENT columns are updated. As of 5.1.40, when one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Unlike other unsafe statements, this does not generate a warning if binlog_format = STATEMENT. "
And this desciption don't show any example.
And if this description show an example, in order to understand situation,
I need some prereq knowledges about auto_increment and SBR. But I cannot
find the descriptions about them. So I suggest to add prereq knowledges
about auto_increment and SBR.
(1) auto_increment's value doesn't rollback.
(2) auto_increment's value is incremented even if an error occurred after its evaluated.
(3) (1) and (2) makes some gaps values stored in auto_increment colum.
(4) SBR can replicate only one value generated by auto_increment colum per statement with 'SET INSERT_ID'.
From (1) to (4) should be added descriptions in Reference Manual,
After adding that, we should add simple example for 'As of 5.1.40, ...'.
How to repeat:
An example for (1)
master [localhost] {msandbox} (test) > create table a(i1 int primary key auto_increment, i2 int, unique(i2)) engine = innodb;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > insert into a(i2) values(1);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > rollback;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > begin;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > insert into a(i2) values(1);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > commit;
Query OK, 0 rows affected (0.05 sec)
master [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 | <- i1 did not rollback.
+----+------+
1 row in set (0.00 sec)
An example for (2) (cont'd (1))
master [localhost] {msandbox} (test) > insert into a(i2) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'i2'
master [localhost] {msandbox} (test) > insert into a(i2) values(2);
Query OK, 1 row affected (0.01 sec)
master [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 |
| 4 | 2 | <- i1 = 3 is lost.
+----+------+
2 rows in set (0.00 sec)
An example for (3) is already shown by (1),(2).
An example for (4)
These operation (1),(2) are replicated to slave correctly.
slave1 [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 |
| 4 | 2 |
+----+------+
2 rows in set (0.00 sec)
We can see statement-base binary log with 'SET INSERT_ID=value' for the value generated by auto_increment.
#100825 17:29:21 server id 1 end_log_pos 5925 Intvar
SET INSERT_ID=2/*!*/;
# at 5925
#100825 17:29:21 server id 1 end_log_pos 6015 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1282724961/*!*/;
insert into a(i2) values(1)
/*!*/;
# at 6015
#100825 17:29:23 server id 1 end_log_pos 6042 Xid = 218
COMMIT/*!*/;
# at 6042
#100825 17:33:00 server id 1 end_log_pos 6110 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1282725180/*!*/;
BEGIN
/*!*/;
# at 6110
#100825 17:31:01 server id 1 end_log_pos 6138 Intvar
SET INSERT_ID=4/*!*/;
# at 6138
#100825 17:31:01 server id 1 end_log_pos 6228 Query thread_id=33 exec_time=0 error_code=0
SET TIMESTAMP=1282725061/*!*/;
insert into a(i2) values(2)
/*!*/;
An example for 'As of 5.1.40,'...
master [localhost] {msandbox} (test) > create table b(i1 int primary key auto_increment, i2 int) engine = innodb;
Query OK, 0 rows affected (0.04 sec)
master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > delimiter |
master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > create trigger a_t before insert on a
-> for each row
-> BEGIN
-> INSERT INTO b(i2) values(NEW.i2);
-> END|
Query OK, 0 rows affected (0.06 sec)
master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > delimiter ;
master [localhost] {msandbox} (test) >
master [localhost] {msandbox} (test) > insert into a(i2) values(3);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > commit;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 |
| 4 | 2 |
| 5 | 3 |
+----+------+
3 rows in set (0.00 sec)
Slave is the same.
slave1 [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 |
| 4 | 2 |
| 5 | 3 |
+----+------+
3 rows in set (0.00 sec)
But once auto_increment column has a gap, master and slave are different.
master [localhost] {msandbox} (test) > insert into a(i2) values(3);
ERROR 1062 (23000): Duplicate entry '3' for key 'i2'
master [localhost] {msandbox} (test) > insert into a(i2) values(4);
Query OK, 1 row affected (0.00 sec)
master [localhost] {msandbox} (test) > commit;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 |
| 4 | 2 |
| 5 | 3 |
| 7 | 4 |
+----+------+
4 rows in set (0.00 sec)
slave1 [localhost] {msandbox} (test) > select * from a;
+----+------+
| i1 | i2 |
+----+------+
| 2 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 | <- different value !
+----+------+
4 rows in set (0.00 sec)
Suggested fix:
Add documents 'How to repeat:' section.
(or add better documents :)
