Debian Codegraph @

mysql-test/suite/handler/handler.inc

raw
1 # handler.inc
2 #
3 # See init.inc for setup of variables for this script
4 #
5 # The variables
6 # $engine_type -- storage engine to be tested
7 # $other_handler_engine_type -- storage engine <> $engine_type, if possible
8 # 1. $other_handler_engine_type must support handler
9 # 2. $other_handler_engine_type must point to an all
10 # time available storage engine
11 # 2006-08 MySQL 5.1 MyISAM and MEMORY only
12 #
13 # test of HANDLER ...
14 #
15 # Last update:
16 # 2006-07-31 ML test refactored (MySQL 5.1)
17 # code of t/handler.test and t/innodb_handler.test united
18 # main testing code put into handler.inc
19 #
20
21 source include/have_csv.inc;
22
23 #
24 # Start testing the table created in init.inc
25 #
26 handler t1 open as t2;
27 handler t2 read b first;
28 handler t2 read b next;
29 handler t2 read b next;
30 handler t2 read b prev;
31 handler t2 read b last;
32 handler t2 read b prev;
33 handler t2 read b prev;
34
35 handler t2 read b first;
36 handler t2 read b prev;
37
38 handler t2 read b last;
39 handler t2 read b prev;
40 handler t2 read b next;
41 handler t2 read b next;
42
43 handler t2 read a=(15);
44 handler t2 read a=(21);
45
46 --error 1070
47 handler t2 read a=(19,"fff");
48
49 handler t2 read b=(19,"fff");
50 handler t2 read b=(19,"yyy");
51 handler t2 read b=(19);
52
53 --error 1109
54 handler t1 read a last;
55
56 handler t2 read a=(11);
57 handler t2 read a>=(11);
58
59 # Search on something we can find
60 handler t2 read b=(18);
61 handler t2 read b>=(18);
62 handler t2 read b>(18);
63 handler t2 read b<=(18);
64 handler t2 read b<(18);
65
66 # Search on something we can't find
67 --sorted_result
68 handler t2 read a=(15);
69 --sorted_result
70 handler t2 read a>=(15) limit 2;
71 --sorted_result
72 handler t2 read a>(15) limit 2;
73 handler t2 read a<=(15);
74 handler t2 read a<(15);
75
76 # Search from upper end
77 handler t2 read a=(54);
78 handler t2 read a>=(54);
79 handler t2 read a>(54);
80 handler t2 read a<=(54);
81 handler t2 read a<(54);
82
83 # Search from lower end
84 handler t2 read a=(1);
85 handler t2 read a>=(1);
86 handler t2 read a>(1);
87 handler t2 read a<=(1);
88 handler t2 read a<(1);
89
90 handler t2 read b first limit 5;
91 handler t2 read b next limit 3;
92 handler t2 read b prev limit 10;
93
94 handler t2 read b>=(16) limit 4;
95 handler t2 read b>=(16) limit 2,2;
96 select * from t1 where a>=16 order by a,b limit 2,2;
97 handler t2 read a last limit 3;
98 handler t2 read b=(16) limit 1,3;
99 handler t2 read b=(19);
100 handler t2 read b=(19) where b="yyy";
101
102 handler t2 read first;
103 handler t2 read next;
104 handler t2 read next;
105 handler t2 close;
106
107 handler t1 open;
108 handler t1 read b next; # this used to crash as a bug#5373
109 handler t1 read b next;
110 handler t1 close;
111
112 handler t1 open;
113 handler t1 read a prev; # this used to crash as a bug#5373
114 handler t1 read a prev;
115 handler t1 close;
116
117 handler t1 open as t2;
118 handler t2 read first;
119 eval alter table t1 engine = $engine_type;
120 --error 1109
121 handler t2 read first;
122
123 handler t1 open;
124 handler t1 read a=(20) limit 1,3;
125 flush tables;
126 handler t1 read a=(20) limit 1,3;
127 handler t1 close;
128
129 #
130 # Search after end and before start of index
131 #
132
133 handler t1 open;
134 handler t1 read a=(25);
135 handler t1 read a next;
136 handler t1 read a next;
137 handler t1 read a next;
138 handler t1 read a prev;
139 handler t1 read a=(1000);
140 handler t1 read a next;
141 handler t1 read a prev;
142 handler t1 read a=(1000);
143 handler t1 read a prev;
144
145 handler t1 read a=(14);
146 handler t1 read a prev;
147 handler t1 read a prev;
148 handler t1 read a next;
149 handler t1 read a=(1);
150 handler t1 read a prev;
151 handler t1 read a next;
152 handler t1 read a=(1);
153 handler t1 read a next;
154
155 handler t1 close;
156
157 #
158 # Test with prepared statements
159 #
160
161 handler t1 open;
162 prepare stmt from 'handler t1 read a=(?) limit ?,?';
163 set @a=20,@b=1,@c=100;
164 execute stmt using @a,@b,@c;
165 set @a=20,@b=2,@c=1;
166 execute stmt using @a,@b,@c;
167 set @a=20,@b=0,@c=2;
168 execute stmt using @a,@b,@c;
169 deallocate prepare stmt;
170
171 prepare stmt from 'handler t1 read a next limit ?';
172 handler t1 read a>=(21);
173 set @a=3;
174 execute stmt using @a;
175 execute stmt using @a;
176 execute stmt using @a;
177 deallocate prepare stmt;
178
179 prepare stmt from 'handler t1 read b prev limit ?';
180 execute stmt using @a;
181 execute stmt using @a;
182 execute stmt using @a;
183 execute stmt using @a;
184 deallocate prepare stmt;
185
186 prepare stmt from 'handler t1 read b=(?,?)';
187 set @a=14, @b='aaa';
188 execute stmt using @a,@b;
189 set @a=14, @b='not found';
190 execute stmt using @a,@b;
191 deallocate prepare stmt;
192
193 prepare stmt from 'handler t1 read b=(1+?) limit 10';
194 set @a=15;
195 execute stmt using @a;
196 execute stmt using @a;
197 deallocate prepare stmt;
198
199 prepare stmt from 'handler t1 read b>=(?) where a < ? limit 5';
200 set @a=17, @b=24;
201 execute stmt using @a,@b;
202 execute stmt using @a,@b;
203 deallocate prepare stmt;
204
205 prepare stmt from 'handler t1 read a=(?)';
206 set @a=17;
207 execute stmt using @a;
208 alter table t1 add c int;
209 --error 1109
210 execute stmt using @a;
211 deallocate prepare stmt;
212 --error 1109
213 handler t1 close;
214
215 handler t1 open;
216 prepare stmt from 'handler t1 read a=(?)';
217 flush tables;
218 set @a=17;
219 execute stmt using @a;
220 deallocate prepare stmt;
221 handler t1 close;
222
223 #
224 # DROP TABLE / ALTER TABLE
225 #
226 handler t1 open as t2;
227 drop table t1;
228 create table t1 (a int not null);
229 insert into t1 values (17);
230 --error 1109
231 handler t2 read first;
232 handler t1 open as t2;
233 alter table t1 engine=csv;
234 --error 1109
235 handler t2 read first;
236 drop table t1;
237
238 #
239 # Test case for the bug #787
240 #
241 create table t1 (a int);
242 insert into t1 values (1),(2),(3),(4),(5),(6);
243 delete from t1 limit 2;
244 handler t1 open;
245 handler t1 read first;
246 handler t1 read first limit 1,1;
247 handler t1 read first limit 2,2;
248 delete from t1 limit 3;
249 handler t1 read first;
250 drop table t1;
251
252 #
253 # Test for #751
254 #
255 create table t1(a int, index using btree (a));
256 insert into t1 values (1), (2), (3);
257 handler t1 open;
258 --error 1054
259 handler t1 read a=(W);
260 --error 1210
261 handler t1 read a=(a);
262 drop table t1;
263 #
264 # BUG#2304
265 #
266 create table t1 (a char(5));
267 insert into t1 values ("Ok");
268 handler t1 open as t;
269 handler t read first;
270 use mysql;
271 handler t read first;
272 handler t close;
273 handler test.t1 open as t;
274 handler t read first;
275 handler t close;
276 use test;
277 drop table t1;
278
279 #
280 # BUG#3649
281 #
282 create table t1 ( a int, b int, INDEX a using btree (a) );
283 insert into t1 values (1,2), (2,1);
284 handler t1 open;
285 handler t1 read a=(1) where b=2;
286 handler t1 read a=(1) where b=3;
287 handler t1 read a=(1) where b=1;
288 handler t1 close;
289 drop table t1;
290
291 #
292 # Test if fix for BUG#4286 correctly closes handler tables.
293 #
294 create table t1 (c1 char(20));
295 insert into t1 values ("t1");
296 handler t1 open as h1;
297 handler h1 read first limit 9;
298 create table t2 (c1 char(20));
299 insert into t2 values ("t2");
300 handler t2 open as h2;
301 handler h2 read first limit 9;
302 create table t3 (c1 char(20));
303 insert into t3 values ("t3");
304 handler t3 open as h3;
305 handler h3 read first limit 9;
306 create table t4 (c1 char(20));
307 insert into t4 values ("t4");
308 handler t4 open as h4;
309 handler h4 read first limit 9;
310 create table t5 (c1 char(20));
311 insert into t5 values ("t5");
312 handler t5 open as h5;
313 handler h5 read first limit 9;
314 # close first
315 eval alter table t1 engine=$other_handler_engine_type;
316 --error 1109
317 handler h1 read first limit 9;
318 handler h2 read first limit 9;
319 handler h3 read first limit 9;
320 handler h4 read first limit 9;
321 handler h5 read first limit 9;
322 # close last
323 eval alter table t5 engine=$other_handler_engine_type;
324 --error 1109
325 handler h1 read first limit 9;
326 handler h2 read first limit 9;
327 handler h3 read first limit 9;
328 handler h4 read first limit 9;
329 --error 1109
330 handler h5 read first limit 9;
331 # close middle
332 eval alter table t3 engine=$other_handler_engine_type;
333 --error 1109
334 handler h1 read first limit 9;
335 handler h2 read first limit 9;
336 --error 1109
337 handler h3 read first limit 9;
338 handler h4 read first limit 9;
339 --error 1109
340 handler h5 read first limit 9;
341 handler h2 close;
342 handler h4 close;
343 # close all depending handler tables
344 handler t1 open as h1_1;
345 handler t1 open as h1_2;
346 handler t1 open as h1_3;
347 handler h1_1 read first limit 9;
348 handler h1_2 read first limit 9;
349 handler h1_3 read first limit 9;
350 eval alter table t1 engine=$engine_type;
351 --error 1109
352 handler h1_1 read first limit 9;
353 --error 1109
354 handler h1_2 read first limit 9;
355 --error 1109
356 handler h1_3 read first limit 9;
357 drop table t1;
358 drop table t2;
359 drop table t3;
360 drop table t4;
361 drop table t5;
362
363 #
364 # Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash
365 #
366 create table t1 (c1 int);
367 insert into t1 values (1);
368 handler t1 open;
369 handler t1 read first;
370 connect (con2,localhost,root,,);
371 connection con2;
372 --echo send the below to another connection, do not wait for the result
373 send optimize table t1;
374 --sleep 1
375 connection default;
376 --disable_ps_protocol
377 handler t1 read next;
378 --enable_ps_protocol
379 handler t1 close;
380 connection con2;
381 reap;
382 connection default;
383 drop table t1;
384
385 CREATE TABLE t1 ( no1 smallint(5) NOT NULL default '0', no2 int(10) NOT NULL default '0', PRIMARY KEY using btree (no1,no2));
386 INSERT INTO t1 VALUES (1,274),(1,275),(2,6),(2,8),(4,1),(4,2);
387 HANDLER t1 OPEN;
388 HANDLER t1 READ `primary` = (1, 1000);
389 HANDLER t1 READ `primary` PREV;
390 HANDLER t1 READ `primary` = (1, 1000);
391 HANDLER t1 READ `primary` NEXT;
392 DROP TABLE t1;
393
394 # End of 4.1 tests
395
396 #
397 # Addendum to Bug#14397 - OPTIMIZE TABLE with an open HANDLER causes a crash
398 # Show that DROP TABLE can no longer deadlock against
399 # FLUSH TABLES WITH READ LOCK. This is a 5.0 issue.
400 #
401 create table t1 (c1 int);
402 insert into t1 values (14397);
403 flush tables with read lock;
404 # The thread with the global read lock cannot drop the table itself:
405 --error 1223
406 drop table t1;
407 #
408 # We need a second connection to try the drop.
409 # The drop waits for the global read lock to go away.
410 # Without the addendum fix it locked LOCK_open before entering the wait loop.
411 connection con2;
412 send drop table t1;
413 --sleep 1
414 #
415 # Now we need something that wants LOCK_open. A simple table access which
416 # opens the table does the trick.
417 connection default;
418 # This would hang on LOCK_open without the 5.0 addendum fix.
419 select * from t1;
420 # Release the read lock. This should make the DROP go through.
421 unlock tables;
422 #
423 # Read the result of the drop command.
424 connection con2;
425 --echo read the result from the other connection
426 reap;
427 #
428 # Now back to normal operation. The table should not exist any more.
429 connection default;
430 --error 1146
431 select * from t1;
432 # Just to be sure and not confuse the next test case writer.
433 drop table if exists t1;
434
435 #
436 # Bug#25856 - HANDLER table OPEN in one connection lock DROP TABLE in another one
437 #
438 create table t1 (a int not null) ENGINE=csv;
439 connection con2;
440 --error 1031
441 handler t1 open;
442 connection default;
443 drop table t1;
444 disconnect con2;
445
446 #
447 # Bug#30632 HANDLER read failure causes hang
448 #
449 create table t1 (a int);
450 handler t1 open as t1_alias;
451 --error 1176
452 handler t1_alias read a next;
453 --error 1054
454 handler t1_alias READ a next where inexistent > 0;
455 --error 1176
456 handler t1_alias read a next;
457 --error 1054
458 handler t1_alias READ a next where inexistent > 0;
459 handler t1_alias close;
460 drop table t1;
461
462 #
463 # Bug#30882 Dropping a temporary table inside a stored function may cause a server crash
464 #
465
466 create temporary table t1 (a int, b char(1), key a using btree (a), key b using btree (a,b));
467 insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
468 (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j"),(9,'k');
469 select a,b from t1;
470 handler t1 open as a1;
471 handler a1 read a=(1);
472 handler a1 read a next;
473 handler a1 read a next;
474 select a,b from t1;
475 handler a1 read a prev;
476 handler a1 read a prev;
477 handler a1 read a=(6) where b="g";
478 handler a1 close;
479 select a,b from t1;
480 handler t1 open as a2;
481 handler a2 read b=(9);
482 handler a2 read b next;
483 handler a2 read b prev limit 2;
484 --error 0,1031
485 handler a2 read b last;
486 handler a2 read b prev;
487 handler a2 close;
488 drop table t1;
489
490 # Test that temporary tables associated with handlers are properly dropped.
491
492 create table t1 (a int);
493 create temporary table t2 (a int, key using btree (a));
494 handler t1 open as a1;
495 handler t2 open as a2;
496 handler a2 read a first;
497 drop table t1, t2;
498 --error ER_UNKNOWN_TABLE
499 handler a2 read a next;
500 --error ER_UNKNOWN_TABLE
501 handler a1 close;
502
503 # Alter table drop handlers
504
505 create table t1 (a int, key using btree (a));
506 create table t2 like t1;
507 handler t1 open as a1;
508 handler t2 open as a2;
509 handler a1 read a first;
510 handler a2 read a first;
511 alter table t1 add b int;
512 --error ER_UNKNOWN_TABLE
513 handler a1 close;
514 handler a2 close;
515 drop table t1, t2;
516
517 # Rename table drop handlers
518
519 create table t1 (a int, key using btree (a));
520 handler t1 open as a1;
521 handler a1 read a first;
522 rename table t1 to t2;
523 --error ER_UNKNOWN_TABLE
524 handler a1 read a first;
525 drop table t2;
526
527 # Optimize table drop handlers
528
529 create table t1 (a int, key using btree (a));
530 create table t2 like t1;
531 handler t1 open as a1;
532 handler t2 open as a2;
533 handler a1 read a first;
534 handler a2 read a first;
535 optimize table t1;
536 --error ER_UNKNOWN_TABLE
537 handler a1 close;
538 handler a2 close;
539 drop table t1, t2;
540
541 --echo #
542 --echo # Add test coverage for HANDLER and LOCK TABLES, HANDLER and DDL.
543 --echo #
544 --disable_warnings
545 drop table if exists t1, t2, t3;
546 --enable_warnings
547 create table t1 (a int, key a (a));
548 insert into t1 (a) values (1), (2), (3), (4), (5);
549 create table t2 (a int, key a (a)) select * from t1;
550 create temporary table t3 (a int, key a (a)) select * from t2;
551 handler t1 open;
552 handler t2 open;
553 handler t3 open;
554 --echo #
555 --echo # No HANDLER sql is allowed under LOCK TABLES.
556 --echo # But it does not implicitly closes all handlers.
557 --echo #
558 lock table t1 read;
559 --error ER_LOCK_OR_ACTIVE_TRANSACTION
560 handler t1 open;
561 --error ER_LOCK_OR_ACTIVE_TRANSACTION
562 handler t1 read next;
563 --error ER_LOCK_OR_ACTIVE_TRANSACTION
564 handler t2 close;
565 --error ER_LOCK_OR_ACTIVE_TRANSACTION
566 handler t3 open;
567 --echo # After UNLOCK TABLES handlers should be around and
568 --echo # we should be able to continue reading through them.
569 unlock tables;
570 handler t1 read next;
571 handler t1 close;
572 handler t2 read next;
573 handler t2 close;
574 handler t3 read next;
575 handler t3 close;
576 drop temporary table t3;
577 --echo #
578 --echo # Other operations that implicitly close handler:
579 --echo #
580 --echo # TRUNCATE
581 --echo #
582 handler t1 open;
583 truncate table t1;
584 --error ER_UNKNOWN_TABLE
585 handler t1 read next;
586 handler t1 open;
587 --echo #
588 --echo # CREATE TRIGGER
589 --echo #
590 create trigger t1_ai after insert on t1 for each row set @a=1;
591 --error ER_UNKNOWN_TABLE
592 handler t1 read next;
593 --echo #
594 --echo # DROP TRIGGER
595 --echo #
596 handler t1 open;
597 drop trigger t1_ai;
598 --error ER_UNKNOWN_TABLE
599 handler t1 read next;
600 --echo #
601 --echo # ALTER TABLE
602 --echo #
603 handler t1 open;
604 alter table t1 add column b int;
605 --error ER_UNKNOWN_TABLE
606 handler t1 read next;
607 --echo #
608 --echo # ANALYZE TABLE
609 --echo #
610 handler t1 open;
611 analyze table t1;
612 --error ER_UNKNOWN_TABLE
613 handler t1 read next;
614 --echo #
615 --echo # OPTIMIZE TABLE
616 --echo #
617 handler t1 open;
618 optimize table t1;
619 --error ER_UNKNOWN_TABLE
620 handler t1 read next;
621 --echo #
622 --echo # REPAIR TABLE
623 --echo #
624 handler t1 open;
625 repair table t1;
626 --error ER_UNKNOWN_TABLE
627 handler t1 read next;
628 --echo #
629 --echo # DROP TABLE, naturally.
630 --echo #
631 handler t1 open;
632 drop table t1;
633 --error ER_UNKNOWN_TABLE
634 handler t1 read next;
635 create table t1 (a int, b int, key a using btree (a)) select a from t2;
636 --echo #
637 --echo # RENAME TABLE, naturally
638 --echo #
639 handler t1 open;
640 rename table t1 to t3;
641 --error ER_UNKNOWN_TABLE
642 handler t1 read next;
643 --echo #
644 --echo # CREATE TABLE (even with IF NOT EXISTS clause,
645 --echo # and the table exists).
646 --echo #
647 handler t2 open;
648 create table if not exists t2 (a int);
649 --error ER_UNKNOWN_TABLE
650 handler t2 read next;
651 rename table t3 to t1;
652 drop table t2;
653 --echo #
654 --echo # FLUSH TABLE doesn't close the table but loses the position
655 --echo #
656 handler t1 open;
657 handler t1 read a prev;
658 flush table t1;
659 handler t1 read a prev;
660 handler t1 close;
661 --echo #
662 --echo # FLUSH TABLES WITH READ LOCK behaves like FLUSH TABLE.
663 --echo #
664 handler t1 open;
665 handler t1 read a prev;
666 flush tables with read lock;
667 handler t1 read a prev;
668 handler t1 close;
669 unlock tables;
670 --echo #
671 --echo # Let us also check that these operations behave in similar
672 --echo # way under LOCK TABLES.
673 --echo #
674 --echo # TRUNCATE under LOCK TABLES.
675 --echo #
676 handler t1 open;
677 lock tables t1 write;
678 truncate table t1;
679 unlock tables;
680 --error ER_UNKNOWN_TABLE
681 handler t1 read next;
682 handler t1 open;
683 --echo #
684 --echo # CREATE TRIGGER under LOCK TABLES.
685 --echo #
686 lock tables t1 write;
687 create trigger t1_ai after insert on t1 for each row set @a=1;
688 unlock tables;
689 --error ER_UNKNOWN_TABLE
690 handler t1 read next;
691 --echo #
692 --echo # DROP TRIGGER under LOCK TABLES.
693 --echo #
694 handler t1 open;
695 lock tables t1 write;
696 drop trigger t1_ai;
697 unlock tables;
698 --error ER_UNKNOWN_TABLE
699 handler t1 read next;
700 --echo #
701 --echo # ALTER TABLE under LOCK TABLES.
702 --echo #
703 handler t1 open;
704 lock tables t1 write;
705 alter table t1 drop column b;
706 unlock tables;
707 --error ER_UNKNOWN_TABLE
708 handler t1 read next;
709 --echo #
710 --echo # ANALYZE TABLE under LOCK TABLES.
711 --echo #
712 handler t1 open;
713 lock tables t1 write;
714 analyze table t1;
715 unlock tables;
716 --error ER_UNKNOWN_TABLE
717 handler t1 read next;
718 --echo #
719 --echo # OPTIMIZE TABLE under LOCK TABLES.
720 --echo #
721 handler t1 open;
722 lock tables t1 write;
723 optimize table t1;
724 unlock tables;
725 --error ER_UNKNOWN_TABLE
726 handler t1 read next;
727 --echo #
728 --echo # REPAIR TABLE under LOCK TABLES.
729 --echo #
730 handler t1 open;
731 lock tables t1 write;
732 repair table t1;
733 unlock tables;
734 --error ER_UNKNOWN_TABLE
735 handler t1 read next;
736 --echo #
737 --echo # DROP TABLE under LOCK TABLES, naturally.
738 --echo #
739 handler t1 open;
740 lock tables t1 write;
741 drop table t1;
742 unlock tables;
743 --error ER_UNKNOWN_TABLE
744 handler t1 read next;
745 create table t1 (a int, b int, key a using btree (a));
746 insert into t1 (a) values (1), (2), (3), (4), (5);
747 --echo #
748 --echo # FLUSH TABLE doesn't close the table but loses the position
749 --echo #
750 handler t1 open;
751 handler t1 read a prev;
752 lock tables t1 write;
753 flush table t1;
754 unlock tables;
755 handler t1 read a prev;
756 handler t1 close;
757 --echo #
758 --echo # Explore the effect of HANDLER locks on concurrent DDL
759 --echo #
760 handler t1 open;
761 connect(con1, localhost, root,,);
762 connect(con2, localhost, root,,);
763 connect(con3, localhost, root,,);
764 connection con1;
765 --echo # Sending:
766 --send drop table t1
767 --echo # We can't use connection 'default' as wait_condition will
768 --echo # autoclose handlers.
769 connection con2;
770 --echo # Waitng for 'drop table t1' to get blocked...
771 let $wait_condition=select count(*)=1 from information_schema.processlist
772 where state='Waiting for table metadata lock' and
773 info='drop table t1';
774 --source include/wait_condition.inc
775 connection default;
776 handler t1 read a prev;
777 handler t1 read a prev;
778 handler t1 close;
779 connection con1;
780 --echo # Reaping 'drop table t1'...
781 --reap
782 connection default;
783 --echo #
784 --echo # Explore the effect of HANDLER locks in parallel with SELECT
785 --echo #
786 create table t1 (a int, key a using btree (a));
787 insert into t1 (a) values (1), (2), (3), (4), (5);
788 begin;
789 select * from t1;
790 handler t1 open;
791 handler t1 read a prev;
792 handler t1 read a prev;
793 handler t1 close;
794 connection con1;
795 --echo # Sending:
796 --send drop table t1
797 connection con2;
798 --echo # Waiting for 'drop table t1' to get blocked...
799 let $wait_condition=select count(*)=1 from information_schema.processlist
800 where state='Waiting for table metadata lock' and
801 info='drop table t1';
802 --source include/wait_condition.inc
803 connection default;
804 --echo # We can still use the table, it's part of the transaction
805 select * from t1;
806 --echo # Such are the circumstances that t1 is a part of transaction,
807 --echo # thus we can reopen it in the handler
808 handler t1 open;
809 --echo # We can commit the transaction, it doesn't close the handler
810 --echo # and doesn't let DROP to proceed.
811 commit;
812 handler t1 read a prev;
813 handler t1 read a prev;
814 handler t1 read a prev;
815 handler t1 close;
816 connection con1;
817 --echo # Now drop can proceed
818 --echo # Reaping 'drop table t1'...
819 --reap
820 connection default;
821 --echo #
822 --echo # Demonstrate that HANDLER locks and transaction locks
823 --echo # reside in the same context.
824 --echo #
825 create table t1 (a int, key a (a));
826 insert into t1 (a) values (1), (2), (3), (4), (5);
827 create table t0 (a int, key a using btree (a));
828 insert into t0 (a) values (1), (2), (3), (4), (5);
829 begin;
830 select * from t1;
831 connection con2;
832 --echo # Sending:
833 send rename table t0 to t3, t1 to t0, t3 to t1;
834 connection con1;
835 --echo # Waiting for 'rename table ...' to get blocked...
836 let $wait_condition=select count(*)=1 from information_schema.processlist
837 where state='Waiting for table metadata lock' and
838 info='rename table t0 to t3, t1 to t0, t3 to t1';
839 --source include/wait_condition.inc
840 connection default;
841 --echo # We back-off on hitting deadlock condition.
842 --error ER_LOCK_DEADLOCK
843 handler t0 open;
844 select * from t0;
845 handler t1 open;
846 commit;
847 handler t1 close;
848 connection con2;
849 --echo # Reaping 'rename table ...'...
850 --reap
851 connection default;
852 handler t1 open;
853 handler t1 read a prev;
854 handler t1 close;
855 drop table t0;
856 --echo #
857 --echo # Originally there was a deadlock error in this test.
858 --echo # With implementation of deadlock detector
859 --echo # we no longer deadlock, but block and wait on a lock.
860 --echo # The HANDLER is auto-closed as soon as the connection
861 --echo # sees a pending conflicting lock against it.
862 --echo #
863 create table t2 (a int, key a (a));
864 handler t1 open;
865 connection con1;
866 lock tables t2 read;
867 connection con2;
868 --echo # Sending 'drop table t2'...
869 --send drop table t2
870 connection con1;
871 --echo # Waiting for 'drop table t2' to get blocked...
872 let $wait_condition=select count(*)=1 from information_schema.processlist
873 where state='Waiting for table metadata lock' and
874 info='drop table t2';
875 --source include/wait_condition.inc
876 connection default;
877 --echo # Sending 'select * from t2'
878 send select * from t2;
879 connection con1;
880 --echo # Waiting for 'select * from t2' to get blocked...
881 let $wait_condition=select count(*)=1 from information_schema.processlist
882 where state='Waiting for table metadata lock' and
883 info='select * from t2';
884 unlock tables;
885 connection con2;
886 --echo # Reaping 'drop table t2'...
887 --reap
888 connection default;
889 --echo # Reaping 'select * from t2'
890 --error ER_NO_SUCH_TABLE
891 reap;
892 handler t1 close;
893 drop table t1;
894
895 --echo #
896 --echo # If we have to wait on an exclusive locks while having
897 --echo # an open HANDLER, ER_LOCK_DEADLOCK is reported.
898 --echo #
899 --disable_ps2_protocol
900 create table t1 (a int, key a(a));
901 handler t1 open;
902 connection con1;
903 select get_lock('lock1', 10);
904 connection default;
905 send select get_lock('lock1', 10);
906 connection con2;
907 --echo # Waiting for 'select get_lock('lock1', 10)' to get blocked...
908 let $wait_condition=select count(*)=1 from information_schema.processlist
909 where state='User lock' and
910 info='select get_lock(\'lock1\', 10)';
911 --source include/wait_condition.inc
912 connection con1;
913 --error ER_LOCK_DEADLOCK
914 drop table t1;
915 select release_lock('lock1');
916 connection default;
917 reap;
918 select release_lock('lock1');
919 --enable_ps2_protocol
920
921 --echo # Demonstrate that there is no deadlock with FLUSH TABLE,
922 --echo # even though it is waiting for the other table to go away
923 create table t2 like t1;
924 --echo # Sending:
925 --send flush table t2
926 connection con2;
927 drop table t1;
928 connection con1;
929 unlock tables;
930 connection default;
931 --echo # Reaping 'flush table t2'...
932 --reap
933 drop table t2;
934
935 --echo #
936 --echo # Bug #46224 HANDLER statements within a transaction might
937 --echo # lead to deadlocks
938 --echo #
939 create table t1 (a int, key using btree (a));
940 insert into t1 values (1), (2);
941
942 connection default;
943 begin;
944 select * from t1;
945 handler t1 open;
946
947 connection con1;
948 --echo # Sending:
949 --send lock tables t1 write
950
951 connection con2;
952 --echo # Check that 'lock tables t1 write' waits until transaction which
953 --echo # has read from the table commits.
954 let $wait_condition=
955 select count(*) = 1 from information_schema.processlist
956 where state = "Waiting for table metadata lock" and
957 info = "lock tables t1 write";
958 --source include/wait_condition.inc
959
960 connection default;
961 --echo # The below 'handler t1 read ...' should not be blocked as
962 --echo # 'lock tables t1 write' has not succeeded yet.
963 handler t1 read a next;
964
965 --echo # Unblock 'lock tables t1 write'.
966 select * from t1; # Release MDL_SHARED_READ held by HANDLER
967 commit;
968
969 connection con1;
970 --echo # Reap 'lock tables t1 write'.
971 --reap
972
973 connection default;
974 --echo # Sending:
975 --send handler t1 read a next
976
977 connection con1;
978 --echo # Waiting for 'handler t1 read a next' to get blocked...
979 let $wait_condition=
980 select count(*) = 1 from information_schema.processlist
981 where state = "Waiting for table metadata lock" and
982 info = "handler t1 read a next";
983 --source include/wait_condition.inc
984
985 --echo # The below 'drop table t1' should be able to proceed without
986 --echo # waiting as it will force HANDLER to be closed.
987 drop table t1;
988 unlock tables;
989
990 connection default;
991 --echo # Reaping 'handler t1 read a next'...
992 --error ER_NO_SUCH_TABLE
993 --reap
994 handler t1 close;
995
996 connection con1;
997 disconnect con1;
998 --source include/wait_until_disconnected.inc
999 connection con2;
1000 disconnect con2;
1001 --source include/wait_until_disconnected.inc
1002 connection con3;
1003 disconnect con3;
1004 --source include/wait_until_disconnected.inc
1005 connection default;
1006
1007 --echo #
1008 --echo # A temporary table test.
1009 --echo # Check that we don't loose positions of HANDLER opened
1010 --echo # against a temporary table.
1011 --echo #
1012 create table t1 (a int, b int, key using btree (a));
1013 insert into t1 (a) values (1), (2), (3), (4), (5);
1014 create temporary table t2 (a int, b int, key using btree (a));
1015 insert into t2 (a) select a from t1;
1016 handler t1 open;
1017 handler t1 read a next;
1018 handler t2 open;
1019 handler t2 read a next;
1020 flush table t1;
1021 handler t2 read a next;
1022 --echo # Sic: the position is lost
1023 handler t1 read a next;
1024 select * from t1;
1025 --echo # Sic: the position is not lost
1026 handler t2 read a next;
1027 select * from t2;
1028 handler t2 read a next;
1029 drop table t1;
1030 drop temporary table t2;
1031
1032 --echo #
1033 --echo # A test for lock_table_names()/unlock_table_names() function.
1034 --echo # It should work properly in presence of open HANDLER.
1035 --echo #
1036 create table t1 (a int, b int, key a (a));
1037 create table t2 like t1;
1038 create table t3 like t1;
1039 create table t4 like t1;
1040 handler t1 open;
1041 handler t2 open;
1042 rename table t4 to t5, t3 to t4, t5 to t3;
1043 handler t1 read first;
1044 handler t2 read first;
1045 drop table t1, t2, t3, t4;
1046
1047 --echo #
1048 --echo # A test for FLUSH TABLES WITH READ LOCK and HANDLER statements.
1049 --echo #
1050 set autocommit=0;
1051 create table t1 (a int, b int, key a (a));
1052 insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
1053 create table t2 like t1;
1054 insert into t2 (a, b) select a, b from t1;
1055 create table t3 like t1;
1056 insert into t3 (a, b) select a, b from t1;
1057 commit;
1058 flush tables with read lock;
1059 handler t1 open;
1060 lock table t1 read;
1061 --error ER_LOCK_OR_ACTIVE_TRANSACTION
1062 handler t1 read next;
1063 --echo # This implicitly leaves LOCK TABLES but doesn't drop the GLR
1064 --error ER_NO_SUCH_TABLE
1065 lock table not_exists_write read;
1066 --echo # We still have the read lock.
1067 --error ER_CANT_UPDATE_WITH_READLOCK
1068 drop table t1;
1069 handler t1 open;
1070 select a from t2;
1071 handler t1 read next;
1072 flush tables with read lock;
1073 handler t2 open;
1074 flush tables with read lock;
1075 handler t1 read next;
1076 select a from t3;
1077 handler t2 read next;
1078 handler t1 close;
1079 rollback;
1080 handler t2 close;
1081 --error ER_CANT_UPDATE_WITH_READLOCK
1082 drop table t1;
1083 commit;
1084 flush tables;
1085 --error ER_CANT_UPDATE_WITH_READLOCK
1086 drop table t1;
1087 unlock tables;
1088 drop table t1;
1089 set autocommit=default;
1090 drop table t2, t3;
1091
1092 --echo #
1093 --echo # HANDLER statement and operation-type aware metadata locks.
1094 --echo # Check that when we clone a ticket for HANDLER we downrade
1095 --echo # the lock.
1096 --echo #
1097 connect (con1,localhost,root,,);
1098 connection default;
1099 create table t1 (a int, b int, key using btree (a));
1100 insert into t1 (a, b) values (1, 1), (2, 1), (3, 2), (4, 2), (5, 5);
1101 begin;
1102 insert into t1 (a, b) values (6, 6);
1103 handler t1 open;
1104 handler t1 read a last;
1105 insert into t1 (a, b) values (7, 7);
1106 handler t1 read a last;
1107 commit;
1108 connection default;
1109 handler t1 read a prev;
1110 handler t1 close;
1111 --echo # Cleanup.
1112 drop table t1;
1113 connection con1;
1114 disconnect con1;
1115 --source include/wait_until_disconnected.inc
1116 connection default;
1117
1118 --echo #
1119 --echo # A test for Bug#50555 "handler commands crash server in
1120 --echo # my_hash_first()".
1121 --echo #
1122 --error ER_UNKNOWN_TABLE
1123 handler no_such_table read no_such_index first;
1124 --error ER_UNKNOWN_TABLE
1125 handler no_such_table close;
1126
1127
1128 --echo #
1129 --echo # Bug#50907 Assertion `hash_tables->table->next == __null' on
1130 --echo # HANDLER OPEN
1131 --echo #
1132
1133 --disable_warnings
1134 DROP TABLE IF EXISTS t1, t2;
1135 --enable_warnings
1136
1137 CREATE TEMPORARY TABLE t1 (i INT);
1138 CREATE TEMPORARY TABLE t2 (i INT);
1139
1140 # This used to trigger the assert
1141 HANDLER t2 OPEN;
1142
1143 # This also used to trigger the assert
1144 HANDLER t2 READ FIRST;
1145
1146 HANDLER t2 CLOSE;
1147 DROP TABLE t1, t2;
1148
1149
1150 --echo #
1151 --echo # Bug#50912 Assertion `ticket->m_type >= mdl_request->type'
1152 --echo # failed on HANDLER + I_S
1153 --echo #
1154
1155 --disable_warnings
1156 DROP TABLE IF EXISTS t1;
1157 --enable_warnings
1158
1159 CREATE TABLE t1 (id INT);
1160 HANDLER t1 OPEN;
1161
1162 # This used to trigger the assert.
1163 SELECT table_name, table_comment FROM information_schema.tables
1164 WHERE table_schema= 'test' AND table_name= 't1';
1165
1166 HANDLER t1 CLOSE;
1167 DROP TABLE t1;
1168
1169
1170 --echo #
1171 --echo # Test for bug #50908 "Assertion `handler_tables_hash.records == 0'
1172 --echo # failed in enter_locked_tables_mode".
1173 --echo #
1174 --disable_warnings
1175 drop tables if exists t1, t2;
1176 drop function if exists f1;
1177 --enable_warnings
1178 create table t1 (i int);
1179 insert into t1 values (1), (2);
1180 create table t2 (j int);
1181 insert into t2 values (1);
1182 create function f1() returns int return (select count(*) from t2);
1183 --echo # Check that open HANDLER survives statement executed in
1184 --echo # prelocked mode.
1185 handler t1 open;
1186 handler t1 read next;
1187 --echo # The below statement were aborted due to an assertion failure.
1188 select f1() from t2;
1189 handler t1 read next;
1190 handler t1 close;
1191 --echo # Check that the same happens under GLOBAL READ LOCK.
1192 flush tables with read lock;
1193 handler t1 open;
1194 handler t1 read next;
1195 select f1() from t2;
1196 handler t1 read next;
1197 unlock tables;
1198 handler t1 close;
1199 --echo # Now, check that the same happens if LOCK TABLES is executed.
1200 handler t1 open;
1201 handler t1 read next;
1202 lock table t2 read;
1203 select * from t2;
1204 unlock tables;
1205 handler t1 read next;
1206 handler t1 close;
1207 --echo # Finally, check scenario with GRL and LOCK TABLES.
1208 flush tables with read lock;
1209 handler t1 open;
1210 handler t1 read next;
1211 lock table t2 read;
1212 select * from t2;
1213 --echo # This unlocks both tables and GRL.
1214 unlock tables;
1215 handler t1 read next;
1216 handler t1 close;
1217 --echo # Clean-up.
1218 drop function f1;
1219 drop tables t1, t2;
1220
1221
1222 --echo #
1223 --echo # Test for bug #51136 "Crash in pthread_rwlock_rdlock on TEMPORARY +
1224 --echo # HANDLER + LOCK + SP".
1225 --echo # Also see additional coverage for this bug in flush.test.
1226 --echo #
1227 --disable_warnings
1228 drop tables if exists t1, t2;
1229 --enable_warnings
1230 create table t1 (i int);
1231 create temporary table t2 (j int);
1232 handler t1 open;
1233 lock table t2 read;
1234 --echo # This commit should not release any MDL locks.
1235 commit;
1236 unlock tables;
1237 --echo # The below statement crashed before the bug fix as it
1238 --echo # has attempted to release metadata lock which was
1239 --echo # already released by commit.
1240 handler t1 close;
1241 drop tables t1, t2;
1242
1243
1244 --echo #
1245 --echo # Bug#51355 handler stmt cause assertion in
1246 --echo # bool MDL_context::try_acquire_lock(MDL_request*)
1247 --echo #
1248
1249 --disable_warnings
1250 DROP TABLE IF EXISTS t1;
1251 --enable_warnings
1252
1253 connect(con51355, localhost, root);
1254
1255 connection default;
1256 CREATE TABLE t1(id INT, KEY id(id));
1257 HANDLER t1 OPEN;
1258
1259 connection con51355;
1260 --echo # Sending:
1261 --send DROP TABLE t1
1262
1263 connection default;
1264 --echo # This I_S query will cause the handler table to be closed and
1265 --echo # the metadata lock to be released. This will allow DROP TABLE
1266 --echo # to proceed. Waiting for the table to be removed.
1267 let $wait_condition=
1268 SELECT COUNT(*) = 0 FROM information_schema.tables WHERE table_name = "t1";
1269 --source include/wait_condition.inc
1270
1271 connection con51355;
1272 --echo # Reaping: DROP TABLE t1
1273 --reap
1274
1275 connection default;
1276 --error ER_NO_SUCH_TABLE
1277 HANDLER t1 READ id NEXT;
1278 # This caused an assertion
1279 --error ER_NO_SUCH_TABLE
1280 HANDLER t1 READ id NEXT;
1281
1282 HANDLER t1 CLOSE;
1283 connection con51355;
1284 disconnect con51355;
1285 --source include/wait_until_disconnected.inc
1286 connection default;
1287
1288
1289 --echo #
1290 --echo # Bug#54401 assert in Diagnostics_area::set_eof_status , HANDLER
1291 --echo #
1292
1293 --disable_warnings
1294 DROP TABLE IF EXISTS t1, t2;
1295 DROP FUNCTION IF EXISTS f1;
1296 --enable_warnings
1297
1298 --enable_prepare_warnings
1299 delimiter |;
1300 CREATE FUNCTION f1() RETURNS INTEGER
1301 BEGIN
1302 SELECT 1 FROM t2 INTO @a;
1303 RETURN 1;
1304 END|
1305 delimiter ;|
1306 --disable_prepare_warnings
1307
1308 # Get f1() parsed and cached
1309 --error ER_NO_SUCH_TABLE
1310 SELECT f1();
1311
1312 CREATE TABLE t1(a INT);
1313 INSERT INTO t1 VALUES (1);
1314 HANDLER t1 OPEN;
1315 # This used to cause the assert
1316 --error ER_NOT_SUPPORTED_YET
1317 HANDLER t1 READ FIRST WHERE f1() = 1;
1318 HANDLER t1 CLOSE;
1319
1320 DROP FUNCTION f1;
1321 DROP TABLE t1;
1322
1323 --echo #
1324 --echo # Bug#54920 Stored functions are allowed in HANDLER statements,
1325 --echo # but broken.
1326 --echo #
1327
1328 --disable_warnings
1329 DROP TABLE IF EXISTS t1;
1330 DROP FUNCTION IF EXISTS f1;
1331 --enable_warnings
1332
1333 CREATE TABLE t1 (a INT);
1334 INSERT INTO t1 VALUES (1), (2);
1335 CREATE FUNCTION f1() RETURNS INT RETURN 1;
1336 HANDLER t1 OPEN;
1337
1338 --error ER_NOT_SUPPORTED_YET
1339 HANDLER t1 READ FIRST WHERE f1() = 1;
1340
1341 HANDLER t1 CLOSE;
1342 DROP FUNCTION f1;
1343 DROP TABLE t1;
1344
1345 --echo #
1346 --echo # BUG#51877 - HANDLER interface causes invalid memory read
1347 --echo #
1348 CREATE TABLE t1(a INT, KEY using btree (a));
1349 HANDLER t1 OPEN;
1350 HANDLER t1 READ a FIRST;
1351 INSERT INTO t1 VALUES(1);
1352 --error 0,ER_CHECKREAD
1353 HANDLER t1 READ a NEXT;
1354 HANDLER t1 CLOSE;
1355 DROP TABLE t1;