MySQL 5.6.14 Source Code Document
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
handler.inc
1 # include/handler.inc
2 #
3 # The variables
4 # $engine_type -- storage engine to be tested
5 # $other_engine_type -- storage engine <> $engine_type
6 # $other_handler_engine_type -- storage engine <> $engine_type, if possible
7 # 1. $other_handler_engine_type must support handler
8 # 2. $other_handler_engine_type must point to an all
9 # time available storage engine
10 # 2006-08 MySQL 5.1 MyISAM and MEMORY only
11 # have to be set before sourcing this script.
12 -- source include/not_embedded.inc
13 #
14 # test of HANDLER ...
15 #
16 # Last update:
17 # 2006-07-31 ML test refactored (MySQL 5.1)
18 # code of t/handler.test and t/innodb_handler.test united
19 # main testing code put into include/handler.inc
20 #
21 
22 eval SET SESSION DEFAULT_STORAGE_ENGINE = $engine_type;
23 
24 --disable_warnings
25 drop table if exists t1,t3,t4,t5;
26 --enable_warnings
27 
28 create table t1 (a int, b char(10), key a(a), key b(a,b));
29 insert into t1 values
30 (17,"ddd"),(18,"eee"),(19,"fff"),(19,"yyy"),
31 (14,"aaa"),(15,"bbb"),(16,"ccc"),(16,"xxx"),
32 (20,"ggg"),(21,"hhh"),(22,"iii");
33 handler t1 open as t2;
34 -- error 1064
35 handler t2 read a=(SELECT 1);
36 handler t2 read a first;
37 handler t2 read a next;
38 handler t2 read a next;
39 handler t2 read a prev;
40 handler t2 read a last;
41 handler t2 read a prev;
42 handler t2 read a prev;
43 
44 handler t2 read a first;
45 handler t2 read a prev;
46 
47 handler t2 read a last;
48 handler t2 read a prev;
49 handler t2 read a next;
50 handler t2 read a next;
51 
52 handler t2 read a=(15);
53 handler t2 read a=(16);
54 
55 --error 1070
56 handler t2 read a=(19,"fff");
57 
58 handler t2 read b=(19,"fff");
59 handler t2 read b=(19,"yyy");
60 handler t2 read b=(19);
61 
62 --error 1109
63 handler t1 read a last;
64 
65 handler t2 read a=(11);
66 handler t2 read a>=(11);
67 
68 handler t2 read a=(18);
69 handler t2 read a>=(18);
70 handler t2 read a>(18);
71 handler t2 read a<=(18);
72 handler t2 read a<(18);
73 
74 handler t2 read a first limit 5;
75 handler t2 read a next limit 3;
76 handler t2 read a prev limit 10;
77 
78 handler t2 read a>=(16) limit 4;
79 handler t2 read a>=(16) limit 2,2;
80 handler t2 read a last limit 3;
81 
82 handler t2 read a=(19);
83 handler t2 read a=(19) where b="yyy";
84 
85 handler t2 read first;
86 handler t2 read next;
87 handler t2 read next;
88 --error 1064
89 handler t2 read last;
90 handler t2 close;
91 
92 handler t1 open;
93 handler t1 read a next; # this used to crash as a bug#5373
94 handler t1 read a next;
95 handler t1 close;
96 
97 handler t1 open;
98 handler t1 read a prev; # this used to crash as a bug#5373
99 handler t1 read a prev;
100 handler t1 close;
101 
102 handler t1 open as t2;
103 handler t2 read first;
104 eval alter table t1 engine = $engine_type;
105 --error 1109
106 handler t2 read first;
107 
108 #
109 # DROP TABLE / ALTER TABLE
110 #
111 handler t1 open as t2;
112 drop table t1;
113 create table t1 (a int);
114 insert into t1 values (17);
115 --error 1109
116 handler t2 read first;
117 handler t1 open as t2;
118 eval alter table t1 engine=$other_engine_type;
119 --error 1109
120 handler t2 read first;
121 drop table t1;
122 
123 #
124 # Test case for the bug #787
125 #
126 create table t1 (a int);
127 insert into t1 values (1),(2),(3),(4),(5),(6);
128 delete from t1 limit 2;
129 handler t1 open;
130 handler t1 read first;
131 handler t1 read first limit 1,1;
132 handler t1 read first limit 2,2;
133 delete from t1 limit 3;
134 handler t1 read first;
135 drop table t1;
136 
137 #
138 # Test for #751
139 #
140 create table t1(a int, index(a));
141 insert into t1 values (1), (2), (3);
142 handler t1 open;
143 --error 1054
144 handler t1 read a=(W);
145 --error 1210
146 handler t1 read a=(a);
147 drop table t1;
148 #
149 # BUG#2304
150 #
151 create table t1 (a char(5));
152 insert into t1 values ("Ok");
153 handler t1 open as t;
154 handler t read first;
155 use mysql;
156 handler t read first;
157 handler t close;
158 handler test.t1 open as t;
159 handler t read first;
160 handler t close;
161 use test;
162 drop table t1;
163 
164 #
165 # BUG#3649
166 #
167 create table t1 ( a int, b int, INDEX a (a) );
168 insert into t1 values (1,2), (2,1);
169 handler t1 open;
170 handler t1 read a=(1) where b=2;
171 handler t1 read a=(1) where b=3;
172 handler t1 read a=(1) where b=1;
173 handler t1 close;
174 drop table t1;
175 
176 #
177 # Check if two database names beginning the same are seen as different.
178 #
179 # This database begins like the usual 'test' database.
180 #
181 --disable_warnings
182 drop database if exists test_test;
183 --enable_warnings
184 create database test_test;
185 use test_test;
186 create table t1(table_id char(20) primary key);
187 insert into t1 values ('test_test.t1');
188 insert into t1 values ('');
189 handler t1 open;
190 handler t1 read first limit 9;
191 create table t2(table_id char(20) primary key);
192 insert into t2 values ('test_test.t2');
193 insert into t2 values ('');
194 handler t2 open;
195 handler t2 read first limit 9;
196 #
197 # This is the usual 'test' database.
198 #
199 use test;
200 --disable_warnings
201 drop table if exists t1;
202 --enable_warnings
203 create table t1(table_id char(20) primary key);
204 insert into t1 values ('test.t1');
205 insert into t1 values ('');
206 --error 1066
207 handler t1 open;
208 #
209 # Check accesibility of all the tables.
210 #
211 use test;
212 --error 1064
213 handler test.t1 read first limit 9;
214 --error 1064
215 handler test_test.t1 read first limit 9;
216 handler t1 read first limit 9;
217 --error 1064
218 handler test_test.t2 read first limit 9;
219 handler t2 read first limit 9;
220 
221 #
222 # Cleanup.
223 #
224 
225 --error 1064
226 handler test_test.t1 close;
227 handler t1 close;
228 drop table test_test.t1;
229 --error 1064
230 handler test_test.t2 close;
231 handler t2 close;
232 drop table test_test.t2;
233 drop database test_test;
234 
235 #
236 use test;
237 --error 1064
238 handler test.t1 close;
239 --error 1109
240 handler t1 close;
241 drop table test.t1;
242 
243 #
244 # BUG#4335
245 #
246 --disable_warnings
247 drop database if exists test_test;
248 drop table if exists t1;
249 drop table if exists t2;
250 drop table if exists t3;
251 --enable_warnings
252 create database test_test;
253 use test_test;
254 create table t1 (c1 char(20));
255 insert into t1 values ('test_test.t1');
256 create table t3 (c1 char(20));
257 insert into t3 values ('test_test.t3');
258 handler t1 open;
259 handler t1 read first limit 9;
260 handler t1 open h1;
261 handler h1 read first limit 9;
262 use test;
263 create table t1 (c1 char(20));
264 create table t2 (c1 char(20));
265 create table t3 (c1 char(20));
266 insert into t1 values ('t1');
267 insert into t2 values ('t2');
268 insert into t3 values ('t3');
269 --error 1066
270 handler t1 open;
271 --error 1066
272 handler t2 open t1;
273 --error 1066
274 handler t3 open t1;
275 handler t1 read first limit 9;
276 --error 1064
277 handler test.t1 close;
278 --error 1066
279 handler test.t1 open h1;
280 --error 1066
281 handler test_test.t1 open h1;
282 handler test_test.t3 open h3;
283 handler test.t1 open h2;
284 handler t1 read first limit 9;
285 handler h1 read first limit 9;
286 handler h2 read first limit 9;
287 handler h3 read first limit 9;
288 handler h2 read first limit 9;
289 --error 1064
290 handler test.h1 close;
291 handler t1 close;
292 handler h1 close;
293 handler h2 close;
294 --error 1109
295 handler t1 read first limit 9;
296 --error 1109
297 handler h1 read first limit 9;
298 --error 1109
299 handler h2 read first limit 9;
300 handler h3 read first limit 9;
301 handler h3 read first limit 9;
302 use test_test;
303 handler h3 read first limit 9;
304 --error 1064
305 handler test.h3 read first limit 9;
306 handler h3 close;
307 use test;
308 drop table t3;
309 drop table t2;
310 drop table t1;
311 drop database test_test;
312 
313 #
314 # Test if fix for BUG#4286 correctly closes handler tables.
315 #
316 create table t1 (c1 char(20));
317 insert into t1 values ("t1");
318 handler t1 open as h1;
319 handler h1 read first limit 9;
320 create table t2 (c1 char(20));
321 insert into t2 values ("t2");
322 handler t2 open as h2;
323 handler h2 read first limit 9;
324 create table t3 (c1 char(20));
325 insert into t3 values ("t3");
326 handler t3 open as h3;
327 handler h3 read first limit 9;
328 create table t4 (c1 char(20));
329 insert into t4 values ("t4");
330 handler t4 open as h4;
331 handler h4 read first limit 9;
332 create table t5 (c1 char(20));
333 insert into t5 values ("t5");
334 handler t5 open as h5;
335 handler h5 read first limit 9;
336 # close first
337 eval alter table t1 engine=$other_handler_engine_type;
338 --error 1109
339 handler h1 read first limit 9;
340 handler h2 read first limit 9;
341 handler h3 read first limit 9;
342 handler h4 read first limit 9;
343 handler h5 read first limit 9;
344 # close last
345 eval alter table t5 engine=$other_handler_engine_type;
346 --error 1109
347 handler h1 read first limit 9;
348 handler h2 read first limit 9;
349 handler h3 read first limit 9;
350 handler h4 read first limit 9;
351 --error 1109
352 handler h5 read first limit 9;
353 # close middle
354 eval alter table t3 engine=$other_handler_engine_type;
355 --error 1109
356 handler h1 read first limit 9;
357 handler h2 read first limit 9;
358 --error 1109
359 handler h3 read first limit 9;
360 handler h4 read first limit 9;
361 --error 1109
362 handler h5 read first limit 9;
363 handler h2 close;
364 handler h4 close;
365 # close all depending handler tables
366 handler t1 open as h1_1;
367 handler t1 open as h1_2;
368 handler t1 open as h1_3;
369 handler h1_1 read first limit 9;
370 handler h1_2 read first limit 9;
371 handler h1_3 read first limit 9;
372 eval alter table t1 engine=$engine_type;
373 --error 1109
374 handler h1_1 read first limit 9;
375 --error 1109
376 handler h1_2 read first limit 9;
377 --error 1109
378 handler h1_3 read first limit 9;
379 drop table t1;
380 drop table t2;
381 drop table t3;
382 drop table t4;
383 drop table t5;
384 
385 #
386 # Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash
387 #
388 create table t1 (c1 int);
389 insert into t1 values (1);
390 # client 1
391 handler t1 open;
392 handler t1 read first;
393 # client 2
394 connect (con2,localhost,root,,);
395 connection con2;
396 --exec echo send the below to another connection, do not wait for the result
397 send optimize table t1;
398 --sleep 1
399 # client 1
400 --exec echo proceed with the normal connection
401 connection default;
402 handler t1 read next;
403 handler t1 close;
404 # client 2
405 --exec echo read the result from the other connection
406 connection con2;
407 reap;
408 # client 1
409 --exec echo proceed with the normal connection
410 connection default;
411 drop table t1;
412 
413 CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY (no1,no2));
414 INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
415 HANDLER t1 OPEN;
416 HANDLER t1 READ `primary` = (1, 1000);
417 HANDLER t1 READ `primary` PREV;
418 DROP TABLE t1;
419 
420 # End of 4.1 tests
421 
422 #
423 # Addendum to Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash
424 # Show that DROP TABLE can no longer deadlock against
425 # FLUSH TABLES WITH READ LOCK. This is a 5.0 issue.
426 #
427 create table t1 (c1 int);
428 insert into t1 values (14397);
429 flush tables with read lock;
430 # The thread with the global read lock cannot drop the table itself:
431 --error 1223
432 drop table t1;
433 #
434 # client 2
435 # We need a second connection to try the drop.
436 # The drop waits for the global read lock to go away.
437 # Without the addendum fix it locked LOCK_open before entering the wait loop.
438 connection con2;
439 --exec echo send the below to another connection, do not wait for the result
440 send drop table t1;
441 --sleep 1
442 #
443 # client 1
444 # Now we need something that wants LOCK_open. A simple table access which
445 # opens the table does the trick.
446 --exec echo proceed with the normal connection
447 connection default;
448 # This would hang on LOCK_open without the 5.0 addendum fix.
449 select * from t1;
450 # Release the read lock. This should make the DROP go through.
451 unlock tables;
452 #
453 # client 2
454 # Read the result of the drop command.
455 connection con2;
456 --exec echo read the result from the other connection
457 reap;
458 #
459 # client 1
460 # Now back to normal operation. The table should not exist any more.
461 --exec echo proceed with the normal connection
462 connection default;
463 --error 1146
464 select * from t1;
465 # Just to be sure and not confuse the next test case writer.
466 drop table if exists t1;
467 
468 #
469 # Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one
470 #
471 --disable_warnings
472 drop table if exists t1;
473 --enable_warnings
474 eval create table t1 (a int) ENGINE=$other_engine_type;
475 --echo --> client 2
476 connection con2;
477 --error 1031
478 handler t1 open;
479 --echo --> client 1
480 connection default;
481 drop table t1;
482 disconnect con2;
483 
484 #
485 # Bug#30632 HANDLER read failure causes hang
486 #
487 --disable_warnings
488 drop table if exists t1;
489 --enable_warnings
490 create table t1 (a int);
491 handler t1 open as t1_alias;
492 --error 1176
493 handler t1_alias read a next;
494 --error 1054
495 handler t1_alias READ a next where inexistent > 0;
496 --error 1176
497 handler t1_alias read a next;
498 --error 1054
499 handler t1_alias READ a next where inexistent > 0;
500 handler t1_alias close;
501 drop table t1;
502 
503 #
504 # Bug#21587 FLUSH TABLES causes server crash when used with HANDLER statements
505 #
506 
507 --disable_warnings
508 drop table if exists t1,t2;
509 --enable_warnings
510 create table t1 (c1 int);
511 create table t2 (c1 int);
512 insert into t1 values (1);
513 insert into t2 values (2);
514 --echo connection: default
515 handler t1 open;
516 handler t1 read first;
517 connect (flush,localhost,root,,);
518 connection flush;
519 --echo connection: flush
520 --send flush tables;
521 connect (waiter,localhost,root,,);
522 connection waiter;
523 --echo connection: waiter
524 let $wait_condition=
525  select count(*) = 1 from information_schema.processlist
526  where state = "Waiting for table flush";
527 --source include/wait_condition.inc
528 connection default;
529 --echo connection: default
530 handler t2 open;
531 handler t2 read first;
532 handler t1 read next;
533 handler t1 close;
534 handler t2 close;
535 connection flush;
536 reap;
537 connection default;
538 drop table t1,t2;
539 disconnect flush;
540 
541 #
542 # Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements
543 #
544 
545 --disable_warnings
546 drop table if exists t1, t0;
547 --enable_warnings
548 create table t1 (c1 int);
549 --echo connection: default
550 handler t1 open;
551 handler t1 read first;
552 connect (flush,localhost,root,,);
553 connection flush;
554 --echo connection: flush
555 --send rename table t1 to t0;
556 connection waiter;
557 --echo connection: waiter
558 let $wait_condition=
559  select count(*) = 1 from information_schema.processlist
560  where state = "Waiting for table metadata lock" and
561  info = "rename table t1 to t0";
562 --source include/wait_condition.inc
563 connection default;
564 --echo connection: default
565 --echo #
566 --echo # RENAME placed two pending locks and waits.
567 --echo # When HANDLER t0 OPEN does open_tables(), it calls
568 --echo # mysql_ha_flush(), which in turn closes the open HANDLER for t1.
569 --echo # RENAME TABLE gets unblocked. If it gets scheduled quickly
570 --echo # and manages to complete before open_tables()
571 --echo # of HANDLER t0 OPEN, open_tables() and therefore the whole
572 --echo # HANDLER t0 OPEN succeeds. Otherwise open_tables()
573 --echo # notices a pending or active exclusive metadata lock on t2
574 --echo # and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK
575 --echo # error.
576 --echo #
577 --error 0, ER_LOCK_DEADLOCK
578 handler t0 open;
579 --error 0, ER_UNKNOWN_TABLE
580 handler t0 close;
581 --echo connection: flush
582 connection flush;
583 reap;
584 --error ER_UNKNOWN_TABLE
585 handler t1 read next;
586 --error ER_UNKNOWN_TABLE
587 handler t1 close;
588 connection default;
589 drop table t0;
590 connection flush;
591 disconnect flush;
592 --source include/wait_until_disconnected.inc
593 connection waiter;
594 disconnect waiter;
595 --source include/wait_until_disconnected.inc
596 connection default;
597 
598 #
599 # Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
600 #
601 # Test HANDLER statements in conjunction with temporary tables. While the temporary table
602 # is open by a HANDLER, no other statement can access it.
603 #
604 
605 --disable_warnings
606 drop table if exists t1;
607 --enable_warnings
608 create temporary table t1 (a int, b char(1), key a(a), key b(a,b));
609 insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
610  (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j");
611 select a,b from t1;
612 handler t1 open as a1;
613 handler a1 read a first;
614 handler a1 read a next;
615 handler a1 read a next;
616 --error ER_CANT_REOPEN_TABLE
617 select a,b from t1;
618 handler a1 read a prev;
619 handler a1 read a prev;
620 handler a1 read a=(6) where b="g";
621 handler a1 close;
622 select a,b from t1;
623 handler t1 open as a2;
624 handler a2 read a first;
625 handler a2 read a last;
626 handler a2 read a prev;
627 handler a2 close;
628 drop table t1;
629 
630 #
631 # Bug#31397 Inconsistent drop table behavior of handler tables.
632 #
633 
634 --disable_warnings
635 drop table if exists t1,t2;
636 --enable_warnings
637 create table t1 (a int);
638 handler t1 open as t1_alias;
639 drop table t1;
640 create table t1 (a int);
641 handler t1 open as t1_alias;
642 flush tables;
643 drop table t1;
644 create table t1 (a int);
645 handler t1 open as t1_alias;
646 handler t1_alias close;
647 drop table t1;
648 create table t1 (a int);
649 handler t1 open as t1_alias;
650 handler t1_alias read first;
651 drop table t1;
652 --error ER_UNKNOWN_TABLE
653 handler t1_alias read next;
654 
655 # Test that temporary tables associated with handlers are properly dropped.
656 
657 create table t1 (a int);
658 create temporary table t2 (a int, key(a));
659 handler t1 open as a1;
660 handler t2 open as a2;
661 handler a2 read a first;
662 drop table t1, t2;
663 --error ER_UNKNOWN_TABLE
664 handler a2 read a next;
665 --error ER_UNKNOWN_TABLE
666 handler a1 close;
667 
668 # Alter table drop handlers
669 
670 create table t1 (a int, key(a));
671 create table t2 like t1;
672 handler t1 open as a1;
673 handler t2 open as a2;
674 handler a1 read a first;
675 handler a2 read a first;
676 alter table t1 add b int;
677 --error ER_UNKNOWN_TABLE
678 handler a1 close;
679 handler a2 close;
680 drop table t1, t2;
681 
682 # Rename table drop handlers
683 
684 create table t1 (a int, key(a));
685 handler t1 open as a1;
686 handler a1 read a first;
687 rename table t1 to t2;
688 --error ER_UNKNOWN_TABLE
689 handler a1 read a first;
690 drop table t2;
691 
692 # Optimize table drop handlers
693 
694 create table t1 (a int, key(a));
695 create table t2 like t1;
696 handler t1 open as a1;
697 handler t2 open as a2;
698 handler a1 read a first;
699 handler a2 read a first;
700 optimize table t1;
701 --error ER_UNKNOWN_TABLE
702 handler a1 close;
703 handler a2 close;
704 drop table t1, t2;
705 
706 # Flush tables causes handlers reopen
707 
708 create table t1 (a int, b char(1), key a(a), key b(a,b));
709 insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
710  (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j");
711 handler t1 open;
712 handler t1 read a first;
713 handler t1 read a next;
714 flush tables;
715 handler t1 read a next;
716 handler t1 read a next;
717 flush tables with read lock;
718 handler t1 read a next;
719 unlock tables;
720 drop table t1;
721 --error ER_UNKNOWN_TABLE
722 handler t1 read a next;
723 
724 #
725 # Bug#41110: crash with handler command when used concurrently with alter table
726 # Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table
727 #
728 
729 connect(con1,localhost,root,,);
730 connect(con2,localhost,root,,);
731 
732 connection default;
733 --disable_warnings
734 drop table if exists t1;
735 --enable_warnings
736 --echo # First test case which is supposed trigger the execution
737 --echo # path on which problem was discovered.
738 create table t1 (a int);
739 insert into t1 values (1);
740 handler t1 open;
741 connection con1;
742 lock table t1 write;
743 send alter table t1 engine=memory;
744 connection con2;
745 let $wait_condition=
746  select count(*) = 1 from information_schema.processlist
747  where state = "Waiting for table metadata lock" and
748  info = "alter table t1 engine=memory";
749 --source include/wait_condition.inc
750 connection default;
751 --error ER_ILLEGAL_HA
752 handler t1 read a next;
753 handler t1 close;
754 connection con1;
755 --reap
756 unlock tables;
757 drop table t1;
758 --echo # Now test case which was reported originally but which no longer
759 --echo # triggers execution path which has caused the problem.
760 connection default;
761 create table t1 (a int, key(a));
762 insert into t1 values (1);
763 handler t1 open;
764 connection con1;
765 send alter table t1 engine=memory;
766 connection con2;
767 let $wait_condition=
768  select count(*) = 1 from information_schema.processlist
769  where state = "Waiting for table metadata lock" and
770  info = "alter table t1 engine=memory";
771 --source include/wait_condition.inc
772 connection default;
773 --echo # Since S metadata lock was already acquired at HANDLER OPEN time
774 --echo # and TL_READ lock requested by HANDLER READ is compatible with
775 --echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
776 --echo # without waiting. The old version of table should be used in it.
777 handler t1 read a next;
778 handler t1 close;
779 connection con1;
780 --reap # Since last in this connection was a send
781 drop table t1;
782 disconnect con1;
783 --source include/wait_until_disconnected.inc
784 connection con2;
785 disconnect con2;
786 --source include/wait_until_disconnected.inc
787 connection default;
788 
789 #
790 # Bug#44151 using handler commands on information_schema tables crashes server
791 #
792 USE information_schema;
793 --error ER_WRONG_USAGE
794 HANDLER COLUMNS OPEN;
795 USE test;
796 
797 --echo #
798 --echo # Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL.
799 --echo #
800 --disable_warnings
801 drop table if exists t1, t2, t3;
802 --enable_warnings
803 create table t1 (a int, key a (a));
804 insert into t1 (a) values (1), (2), (3), (4), (5);
805 create table t2 (a int, key a (a)) select * from t1;
806 create temporary table t3 (a int, key a (a)) select * from t2;
807 create temporary table t4 like t3;
808 handler t1 open;
809 handler t2 open;
810 handler t3 open;
811 --echo #
812 --echo # No HANDLER sql is allowed under LOCK TABLES.
813 --echo # But it does not implicitly closes all handlers.
814 --echo #
815 lock table t1 read;
816 --error ER_LOCK_OR_ACTIVE_TRANSACTION
817 handler t1 open;
818 --error ER_LOCK_OR_ACTIVE_TRANSACTION
819 handler t1 read next;
820 --error ER_LOCK_OR_ACTIVE_TRANSACTION
821 handler t2 close;
822 --error ER_LOCK_OR_ACTIVE_TRANSACTION
823 handler t3 open;
824 --error ER_LOCK_OR_ACTIVE_TRANSACTION
825 handler t4 open;
826 --echo # After UNLOCK TABLES handlers should be around and
827 --echo # we should be able to continue reading through them.
828 unlock tables;
829 handler t1 read next;
830 handler t1 close;
831 handler t2 read next;
832 handler t2 close;
833 handler t3 read next;
834 handler t3 close;
835 drop temporary tables t3, t4;
836 --echo #
837 --echo # Other operations that implicitly close handler:
838 --echo #
839 --echo # TRUNCATE
840 --echo #
841 handler t1 open;
842 truncate table t1;
843 --error ER_UNKNOWN_TABLE
844 handler t1 read next;
845 handler t1 open;
846 --echo #
847 --echo # CREATE TRIGGER
848 --echo #
849 create trigger t1_ai after insert on t1 for each row set @a=1;
850 --error ER_UNKNOWN_TABLE
851 handler t1 read next;
852 --echo #
853 --echo # DROP TRIGGER
854 --echo #
855 handler t1 open;
856 drop trigger t1_ai;
857 --error ER_UNKNOWN_TABLE
858 handler t1 read next;
859 --echo #
860 --echo # ALTER TABLE
861 --echo #
862 handler t1 open;
863 alter table t1 add column b int;
864 --error ER_UNKNOWN_TABLE
865 handler t1 read next;
866 --echo #
867 --echo # ANALYZE TABLE
868 --echo #
869 handler t1 open;
870 analyze table t1;
871 --error ER_UNKNOWN_TABLE
872 handler t1 read next;
873 --echo #
874 --echo # OPTIMIZE TABLE
875 --echo #
876 handler t1 open;
877 optimize table t1;
878 --error ER_UNKNOWN_TABLE
879 handler t1 read next;
880 --echo #
881 --echo # REPAIR TABLE
882 --echo #
883 handler t1 open;
884 repair table t1;
885 --error ER_UNKNOWN_TABLE
886 handler t1 read next;
887 --echo #
888 --echo # DROP TABLE, naturally.
889 --echo #
890 handler t1 open;
891 drop table t1;
892 --error ER_UNKNOWN_TABLE
893 handler t1 read next;
894 create table t1 (a int, b int, key a (a)) select a from t2;
895 --echo #
896 --echo # RENAME TABLE, naturally
897 --echo #
898 handler t1 open;
899 rename table t1 to t3;
900 --error ER_UNKNOWN_TABLE
901 handler t1 read next;
902 --echo #
903 --echo # CREATE TABLE (even with IF NOT EXISTS clause,
904 --echo # and the table exists).
905 --echo #
906 handler t2 open;
907 create table if not exists t2 (a int);
908 --error ER_UNKNOWN_TABLE
909 handler t2 read next;
910 rename table t3 to t1;
911 drop table t2;
912 --echo #
913 --echo # FLUSH TABLE doesn't close the table but loses the position
914 --echo #
915 handler t1 open;
916 handler t1 read a prev;
917 flush table t1;
918 handler t1 read a prev;
919 handler t1 close;
920 --echo #
921 --echo # FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE.
922 --echo #
923 handler t1 open;
924 handler t1 read a prev;
925 flush tables with read lock;
926 handler t1 read a prev;
927 handler t1 close;
928 unlock tables;
929 --echo #
930 --echo # Let us also check that these operations behave in similar
931 --echo # way under LOCK TABLES.
932 --echo #
933 --echo # TRUNCATE under LOCK TABLES.
934 --echo #
935 handler t1 open;
936 lock tables t1 write;
937 truncate table t1;
938 unlock tables;
939 --error ER_UNKNOWN_TABLE
940 handler t1 read next;
941 handler t1 open;
942 --echo #
943 --echo # CREATE TRIGGER under LOCK TABLES.
944 --echo #
945 lock tables t1 write;
946 create trigger t1_ai after insert on t1 for each row set @a=1;
947 unlock tables;
948 --error ER_UNKNOWN_TABLE
949 handler t1 read next;
950 --echo #
951 --echo # DROP TRIGGER under LOCK TABLES.
952 --echo #
953 handler t1 open;
954 lock tables t1 write;
955 drop trigger t1_ai;
956 unlock tables;
957 --error ER_UNKNOWN_TABLE
958 handler t1 read next;
959 --echo #
960 --echo # ALTER TABLE under LOCK TABLES.
961 --echo #
962 handler t1 open;
963 lock tables t1 write;
964 alter table t1 drop column b;
965 unlock tables;
966 --error ER_UNKNOWN_TABLE
967 handler t1 read next;
968 --echo #
969 --echo # ANALYZE TABLE under LOCK TABLES.
970 --echo #
971 handler t1 open;
972 lock tables t1 write;
973 analyze table t1;
974 unlock tables;
975 --error ER_UNKNOWN_TABLE
976 handler t1 read next;
977 --echo #
978 --echo # OPTIMIZE TABLE under LOCK TABLES.
979 --echo #
980 handler t1 open;
981 lock tables t1 write;
982 optimize table t1;
983 unlock tables;
984 --error ER_UNKNOWN_TABLE
985 handler t1 read next;
986 --echo #
987 --echo # REPAIR TABLE under LOCK TABLES.
988 --echo #
989 handler t1 open;
990 lock tables t1 write;
991 repair table t1;
992 unlock tables;
993 --error ER_UNKNOWN_TABLE
994 handler t1 read next;
995 --echo #
996 --echo # DROP TABLE under LOCK TABLES, naturally.
997 --echo #
998 handler t1 open;
999 lock tables t1 write;
1000 drop table t1;
1001 unlock tables;
1002 --error ER_UNKNOWN_TABLE
1003 handler t1 read next;
1004 create table t1 (a int, b int, key a (a));
1005 insert into t1 (a) values (1), (2), (3), (4), (5);
1006 --echo #
1007 --echo # FLUSH TABLE doesn't close the table but loses the position
1008 --echo #
1009 handler t1 open;
1010 handler t1 read a prev;
1011 lock tables t1 write;
1012 flush table t1;
1013 unlock tables;
1014 handler t1 read a prev;
1015 handler t1 close;
1016 --echo #
1017 --echo # Explore the effect of HANDLER locks on concurrent DDL
1018 --echo #
1019 handler t1 open;
1020 --echo # Establishing auxiliary connections con1, con2, con3
1021 connect(con1, localhost, root,,);
1022 connect(con2, localhost, root,,);
1023 connect(con3, localhost, root,,);
1024 --echo # --> connection con1;
1025 connection con1;
1026 --echo # Sending:
1027 --send drop table t1
1028 --echo # We can't use connection 'default' as wait_condition will
1029 --echo # autoclose handlers.
1030 --echo # --> connection con2
1031 connection con2;
1032 --echo # Waitng for 'drop table t1' to get blocked...
1033 let $wait_condition=select count(*)=1 from information_schema.processlist
1034  where state='Waiting for table metadata lock' and
1035  info='drop table t1';
1036 --source include/wait_condition.inc
1037 --echo # --> connection default
1038 connection default;
1039 handler t1 read a prev;
1040 handler t1 read a prev;
1041 handler t1 close;
1042 --echo # --> connection con1
1043 connection con1;
1044 --echo # Reaping 'drop table t1'...
1045 --reap
1046 --echo # --> connection default
1047 connection default;
1048 --echo #
1049 --echo # Explore the effect of HANDLER locks in parallel with SELECT
1050 --echo #
1051 create table t1 (a int, key a (a));
1052 insert into t1 (a) values (1), (2), (3), (4), (5);
1053 begin;
1054 select * from t1;
1055 handler t1 open;
1056 handler t1 read a prev;
1057 handler t1 read a prev;
1058 handler t1 close;
1059 --echo # --> connection con1;
1060 connection con1;
1061 --echo # Sending:
1062 --send drop table t1
1063 --echo # --> connection con2
1064 connection con2;
1065 --echo # Waiting for 'drop table t1' to get blocked...
1066 let $wait_condition=select count(*)=1 from information_schema.processlist
1067  where state='Waiting for table metadata lock' and
1068  info='drop table t1';
1069 --source include/wait_condition.inc
1070 --echo # --> connection default
1071 connection default;
1072 --echo # We can still use the table, it's part of the transaction
1073 select * from t1;
1074 --echo # Such are the circumstances that t1 is a part of transaction,
1075 --echo # thus we can reopen it in the handler
1076 handler t1 open;
1077 --echo # We can commit the transaction, it doesn't close the handler
1078 --echo # and doesn't let DROP to proceed.
1079 commit;
1080 handler t1 read a prev;
1081 handler t1 read a prev;
1082 handler t1 read a prev;
1083 handler t1 close;
1084 --echo # --> connection con1
1085 connection con1;
1086 --echo # Now drop can proceed
1087 --echo # Reaping 'drop table t1'...
1088 --reap
1089 --echo # --> connection default
1090 connection default;
1091 --echo #
1092 --echo # Demonstrate that HANDLER locks and transaction locks
1093 --echo # reside in the same context.
1094 --echo #
1095 create table t1 (a int, key a (a));
1096 insert into t1 (a) values (1), (2), (3), (4), (5);
1097 create table t0 (a int, key a (a));
1098 insert into t0 (a) values (1), (2), (3), (4), (5);
1099 begin;
1100 select * from t1;
1101 --echo # --> connection con2
1102 connection con2;
1103 --echo # Sending:
1104 send rename table t0 to t3, t1 to t0, t3 to t1;
1105 --echo # --> connection con1
1106 connection con1;
1107 --echo # Waiting for 'rename table ...' to get blocked...
1108 let $wait_condition=select count(*)=1 from information_schema.processlist
1109  where state='Waiting for table metadata lock' and
1110  info='rename table t0 to t3, t1 to t0, t3 to t1';
1111 --source include/wait_condition.inc
1112 --echo # --> connection default
1113 connection default;
1114 --echo # We back-off on hitting deadlock condition.
1115 --error ER_LOCK_DEADLOCK
1116 handler t0 open;
1117 select * from t0;
1118 handler t1 open;
1119 commit;
1120 handler t1 close;
1121 --echo # --> connection con2
1122 connection con2;
1123 --echo # Reaping 'rename table ...'...
1124 --reap
1125 --echo # --> connection default
1126 connection default;
1127 handler t1 open;
1128 handler t1 read a prev;
1129 handler t1 close;
1130 drop table t0;
1131 --echo #
1132 --echo # Originally there was a deadlock error in this test.
1133 --echo # With implementation of deadlock detector
1134 --echo # we no longer deadlock, but block and wait on a lock.
1135 --echo # The HANDLER is auto-closed as soon as the connection
1136 --echo # sees a pending conflicting lock against it.
1137 --echo #
1138 create table t2 (a int, key a (a));
1139 handler t1 open;
1140 --echo # --> connection con1
1141 connection con1;
1142 lock tables t2 read;
1143 --echo # --> connection con2
1144 connection con2;
1145 --echo # Sending 'drop table t2'...
1146 --send drop table t2
1147 --echo # --> connection con1
1148 connection con1;
1149 --echo # Waiting for 'drop table t2' to get blocked...
1150 let $wait_condition=select count(*)=1 from information_schema.processlist
1151  where state='Waiting for table metadata lock' and
1152  info='drop table t2';
1153 --source include/wait_condition.inc
1154 --echo # --> connection default
1155 connection default;
1156 --echo # Sending 'select * from t2'
1157 send select * from t2;
1158 --echo # --> connection con1
1159 connection con1;
1160 --echo # Waiting for 'select * from t2' to get blocked...
1161 let $wait_condition=select count(*)=1 from information_schema.processlist
1162  where state='Waiting for table metadata lock' and
1163  info='select * from t2';
1164 unlock tables;
1165 --echo # --> connection con2
1166 connection con2;
1167 --echo # Reaping 'drop table t2'...
1168 --reap
1169 --echo # --> connection default
1170 connection default;
1171 --echo # Reaping 'select * from t2'
1172 --error ER_NO_SUCH_TABLE
1173 reap;
1174 handler t1 close;
1175 
1176 --echo #
1177 --echo # ROLLBACK TO SAVEPOINT releases transactional locks,
1178 --echo # but has no effect on open HANDLERs
1179 --echo #
1180 create table t2 like t1;
1181 create table t3 like t1;
1182 begin;
1183 --echo # Have something before the savepoint
1184 select * from t3;
1185 savepoint sv;
1186 handler t1 open;
1187 handler t1 read a first;
1188 handler t1 read a next;
1189 select * from t2;
1190 --echo # --> connection con1
1191 connection con1;
1192 --echo # Sending:
1193 --send drop table t1
1194 --echo # --> connection con2
1195 connection con2;
1196 --echo # Sending:
1197 --send drop table t2
1198 --echo # --> connection default
1199 connection default;
1200 --echo # Let DROP TABLE statements sync in. We must use
1201 --echo # a separate connection for that, because otherwise SELECT
1202 --echo # will auto-close the HANDLERs, becaues there are pending
1203 --echo # exclusive locks against them.
1204 --echo # --> connection con3
1205 connection con3;
1206 --echo # Waiting for 'drop table t1' to get blocked...
1207 let $wait_condition=select count(*)=1 from information_schema.processlist
1208  where state='Waiting for table metadata lock' and
1209  info='drop table t1';
1210 --source include/wait_condition.inc
1211 --echo # Waiting for 'drop table t2' to get blocked...
1212 let $wait_condition=select count(*)=1 from information_schema.processlist
1213  where state='Waiting for table metadata lock' and
1214  info='drop table t2';
1215 --source include/wait_condition.inc
1216 --echo # Demonstrate that t2 lock was released and t2 was dropped
1217 --echo # after ROLLBACK TO SAVEPOINT
1218 --echo # --> connection default
1219 connection default;
1220 rollback to savepoint sv;
1221 --echo # --> connection con2
1222 connection con2;
1223 --echo # Reaping 'drop table t2'...
1224 --reap
1225 --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler
1226 --echo # lock.
1227 --echo # --> connection default
1228 connection default;
1229 handler t1 read a next;
1230 handler t1 read a next;
1231 --echo # Demonstrate that the drop will go through as soon as we close the
1232 --echo # HANDLER
1233 handler t1 close;
1234 --echo # connection con1
1235 connection con1;
1236 --echo # Reaping 'drop table t1'...
1237 --reap
1238 --echo # --> connection default
1239 connection default;
1240 commit;
1241 drop table t3;
1242 --echo #
1243 --echo # A few special cases when using SAVEPOINT/ROLLBACK TO
1244 --echo # SAVEPOINT and HANDLER.
1245 --echo #
1246 --echo # Show that rollback to the savepoint taken in the beginning
1247 --echo # of the transaction doesn't release mdl lock on
1248 --echo # the HANDLER that was opened later.
1249 --echo #
1250 create table t1 (a int, key a(a));
1251 insert into t1 (a) values (1), (2), (3), (4), (5);
1252 create table t2 like t1;
1253 begin;
1254 savepoint sv;
1255 handler t1 open;
1256 handler t1 read a first;
1257 handler t1 read a next;
1258 select * from t2;
1259 --echo # --> connection con1
1260 connection con1;
1261 --echo # Sending:
1262 --send drop table t1
1263 --echo # --> connection con2
1264 connection con2;
1265 --echo # Sending:
1266 --send drop table t2
1267 --echo # --> connection default
1268 connection default;
1269 --echo # Let DROP TABLE statements sync in. We must use
1270 --echo # a separate connection for that, because otherwise SELECT
1271 --echo # will auto-close the HANDLERs, becaues there are pending
1272 --echo # exclusive locks against them.
1273 --echo # --> connection con3
1274 connection con3;
1275 --echo # Waiting for 'drop table t1' to get blocked...
1276 let $wait_condition=select count(*)=1 from information_schema.processlist
1277  where state='Waiting for table metadata lock' and
1278  info='drop table t1';
1279 --source include/wait_condition.inc
1280 --echo # Waiting for 'drop table t2' to get blocked...
1281 let $wait_condition=select count(*)=1 from information_schema.processlist
1282  where state='Waiting for table metadata lock' and
1283  info='drop table t2';
1284 --source include/wait_condition.inc
1285 --echo # Demonstrate that t2 lock was released and t2 was dropped
1286 --echo # after ROLLBACK TO SAVEPOINT
1287 --echo # --> connection default
1288 connection default;
1289 rollback to savepoint sv;
1290 --echo # --> connection con2
1291 connection con2;
1292 --echo # Reaping 'drop table t2'...
1293 --reap
1294 --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler
1295 --echo # lock.
1296 --echo # --> connection default
1297 connection default;
1298 handler t1 read a next;
1299 handler t1 read a next;
1300 --echo # Demonstrate that the drop will go through as soon as we close the
1301 --echo # HANDLER
1302 handler t1 close;
1303 --echo # connection con1
1304 connection con1;
1305 --echo # Reaping 'drop table t1'...
1306 --reap
1307 --echo # --> connection default
1308 connection default;
1309 commit;
1310 --echo #
1311 --echo # Show that rollback to the savepoint taken in the beginning
1312 --echo # of the transaction works properly (no valgrind warnins, etc),
1313 --echo # even though it's done after the HANDLER mdl lock that was there
1314 --echo # at the beginning is released and added again.
1315 --echo #
1316 create table t1 (a int, key a(a));
1317 insert into t1 (a) values (1), (2), (3), (4), (5);
1318 create table t2 like t1;
1319 create table t3 like t1;
1320 insert into t3 (a) select a from t1;
1321 begin;
1322 handler t1 open;
1323 savepoint sv;
1324 handler t1 read a first;
1325 select * from t2;
1326 handler t1 close;
1327 handler t3 open;
1328 handler t3 read a first;
1329 rollback to savepoint sv;
1330 --echo # --> connection con1
1331 connection con1;
1332 drop table t1, t2;
1333 --echo # Sending:
1334 --send drop table t3
1335 --echo # Let DROP TABLE statement sync in.
1336 --echo # --> connection con2
1337 connection con2;
1338 --echo # Waiting for 'drop table t3' to get blocked...
1339 let $wait_condition=select count(*)=1 from information_schema.processlist
1340  where state='Waiting for table metadata lock' and
1341  info='drop table t3';
1342 --source include/wait_condition.inc
1343 --echo # Demonstrate that ROLLBACK TO SAVEPOINT didn't release the handler
1344 --echo # lock.
1345 --echo # --> connection default
1346 connection default;
1347 handler t3 read a next;
1348 --echo # Demonstrate that the drop will go through as soon as we close the
1349 --echo # HANDLER
1350 handler t3 close;
1351 --echo # connection con1
1352 connection con1;
1353 --echo # Reaping 'drop table t3'...
1354 --reap
1355 --echo # --> connection default
1356 connection default;
1357 commit;
1358 
1359 --echo #
1360 --echo # If we have to wait on an exclusive locks while having
1361 --echo # an open HANDLER, ER_LOCK_DEADLOCK is reported.
1362 --echo #
1363 create table t1 (a int, key a(a));
1364 create table t2 like t1;
1365 handler t1 open;
1366 --echo # --> connection con1
1367 connection con1;
1368 lock table t1 write, t2 write;
1369 --echo # --> connection default
1370 connection default;
1371 send drop table t2;
1372 --echo # --> connection con2
1373 connection con2;
1374 --echo # Waiting for 'drop table t2' to get blocked...
1375 let $wait_condition=select count(*)=1 from information_schema.processlist
1376  where state='Waiting for table metadata lock' and
1377  info='drop table t2';
1378 --source include/wait_condition.inc
1379 --echo # --> connection con1
1380 connection con1;
1381 --error ER_LOCK_DEADLOCK
1382 drop table t1;
1383 unlock tables;
1384 --echo # --> connection default
1385 connection default;
1386 reap;
1387 
1388 --echo # Demonstrate that there is no deadlock with FLUSH TABLE,
1389 --echo # even though it is waiting for the other table to go away
1390 create table t2 like t1;
1391 --echo # Sending:
1392 --send flush table t2
1393 --echo # --> connection con2
1394 connection con2;
1395 drop table t1;
1396 --echo # --> connection con1
1397 connection con1;
1398 unlock tables;
1399 --echo # --> connection default
1400 connection default;
1401 --echo # Reaping 'flush table t2'...
1402 --reap
1403 drop table t2;
1404 
1405 --echo #
1406 --echo # Bug #46224 HANDLER statements within a transaction might
1407 --echo # lead to deadlocks
1408 --echo #
1409 create table t1 (a int, key a(a));
1410 insert into t1 values (1), (2);
1411 
1412 --echo # --> connection default
1413 connection default;
1414 begin;
1415 select * from t1;
1416 handler t1 open;
1417 
1418 --echo # --> connection con1
1419 connection con1;
1420 --echo # Sending:
1421 --send lock tables t1 write
1422 
1423 --echo # --> connection con2
1424 connection con2;
1425 --echo # Check that 'lock tables t1 write' waits until transaction which
1426 --echo # has read from the table commits.
1427 let $wait_condition=
1428  select count(*) = 1 from information_schema.processlist
1429  where state = "Waiting for table metadata lock" and
1430  info = "lock tables t1 write";
1431 --source include/wait_condition.inc
1432 
1433 --echo # --> connection default
1434 connection default;
1435 --echo # The below 'handler t1 read ...' should not be blocked as
1436 --echo # 'lock tables t1 write' has not succeeded yet.
1437 handler t1 read a next;
1438 
1439 --echo # Unblock 'lock tables t1 write'.
1440 commit;
1441 
1442 --echo # --> connection con1
1443 connection con1;
1444 --echo # Reap 'lock tables t1 write'.
1445 --reap
1446 
1447 --echo # --> connection default
1448 connection default;
1449 --echo # Sending:
1450 --send handler t1 read a next
1451 
1452 --echo # --> connection con1
1453 connection con1;
1454 --echo # Waiting for 'handler t1 read a next' to get blocked...
1455 let $wait_condition=
1456  select count(*) = 1 from information_schema.processlist
1457  where state = "Waiting for table level lock" and
1458  info = "handler t1 read a next";
1459 --source include/wait_condition.inc
1460 
1461 --echo # The below 'drop table t1' should be able to proceed without
1462 --echo # waiting as it will force HANDLER to be closed.
1463 drop table t1;
1464 unlock tables;
1465 
1466 --echo # --> connection default
1467 connection default;
1468 --echo # Reaping 'handler t1 read a next'...
1469 --error ER_NO_SUCH_TABLE
1470 --reap
1471 handler t1 close;
1472 
1473 --echo # --> connection con1
1474 connection con1;
1475 disconnect con1;
1476 --source include/wait_until_disconnected.inc
1477 --echo # --> connection con2
1478 connection con2;
1479 disconnect con2;
1480 --source include/wait_until_disconnected.inc
1481 --echo # --> connection con3
1482 connection con3;
1483 disconnect con3;
1484 --source include/wait_until_disconnected.inc
1485 connection default;
1486 
1487 --echo #
1488 --echo # A temporary table test.
1489 --echo # Check that we don't loose positions of HANDLER opened
1490 --echo # against a temporary table.
1491 --echo #
1492 create table t1 (a int, b int, key a (a));
1493 insert into t1 (a) values (1), (2), (3), (4), (5);
1494 create temporary table t2 (a int, b int, key a (a));
1495 insert into t2 (a) select a from t1;
1496 handler t1 open;
1497 handler t1 read a next;
1498 handler t2 open;
1499 handler t2 read a next;
1500 flush table t1;
1501 handler t2 read a next;
1502 --echo # Sic: the position is lost
1503 handler t1 read a next;
1504 select * from t1;
1505 --echo # Sic: the position is not lost
1506 handler t2 read a next;
1507 --error ER_CANT_REOPEN_TABLE
1508 select * from t2;
1509 handler t2 read a next;
1510 drop table t1;
1511 drop temporary table t2;
1512 
1513 --echo #
1514 --echo # A test for lock_table_names()/unlock_table_names() function.
1515 --echo # It should work properly in presence of open HANDLER.
1516 --echo #
1517 create table t1 (a int, b int, key a (a));
1518 create table t2 like t1;
1519 create table t3 like t1;
1520 create table t4 like t1;
1521 handler t1 open;
1522 handler t2 open;
1523 rename table t4 to t5, t3 to t4, t5 to t3;
1524 handler t1 read first;
1525 handler t2 read first;
1526 drop table t1, t2, t3, t4;
1527 
1528 --echo #
1529 --echo # A test for FLUSH TABLES WITH READ LOCK and HANDLER statements.
1530 --echo #
1531 set autocommit=0;
1532 create table t1 (a int, b int, key a (a));
1533 insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
1534 create table t2 like t1;
1535 insert into t2 (a, b) select a, b from t1;
1536 create table t3 like t1;
1537 insert into t3 (a, b) select a, b from t1;
1538 commit;
1539 flush tables with read lock;
1540 handler t1 open;
1541 lock table t1 read;
1542 --error ER_LOCK_OR_ACTIVE_TRANSACTION
1543 handler t1 read next;
1544 --echo # This implicitly leaves LOCK TABLES but doesn't drop the GLR
1545 --error ER_NO_SUCH_TABLE
1546 lock table not_exists_write read;
1547 --echo # We still have the read lock.
1548 --error ER_CANT_UPDATE_WITH_READLOCK
1549 drop table t1;
1550 handler t1 open;
1551 select a from t2;
1552 handler t1 read next;
1553 flush tables with read lock;
1554 handler t2 open;
1555 flush tables with read lock;
1556 handler t1 read next;
1557 select a from t3;
1558 handler t2 read next;
1559 handler t1 close;
1560 rollback;
1561 handler t2 close;
1562 --error ER_CANT_UPDATE_WITH_READLOCK
1563 drop table t1;
1564 commit;
1565 flush tables;
1566 --error ER_CANT_UPDATE_WITH_READLOCK
1567 drop table t1;
1568 unlock tables;
1569 drop table t1;
1570 set autocommit=default;
1571 drop table t2, t3;
1572 
1573 --echo #
1574 --echo # HANDLER statement and operation-type aware metadata locks.
1575 --echo # Check that when we clone a ticket for HANDLER we downrade
1576 --echo # the lock.
1577 --echo #
1578 --echo # Establish an auxiliary connection con1.
1579 connect (con1,localhost,root,,);
1580 --echo # -> connection default
1581 connection default;
1582 create table t1 (a int, b int, key a (a));
1583 insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
1584 begin;
1585 insert into t1 (a, b) values (6, 6);
1586 handler t1 open;
1587 handler t1 read a last;
1588 insert into t1 (a, b) values (7, 7);
1589 handler t1 read a last;
1590 commit;
1591 --echo # -> connection con1
1592 connection con1;
1593 --echo # Demonstrate that the HANDLER doesn't hold MDL_SHARED_WRITE.
1594 lock table t1 write;
1595 unlock tables;
1596 --echo # -> connection default
1597 connection default;
1598 handler t1 read a prev;
1599 handler t1 close;
1600 --echo # Cleanup.
1601 drop table t1;
1602 --echo # -> connection con1
1603 connection con1;
1604 disconnect con1;
1605 --source include/wait_until_disconnected.inc
1606 --echo # -> connection default
1607 connection default;
1608 
1609 --echo #
1610 --echo # A test for Bug#50555 "handler commands crash server in
1611 --echo # my_hash_first()".
1612 --echo #
1613 --error ER_UNKNOWN_TABLE
1614 handler no_such_table read no_such_index first;
1615 --error ER_UNKNOWN_TABLE
1616 handler no_such_table close;
1617 
1618 
1619 --echo #
1620 --echo # Bug#50907 Assertion `hash_tables->table->next == __null' on
1621 --echo # HANDLER OPEN
1622 --echo #
1623 
1624 --disable_warnings
1625 DROP TABLE IF EXISTS t1, t2;
1626 --enable_warnings
1627 
1628 CREATE TEMPORARY TABLE t1 (i INT);
1629 CREATE TEMPORARY TABLE t2 (i INT);
1630 
1631 # This used to trigger the assert
1632 HANDLER t2 OPEN;
1633 
1634 # This also used to trigger the assert
1635 HANDLER t2 READ FIRST;
1636 
1637 HANDLER t2 CLOSE;
1638 DROP TABLE t1, t2;
1639 
1640 
1641 --echo #
1642 --echo # Bug#50912 Assertion `ticket->m_type >= mdl_request->type'
1643 --echo # failed on HANDLER + I_S
1644 --echo #
1645 
1646 --disable_warnings
1647 DROP TABLE IF EXISTS t1;
1648 --enable_warnings
1649 
1650 CREATE TABLE t1 (id INT);
1651 HANDLER t1 OPEN;
1652 
1653 # This used to trigger the assert.
1654 SELECT table_name, table_comment FROM information_schema.tables
1655  WHERE table_schema= 'test' AND table_name= 't1';
1656 
1657 HANDLER t1 CLOSE;
1658 DROP TABLE t1;
1659 
1660 
1661 --echo #
1662 --echo # Test for bug #50908 "Assertion `handler_tables_hash.records == 0'
1663 --echo # failed in enter_locked_tables_mode".
1664 --echo #
1665 --disable_warnings
1666 drop tables if exists t1, t2;
1667 drop function if exists f1;
1668 --enable_warnings
1669 create table t1 (i int);
1670 insert into t1 values (1), (2);
1671 create table t2 (j int);
1672 insert into t2 values (1);
1673 create function f1() returns int return (select count(*) from t2);
1674 --echo # Check that open HANDLER survives statement executed in
1675 --echo # prelocked mode.
1676 handler t1 open;
1677 handler t1 read next;
1678 --echo # The below statement were aborted due to an assertion failure.
1679 select f1() from t2;
1680 handler t1 read next;
1681 handler t1 close;
1682 --echo # Check that the same happens under GLOBAL READ LOCK.
1683 flush tables with read lock;
1684 handler t1 open;
1685 handler t1 read next;
1686 select f1() from t2;
1687 handler t1 read next;
1688 unlock tables;
1689 handler t1 close;
1690 --echo # Now, check that the same happens if LOCK TABLES is executed.
1691 handler t1 open;
1692 handler t1 read next;
1693 lock table t2 read;
1694 select * from t2;
1695 unlock tables;
1696 handler t1 read next;
1697 handler t1 close;
1698 --echo # Finally, check scenario with GRL and LOCK TABLES.
1699 flush tables with read lock;
1700 handler t1 open;
1701 handler t1 read next;
1702 lock table t2 read;
1703 select * from t2;
1704 --echo # This unlocks both tables and GRL.
1705 unlock tables;
1706 handler t1 read next;
1707 handler t1 close;
1708 --echo # Clean-up.
1709 drop function f1;
1710 drop tables t1, t2;
1711 
1712 
1713 --echo #
1714 --echo # Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY +
1715 --echo # HANDLER + LOCK + SP".
1716 --echo # Also see additional coverage for this bug in flush.test.
1717 --echo #
1718 --disable_warnings
1719 drop tables if exists t1, t2;
1720 --enable_warnings
1721 create table t1 (i int);
1722 create temporary table t2 (j int);
1723 handler t1 open;
1724 lock table t2 read;
1725 --echo # This commit should not release any MDL locks.
1726 commit;
1727 unlock tables;
1728 --echo # The below statement crashed before the bug fix as it
1729 --echo # has attempted to release metadata lock which was
1730 --echo # already released by commit.
1731 handler t1 close;
1732 drop tables t1, t2;
1733 
1734 
1735 --echo #
1736 --echo # Bug#51355 handler stmt cause assertion in
1737 --echo # bool MDL_context::try_acquire_lock(MDL_request*)
1738 --echo #
1739 
1740 --disable_warnings
1741 DROP TABLE IF EXISTS t1;
1742 --enable_warnings
1743 
1744 connect(con51355, localhost, root);
1745 
1746 --echo # Connection default
1747 connection default;
1748 CREATE TABLE t1(id INT, KEY id(id));
1749 HANDLER t1 OPEN;
1750 
1751 --echo # Connection con51355
1752 connection con51355;
1753 --echo # Sending:
1754 --send DROP TABLE t1
1755 
1756 --echo # Connection default
1757 connection default;
1758 --echo # This I_S query will cause the handler table to be closed and
1759 --echo # the metadata lock to be released. This will allow DROP TABLE
1760 --echo # to proceed. Waiting for the table to be removed.
1761 let $wait_condition=
1762  SELECT COUNT(*) = 0 FROM information_schema.tables WHERE table_name = "t1";
1763 --source include/wait_condition.inc
1764 
1765 --echo # Connection con51355
1766 connection con51355;
1767 --echo # Reaping: DROP TABLE t1
1768 --reap
1769 
1770 --echo # Connection default
1771 connection default;
1772 --error ER_NO_SUCH_TABLE
1773 HANDLER t1 READ id NEXT;
1774 # This caused an assertion
1775 --error ER_NO_SUCH_TABLE
1776 HANDLER t1 READ id NEXT;
1777 
1778 HANDLER t1 CLOSE;
1779 --echo # Connection con51355
1780 connection con51355;
1781 disconnect con51355;
1782 --source include/wait_until_disconnected.inc
1783 --echo # Connection default
1784 connection default;
1785 
1786 
1787 --echo #
1788 --echo # Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER
1789 --echo #
1790 
1791 --disable_warnings
1792 DROP TABLE IF EXISTS t1, t2;
1793 DROP FUNCTION IF EXISTS f1;
1794 --enable_warnings
1795 
1796 delimiter |;
1797 CREATE FUNCTION f1() RETURNS INTEGER
1798 BEGIN
1799  SELECT 1 FROM t2 INTO @a;
1800  RETURN 1;
1801 END|
1802 delimiter ;|
1803 
1804 # Get f1() parsed and cached
1805 --error ER_NO_SUCH_TABLE
1806 SELECT f1();
1807 
1808 CREATE TABLE t1(a INT);
1809 INSERT INTO t1 VALUES (1);
1810 HANDLER t1 OPEN;
1811 # This used to cause the assert
1812 --error ER_NOT_SUPPORTED_YET
1813 HANDLER t1 READ FIRST WHERE f1() = 1;
1814 HANDLER t1 CLOSE;
1815 
1816 DROP FUNCTION f1;
1817 DROP TABLE t1;
1818 
1819 
1820 --echo #
1821 --echo # Bug#54920 Stored functions are allowed in HANDLER statements,
1822 --echo # but broken.
1823 --echo #
1824 
1825 --disable_warnings
1826 DROP TABLE IF EXISTS t1;
1827 DROP FUNCTION IF EXISTS f1;
1828 --enable_warnings
1829 
1830 CREATE TABLE t1 (a INT);
1831 INSERT INTO t1 VALUES (1), (2);
1832 CREATE FUNCTION f1() RETURNS INT RETURN 1;
1833 HANDLER t1 OPEN;
1834 
1835 --error ER_NOT_SUPPORTED_YET
1836 HANDLER t1 READ FIRST WHERE f1() = 1;
1837 
1838 HANDLER t1 CLOSE;
1839 DROP FUNCTION f1;
1840 DROP TABLE t1;
1841 
1842 --echo #
1843 --echo # Bug#13008220 HANDLER SQL STATEMENT CAN MISS TO INITIALIZE
1844 --echo # FOR RANDOM READ
1845 --echo #
1846 --echo # A handler can only have one active 'cursor' at a time,
1847 --echo # so switching between index and/or random should restart the cursor.
1848 
1849 CREATE TABLE t1(a INT, b INT, KEY b(b), KEY ab(a, b));
1850 INSERT INTO t1 VALUES (2, 20), (1, 10), (4, 40), (3, 30);
1851 HANDLER t1 OPEN;
1852 HANDLER t1 READ b FIRST;
1853 HANDLER t1 READ NEXT;
1854 HANDLER t1 READ NEXT;
1855 HANDLER t1 READ b FIRST;
1856 HANDLER t1 READ b NEXT;
1857 HANDLER t1 READ b NEXT;
1858 HANDLER t1 READ FIRST;
1859 HANDLER t1 READ b FIRST;
1860 HANDLER t1 READ NEXT;
1861 HANDLER t1 READ NEXT;
1862 HANDLER t1 READ NEXT;
1863 HANDLER t1 READ NEXT;
1864 HANDLER t1 READ NEXT;
1865 HANDLER t1 READ b NEXT;
1866 HANDLER t1 READ b NEXT;
1867 HANDLER t1 READ b NEXT;
1868 HANDLER t1 READ b NEXT;
1869 HANDLER t1 READ b NEXT;
1870 HANDLER t1 READ NEXT;
1871 HANDLER t1 READ b NEXT;
1872 HANDLER t1 READ FIRST;
1873 HANDLER t1 READ b PREV;
1874 HANDLER t1 READ b LAST;
1875 HANDLER t1 READ NEXT;
1876 HANDLER t1 READ ab FIRST;
1877 HANDLER t1 READ b NEXT;
1878 HANDLER t1 READ ab LAST;
1879 HANDLER t1 READ b PREV;
1880 HANDLER t1 CLOSE;
1881 DROP TABLE t1;