[Commits] d95fb71: MDEV-6997: SET STATEMENT last_insert_id FOR ... does not affect the value written to the binlog

sanja at mariadb.com sanja at mariadb.com
Fri Mar 13 21:42:01 EET 2015


revision-id: d95fb711ea99f32d4d09514024319f7b7b0205e0
parent(s): ba3573cae8f0d269452cc6213f1a8243e455830e
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-03-13 20:41:58 +0100
message:

MDEV-6997: SET STATEMENT last_insert_id FOR ... does not affect the value written to the binlog

Problem was in reinitialization of first_successful_insert_id_in_prev_stmt_for_binlog after setting variables.

---
 mysql-test/r/set_statement.result                  |  2 -
 .../r/set_statement_notembedded_binlog.result      | 89 ++++++++++++++++++++++
 mysql-test/t/set_statement.test                    |  2 -
 mysql-test/t/set_statement_notembedded_binlog.test | 65 ++++++++++++++++
 sql/sql_parse.cc                                   | 12 +++
 sql/sys_vars.cc                                    |  2 +-
 6 files changed, 167 insertions(+), 5 deletions(-)

diff --git a/mysql-test/r/set_statement.result b/mysql-test/r/set_statement.result
index f302975..699e190 100644
--- a/mysql-test/r/set_statement.result
+++ b/mysql-test/r/set_statement.result
@@ -1128,8 +1128,6 @@ set statement rand_seed2=default for select 1;
 ERROR 42000: The system variable rand_seed2 cannot be set in SET STATEMENT.
 set statement skip_replication=default for select 1;
 ERROR 42000: The system variable skip_replication cannot be set in SET STATEMENT.
-set statement last_insert_id=1 for select 1;
-ERROR 42000: The system variable last_insert_id cannot be set in SET STATEMENT.
 set statement sql_log_off=default for select 1;
 ERROR 42000: The system variable sql_log_off cannot be set in SET STATEMENT.
 set statement character_set_client=default for select 1;
diff --git a/mysql-test/r/set_statement_notembedded_binlog.result b/mysql-test/r/set_statement_notembedded_binlog.result
index 9d201b7..f23d4e9 100644
--- a/mysql-test/r/set_statement_notembedded_binlog.result
+++ b/mysql-test/r/set_statement_notembedded_binlog.result
@@ -17,3 +17,92 @@ x	x	x	x	x	COMMIT
 x	x	x	x	x	BEGIN GTID 20-1-1
 x	x	x	x	x	use `test`; set statement gtid_domain_id = 20 for insert into t1 values (3),(4)
 drop table t1;
+reset master;
+SET @a=11;
+create table t1 (a int not null auto_increment, c int, d int, primary key (a));
+create table t2 (b int);
+insert into t2 values (1),(2);
+CREATE function f1() returns int
+BEGIN
+SET STATEMENT last_insert_id=@a for insert into t1 values (NULL, @a,
+last_insert_id());
+SET @a:=@a*100+13;
+return @a;
+end|
+call mtr.add_suppression("Unsafe statement written to the binary log using");
+select f1() from t2;
+f1()
+1113
+111313
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
+show binlog events limit 16, 100;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+x	x	x	x	x	LAST_INSERT_ID=0
+x	x	x	x	x	INSERT_ID=1
+x	x	x	x	x	@`a`=11
+x	x	x	x	x	@`a`=11
+x	x	x	x	x	use `test`; SELECT `test`.`f1`()
+x	x	x	x	x	LAST_INSERT_ID=0
+x	x	x	x	x	INSERT_ID=2
+x	x	x	x	x	@`a`=1113
+x	x	x	x	x	@`a`=1113
+x	x	x	x	x	use `test`; SELECT `test`.`f1`()
+x	x	x	x	x	COMMIT
+select * from t1;
+a	c	d
+1	11	11
+2	1113	1113
+drop function f1;
+drop table t1,t2;
+reset master;
+SET @a=11;
+create table t1 (a int not null auto_increment, c int, d int, primary key (a));
+create table t2 (b int);
+insert into t2 values (1),(2);
+CREATE function f1() returns int
+BEGIN
+SET @save= @@last_insert_id;
+SET session last_insert_id=@a;
+insert into t1 values (NULL, @a, last_insert_id());
+SET session last_insert_id=@save;
+SET @a:=@a*100+13;
+return @a;
+end|
+select f1() from t2;
+f1()
+1113
+111313
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly.
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
+show binlog events limit 13, 100;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+x	x	x	x	x	LAST_INSERT_ID=0
+x	x	x	x	x	INSERT_ID=1
+x	x	x	x	x	@`a`=11
+x	x	x	x	x	@`save`=0
+x	x	x	x	x	use `test`; SELECT `test`.`f1`()
+x	x	x	x	x	LAST_INSERT_ID=0
+x	x	x	x	x	INSERT_ID=2
+x	x	x	x	x	@`a`=1113
+x	x	x	x	x	@`save`=0
+x	x	x	x	x	use `test`; SELECT `test`.`f1`()
+x	x	x	x	x	COMMIT
+select * from t1;
+a	c	d
+1	11	11
+2	1113	1113
+drop function f1;
+drop table t1,t2;
+reset master;
+set statement last_insert_id = 112 for create table t1 as select last_insert_id();
+show binlog events limit 4,1;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+x	x	x	x	x	LAST_INSERT_ID=112
+drop table t1;
diff --git a/mysql-test/t/set_statement.test b/mysql-test/t/set_statement.test
index 29ed9d0..2939767 100644
--- a/mysql-test/t/set_statement.test
+++ b/mysql-test/t/set_statement.test
@@ -1056,8 +1056,6 @@ set statement rand_seed2=default for select 1;
 --error ER_SET_STATEMENT_NOT_SUPPORTED
 set statement skip_replication=default for select 1;
 --error ER_SET_STATEMENT_NOT_SUPPORTED
-set statement last_insert_id=1 for select 1;
---error ER_SET_STATEMENT_NOT_SUPPORTED
 set statement sql_log_off=default for select 1;
 --error ER_SET_STATEMENT_NOT_SUPPORTED
 set statement character_set_client=default for select 1;
diff --git a/mysql-test/t/set_statement_notembedded_binlog.test b/mysql-test/t/set_statement_notembedded_binlog.test
index c9c2334..62c351e 100644
--- a/mysql-test/t/set_statement_notembedded_binlog.test
+++ b/mysql-test/t/set_statement_notembedded_binlog.test
@@ -20,3 +20,68 @@ set statement gtid_domain_id = 20 for insert into t1 values (3),(4);
 show binlog events limit 5,5;
 
 drop table t1;
+
+reset master;
+SET @a=11;
+
+create table t1 (a int not null auto_increment, c int, d int, primary key (a));
+create table t2 (b int);
+insert into t2 values (1),(2);
+
+DELIMITER |;
+CREATE function f1() returns int
+BEGIN
+  SET STATEMENT last_insert_id=@a for insert into t1 values (NULL, @a,
+  last_insert_id());
+  SET @a:=@a*100+13;
+  return @a;
+end|
+DELIMITER ;|
+
+
+call mtr.add_suppression("Unsafe statement written to the binary log using");
+select f1() from t2;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x
+show binlog events limit 16, 100;
+
+select * from t1;
+
+drop function f1;
+drop table t1,t2;
+
+
+reset master;
+SET @a=11;
+
+create table t1 (a int not null auto_increment, c int, d int, primary key (a));
+create table t2 (b int);
+insert into t2 values (1),(2);
+
+DELIMITER |;
+CREATE function f1() returns int
+BEGIN
+  SET @save= @@last_insert_id;
+  SET session last_insert_id=@a;
+  insert into t1 values (NULL, @a, last_insert_id());
+  SET session last_insert_id=@save;
+  SET @a:=@a*100+13;
+  return @a;
+end|
+DELIMITER ;|
+
+select f1() from t2;
+
+--replace_column 1 x 2 x 3 x 4 x 5 x
+show binlog events limit 13, 100;
+
+select * from t1;
+
+drop function f1;
+drop table t1,t2;
+
+reset master;
+set statement last_insert_id = 112 for create table t1 as select last_insert_id();
+--replace_column 1 x 2 x 3 x 4 x 5 x
+show binlog events limit 4,1;
+drop table t1;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 0837a70..30c4416 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2743,6 +2743,18 @@ static bool do_execute_sp(THD *thd, sp_head *sp)
       lex->restore_set_statement_var();
       goto error;
     }
+    /*
+      The value of last_insert_id is remembered in THD to be written to binlog
+      when it's used *the first time* in the statement. But SET STATEMENT
+      must read the old value of last_insert_id to be able to restore it at
+      the end. This should not count at "reading of last_insert_id" and
+      should not remember last_insert_id for binlog. That is, it should clear
+      stmt_depends_on_first_successful_insert_id_in_prev_stmt flag.
+    */
+    if (!thd->in_sub_stmt)
+    {
+      thd->stmt_depends_on_first_successful_insert_id_in_prev_stmt= 0;
+    }
   }
 
   if (thd->lex->mi.connection_name.str == NULL)
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index a65e9ba..0762e44 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -3759,7 +3759,7 @@ static ulonglong read_last_insert_id(THD *thd)
 }
 static Sys_var_session_special Sys_last_insert_id(
        "last_insert_id", "The value to be returned from LAST_INSERT_ID()",
-       NO_SET_STMT sys_var::ONLY_SESSION, NO_CMD_LINE,
+       sys_var::ONLY_SESSION, NO_CMD_LINE,
        VALID_RANGE(0, ULONGLONG_MAX), BLOCK_SIZE(1),
        NO_MUTEX_GUARD, IN_BINLOG, ON_CHECK(0),
        ON_UPDATE(update_last_insert_id), ON_READ(read_last_insert_id));


More information about the commits mailing list