Debian Codegraph @

mysql-test/suite/innodb/t/innodb_information_schema.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 # lock data that is part of result set for this testcase
9 # is retreived using buf_page_try_get. i.e only show if page
10 # happen to be in buffer pool, with key rotation threads
11 # chances are substantial that pages have been evicted and lock_data
12 # get NULL
13 -- source include/not_encrypted.inc
14
15 SET default_storage_engine=InnoDB;
16
17 let $table_def =
18 (
19 c01 TINYINT,
20 c02 TINYINT UNSIGNED,
21 c03 SMALLINT,
22 c04 SMALLINT UNSIGNED,
23 c05 MEDIUMINT,
24 c06 MEDIUMINT UNSIGNED,
25 c07 INT,
26 c08 INT UNSIGNED,
27 c09 BIGINT,
28 c10 BIGINT UNSIGNED,
29 PRIMARY KEY(c01, c02, c03, c04, c05, c06, c07, c08, c09, c10)
30 );
31
32 -- eval CREATE TABLE t_min $table_def;
33 INSERT INTO t_min VALUES
34 (-128, 0,
35 -32768, 0,
36 -8388608, 0,
37 -2147483648, 0,
38 -9223372036854775808, 0);
39
40 -- eval CREATE TABLE t_max $table_def;
41 INSERT INTO t_max VALUES
42 (127, 255,
43 32767, 65535,
44 8388607, 16777215,
45 2147483647, 4294967295,
46 9223372036854775807, 18446744073709551615);
47
48 CREATE TABLE ```t'\"_str` (
49 c1 VARCHAR(32),
50 c2 VARCHAR(32),
51 c3 VARCHAR(32),
52 c4 VARCHAR(32),
53 c5 VARCHAR(32),
54 c6 VARCHAR(32),
55 c7 VARCHAR(32),
56 PRIMARY KEY(c1, c2, c3, c4, c5, c6, c7)
57 );
58 INSERT INTO ```t'\"_str` VALUES
59 ('1', 'abc', '''abc', 'abc''', 'a''bc', 'a''bc''', '''abc''''');
60 INSERT INTO ```t'\"_str` VALUES
61 ('2', 'abc', '"abc', 'abc"', 'a"bc', 'a"bc"', '"abc""');
62 INSERT INTO ```t'\"_str` VALUES
63 ('3', 'abc', '\\abc', 'abc\\', 'a\\bc', 'a\\bc\\', '\\abc\\\\');
64 INSERT INTO ```t'\"_str` VALUES
65 ('4', 'abc', 0x00616263, 0x61626300, 0x61006263, 0x6100626300, 0x610062630000);
66
67 --source include/count_sessions.inc
68
69 -- connect (con_lock,localhost,root,,)
70 -- connect (con_min_trylock,localhost,root,,)
71 -- connect (con_max_trylock,localhost,root,,)
72 -- connect (con_str_insert_supremum,localhost,root,,)
73 -- connect (con_str_lock_row1,localhost,root,,)
74 -- connect (con_str_lock_row2,localhost,root,,)
75 -- connect (con_str_lock_row3,localhost,root,,)
76 -- connect (con_str_lock_row4,localhost,root,,)
77 -- connect (con_verify_innodb_locks,localhost,root,,)
78
79 -- connection con_lock
80 SET autocommit=0;
81 SELECT * FROM t_min FOR UPDATE;
82 SELECT * FROM t_max FOR UPDATE;
83 SELECT * FROM ```t'\"_str` FOR UPDATE;
84
85 -- connection con_min_trylock
86 -- send
87 SELECT * FROM t_min FOR UPDATE;
88
89 -- connection con_max_trylock
90 -- send
91 SELECT * FROM t_max FOR UPDATE;
92
93 -- connection con_str_insert_supremum
94 -- send
95 INSERT INTO ```t'\"_str` VALUES
96 ('z', 'z', 'z', 'z', 'z', 'z', 'z');
97
98 -- connection con_str_lock_row1
99 -- send
100 SELECT * FROM ```t'\"_str` WHERE c1 = '1' FOR UPDATE;
101
102 -- connection con_str_lock_row2
103 -- send
104 SELECT * FROM ```t'\"_str` WHERE c1 = '2' FOR UPDATE;
105
106 -- connection con_str_lock_row3
107 -- send
108 SELECT * FROM ```t'\"_str` WHERE c1 = '3' FOR UPDATE;
109
110 -- connection con_str_lock_row4
111 -- send
112 SELECT * FROM ```t'\"_str` WHERE c1 = '4' FOR UPDATE;
113
114 -- connection con_verify_innodb_locks
115 # Wait for the above queries to execute before continuing.
116 # Without this, it sometimes happens that the SELECT from innodb_locks
117 # executes before some of them, resulting in less than expected number
118 # of rows being selected from innodb_locks. If there is a bug and there
119 # are no 14 rows in innodb_locks then this test will fail with timeout.
120 # Notice that if we query INNODB_LOCKS more often than once per 0.1 sec
121 # then its contents will never change because the cache from which it is
122 # filled is updated only if it has not been read for 0.1 seconds. See
123 # CACHE_MIN_IDLE_TIME_US in trx/trx0i_s.c.
124 let $cnt=200;
125 while ($cnt)
126 {
127 let $success=`SELECT COUNT(*) = 14 FROM INFORMATION_SCHEMA.INNODB_LOCKS`;
128 if ($success)
129 {
130 let $cnt=0;
131 }
132 if (!$success)
133 {
134 real_sleep 0.2;
135 dec $cnt;
136 }
137 }
138 if (!$success)
139 {
140 -- echo Timeout waiting for rows in INNODB_LOCKS to appear
141 }
142
143 --replace_column 6 #
144 --sorted_result
145 SELECT lock_mode, lock_type, lock_table, lock_index, lock_rec, lock_data
146 FROM INFORMATION_SCHEMA.INNODB_LOCKS ORDER BY lock_data;
147
148 SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
149 GROUP BY lock_table;
150
151 set @save_sql_mode = @@sql_mode;
152 SET SQL_MODE='ANSI_QUOTES';
153 SELECT lock_table,COUNT(*) FROM INFORMATION_SCHEMA.INNODB_LOCKS
154 GROUP BY lock_table;
155 SET @@sql_mode=@save_sql_mode;
156
157 -- connection default
158
159 -- disconnect con_lock
160 -- disconnect con_min_trylock
161 -- disconnect con_max_trylock
162 -- disconnect con_str_insert_supremum
163 -- disconnect con_str_lock_row1
164 -- disconnect con_str_lock_row2
165 -- disconnect con_str_lock_row3
166 -- disconnect con_str_lock_row4
167 -- disconnect con_verify_innodb_locks
168
169 DROP TABLE t_min, t_max, ```t'\"_str`;
170
171 --source include/wait_until_count_sessions.inc
172
173 #
174 # Test that transaction data is correctly "visualized" in
175 # INFORMATION_SCHEMA.INNODB_TRX
176 #
177
178 DESCRIBE INFORMATION_SCHEMA.INNODB_TRX;
179
180 -- disable_warnings
181 DROP TABLE IF EXISTS t1;
182 -- enable_warnings
183
184 CREATE TABLE t1 (
185 c01 INT,
186 c02 INT,
187 PRIMARY KEY (c01)
188 ) ENGINE = InnoDB;
189
190 INSERT INTO t1 VALUES
191 (1,2),(2,4),(3,6),(4,8);
192
193 CREATE TABLE t2 (
194 c01 INT,
195 c02 INT,
196 PRIMARY KEY (c01),
197 FOREIGN KEY fk1 (c02) REFERENCES t1 (c01)
198 ) ENGINE = InnoDB;
199
200 INSERT INTO t2 VALUES
201 (1,1),(2,2),(3,3);
202
203 -- connect (con_trx,localhost,root,,)
204 -- connect (con_verify_innodb_trx,localhost,root,,)
205
206 -- connection con_trx
207 SET autocommit=0;
208 INSERT INTO t1 VALUES (5,10);
209 SELECT * FROM t1 FOR UPDATE;
210
211 let $wait_condition=
212 SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_TRX;
213 --source include/wait_condition.inc
214
215 -- connection con_verify_innodb_trx
216 SELECT trx_state, trx_weight, trx_tables_in_use, trx_tables_locked,
217 trx_rows_locked, trx_rows_modified, trx_concurrency_tickets,
218 trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
219 FROM INFORMATION_SCHEMA.INNODB_TRX;
220
221 -- connection con_trx
222 ROLLBACK;
223 SET FOREIGN_KEY_CHECKS = 0;
224 SET UNIQUE_CHECKS = 0;
225 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
226 BEGIN;
227 INSERT INTO t1 VALUES (6,12);
228
229 let $wait_condition=
230 SELECT trx_unique_checks = 0 FROM INFORMATION_SCHEMA.INNODB_TRX;
231 --source include/wait_condition.inc
232
233 -- connection con_verify_innodb_trx
234 SELECT trx_isolation_level, trx_unique_checks, trx_foreign_key_checks
235 FROM INFORMATION_SCHEMA.INNODB_TRX;
236
237 -- connection con_trx
238 ROLLBACK;
239 SET FOREIGN_KEY_CHECKS = 1;
240 SET UNIQUE_CHECKS = 1;
241 BEGIN;
242 -- error 1452
243 INSERT INTO t2 VALUES (4,10);
244
245 let $wait_condition=
246 SELECT trx_unique_checks = 1 FROM INFORMATION_SCHEMA.INNODB_TRX;
247 --source include/wait_condition.inc
248
249 -- connection con_verify_innodb_trx
250 SELECT trx_state, trx_isolation_level, trx_last_foreign_key_error
251 FROM INFORMATION_SCHEMA.INNODB_TRX;
252
253 -- connection default
254
255 -- disconnect con_trx
256 -- disconnect con_verify_innodb_trx
257
258 DROP TABLE t2;
259 DROP TABLE t1;