Debian Codegraph @

mysql-test/suite/handler/interface.test

raw
1 #
2 # Tests of handler interface that are system independent
3 #
4 # Handler tests don't work yet with embedded server
5 #
6 -- source include/not_embedded.inc
7
8 --disable_warnings
9 drop table if exists t1,t3,t4,t5;
10 drop database if exists test_test;
11 --enable_warnings
12
13 # Run tests with myisam (any engine should be ok)
14
15 let $engine_type= MyISAM;
16
17 --source init.inc
18
19 #
20 # Do some syntax checking
21 #
22
23 handler t1 open;
24 --error ER_PARSE_ERROR
25 handler t1 read a=(SELECT 1);
26 --error ER_PARSE_ERROR
27 handler t1 read a=(1) FIRST;
28 --error ER_PARSE_ERROR
29 handler t1 read a=(1) NEXT;
30 --error ER_PARSE_ERROR
31 handler t1 read last;
32 handler t1 close;
33 drop table t1;
34
35 CREATE TABLE t1(a INT, PRIMARY KEY(a));
36 insert into t1 values(1),(2);
37 handler t1 open;
38 --error ER_PARSE_ERROR
39 handler t1 read primary=(1);
40 handler t1 read `primary`=(1);
41 handler t1 close;
42 drop table t1;
43
44 #
45 # Check if two database names beginning the same are seen as different.
46 #
47 # This database begins like the usual 'test' database.
48 #
49 create database test_test;
50 use test_test;
51 eval create table t1(table_id char(20), primary key $key_type (table_id));
52 insert into t1 values ('test_test.t1');
53 insert into t1 values ('');
54 handler t1 open;
55 handler t1 read first limit 9;
56 eval create table t2(table_id char(20), primary key $key_type (table_id));
57 insert into t2 values ('test_test.t2');
58 insert into t2 values ('');
59 handler t2 open;
60 handler t2 read first limit 9;
61 #
62 # This is the usual 'test' database.
63 #
64 use test;
65 eval create table t1(table_id char(20), primary key $key_type (table_id));
66 insert into t1 values ('test.t1');
67 insert into t1 values ('');
68 --error 1066
69 handler t1 open;
70 #
71 # Check accessibility of all the tables.
72 #
73 use test;
74 --error 1064
75 handler test.t1 read first limit 9;
76 --error 1064
77 handler test_test.t1 read first limit 9;
78 handler t1 read first limit 9;
79 --error 1064
80 handler test_test.t2 read first limit 9;
81 handler t2 read first limit 9;
82
83 #
84 # Cleanup.
85 #
86
87 --error 1064
88 handler test_test.t1 close;
89 handler t1 close;
90 drop table test_test.t1;
91 --error 1064
92 handler test_test.t2 close;
93 handler t2 close;
94 drop table test_test.t2;
95 drop database test_test;
96
97 #
98 use test;
99 --error 1064
100 handler test.t1 close;
101 --error 1109
102 handler t1 close;
103 drop table test.t1;
104
105 #
106 # BUG#4335 one name can be handler open'ed many times
107 #
108
109 create database test_test;
110 use test_test;
111 create table t1 (c1 char(20));
112 insert into t1 values ('test_test.t1');
113 create table t3 (c1 char(20));
114 insert into t3 values ('test_test.t3');
115 handler t1 open;
116 handler t1 read first limit 9;
117 handler t1 open h1;
118 handler h1 read first limit 9;
119 use test;
120 create table t1 (c1 char(20));
121 create table t2 (c1 char(20));
122 create table t3 (c1 char(20));
123 insert into t1 values ('t1');
124 insert into t2 values ('t2');
125 insert into t3 values ('t3');
126 --error 1066
127 handler t1 open;
128 --error 1066
129 handler t2 open t1;
130 --error 1066
131 handler t3 open t1;
132 handler t1 read first limit 9;
133 --error 1064
134 handler test.t1 close;
135 --error 1066
136 handler test.t1 open h1;
137 --error 1066
138 handler test_test.t1 open h1;
139 handler test_test.t3 open h3;
140 handler test.t1 open h2;
141 handler t1 read first limit 9;
142 handler h1 read first limit 9;
143 handler h2 read first limit 9;
144 handler h3 read first limit 9;
145 handler h2 read first limit 9;
146 --error 1064
147 handler test.h1 close;
148 handler t1 close;
149 handler h1 close;
150 handler h2 close;
151 --error 1109
152 handler t1 read first limit 9;
153 --error 1109
154 handler h1 read first limit 9;
155 --error 1109
156 handler h2 read first limit 9;
157 handler h3 read first limit 9;
158 handler h3 read first limit 9;
159 use test_test;
160 handler h3 read first limit 9;
161 --error 1064
162 handler test.h3 read first limit 9;
163 handler h3 close;
164 use test;
165 drop table t3;
166 drop table t2;
167 drop table t1;
168 drop database test_test;
169
170 #
171 # Bug#21587 FLUSH TABLES causes server crash when used with HANDLER statements
172 #
173
174 create table t1 (c1 int);
175 create table t2 (c1 int);
176 insert into t1 values (1);
177 insert into t2 values (2);
178 handler t1 open;
179 handler t1 read first;
180 connect (flush,localhost,root,,);
181 connection flush;
182 flush tables;
183 send flush table t1;
184 connect (waiter,localhost,root,,);
185 connection waiter;
186 let $wait_condition=
187 select count(*) = 1 from information_schema.processlist
188 where state = "Waiting for table metadata lock";
189 --source include/wait_condition.inc
190 connection default;
191 handler t2 open;
192 handler t2 read first;
193 handler t1 read next;
194 handler t1 close;
195 handler t2 close;
196 connection flush;
197 reap;
198 connection default;
199 drop table t1,t2;
200 disconnect flush;
201
202 #
203 # Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements
204 #
205
206 --disable_warnings
207 drop table if exists t1, t0;
208 --enable_warnings
209 create table t1 (c1 int);
210 handler t1 open;
211 handler t1 read first;
212 connect (flush,localhost,root,,);
213 connection flush;
214 send rename table t1 to t0;
215 connection waiter;
216 let $wait_condition=
217 select count(*) = 1 from information_schema.processlist
218 where state = "Waiting for table metadata lock" and
219 info = "rename table t1 to t0";
220 --source include/wait_condition.inc
221 connection default;
222 --echo #
223 --echo # RENAME placed two pending locks and waits.
224 --echo # When HANDLER t0 OPEN does open_tables(), it calls
225 --echo # mysql_ha_flush(), which in turn closes the open HANDLER for t1.
226 --echo # RENAME TABLE gets unblocked. If it gets scheduled quickly
227 --echo # and manages to complete before open_tables()
228 --echo # of HANDLER t0 OPEN, open_tables() and therefore the whole
229 --echo # HANDLER t0 OPEN succeeds. Otherwise open_tables()
230 --echo # notices a pending or active exclusive metadata lock on t2
231 --echo # and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK
232 --echo # error.
233 --echo #
234 --error 0, ER_LOCK_DEADLOCK
235 handler t0 open;
236 --error 0, ER_UNKNOWN_TABLE
237 handler t0 close;
238 connection flush;
239 reap;
240 --error ER_UNKNOWN_TABLE
241 handler t1 read next;
242 --error ER_UNKNOWN_TABLE
243 handler t1 close;
244 connection default;
245 drop table t0;
246 connection flush;
247 disconnect flush;
248 --source include/wait_until_disconnected.inc
249 connection waiter;
250 disconnect waiter;
251 --source include/wait_until_disconnected.inc
252 connection default;
253
254 #
255 # Bug#31397 Inconsistent drop table behavior of handler tables.
256 #
257
258 create table t1 (a int);
259 handler t1 open as t1_alias;
260 drop table t1;
261 create table t1 (a int);
262 handler t1 open as t1_alias;
263 flush tables;
264 drop table t1;
265 create table t1 (a int);
266 handler t1 open as t1_alias;
267 handler t1_alias close;
268 drop table t1;
269 create table t1 (a int);
270 handler t1 open as t1_alias;
271 handler t1_alias read first;
272 drop table t1;
273 --error ER_UNKNOWN_TABLE
274 handler t1_alias read next;
275
276 # Flush tables causes handlers reopen
277
278 create table t1 (a int, b char(1), key a (a), key b (a,b));
279 insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
280 (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j");
281 handler t1 open;
282 handler t1 read a first;
283 handler t1 read a next;
284 flush tables;
285 handler t1 read a next;
286 flush tables t1;
287 handler t1 read a next;
288 flush tables with read lock;
289 handler t1 read a next;
290 unlock tables;
291 drop table t1;
292 --error ER_UNKNOWN_TABLE
293 handler t1 read a next;
294
295 #
296 # Bug#41110: crash with handler command when used concurrently with alter table
297 # Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table
298 #
299
300 connect(con1,localhost,root,,);
301 connect(con2,localhost,root,,);
302
303 --echo # Now test case which was reported originally but which no longer
304 --echo # triggers execution path which has caused the problem.
305 connection default;
306 create table t1 (a int not null);
307 insert into t1 values (1);
308 handler t1 open;
309 connection con1;
310 send alter table t1 engine=csv;
311 connection con2;
312 let $wait_condition=
313 select count(*) = 1 from information_schema.processlist
314 where state = "Waiting for table metadata lock" and
315 info = "alter table t1 engine=csv";
316 --source include/wait_condition.inc
317 connection default;
318 --echo # Since S metadata lock was already acquired at HANDLER OPEN time
319 --echo # and TL_READ lock requested by HANDLER READ is compatible with
320 --echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
321 --echo # without waiting. The old version of table should be used in it.
322 handler t1 read next;
323 handler t1 close;
324 connection con1;
325 --reap # Since last in this connection was a send
326 drop table t1;
327 disconnect con1;
328 --source include/wait_until_disconnected.inc
329 connection con2;
330 disconnect con2;
331 --source include/wait_until_disconnected.inc
332 connection default;
333
334 #
335 # Bug#44151 using handler commands on information_schema tables crashes server
336 #
337 USE information_schema;
338 --error ER_WRONG_USAGE
339 HANDLER COLUMNS OPEN;
340 USE test;
341
342 #
343 # lp:697622 Assertion `! is_set()' failed when preparing a HANDLER statement
344 #
345 --error ER_UNKNOWN_TABLE
346 PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST';
347
348 #
349 # MySQL Bug#16385711: HANDLER, CREATE TABLE IF NOT EXISTS, PROBLEM AFTER MYSQL_HA_FIND
350 #
351 create view v as select 1;
352 create temporary table v as select 2;
353 handler v open;
354 prepare stmt from 'create table if not exists v as select 3';
355 execute stmt;
356 --error ER_UNKNOWN_TABLE
357 handler v read next;
358 drop view v;
359
360 --echo #
361 --echo # 10.2 Test
362 --echo #
363 --echo # MDEV-20207: Assertion `! is_set()' failed in
364 --echo # Diagnostics_area::set_eof_status upon HANDLER READ
365 --echo #
366
367 DROP TABLE IF EXISTS t1;
368
369 CREATE TABLE t1 (a POINT, KEY(a));
370 HANDLER t1 OPEN h;
371
372 --error ER_CANT_CREATE_GEOMETRY_OBJECT
373 HANDLER h READ a = (0);
374
375 HANDLER h CLOSE;
376 DROP TABLE t1;
377
378 --echo # End of 10.2 Test
379
380 --echo #
381 --echo # MDEV-15813 ASAN use-after-poison in hp_hashnr upon
382 --echo # HANDLER READ on a versioned HEAP table
383 --echo #
384
385 CREATE TABLE t1 (g GEOMETRY NOT NULL, SPATIAL gi(g));
386 INSERT INTO t1 VALUES (POINT(0,0));
387 HANDLER t1 OPEN AS h;
388 --error ER_CANT_CREATE_GEOMETRY_OBJECT
389 HANDLER h READ `gi`= (10);
390 --error ER_CANT_CREATE_GEOMETRY_OBJECT
391 HANDLER h READ `gi`> (10);
392 HANDLER h CLOSE;
393 DROP TABLE t1;
394
395 CREATE TABLE t1 (w VARCHAR(100), FULLTEXT fk(w));
396 INSERT INTO t1 VALUES ('one two three');
397 HANDLER t1 OPEN AS h;
398 --error ER_KEY_DOESNT_SUPPORT
399 HANDLER h READ `fk`= (10);
400 --error ER_KEY_DOESNT_SUPPORT
401 HANDLER h READ `fk`> (10);
402 HANDLER h CLOSE;
403 DROP TABLE t1;
404
405 --echo #
406 --echo # MDEV-35082 HANDLER with FULLTEXT keys is not always rejected
407 --echo #
408 create table t (a int primary key, v text not null, fulltext(v));
409 handler t open;
410 --error ER_KEY_DOESNT_SUPPORT
411 handler t read v next;
412 drop table t;
413
414 --echo # End of 10.5 tests