#At file:///home/daogangqu/mysql/bzrwork/bug45827/mysql-pe/ based on revid:jperkin@stripped
3605 Dao-Gang.Qu@stripped 2009-09-27
Bug #45827 Stmt using two autoinc values does not produce unsafe warning
One statement that have more than one different tables to update with
autoinc columns just was marked as unsafe in mixed mode, so the unsafe
warning can't be produced in statement mode.
To fix the problem, mark the statement as unsafe in statement mode too.
@ mysql-test/extra/rpl_tests/rpl_insert_id.test
The test case is updated due to a patch of bug#45827.
@ mysql-test/r/merge_recover.result
The test result is updated due to a patch of bug#45827.
@ mysql-test/suite/backup_ptr/t/backup_ptr_objects.test
The test case is updated due to a patch of bug#45827.
@ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
The test result is updated due to a patch of bug#45827.
@ mysql-test/suite/binlog/r/binlog_unsafe.result
Test result for bug#45827
@ mysql-test/suite/binlog/t/binlog_unsafe.test
Added test to verify if stmt that have more than one
different tables to update with autoinc columns will
produce unsafe warning
@ mysql-test/suite/rpl/t/rpl_trigger.test
The test case is updated due to a patch of bug#45827.
@ sql/sql_base.cc
Reomved the 'set_current_stmt_binlog_row_based_if_mixed' function
for producing unsafe warnings by executing 'decide_logging_format'
function later in statement mode.
modified:
mysql-test/extra/rpl_tests/rpl_insert_id.test
mysql-test/r/merge_recover.result
mysql-test/suite/backup_ptr/t/backup_ptr_objects.test
mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
mysql-test/suite/binlog/r/binlog_unsafe.result
mysql-test/suite/binlog/t/binlog_unsafe.test
mysql-test/suite/rpl/t/rpl_trigger.test
sql/sql_base.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_insert_id.test'
--- a/mysql-test/extra/rpl_tests/rpl_insert_id.test 2008-10-08 11:23:53 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_insert_id.test 2009-09-27 08:25:15 +0000
@@ -178,8 +178,9 @@ begin
set NEW.last_id = res;
end|
delimiter ;|
-
+--disable_warnings
insert into t1 (last_id) values (0);
+--enable_warnings
drop trigger t1_bi;
=== modified file 'mysql-test/r/merge_recover.result'
--- a/mysql-test/r/merge_recover.result 2009-08-14 11:04:35 +0000
+++ b/mysql-test/r/merge_recover.result 2009-09-27 08:25:15 +0000
@@ -15,6 +15,8 @@
#
set global table_open_cache=256;
set global table_definition_cache=256;
+Warnings:
+Warning 1292 Truncated incorrect table_definition_cache value: '256'
drop procedure if exists p_create;
create procedure p_create()
begin
=== modified file 'mysql-test/suite/backup_ptr/t/backup_ptr_objects.test'
--- a/mysql-test/suite/backup_ptr/t/backup_ptr_objects.test 2009-05-21 13:17:37 +0000
+++ b/mysql-test/suite/backup_ptr/t/backup_ptr_objects.test 2009-09-27 08:25:15 +0000
@@ -118,10 +118,12 @@ BEGIN
END;||
delimiter ;||
+--disable_warnings
INSERT INTO ptr_ob1.t2 VALUES (NULL, 'Normal Insert1');
INSERT INTO ptr_ob1.t2 VALUES (NULL, 'Normal Insert2');
INSERT INTO ptr_ob1.t2 VALUES (NULL, 'Normal Insert3');
INSERT INTO ptr_ob1.t2 VALUES (NULL, 'Normal Insert4');
+--enable_warnings
SELECT * FROM ptr_ob1.t2;
SELECT * FROM ptr_ob1.t3;
@@ -168,7 +170,9 @@ END;
||
delimiter ;||
+--disable_warnings
UPDATE ptr_ob2.t1 SET b='alert' WHERE id=3;
+--enable_warnings
SELECT * FROM ptr_ob2.t1;
SELECT * FROM ptr_ob1.t2;
SELECT * FROM ptr_ob1.v1;
@@ -220,13 +224,16 @@ CALL ptr_ob1.p1('procedure p1 called for
SELECT * FROM ptr_ob1.v1;
SELECT * FROM ptr_ob2.t1;
+--disable_warnings
CALL ptr_ob1.p2('procedure p2 called for trg2');
CALL ptr_ob1.p2('procedure p2 again called for trg2');
-
+--enable_warnings
SELECT * FROM ptr_ob1.t2;
SELECT * FROM ptr_ob1.t3;
+--disable_warnings
CALL ptr_ob1.p3('procedure p3 called for trg6');
+--enable_warnings
SELECT * FROM ptr_ob2.t1;
SELECT * FROM ptr_ob1.t2;
@@ -298,7 +305,9 @@ SELECT @a;
--echo Firing existing triggers and calling procedures.
+--disable_warnings
INSERT INTO ptr_ob1.t2 VALUES(NULL, 'Insert after backup');
+--enable_warnings
INSERT INTO ptr_ob2.t2 VALUES(10, 'af_bk1'),(2, 'af_bk2'),(7,'af_bk3'),(2,'af_bk4');
DELETE FROM ptr_ob2.t2 WHERE id=10;
SELECT @del_sum;
@@ -307,11 +316,15 @@ CALL ptr_ob1.p1('procedure p1 af_bkup fo
SELECT * FROM ptr_ob1.v1;
SELECT * FROM ptr_ob2.t1;
+--disable_warnings
CALL ptr_ob1.p2('procedure p2 called af_bkup for trg2');
+--enable_warnings
SELECT * FROM ptr_ob1.t2;
SELECT * FROM ptr_ob1.t3;
+--disable_warnings
CALL ptr_ob1.p3('procedure p3 called af_bkup for trg6');
+--enable_warnings
SELECT * FROM ptr_ob2.t1;
SELECT * FROM ptr_ob1.t2;
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2009-08-28 09:58:34 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result 2009-09-27 08:25:15 +0000
@@ -458,7 +458,11 @@ return n;
end|
reset master;
insert into t2 values (bug27417(1));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
insert into t2 select bug27417(2);
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
reset master;
insert into t2 values (bug27417(2));
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
@@ -475,6 +479,8 @@ select count(*) from t2;
count(*)
2
delete from t2 where a=bug27417(3);
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
select count(*) from t2 /* nothing got deleted */;
count(*)
2
@@ -488,6 +494,8 @@ count(*)
5
delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
affected rows: 0
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
select count(*) from t1 /* must be 7 */;
count(*)
7
@@ -702,7 +710,11 @@ return n;
end|
reset master;
insert into t2 values (bug27417(1));
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
insert into t2 select bug27417(2);
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
reset master;
insert into t2 values (bug27417(2));
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
@@ -718,6 +730,8 @@ select count(*) from t2;
count(*)
2
delete from t2 where a=bug27417(3);
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
select count(*) from t2 /* nothing got deleted */;
count(*)
2
@@ -730,6 +744,8 @@ count(*)
5
delete t2 from t2 where t2.a=bug27417(100) /* must not affect t2 */;
affected rows: 0
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
select count(*) from t1 /* must be 7 */;
count(*)
7
=== modified file 'mysql-test/suite/binlog/r/binlog_unsafe.result'
--- a/mysql-test/suite/binlog/r/binlog_unsafe.result 2009-07-28 14:16:37 +0000
+++ b/mysql-test/suite/binlog/r/binlog_unsafe.result 2009-09-27 08:25:15 +0000
@@ -300,4 +300,59 @@ Warnings:
Note 1592 Statement may not be safe to log in statement format.
DROP TABLE t1, t2;
SET @@SESSION.SQL_MODE = @save_sql_mode;
+CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE FUNCTION func_modify_t1 ()
+RETURNS INT
+BEGIN
+INSERT INTO t1 SET a = 1;
+RETURN 0;
+END|
+# The following statement causes auto-incrementation
+# of both t1 and t2. It is logged in statement format,
+# so it should produce unsafe warning.
+INSERT INTO t2 SET a = func_modify_t1();
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+SET SESSION binlog_format = MIXED;
+# Check if the statement is logged in row format.
+INSERT INTO t2 SET a = func_modify_t1();
+SHOW BINLOG EVENTS FROM 12283;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 12283 Query 1 12351 BEGIN
+master-bin.000001 12351 Table_map 1 12393 table_id: 43 (test.t2)
+master-bin.000001 12393 Table_map 1 12435 table_id: 44 (test.t1)
+master-bin.000001 12435 Write_rows 1 12473 table_id: 44
+master-bin.000001 12473 Write_rows 1 12511 table_id: 43 flags: STMT_END_F
+master-bin.000001 12511 Query 1 12580 COMMIT
+DROP TABLE t1,t2;
+DROP FUNCTION func_modify_t1;
+SET SESSION binlog_format = STATEMENT;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+create trigger tri_modify_two_tables before insert on t1 for each row begin
+insert into t2(a) values(new.a);
+insert into t3(a) values(new.a);
+end |
+# The following statement causes auto-incrementation
+# of both t2 and t3. It is logged in statement format,
+# so it should produce unsafe warning
+INSERT INTO t1 SET a = 1;
+Warnings:
+Note 1592 Statement may not be safe to log in statement format.
+SET SESSION binlog_format = MIXED;
+# Check if the statement is logged in row format.
+INSERT INTO t1 SET a = 2;
+SHOW BINLOG EVENTS FROM 13426;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 13426 Query 1 13494 BEGIN
+master-bin.000001 13494 Table_map 1 13535 table_id: 46 (test.t1)
+master-bin.000001 13535 Table_map 1 13577 table_id: 47 (test.t3)
+master-bin.000001 13577 Table_map 1 13619 table_id: 48 (test.t2)
+master-bin.000001 13619 Write_rows 1 13657 table_id: 48
+master-bin.000001 13657 Write_rows 1 13695 table_id: 47
+master-bin.000001 13695 Write_rows 1 13729 table_id: 46 flags: STMT_END_F
+master-bin.000001 13729 Query 1 13798 COMMIT
+DROP TABLE t1,t2,t3;
"End of tests"
=== modified file 'mysql-test/suite/binlog/t/binlog_unsafe.test'
--- a/mysql-test/suite/binlog/t/binlog_unsafe.test 2009-07-28 14:16:37 +0000
+++ b/mysql-test/suite/binlog/t/binlog_unsafe.test 2009-09-27 08:25:15 +0000
@@ -47,6 +47,7 @@
# BUG#34768: nondeterministic INSERT using LIMIT logged in stmt mode if binlog_format=mixed
# BUG#41980, SBL, INSERT .. SELECT .. LIMIT = ERROR, even when @@SQL_LOG_BIN is 0
# BUG#42640: mysqld crashes when unsafe statements are executed (STRICT_TRANS_TABLES mode)
+# Bug#45827: Stmt using two autoinc values does not produce unsafe warning
#
# ==== Related test cases ====
#
@@ -57,6 +58,8 @@
# rpl.rpl_variables_stm tests the small subset of variables that
# actually can be replicated safely in statement mode.
#
+# Tests if stmt that have more than one different tables to update
+# with autoinc columns will produce unsafe warning.
#
# ==== Todo ====
#
@@ -388,4 +391,73 @@ DELETE FROM t1 LIMIT 1;
DROP TABLE t1, t2;
SET @@SESSION.SQL_MODE = @save_sql_mode;
+
+#
+# BUG#45827
+# The test verifies if stmt that have more than one
+# different tables to update with autoinc columns
+# will produce unsafe warning
+#
+
+# Test case1: stmt that have more than one different tables
+# to update with autoinc columns should produce
+# unsafe warning when calling a function
+CREATE TABLE t1 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+
+# The purpose of this function is to insert into t1 so that the second
+# column is auto_increment'ed.
+DELIMITER |;
+CREATE FUNCTION func_modify_t1 ()
+RETURNS INT
+BEGIN
+ INSERT INTO t1 SET a = 1;
+ RETURN 0;
+END|
+DELIMITER ;|
+--echo # The following statement causes auto-incrementation
+--echo # of both t1 and t2. It is logged in statement format,
+--echo # so it should produce unsafe warning.
+INSERT INTO t2 SET a = func_modify_t1();
+
+SET SESSION binlog_format = MIXED;
+--echo # Check if the statement is logged in row format.
+let $pos0_master= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t2 SET a = func_modify_t1();
+eval SHOW BINLOG EVENTS FROM $pos0_master;
+
+# clean up
+DROP TABLE t1,t2;
+DROP FUNCTION func_modify_t1;
+
+# Test case2: stmt that have more than one different tables
+# to update with autoinc columns should produce
+# unsafe warning when invoking a trigger
+SET SESSION binlog_format = STATEMENT;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+CREATE TABLE t3 (a INT, b INT PRIMARY KEY AUTO_INCREMENT);
+
+# The purpose of this function is to insert into t1 so that the second
+# column is auto_increment'ed.
+delimiter |;
+create trigger tri_modify_two_tables before insert on t1 for each row begin
+ insert into t2(a) values(new.a);
+ insert into t3(a) values(new.a);
+end |
+delimiter ;|
+--echo # The following statement causes auto-incrementation
+--echo # of both t2 and t3. It is logged in statement format,
+--echo # so it should produce unsafe warning
+INSERT INTO t1 SET a = 1;
+
+SET SESSION binlog_format = MIXED;
+--echo # Check if the statement is logged in row format.
+let $pos1_master= query_get_value(SHOW MASTER STATUS, Position, 1);
+INSERT INTO t1 SET a = 2;
+eval SHOW BINLOG EVENTS FROM $pos1_master;
+
+# clean up
+DROP TABLE t1,t2,t3;
+
--echo "End of tests"
=== modified file 'mysql-test/suite/rpl/t/rpl_trigger.test'
--- a/mysql-test/suite/rpl/t/rpl_trigger.test 2009-08-03 15:10:14 +0000
+++ b/mysql-test/suite/rpl/t/rpl_trigger.test 2009-09-27 08:25:15 +0000
@@ -38,10 +38,12 @@ insert into t3 values(100,"log",0,0,0);
SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;
# Emulate that we have rows 2-9 deleted on the slave
+--disable_warnings
insert into t1 values(1,1,rand()),(NULL,2,rand());
insert into t2 (b) values(last_insert_id());
insert into t2 values(3,0),(NULL,0);
insert into t2 values(NULL,0),(500,0);
+--enable_warnings
select a,b, truncate(rand_value,4) from t1;
select * from t2;
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2009-09-14 16:03:38 +0000
+++ b/sql/sql_base.cc 2009-09-27 08:25:15 +0000
@@ -4995,14 +4995,14 @@ bool lock_tables(THD *thd, TABLE_LIST *t
{
/*
If we have >= 2 different tables to update with auto_inc columns,
- statement-based binlogging won't work. We can solve this problem in
+ statement-based binlogging will make the autoinc values become
+ inconsistent on master and slave. We can solve this problem in
mixed mode by switching to row-based binlogging:
*/
- if (thd->variables.binlog_format == BINLOG_FORMAT_MIXED &&
+ if (thd->variables.binlog_format != BINLOG_FORMAT_ROW &&
has_two_write_locked_tables_with_auto_increment(tables))
{
thd->lex->set_stmt_unsafe();
- thd->set_current_stmt_binlog_row_based_if_mixed();
}
}
Attachment: [text/bzr-bundle] bzr/dao-gang.qu@sun.com-20090927082515-chcysgf6c3nkj0w9.bundle
| Thread |
|---|
| • bzr commit into mysql-6.0 branch (Dao-Gang.Qu:3605) Bug#45827 | Dao-Gang.Qu | 27 Sep |
