Debian Codegraph @

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

raw
1 --echo # Bug #28523025: INNODB: ASSERTION FAILURE:
2 --echo # LOCK0LOCK.CC:1468:LOCK_REC_GET_REC_NOT_GAP(LOCK)
3
4 --source include/have_debug_sync.inc
5
6 # Minimal reproduction of original issue:
7 CREATE TABLE t0(a INT PRIMARY KEY);
8 CREATE VIEW v0 AS SELECT a FROM t0;
9 DROP TABLE t0;
10 CREATE TABLE t0(a int) REPLACE SELECT 0 AS a;
11 DROP TABLE t0;
12 DROP VIEW v0;
13
14 # A version in which there are conflicting rows and REPLACE is used:
15 CREATE TABLE t0(a INT, b INT);
16 INSERT INTO t0 VALUES (1,1), (1,2), (2,2);
17
18 CREATE TABLE t1(a INT, b INT);
19 CREATE VIEW v0 AS SELECT b FROM t1;
20 DROP TABLE t1;
21 CREATE TABLE t1(
22 a INT PRIMARY KEY,
23 b INT
24 ) REPLACE SELECT a,b FROM t0 ORDER BY a,b;
25 SELECT * FROM t1;
26 DROP TABLE t1;
27 DROP TABLE t0;
28 DROP VIEW v0;
29
30 # A version in which there are conflicting rows and IGNORE is used:
31 CREATE TABLE t0(a INT, b INT);
32 INSERT INTO t0 VALUES (1,1), (1,2), (2,2);
33
34 CREATE TABLE t1(a INT, b INT);
35 CREATE VIEW v0 AS SELECT b FROM t1;
36 DROP TABLE t1;
37 CREATE TABLE t1(
38 a INT PRIMARY KEY,
39 b INT
40 ) IGNORE SELECT a,b FROM t0 ORDER BY a,b;
41 SELECT * FROM t1;
42 DROP TABLE t1;
43 DROP TABLE t0;
44 DROP VIEW v0;
45
46 # A version in which there are conflicting rows and no modifier is used:
47 CREATE TABLE t0(a INT, b INT);
48 INSERT INTO t0 VALUES (1,1), (1,2), (2,2);
49
50 CREATE TABLE t1(a INT, b INT);
51 CREATE VIEW v0 AS SELECT b FROM t1;
52 DROP TABLE t1;
53 --error ER_DUP_ENTRY
54 CREATE TABLE t1(
55 a INT PRIMARY KEY,
56 b INT
57 ) SELECT a,b FROM t0 ORDER BY a,b;
58 DROP TABLE t0;
59 DROP VIEW v0;
60
61 # Demonstration that correct type of locks (X instead of S followed by X) is
62 # taken for REPLACE and IODKU, regardless of whether or not there is a TRIGGER
63 # which causes a "subquery" with it's own lifecycle, even if this "subquery"
64 # also operates on the same table.
65 # This is to demonstrate that REPLCAE/IODKU flags should be tracked not per
66 # per trx, nor per table, not even per <trx,table> pair, but rather per cursor.
67 # In this test the exact list of locks reported by performance_schema is not as
68 # important, as the fact the list for i=1 is the same as list for i=2, which
69 # means that the "subquery" did not confuse the logic of the outer query.
70 # The only allowed difference between the two lists is the IS table lock
71 # and S lock on supremum pseudo-record taken by "subquery" intself.
72 --let i=1
73 while ($i <= 2)
74 {
75 --let table=t$i
76
77 eval CREATE TABLE $table(a INT NOT NULL PRIMARY KEY, b INT NOT NULL UNIQUE);
78 eval INSERT INTO $table (a,b) VALUES
79 (1,10), (2,20), (3,30), (4,40), (5,50), (6,60), (8,80), (10,100);
80
81 if ($i==2)
82 {
83 CREATE TABLE tt (i INT) ENGINE = InnoDB;
84 CREATE TRIGGER t2_bi BEFORE INSERT ON t2
85 FOR EACH ROW
86 INSERT INTO tt SELECT a as i FROM t2 WHERE a=10000;
87 }
88
89 BEGIN;
90
91 --eval REPLACE INTO $table (a,b) VALUES (2,13)
92 --eval INSERT INTO $table (a,b) VALUES (4,43) ON DUPLICATE KEY UPDATE b=43
93
94 --eval REPLACE INTO $table (a,b) VALUES (7,60)
95 --eval INSERT INTO $table (a,b) VALUES (9,80) ON DUPLICATE KEY UPDATE a=9
96
97
98 eval SELECT index_name,lock_type,lock_mode,lock_status,lock_data
99 FROM performance_schema.data_locks
100 WHERE object_schema='test' AND object_name = '$table'
101 ORDER BY 1,2,3,4,5;
102
103
104 ROLLBACK;
105 --inc $i
106 }
107
108 DROP TRIGGER t2_bi;
109 DROP TABLE tt;
110 DROP TABLE t2;
111 DROP TABLE t1;
112
113