Debian Codegraph @

mysql-test/suite/innodb/t/trigger_function_lock_compare.test

raw
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