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#4443 | Mattias Jonsson | 7 Jun |
