#At file:///Users/thek/Development/50-bug42108/ based on revid:sergey.glukhov@stripped
2809 Kristofer Pettersson 2009-09-22
Bug#42108 Wrong locking for UPDATE with subqueries leads to broken statement
replication
MySQL server uses wrong lock type (always TL_READ instead of
TL_READ_NO_INSERT when appropriate) for tables used in
subqueries of UPDATE statement. This leads in some cases to
a broken replication as statements are written in the wrong
order to the binlog.
The patch explicitly sets the lock level to TL_READ_NO_INSERT
for any subselect following an UPDATE-statement.
@ mysql-test/r/binlog.result
* Added test case for bug 42108
@ mysql-test/t/binlog.test
* Added test case for bug 42108
@ sql/sql_yacc.yy
* Set lock_option to either TL_READ_NO_INSERT or
TL_READ for any sub-SELECT following UPDATE.
* Changed line adjusted for parser identation
rules; code begins at column 13.
modified:
mysql-test/r/binlog.result
mysql-test/t/binlog.test
sql/sql_yacc.yy
=== modified file 'mysql-test/r/binlog.result'
--- a/mysql-test/r/binlog.result 2009-05-31 03:26:58 +0000
+++ b/mysql-test/r/binlog.result 2009-09-22 12:03:23 +0000
@@ -653,4 +653,23 @@ DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
+#
+# Bug 42108 Wrong locking for UPDATE with subqueries leads to broken
+# statement replication
+#
+RESET MASTER;
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (j int);
+INSERT INTO t2 VALUES (1);
+UPDATE t1 SET i=10 WHERE sleep(5) + 1 AND (SELECT count(*) FROM t2 WHERE j = i);
+INSERT INTO t2 VALUES (2);
+** Last row should be INSERT INTO t2 VALUES (2) or the binlog order is
+** mixed up!
+use `test`; INSERT INTO t2 VALUES (2)
+SELECT * FROM t1;
+i
+10
+2
+DROP TABLE t1,t2;
End of 5.0 tests
=== modified file 'mysql-test/t/binlog.test'
--- a/mysql-test/t/binlog.test 2009-03-25 16:48:10 +0000
+++ b/mysql-test/t/binlog.test 2009-09-22 12:03:23 +0000
@@ -201,4 +201,32 @@ DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
+--source include/have_log_bin.inc
+--echo #
+--echo # Bug 42108 Wrong locking for UPDATE with subqueries leads to broken
+--echo # statement replication
+--echo #
+RESET MASTER;
+connect (con1, localhost, root,,);
+connection default;
+CREATE TABLE t1 (i int);
+INSERT INTO t1 VALUES (1), (2);
+CREATE TABLE t2 (j int);
+INSERT INTO t2 VALUES (1);
+# Using sleep here to emulate long running update or unfortunate scheduling
+send;
+UPDATE t1 SET i=10 WHERE sleep(5) + 1 AND (SELECT count(*) FROM t2 WHERE j = i);
+connection con1;
+--sleep 2
+INSERT INTO t2 VALUES (2);
+connection default;
+--reap
+--echo ** Last row should be INSERT INTO t2 VALUES (2) or the binlog order is
+--echo ** mixed up!
+let $value= query_get_value(SHOW BINLOG EVENTS, Info, 7);
+echo $value;
+SELECT * FROM t1;
+DROP TABLE t1,t2;
+disconnect con1;
+
--echo End of 5.0 tests
=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy 2009-08-27 10:22:19 +0000
+++ b/sql/sql_yacc.yy 2009-09-22 12:03:23 +0000
@@ -7810,7 +7810,7 @@ update:
LEX *lex= Lex;
mysql_init_select(lex);
lex->sql_command= SQLCOM_UPDATE;
- lex->lock_option= TL_UNLOCK; /* Will be set later */
+ lex->lock_option= using_update_log ? TL_READ_NO_INSERT : TL_READ;
lex->duplicates= DUP_ERROR;
}
opt_low_priority opt_ignore join_table_list
Attachment: [text/bzr-bundle]
