summaryrefslogtreecommitdiff
path: root/mysql-test/suite/handler/interface.test
blob: a4ed640dbc0c93600d9583196fd099ad7b6b254e (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
#
# Tests of handler interface that are system independent
#
# Handler tests don't work yet with embedded server
#
-- source include/not_embedded.inc

--disable_warnings
drop table if exists t1,t3,t4,t5;
drop database if exists test_test;
--enable_warnings

# Run tests with myisam (any engine should be ok)

let $engine_type= MyISAM;

--source init.inc

#
# Do some syntax checking
#

handler t1 open;
--error ER_PARSE_ERROR
handler t1 read a=(SELECT 1);
--error ER_PARSE_ERROR
handler t1 read a=(1) FIRST;
--error ER_PARSE_ERROR
handler t1 read a=(1) NEXT;
--error ER_PARSE_ERROR
handler t1 read last;
handler t1 close;
drop table t1;

CREATE TABLE t1(a INT, PRIMARY KEY(a));
insert into t1 values(1),(2);
handler t1 open;
--error ER_PARSE_ERROR
handler t1 read primary=(1);
handler t1 read `primary`=(1);
handler t1 close;
drop table t1;

#
# Check if two database names beginning the same are seen as different.
#
# This database begins like the usual 'test' database.
#
create database test_test;
use test_test;
eval create table t1(table_id char(20), primary key $key_type (table_id));
insert into t1 values ('test_test.t1');
insert into t1 values ('');
handler t1 open;
handler t1 read first limit 9;
eval create table t2(table_id char(20), primary key $key_type (table_id));
insert into t2 values ('test_test.t2');
insert into t2 values ('');
handler t2 open;
handler t2 read first limit 9;
#
# This is the usual 'test' database.
#
use test;
eval create table t1(table_id char(20), primary key $key_type (table_id));
insert into t1 values ('test.t1');
insert into t1 values ('');
--error 1066
handler t1 open;
#
# Check accessibility of all the tables.
#
use test;
--error 1064
handler test.t1 read first limit 9;
--error 1064
handler test_test.t1 read first limit 9;
handler t1 read first limit 9;
--error 1064
handler test_test.t2 read first limit 9;
handler t2 read first limit 9;

#
# Cleanup.
#

--error 1064
handler test_test.t1 close;
handler t1 close;
drop table test_test.t1;
--error 1064
handler test_test.t2 close;
handler t2 close;
drop table test_test.t2;
drop database test_test;

#
use test;
--error 1064
handler test.t1 close;
--error 1109
handler t1 close;
drop table test.t1;

#
# BUG#4335 one name can be handler open'ed many times
#

create database test_test;
use test_test;
create table t1 (c1 char(20));
insert into t1 values ('test_test.t1');
create table t3 (c1 char(20));
insert into t3 values ('test_test.t3');
handler t1 open;
handler t1 read first limit 9;
handler t1 open h1;
handler h1 read first limit 9;
use test;
create table t1 (c1 char(20));
create table t2 (c1 char(20));
create table t3 (c1 char(20));
insert into t1 values ('t1');
insert into t2 values ('t2');
insert into t3 values ('t3');
--error 1066
handler t1 open;
--error 1066
handler t2 open t1;
--error 1066
handler t3 open t1;
handler t1 read first limit 9;
--error 1064
handler test.t1 close;
--error 1066
handler test.t1 open h1;
--error 1066
handler test_test.t1 open h1;
handler test_test.t3 open h3;
handler test.t1 open h2;
handler t1 read first limit 9;
handler h1 read first limit 9;
handler h2 read first limit 9;
handler h3 read first limit 9;
handler h2 read first limit 9;
--error 1064
handler test.h1 close;
handler t1 close;
handler h1 close;
handler h2 close;
--error 1109
handler t1 read first limit 9;
--error 1109
handler h1 read first limit 9;
--error 1109
handler h2 read first limit 9;
handler h3 read first limit 9;
handler h3 read first limit 9;
use test_test;
handler h3 read first limit 9;
--error 1064
handler test.h3 read first limit 9;
handler h3 close;
use test;
drop table t3;
drop table t2;
drop table t1;
drop database test_test;

#
# Bug#21587 FLUSH TABLES causes server crash when used with HANDLER statements
#

create table t1 (c1 int);
create table t2 (c1 int);
insert into t1 values (1);
insert into t2 values (2);
handler t1 open;
handler t1 read first;
connect (flush,localhost,root,,);
connection flush;
flush tables;
send flush table t1;
connect (waiter,localhost,root,,);
connection waiter;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock";
--source include/wait_condition.inc
connection default;
handler t2 open;
handler t2 read first;
handler t1 read next;
handler t1 close;
handler t2 close;
connection flush;
reap;
connection default;
drop table t1,t2;
disconnect flush;

#
# Bug#31409 RENAME TABLE causes server crash or deadlock when used with HANDLER statements
#

--disable_warnings
drop table if exists t1, t0;
--enable_warnings
create table t1 (c1 int);
handler t1 open;
handler t1 read first;
connect (flush,localhost,root,,);
connection flush;
send rename table t1 to t0;
connection waiter;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "rename table t1 to t0";
--source include/wait_condition.inc
connection default;
--echo #
--echo # RENAME placed two pending locks and waits.
--echo # When HANDLER t0 OPEN does open_tables(), it calls
--echo # mysql_ha_flush(), which in turn closes the open HANDLER for t1.
--echo # RENAME TABLE gets unblocked. If it gets scheduled quickly
--echo # and manages to complete before open_tables()
--echo # of HANDLER t0 OPEN, open_tables() and therefore the whole
--echo # HANDLER t0 OPEN succeeds. Otherwise open_tables() 
--echo # notices a pending or active exclusive metadata lock on t2
--echo # and the whole HANDLER t0 OPEN fails with ER_LOCK_DEADLOCK
--echo # error.
--echo #
--error 0, ER_LOCK_DEADLOCK
handler t0 open;
--error 0, ER_UNKNOWN_TABLE
handler t0 close;
connection flush;
reap;
--error ER_UNKNOWN_TABLE
handler t1 read next;
--error ER_UNKNOWN_TABLE
handler t1 close;
connection default;
drop table t0;
connection flush;
disconnect flush;
--source include/wait_until_disconnected.inc
connection waiter;
disconnect waiter;
--source include/wait_until_disconnected.inc
connection default;

#
# Bug#31397 Inconsistent drop table behavior of handler tables.
#

create table t1 (a int);
handler t1 open as t1_alias;
drop table t1;
create table t1 (a int);
handler t1 open as t1_alias;
flush tables;
drop table t1;
create table t1 (a int);
handler t1 open as t1_alias;
handler t1_alias close;
drop table t1;
create table t1 (a int);
handler t1 open as t1_alias;
handler t1_alias read first;
drop table t1;
--error ER_UNKNOWN_TABLE
handler t1_alias read next;

# Flush tables causes handlers reopen

create table t1 (a int, b char(1), key a (a), key b (a,b));
insert into t1 values (0,"a"),(1,"b"),(2,"c"),(3,"d"),(4,"e"),
                      (5,"f"),(6,"g"),(7,"h"),(8,"i"),(9,"j");
handler t1 open;
handler t1 read a first;
handler t1 read a next;
flush tables;
handler t1 read a next;
flush tables t1;
handler t1 read a next;
flush tables with read lock;
handler t1 read a next;
unlock tables;
drop table t1;
--error ER_UNKNOWN_TABLE
handler t1 read a next;

#
# Bug#41110: crash with handler command when used concurrently with alter table
# Bug#41112: crash in mysql_ha_close_table/get_lock_data with alter table
#

connect(con1,localhost,root,,);
connect(con2,localhost,root,,);

--echo # Now test case which was reported originally but which no longer
--echo # triggers execution path which has caused the problem.
connection default;
create table t1 (a int not null);
insert into t1 values (1);
handler t1 open;
connection con1;
send alter table t1 engine=csv;
connection con2;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "alter table t1 engine=csv";
--source include/wait_condition.inc
connection default;
--echo # Since S metadata lock was already acquired at HANDLER OPEN time
--echo # and TL_READ lock requested by HANDLER READ is compatible with
--echo # ALTER's TL_WRITE_ALLOW_READ the below statement should succeed
--echo # without waiting. The old version of table should be used in it.
handler t1 read next;
handler t1 close;
connection con1;
--reap # Since last in this connection was a send
drop table t1;
disconnect con1;
--source include/wait_until_disconnected.inc
connection con2;
disconnect con2;
--source include/wait_until_disconnected.inc
connection default;

#
# Bug#44151 using handler commands on information_schema tables crashes server
#
USE information_schema;
--error ER_WRONG_USAGE
HANDLER COLUMNS OPEN;
USE test;

#
# lp:697622 Assertion `! is_set()' failed when preparing a HANDLER statement
#
--error ER_UNKNOWN_TABLE
PREPARE h_r FROM 'HANDLER t1 READ `PRIMARY` LAST';

#
# MySQL Bug#16385711: HANDLER, CREATE TABLE IF NOT EXISTS, PROBLEM AFTER MYSQL_HA_FIND
#
create view v as select 1;
create temporary table v as select 2;
handler v open;
prepare stmt from 'create table if not exists v as select 3';
execute stmt;
--error ER_UNKNOWN_TABLE
handler v read next;
drop view v;

--echo #
--echo # 10.2 Test
--echo #
--echo # MDEV-20207: Assertion `! is_set()' failed in
--echo # Diagnostics_area::set_eof_status upon HANDLER READ
--echo #

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a POINT, KEY(a));
HANDLER t1 OPEN h;

--error ER_CANT_CREATE_GEOMETRY_OBJECT
HANDLER h READ a = (0);

HANDLER h CLOSE;
DROP TABLE t1;

--echo # End of 10.2 Test

--echo #
--echo # MDEV-15813 ASAN use-after-poison in hp_hashnr upon
--echo #            HANDLER READ on a versioned HEAP table
--echo #

CREATE TABLE t1 (g GEOMETRY NOT NULL, SPATIAL gi(g));
INSERT INTO t1 VALUES (POINT(0,0));
HANDLER t1 OPEN AS h;
--error ER_KEY_DOESNT_SUPPORT
HANDLER h READ `gi`= (10);
--error ER_KEY_DOESNT_SUPPORT
HANDLER h READ `gi`> (10);
HANDLER h CLOSE;
DROP TABLE t1;

CREATE TABLE t1 (w VARCHAR(100), FULLTEXT fk(w));
INSERT INTO t1 VALUES ('one two three');
HANDLER t1 OPEN AS h;
--error ER_KEY_DOESNT_SUPPORT
HANDLER h READ `fk`= (10);
--error ER_KEY_DOESNT_SUPPORT
HANDLER h READ `fk`> (10);
HANDLER h CLOSE;
DROP TABLE t1;