Debian Codegraph @

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

raw
1 #
2 # Test that user data is correctly "visualized" in
3 # INFORMATION_SCHEMA.innodb_locks.lock_data
4 #
5
6 -- source include/have_innodb.inc
7
8 SET @save_timeout=@@GLOBAL.innodb_lock_wait_timeout;
9 SET GLOBAL innodb_lock_wait_timeout=100000000;
10
11 let $table_def =
12 (
13 c01 TINYINT,
14 c02 TINYINT UNSIGNED,
15 c03 SMALLINT,
16 c04 SMALLINT UNSIGNED,
17 c05 MEDIUMINT,
18 c06 MEDIUMINT UNSIGNED,
19 c07 INT,
20 c08 INT UNSIGNED,
21 c09 BIGINT,
22 c10 BIGINT UNSIGNED,
23 PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
24 ) ENGINE=INNODB;
25
26 -- eval CREATE TABLE t_min $table_def;
27 INSERT INTO t_min VALUES
28 (-128, 0,
29 -32768, 0,
30 -8388608, 0,
31 -2147483648, 0,
32 -9223372036854775808, 0);
33
34 -- eval CREATE TABLE t_max $table_def;
35 INSERT INTO t_max VALUES
36 (127, 255,
37 32767, 65535,
38 8388607, 16777215,
39 2147483647, 4294967295,
40 9223372036854775807, 18446744073709551615);
41
42 CREATE TABLE ```t'\"_str` (
43 c1 VARCHAR(32),
44 c2 VARCHAR(32),
45 c3 VARCHAR(32),
46 c4 VARCHAR(32),
47 c5 VARCHAR(32),
48 c6 VARCHAR(32),
49 c7 VARCHAR(32),
50 PRIMARY KEY(c1, c2, c3, c4, c5, c6, c7)
51 ) ENGINE=INNODB;
52 INSERT INTO ```t'\"_str` VALUES
53 ('1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''');
54 INSERT INTO ```t'\"_str` VALUES
55 ('2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""');
56 INSERT INTO ```t'\"_str` VALUES
57 ('3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\');
58 INSERT INTO ```t'\"_str` VALUES
59 ('4', 'abc', 0x00616263, 0x61626300, 0x61006263, 0x6100626300, 0x610062630000);
60
61 -- source include/count_sessions.inc
62
63 -- connect (con_lock,localhost,root,,)
64 -- connect (con_min_trylock,localhost,root,,)
65 -- connect (con_max_trylock,localhost,root,,)
66 -- connect (con_str_insert_supremum,localhost,root,,)
67 -- connect (con_str_lock_row1,localhost,root,,)
68 -- connect (con_str_lock_row2,localhost,root,,)
69 -- connect (con_str_lock_row3,localhost,root,,)
70 -- connect (con_str_lock_row4,localhost,root,,)
71 -- connect (con_verify_innodb_locks,localhost,root,,)
72
73 -- connection con_lock
74 SET autocommit=0;
75 SELECT * FROM t_min FOR UPDATE;
76 SELECT * FROM t_max FOR UPDATE;
77 SELECT * FROM ```t'\"_str` FOR UPDATE;
78
79 -- connection con_min_trylock
80 -- send
81 SELECT * FROM t_min FOR UPDATE;
82
83 -- connection con_max_trylock
84 -- send
85 SELECT * FROM t_max FOR UPDATE;
86
87 -- connection con_str_insert_supremum
88 -- send
89 INSERT INTO ```t'\"_str` VALUES
90 ('z', 'z', 'z', 'z', 'z', 'z', 'z');
91
92 -- connection con_str_lock_row1
93 -- send
94 SELECT * FROM ```t'\"_str` WHERE c1 = '1' FOR UPDATE;
95
96 -- connection con_str_lock_row2
97 -- send
98 SELECT * FROM ```t'\"_str` WHERE c1 = '2' FOR UPDATE;
99
100 -- connection con_str_lock_row3
101 -- send
102 SELECT * FROM ```t'\"_str` WHERE c1 = '3' FOR UPDATE;
103
104 -- connection con_str_lock_row4
105 -- send
106 SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE;
107
108 -- connection con_verify_innodb_locks
109 # Wait for the above queries to execute before continuing.
110 # Without this, it sometimes happens that the SELECT from innodb_locks
111 # executes before some of them, resulting in less than expected number
112 # of rows being selected from innodb_locks. If there is a bug and there
113 # are no 14 rows in innodb_locks then this test will fail with timeout.
114 # Notice that if we query INNODB_LOCKS more often than once per 0.1 sec
115 # then its contents will never change because the cache from which it is
116 # filled is updated only if it has not been read for 0.1 seconds. See
117 # CACHE_MIN_IDLE_TIME_US in trx/trx0i_s.c.
118 let $cnt=10;
119 while ($cnt)
120 {
121 let $success=`SELECT COUNT(*) = 14 FROM INFORMATION_SCHEMA.INNODB_LOCKS`;
122 if ($success)
123 {
124 let $cnt=0;
125 }
126 if (!$success)
127 {
128 real_sleep 0.2;
129 dec $cnt;
130 }
131 }
132 if (!$success)
133 {
134 -- echo Timeout waiting for rows in INNODB_LOCKS to appear
135 }
136
137 SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data
138 FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data;
139
140 SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
141 GROUP BY lock_table;
142
143 set @save_sql_mode = @@sql_mode;
144 SET SQL_MODE='ANSI_QUOTES';
145 SELECT lock_table, COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
146 GROUP BY lock_table;
147 SET @@sql_mode=@save_sql_mode;
148
149 # Release all the locks;
150 -- connection con_lock
151 COMMIT;
152
153 -- connection default
154
155 -- disconnect con_lock
156 -- disconnect con_min_trylock
157 -- disconnect con_max_trylock
158 -- disconnect con_str_insert_supremum
159 -- disconnect con_str_lock_row1
160 -- disconnect con_str_lock_row2
161 -- disconnect con_str_lock_row3
162 -- disconnect con_str_lock_row4
163 -- disconnect con_verify_innodb_locks
164
165 DROP TABLE t_min, t_max, ```t'\"_str`;
166
167 -- source include/wait_until_count_sessions.inc
168
169 SET GLOBAL innodb_lock_wait_timeout=@save_timeout;