| 1 |
--echo # |
| 2 |
--echo # BUG#32636792: IN THE TRIGGER, INSERT INTO SELECT LOCKS THE SELECTED |
| 3 |
--echo # ROWS, EVEN READ-COMMITTED |
| 4 |
--echo # |
| 5 |
|
| 6 |
--disable_query_log |
| 7 |
SET @saved_binlog_format= @@GLOBAL.binlog_format; |
| 8 |
CREATE TABLE cases ( |
| 9 |
id INT PRIMARY KEY AUTO_INCREMENT, |
| 10 |
statements TEXT NOT NULL |
| 11 |
); |
| 12 |
INSERT INTO cases (statements) VALUES |
| 13 |
("SELECT id FROM t1 WHERE val = 'a' INTO @v1"), |
| 14 |
("SELECT id FROM t1 WHERE val = 'a' FOR SHARE INTO @v1"), |
| 15 |
("SELECT id FROM t1 WHERE val = 'a' FOR UPDATE INTO @v1"), |
| 16 |
("UPDATE t1 SET val='A' WHERE val = 'a'"), |
| 17 |
("INSERT INTO t1 SELECT id+100,val FROM t2"), |
| 18 |
("INSERT INTO t1 ( id, val ) SELECT 2, id FROM t1 WHERE val = 'a'"), |
| 19 |
("REPLACE INTO t1 ( id, val ) SELECT 2, id FROM t1 WHERE val = 'a'"), |
| 20 |
("INSERT INTO t1 VALUES (30, 'b'); |
| 21 |
UPDATE t2 SET val='c' WHERE val IN (SELECT val FROM t1 where id = 30)"), |
| 22 |
("INSERT INTO t1 VALUES (2, 'A'); |
| 23 |
SELECT val FROM t1 WHERE id = 10 INTO @v1"); |
| 24 |
|
| 25 |
|
| 26 |
--let $binlog_formats=ROW,STATEMENT |
| 27 |
while ($binlog_formats) |
| 28 |
{ |
| 29 |
--let $binlog_format= `SELECT SUBSTRING_INDEX('$binlog_formats', ',', 1)` |
| 30 |
--let $binlog_formats= `SELECT SUBSTRING('$binlog_formats', LENGTH('$binlog_format') + 2)` |
| 31 |
--eval SET GLOBAL BINLOG_FORMAT='$binlog_format' |
| 32 |
--echo ########## BINLOG FORMAT: $binlog_format ########### |
| 33 |
|
| 34 |
--let $cases_cnt = `SELECT COUNT(1) FROM cases` |
| 35 |
--let $case_id = 1 |
| 36 |
while($case_id <= $cases_cnt) |
| 37 |
{ |
| 38 |
--let $statements = `SELECT statements FROM cases WHERE id = $case_id` |
| 39 |
--echo # Query: $statements |
| 40 |
--source include/trigger_function_lock_compare.inc |
| 41 |
--inc $case_id |
| 42 |
} |
| 43 |
} |
| 44 |
|
| 45 |
# Because of Bug#33307684, under REPEATABLE READ, the below queries have a |
| 46 |
# discrepancy in the locks taken when executed inside a stored function. Hence |
| 47 |
# skipping the lock comparison. |
| 48 |
|
| 49 |
TRUNCATE TABLE cases; |
| 50 |
INSERT INTO cases (statements) VALUES |
| 51 |
("INSERT INTO t2 SELECT id+100,val FROM t1"), |
| 52 |
("CREATE TEMPORARY TABLE t3 SELECT id FROM t1; |
| 53 |
DROP TEMPORARY TABLE t3"); |
| 54 |
|
| 55 |
--let $binlog_formats=ROW,STATEMENT |
| 56 |
while ($binlog_formats) |
| 57 |
{ |
| 58 |
--let $binlog_format= `SELECT SUBSTRING_INDEX('$binlog_formats', ',', 1)` |
| 59 |
--let $binlog_formats= `SELECT SUBSTRING('$binlog_formats', LENGTH('$binlog_format') + 2)` |
| 60 |
--eval SET GLOBAL BINLOG_FORMAT='$binlog_format' |
| 61 |
--echo ########## BINLOG FORMAT: $binlog_format ########### |
| 62 |
|
| 63 |
--let $cases_cnt = `SELECT COUNT(1) FROM cases` |
| 64 |
--let $case_id = 1 |
| 65 |
--let $skip_lock_check = 1 |
| 66 |
while($case_id <= $cases_cnt) |
| 67 |
{ |
| 68 |
--let $statements = `SELECT statements FROM cases WHERE id = $case_id` |
| 69 |
--echo # Query: $statements |
| 70 |
--source include/trigger_function_lock_compare.inc |
| 71 |
--inc $case_id |
| 72 |
} |
| 73 |
} |
| 74 |
|
| 75 |
# Cleanup |
| 76 |
DROP TABLE cases; |
| 77 |
SET GLOBAL BINLOG_FORMAT=@saved_binlog_format; |
| 78 |
# Remove this when Bug#33594199 has been fixed |
| 79 |
RESET MASTER; |
| 80 |
--enable_query_log |