List:Commits« Previous MessageNext Message »
From:Mattias Jonsson Date:May 16 2012 10:27pm
Subject:bzr push into mysql-trunk branch (mattias.jonsson:3833 to 3834) WL#4443
View as plain text  
 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<Item> &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).
Thread
bzr push into mysql-trunk branch (mattias.jonsson:3833 to 3834) WL#4443Mattias Jonsson7 Jun