| 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; |