Below is the list of changes that have just been committed into a local
5.0 repository of guilhem. When guilhem does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet
1.2191 06/06/26 22:56:44 guilhem@stripped +3 -0
For for BUG#20341 "stored function inserting into one auto_increment puts
bad data in slave". The bug was that a stored function inserting into
one auto_increment column inserts auto_increment values different from those
inserted on master.
The fix is to do thd->next_insert_id=0 in reset_sub_statement_state()
only if thd->clear_next_insert_id is true. There is however a subtility
explained in the comment of sql_class.cc.
sql/sql_class.cc
1.236 06/06/26 22:56:40 guilhem@stripped +23 -1
In a slave replication thread:
When calling a stored function we used to reset next_insert_id to 0,
this cancelled INSERT_ID events, breaking replication.
A simple fix was to not do this resetting, but in some cases (non-slave
thread), when a function inserts into a table different from where caller
inserted, we must reset next_insert_id to 0.
Another simple fix was to reset only if clear_next_insert_id was set; it
is a good logical fix.
However, it breaks "broken statement-based binlogging", that is, with this
fix, SBB of a function inserting into two auto_increment columns, which
is broken (BUG#19630) becomes more likely to break (e.g. rpl_insert_id
does). So we make the fix a bit more complicate:
assume a slave thread reading executing a function call: it is either
- SELECT|DO func(), or
- STMT ... func() where STMT is of type "update" (INSERT, UPDATE, etc)
Assume the event has an INSERT_ID event with it.
In the first case, the INSERT_ID is about rows inserted by the function.
In the second case, it is about rows inserted by STMT.
So in the second case, we must unconditionally reset next_insert_id for
the function, so that the function does not use the INSERT_ID which belongs
to STMT.
mysql-test/t/rpl_insert_id.test
1.17 06/06/26 22:56:40 guilhem@stripped +39 -9
adding "tags" to every line, so that we know in the result file which query
inserted what (this tests uses triggers so it tends to be complicated).
Adding testcase for BUG#20341
mysql-test/r/rpl_insert_id.result
1.15 06/06/26 22:56:40 guilhem@stripped +51 -15
result update
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: guilhem
# Host: gbichot3.local
# Root: /home/mysql_src/mysql-5.0-20524
--- 1.235/sql/sql_class.cc 2006-06-16 11:05:44 +02:00
+++ 1.236/sql/sql_class.cc 2006-06-26 22:56:40 +02:00
@@ -2062,11 +2062,33 @@
backup->savepoints= transaction.savepoints;
if (!lex->requires_prelocking() || is_update_query(lex->sql_command))
+ {
options&= ~OPTION_BIN_LOG;
+ /*
+ On master, this function call didn't go to binlog (the caller probably
+ did). So on slave, the INSERT_ID found in binlog is not about it (but
+ probably about the caller which inserted into some other
+ auto_increment): forget about it.
+ Note that a stored function/trigger inserting into two auto_increment
+ column potentially has broken statement-based replication anyway, but we
+ try to make problems the least likely.
+ */
+ next_insert_id= 0;
+ }
+ else
+ {
+ /*
+ On master, this query went to binlog. So on slave the INSERT_ID
+ found in the binlog is about it: do not forget about it (we know if the
+ INSERT_ID was found by testing clear_next_insert_id).
+ */
+ if (clear_next_insert_id)
+ next_insert_id= 0;
+ }
+
/* Disable result sets */
client_capabilities &= ~CLIENT_MULTI_RESULTS;
in_sub_stmt|= new_state;
- next_insert_id= 0;
insert_id_used= 0;
examined_row_count= 0;
sent_row_count= 0;
--- 1.14/mysql-test/r/rpl_insert_id.result 2006-04-21 16:54:56 +02:00
+++ 1.15/mysql-test/r/rpl_insert_id.result 2006-06-26 22:56:40 +02:00
@@ -80,21 +80,23 @@
create table t1 (
id int not null auto_increment,
last_id int,
+tag char(2),
primary key (id)
);
create function bug15728() returns int(11)
return last_insert_id();
-insert into t1 (last_id) values (0);
-insert into t1 (last_id) values (last_insert_id());
-insert into t1 (last_id) values (bug15728());
+insert into t1 (last_id,tag) values (0,"A");
+insert into t1 (last_id,tag) values (last_insert_id(),"B");
+insert into t1 (last_id,tag) values (bug15728(),"C");
create table t2 (
id int not null auto_increment,
last_id int,
+tag char(2),
primary key (id)
);
create function bug15728_insert() returns int(11) modifies sql data
begin
-insert into t2 (last_id) values (bug15728());
+insert into t2 (last_id,tag) values (bug15728(),@tag);
return bug15728();
end|
create trigger t1_bi before insert on t1 for each row
@@ -103,32 +105,66 @@
select bug15728_insert() into res;
set NEW.last_id = res;
end|
-insert into t1 (last_id) values (0);
+set @tag="D";
+insert into t1 (last_id,tag) values (0,@tag);
drop trigger t1_bi;
select last_insert_id();
last_insert_id()
4
+set @tag="E";
select bug15728_insert();
bug15728_insert()
2
select last_insert_id();
last_insert_id()
4
-insert into t1 (last_id) values (bug15728());
+set @tag="F";
+insert into t1 (last_id,tag) values (bug15728(),@tag);
select last_insert_id();
last_insert_id()
5
select * from t1;
-id last_id
-1 0
-2 1
-3 2
-4 1
-5 4
+id last_id tag
+1 0 A
+2 1 B
+3 2 C
+4 1 D
+5 4 F
select * from t2;
-id last_id
-1 3
-2 4
+id last_id tag
+1 3 D
+2 4 E
+select * from t1;
+id last_id tag
+1 0 A
+2 1 B
+3 2 C
+4 1 D
+5 4 F
+select * from t2;
+id last_id tag
+1 3 D
+2 4 E
drop function bug15728;
drop function bug15728_insert;
+truncate table t2;
+create function insid() returns int
+begin
+insert into t2 (last_id,tag) values (0,@tag);
+return 0;
+end|
+set sql_log_bin=0;
+insert into t2 values(1,0,0),(2,0,0),(3,0,0);
+delete from t2;
+set sql_log_bin=1;
+set @tag="G";
+select insid();
+insid()
+0
+select * from t2;
+id last_id tag
+4 0 G
+select * from t2;
+id last_id tag
+4 0 G
drop table t1, t2;
--- 1.16/mysql-test/t/rpl_insert_id.test 2006-05-29 12:45:15 +02:00
+++ 1.17/mysql-test/t/rpl_insert_id.test 2006-06-26 22:56:40 +02:00
@@ -95,25 +95,27 @@
create table t1 (
id int not null auto_increment,
last_id int,
+ tag char(2),
primary key (id)
);
create function bug15728() returns int(11)
return last_insert_id();
-insert into t1 (last_id) values (0);
-insert into t1 (last_id) values (last_insert_id());
-insert into t1 (last_id) values (bug15728());
+insert into t1 (last_id,tag) values (0,"A");
+insert into t1 (last_id,tag) values (last_insert_id(),"B");
+insert into t1 (last_id,tag) values (bug15728(),"C");
# Check that nested call replicates too.
create table t2 (
id int not null auto_increment,
last_id int,
+ tag char(2),
primary key (id)
);
delimiter |;
create function bug15728_insert() returns int(11) modifies sql data
begin
- insert into t2 (last_id) values (bug15728());
+ insert into t2 (last_id,tag) values (bug15728(),@tag);
return bug15728();
end|
create trigger t1_bi before insert on t1 for each row
@@ -124,27 +126,55 @@
end|
delimiter ;|
-insert into t1 (last_id) values (0);
+set @tag="D";
+insert into t1 (last_id,tag) values (0,@tag);
drop trigger t1_bi;
# Check that nested call doesn't affect outer context.
select last_insert_id();
+set @tag="E";
select bug15728_insert();
select last_insert_id();
-insert into t1 (last_id) values (bug15728());
+set @tag="F";
+insert into t1 (last_id,tag) values (bug15728(),@tag);
# This should be exactly one greater than in the previous call.
select last_insert_id();
-save_master_pos;
-connection slave;
-sync_with_master;
+select * from t1;
+select * from t2;
+sync_slave_with_master;
select * from t1;
select * from t2;
connection master;
drop function bug15728;
drop function bug15728_insert;
+
+# Test for BUG#20341 "stored function inserting into one
+# auto_increment puts bad data in slave"
+
+truncate table t2;
+
+delimiter |;
+create function insid() returns int
+begin
+ insert into t2 (last_id,tag) values (0,@tag);
+ return 0;
+end|
+delimiter ;|
+set sql_log_bin=0;
+insert into t2 values(1,0,0),(2,0,0),(3,0,0);
+delete from t2;
+set sql_log_bin=1;
+set @tag="G";
+select insid();
+select * from t2;
+
+sync_slave_with_master;
+select * from t2;
+
+connection master;
drop table t1, t2;
# End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (guilhem:1.2191) BUG#20341 | guilhem | 26 Jun |
