From: Mattias Jonsson Date: May 16 2012 10:27pm Subject: bzr push into mysql-trunk branch (mattias.jonsson:3833 to 3834) WL#4443 List-Archive: http://lists.mysql.com/commits/144122 Message-Id: <201205162227.q4GMRdME006775@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3834 Mattias Jonsson 2012-05-17 WL#4443 Fix + test for multi UPDATE with BEFORE UPDATE triggers. modified: mysql-test/r/partition_locking.result mysql-test/t/partition_locking.test sql/sql_update.cc 3833 Mattias Jonsson 2012-05-16 WL4443: fixed test to work on non default innodb-page-size removed unused variable. modified: mysql-test/suite/parts/r/partition_debug_sync_innodb.result mysql-test/suite/parts/t/partition_debug_sync_innodb.test sql/sql_partition_admin.cc === modified file 'mysql-test/r/partition_locking.result' --- a/mysql-test/r/partition_locking.result revid:mattias.jonsson@stripped +++ b/mysql-test/r/partition_locking.result revid:mattias.jonsson@stripped @@ -4598,13 +4598,16 @@ a b 3 old a: 0 new b: old a: 0 new b: old a: 0 new b: b: 1st p0, dup key, Updated, a was 0 4 new a: 4 new b: new a: 0 new b: new a: 0 new b: b: 1st p0, dup key, Updated, a was 0 5 new b: new b: new b: b: 1st p0, dup key, Updated, a was 0 +DROP TABLE t1, t2, t3; # # Test of BEFORE UPDATE triggers and multi UPDATE # -DROP TRIGGER t1_before_insert; -DROP TRIGGER t1_before_update; -TRUNCATE TABLE t1; -TRUNCATE TABLE t2; +CREATE TABLE t1 (a int, b varchar(128), KEY (b)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 13; +CREATE TABLE t2 (a int PRIMARY KEY, b varchar(128)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 13; INSERT INTO t1 VALUES (1, "MultiUpdate1"); INSERT INTO t1 VALUES (2, "MultiUpdate2"); INSERT INTO t2 VALUES (1, "MultiUpdate1"); @@ -4616,6 +4619,36 @@ CREATE TRIGGER t2_before_update BEFORE U ON t2 FOR EACH ROW SET NEW.b = CONCAT("new2 a: ", NEW.a, " new2 b: ", NEW.b); FLUSH STATUS; +EXPLAIN EXTENDED UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b), +t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b) +WHERE t2.b = t1.b and t1.a = 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL b NULL NULL NULL 2 100.00 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 13 100.00 Using where; Using join buffer (Block Nested Loop) +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 32 +HANDLER_WRITE 17 +# 32 locks (2 table, 13 + 1 partitions lock/unlock) +FLUSH STATUS; +EXPLAIN PARTITIONS UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b), +t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b) +WHERE t2.b = t1.b and t1.a = 1; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t1 p1 ALL b NULL NULL NULL 2 Using where +1 SIMPLE t2 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ALL NULL NULL NULL NULL 13 Using where; Using join buffer (Block Nested Loop) +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 32 +HANDLER_WRITE 17 +# 32 locks (2 table, 13 + 1 partitions lock/unlock) +FLUSH STATUS; UPDATE t1, t2 SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b), t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b) @@ -4625,18 +4658,53 @@ WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 HANDLER_EXTERNAL_LOCK 32 -HANDLER_READ_FIRST 13 -HANDLER_READ_KEY 15 -HANDLER_READ_RND 1 -HANDLER_READ_RND_NEXT 17 +HANDLER_READ_FIRST 14 +HANDLER_READ_KEY 16 +HANDLER_READ_RND 2 +HANDLER_READ_RND_NEXT 21 HANDLER_UPDATE 2 -HANDLER_WRITE 18 +HANDLER_WRITE 19 # 32 locks (2 table, 13 + 1 partitions lock/unlock) -# 15 read_key -# 1 read_next, read_rnd -# 2 rean_rnd_next +# 14 read_first +# 16 read_key +# 2 read_rnd +# 21 rean_rnd_next # 2 update FLUSH STATUS; +EXPLAIN EXTENDED UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b), +t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b) +WHERE t1.b = t2.b and t2.a = 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 const PRIMARY PRIMARY 4 const 1 100.00 NULL +1 SIMPLE t1 ref b b 131 const 13 100.00 NULL +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 56 +HANDLER_READ_KEY 1 +HANDLER_WRITE 17 +# Trigger touches partitioning column, unable to prune locks +# 56 locks (2 tables + 2 * 13 partitions lock/unlock) +FLUSH STATUS; +EXPLAIN PARTITIONS UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b), +t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b) +WHERE t1.b = t2.b and t2.a = 2; +id select_type table partitions type possible_keys key key_len ref rows Extra +1 SIMPLE t2 p2 const PRIMARY PRIMARY 4 const 1 NULL +1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12 ref b b 131 const 13 NULL +SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS +WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; +VARIABLE_NAME VARIABLE_VALUE +HANDLER_COMMIT 1 +HANDLER_EXTERNAL_LOCK 56 +HANDLER_READ_KEY 1 +HANDLER_WRITE 17 +# Trigger touches partitioning column, unable to prune locks +# 56 locks (2 tables + 2 * 13 partitions lock/unlock) +FLUSH STATUS; UPDATE t1, t2 SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b), t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b) @@ -4645,28 +4713,29 @@ SELECT * FROM INFORMATION_SCHEMA.SESSION WHERE VARIABLE_NAME LIKE 'HANDLER_%' AND VARIABLE_VALUE > 0; VARIABLE_NAME VARIABLE_VALUE HANDLER_COMMIT 1 -HANDLER_EXTERNAL_LOCK 32 +HANDLER_EXTERNAL_LOCK 56 HANDLER_READ_KEY 15 HANDLER_READ_NEXT 1 HANDLER_READ_RND 1 HANDLER_READ_RND_NEXT 2 HANDLER_UPDATE 2 HANDLER_WRITE 18 -# 32 locks (2 table, 13 + 1 partitions lock/unlock) +# Due to the BEFORE UPDATE trigger on t2 that looks at 'a', +# no locks can be pruned. +# 56 locks (2 table, 2 * 13 partitions lock/unlock) # 15 read_key # 1 read_next, read_rnd -# 2 rean_rnd_next +# 2 read_rnd_next # 2 update SELECT * FROM t1 ORDER BY a; a b 1 new1 b: MultiUpdate1,(1) t2.b:MultiUpdate1 2 new1 b: MultiUpdate2,(2) t2.b:MultiUpdate2 -# TODO: FIXME: Why is the first row in t2 also affected by t1 trigger? SELECT * FROM t2 ORDER BY a; a b -1 new2 a: 1 new2 b: MultiUpdate1,(1) t1.b:new1 b: MultiUpdate1,(1) t2.b:MultiUpdate1 +1 new2 a: 1 new2 b: MultiUpdate1,(1) t1.b:MultiUpdate1 2 new2 a: 2 new2 b: MultiUpdate2,(2) t1.b:MultiUpdate2 -DROP TABLE t1, t2, t3; +DROP TABLE t1, t2; # # DO is not supported by WL#4443 !!! # Test of DO (eg. SELECT without returning values) === modified file 'mysql-test/t/partition_locking.test' --- a/mysql-test/t/partition_locking.test revid:mattias.jonsson@stripped +++ b/mysql-test/t/partition_locking.test revid:mattias.jonsson@stripped @@ -1687,14 +1687,19 @@ eval $get_handler_status_counts; --echo # 28 locks (1 tables, 13 partitions lock/unlock) SELECT * FROM t1 ORDER BY a; +DROP TABLE t1, t2, t3; --echo # --echo # Test of BEFORE UPDATE triggers and multi UPDATE --echo # -DROP TRIGGER t1_before_insert; -DROP TRIGGER t1_before_update; -TRUNCATE TABLE t1; -TRUNCATE TABLE t2; +CREATE TABLE t1 (a int, b varchar(128), KEY (b)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 13; + +CREATE TABLE t2 (a int PRIMARY KEY, b varchar(128)) +ENGINE = InnoDB +PARTITION BY HASH (a) PARTITIONS 13; + INSERT INTO t1 VALUES (1, "MultiUpdate1"); INSERT INTO t1 VALUES (2, "MultiUpdate2"); INSERT INTO t2 VALUES (1, "MultiUpdate1"); @@ -1709,34 +1714,69 @@ ON t2 FOR EACH ROW SET NEW.b = CONCAT("new2 a: ", NEW.a, " new2 b: ", NEW.b); FLUSH STATUS; +EXPLAIN EXTENDED UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b), + t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b) +WHERE t2.b = t1.b and t1.a = 1; +eval $get_handler_status_counts; +--echo # 32 locks (2 table, 13 + 1 partitions lock/unlock) + +FLUSH STATUS; +EXPLAIN PARTITIONS UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b), + t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b) +WHERE t2.b = t1.b and t1.a = 1; +eval $get_handler_status_counts; +--echo # 32 locks (2 table, 13 + 1 partitions lock/unlock) + +FLUSH STATUS; UPDATE t1, t2 SET t1.b = CONCAT(t1.b, ",(1) t2.b:", t2.b), t2.b = CONCAT(t2.b, ",(1) t1.b:", t1.b) WHERE t2.b = t1.b and t1.a = 1; eval $get_handler_status_counts; --echo # 32 locks (2 table, 13 + 1 partitions lock/unlock) ---echo # 15 read_key ---echo # 1 read_next, read_rnd ---echo # 2 rean_rnd_next +--echo # 14 read_first +--echo # 16 read_key +--echo # 2 read_rnd +--echo # 21 rean_rnd_next --echo # 2 update FLUSH STATUS; +EXPLAIN EXTENDED UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b), + t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b) +WHERE t1.b = t2.b and t2.a = 2; +eval $get_handler_status_counts; +--echo # Trigger touches partitioning column, unable to prune locks +--echo # 56 locks (2 tables + 2 * 13 partitions lock/unlock) + +FLUSH STATUS; +EXPLAIN PARTITIONS UPDATE t1, t2 +SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b), + t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b) +WHERE t1.b = t2.b and t2.a = 2; +eval $get_handler_status_counts; +--echo # Trigger touches partitioning column, unable to prune locks +--echo # 56 locks (2 tables + 2 * 13 partitions lock/unlock) + +FLUSH STATUS; UPDATE t1, t2 SET t1.b = CONCAT(t1.b, ",(2) t2.b:", t2.b), t2.b = CONCAT(t2.b, ",(2) t1.b:", t1.b) WHERE t1.b = t2.b and t2.a = 2; eval $get_handler_status_counts; ---echo # 32 locks (2 table, 13 + 1 partitions lock/unlock) +--echo # Due to the BEFORE UPDATE trigger on t2 that looks at 'a', +--echo # no locks can be pruned. +--echo # 56 locks (2 table, 2 * 13 partitions lock/unlock) --echo # 15 read_key --echo # 1 read_next, read_rnd ---echo # 2 rean_rnd_next +--echo # 2 read_rnd_next --echo # 2 update SELECT * FROM t1 ORDER BY a; ---echo # TODO: FIXME: Why is the first row in t2 also affected by t1 trigger? SELECT * FROM t2 ORDER BY a; - -DROP TABLE t1, t2, t3; +DROP TABLE t1, t2; --echo # --echo # DO is not supported by WL#4443 !!! === modified file 'sql/sql_update.cc' --- a/sql/sql_update.cc revid:mattias.jonsson@stripped +++ b/sql/sql_update.cc revid:mattias.jonsson@stripped @@ -1680,6 +1680,8 @@ int multi_update::prepare(List &no */ bitmap_union(table->read_set, table->write_set); } + /* All needed columns must be marked before prune_partitions(). */ + table->mark_columns_needed_for_update(); } } No bundle (reason: useless for push emails).