Debian Codegraph @

mysql-test/suite/x/t/drop_index_crud.test

raw
1 ########### ../t/drop_index_crud.test ######################
2 ## #
3 ## This test runs aims index creation/drop using crud calls #
4 ## Test covers #
5 ## - indexes with virtual columns of the all supported datatypes #
6 ## - creating and dropping indexes on multiple virtual columns #
7 ## - creating and dropping indexes that share the same virtual column #
8 ## - error scenarios when dropping the index #
9 ## - error scenario with virtual column with not supported datatype #
10 ########################################################################
11
12
13
14 # drop_collection_index
15
16
17 ## Preamble
18 --source include/not_valgrind.inc
19 --source include/xplugin_preamble.inc
20 --source include/xplugin_create_user.inc
21
22 call mtr.add_suppression("Error parsing message of type 12: args");
23 call mtr.add_suppression("Error handling message: args");
24
25 ## Test starts here
26 --write_file $MYSQL_TMP_DIR/mysqlx-in.xpl
27
28
29 -->macro Create_collection_index %INDEX_NAME% %TYPE%
30 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table1", "name":"%INDEX_NAME%", "unique":true, "fields":{"field":"$.col", "type":"%TYPE%","required":true}}
31 -->recv
32 -->endmacro
33
34 -->macro Drop_collection_index %INDEX_NAME%
35 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"%INDEX_NAME%"}
36 -->recv
37 -->endmacro
38
39 -->sql
40 #-- "Creating database for testcase"
41 CREATE DATABASE xplugintest;
42 USE xplugintest;
43 -->endsql
44
45 -->stmtadmin create_collection {"schema":"xplugintest" ,"name":"table1"}
46 -->recvresult
47
48 -->echo Create and drop indexes of different types on single fields
49
50 -->callmacro Create_collection_index ix TINYINT
51
52 -->sql
53 ANALYZE TABLE xplugintest.table1;
54 SHOW INDEX FROM xplugintest.table1;
55 SHOW CREATE TABLE xplugintest.table1;
56 -->endsql
57 -->callmacro Drop_collection_index ix
58 -->sql
59 SHOW INDEX FROM xplugintest.table1;
60 -->endsql
61
62 -->callmacro Create_collection_index ix TINYINT UNSIGNED
63 -->sql
64 ANALYZE TABLE xplugintest.table1;
65 SHOW INDEX FROM xplugintest.table1;
66 SHOW CREATE TABLE xplugintest.table1;
67 -->endsql
68 -->callmacro Drop_collection_index ix
69 -->sql
70 SHOW INDEX FROM xplugintest.table1;
71 -->endsql
72
73 -->callmacro Create_collection_index ix TINYINT(2) UNSIGNED
74 -->sql
75 ANALYZE TABLE xplugintest.table1;
76 SHOW INDEX FROM xplugintest.table1;
77 SHOW CREATE TABLE xplugintest.table1;
78 -->endsql
79 -->callmacro Drop_collection_index ix
80 -->sql
81 SHOW INDEX FROM xplugintest.table1;
82 -->endsql
83
84 -->callmacro Create_collection_index ix SMALLINT
85 -->sql
86 ANALYZE TABLE xplugintest.table1;
87 SHOW INDEX FROM xplugintest.table1;
88 SHOW CREATE TABLE xplugintest.table1;
89 -->endsql
90 -->callmacro Drop_collection_index ix
91 -->sql
92 SHOW INDEX FROM xplugintest.table1;
93 -->endsql
94
95 -->callmacro Create_collection_index ix SMALLINT UNSIGNED
96 -->sql
97 ANALYZE TABLE xplugintest.table1;
98 SHOW INDEX FROM xplugintest.table1;
99 SHOW CREATE TABLE xplugintest.table1;
100 -->endsql
101 -->callmacro Drop_collection_index ix
102 -->sql
103 SHOW INDEX FROM xplugintest.table1;
104 -->endsql
105
106 -->callmacro Create_collection_index ix SMALLINT(4) UNSIGNED
107 -->sql
108 ANALYZE TABLE xplugintest.table1;
109 SHOW INDEX FROM xplugintest.table1;
110 SHOW CREATE TABLE xplugintest.table1;
111 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345","_id":1}');
112 SELECT * FROM xplugintest.table1;
113 DELETE FROM xplugintest.table1;
114 -->endsql
115 -->callmacro Drop_collection_index ix
116 -->sql
117 SHOW INDEX FROM xplugintest.table1;
118 -->endsql
119
120 -->callmacro Create_collection_index ix MEDIUMINT
121 -->sql
122 ANALYZE TABLE xplugintest.table1;
123 SHOW INDEX FROM xplugintest.table1;
124 SHOW CREATE TABLE xplugintest.table1;
125 -->endsql
126 -->callmacro Drop_collection_index ix
127 -->sql
128 SHOW INDEX FROM xplugintest.table1;
129 -->endsql
130
131 -->callmacro Create_collection_index ix MEDIUMINT UNSIGNED
132 -->sql
133 ANALYZE TABLE xplugintest.table1;
134 SHOW INDEX FROM xplugintest.table1;
135 SHOW CREATE TABLE xplugintest.table1;
136 -->endsql
137 -->callmacro Drop_collection_index ix
138 -->sql
139 SHOW INDEX FROM xplugintest.table1;
140 -->endsql
141
142 -->callmacro Create_collection_index ix MEDIUMINT(7) UNSIGNED
143 -->sql
144 ANALYZE TABLE xplugintest.table1;
145 SHOW INDEX FROM xplugintest.table1;
146 SHOW CREATE TABLE xplugintest.table1;
147 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345678","_id":1}');
148 SELECT * FROM xplugintest.table1;
149 DELETE FROM xplugintest.table1;
150 -->endsql
151 -->callmacro Drop_collection_index ix
152 -->sql
153 SHOW INDEX FROM xplugintest.table1;
154 -->endsql
155
156 -->callmacro Create_collection_index ix INTEGER
157 -->sql
158 ANALYZE TABLE xplugintest.table1;
159 SHOW INDEX FROM xplugintest.table1;
160 SHOW CREATE TABLE xplugintest.table1;
161 -->endsql
162 -->callmacro Drop_collection_index ix
163 -->sql
164 SHOW INDEX FROM xplugintest.table1;
165 -->endsql
166
167 -->callmacro Create_collection_index ix INTEGER UNSIGNED
168 -->sql
169 ANALYZE TABLE xplugintest.table1;
170 SHOW INDEX FROM xplugintest.table1;
171 SHOW CREATE TABLE xplugintest.table1;
172 -->endsql
173 -->callmacro Drop_collection_index ix
174 -->sql
175 SHOW INDEX FROM xplugintest.table1;
176 -->endsql
177
178 -->callmacro Create_collection_index ix INTEGER(6) UNSIGNED
179 -->sql
180 ANALYZE TABLE xplugintest.table1;
181 SHOW INDEX FROM xplugintest.table1;
182 SHOW CREATE TABLE xplugintest.table1;
183 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"1234567","_id":1}');
184 SELECT * FROM xplugintest.table1;
185 DELETE FROM xplugintest.table1;
186 -->endsql
187 -->callmacro Drop_collection_index ix
188 -->sql
189 SHOW INDEX FROM xplugintest.table1;
190 -->endsql
191
192 -->callmacro Create_collection_index ix BIGINT
193 -->sql
194 ANALYZE TABLE xplugintest.table1;
195 SHOW INDEX FROM xplugintest.table1;
196 SHOW CREATE TABLE xplugintest.table1;
197 -->endsql
198 -->callmacro Drop_collection_index ix
199 -->sql
200 SHOW INDEX FROM xplugintest.table1;
201 -->endsql
202
203 -->callmacro Create_collection_index ix BIGINT UNSIGNED
204 -->sql
205 ANALYZE TABLE xplugintest.table1;
206 SHOW INDEX FROM xplugintest.table1;
207 SHOW CREATE TABLE xplugintest.table1;
208 -->endsql
209 -->callmacro Drop_collection_index ix
210 -->sql
211 SHOW INDEX FROM xplugintest.table1;
212 -->endsql
213
214 -->callmacro Create_collection_index ix BIGINT(13)
215 -->sql
216 ANALYZE TABLE xplugintest.table1;
217 SHOW INDEX FROM xplugintest.table1;
218 SHOW CREATE TABLE xplugintest.table1;
219 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123456789012345","_id":1}');
220 SELECT * FROM xplugintest.table1;
221 DELETE FROM xplugintest.table1;
222 -->endsql
223 -->callmacro Drop_collection_index ix
224 -->sql
225 SHOW INDEX FROM xplugintest.table1;
226 -->endsql
227
228 -->callmacro Create_collection_index ix BIGINT(22) UNSIGNED
229 -->sql
230 ANALYZE TABLE xplugintest.table1;
231 SHOW INDEX FROM xplugintest.table1;
232 SHOW CREATE TABLE xplugintest.table1;
233 -->endsql
234 -->callmacro Drop_collection_index ix
235 -->sql
236 SHOW INDEX FROM xplugintest.table1;
237 -->endsql
238
239 -->callmacro Create_collection_index ix REAL
240 -->sql
241 ANALYZE TABLE xplugintest.table1;
242 SHOW INDEX FROM xplugintest.table1;
243 SHOW CREATE TABLE xplugintest.table1;
244 -->endsql
245 -->callmacro Drop_collection_index ix
246 -->sql
247 SHOW INDEX FROM xplugintest.table1;
248 -->endsql
249
250 -->callmacro Create_collection_index ix REAL UNSIGNED
251 -->sql
252 ANALYZE TABLE xplugintest.table1;
253 SHOW INDEX FROM xplugintest.table1;
254 SHOW CREATE TABLE xplugintest.table1;
255 -->endsql
256 -->callmacro Drop_collection_index ix
257 -->sql
258 SHOW INDEX FROM xplugintest.table1;
259 -->endsql
260
261 -->callmacro Create_collection_index ix FLOAT
262 -->sql
263 ANALYZE TABLE xplugintest.table1;
264 SHOW INDEX FROM xplugintest.table1;
265 SHOW CREATE TABLE xplugintest.table1;
266 -->endsql
267 -->callmacro Drop_collection_index ix
268 -->sql
269 SHOW INDEX FROM xplugintest.table1;
270 -->endsql
271
272 -->callmacro Create_collection_index ix FLOAT UNSIGNED
273 -->sql
274 ANALYZE TABLE xplugintest.table1;
275 SHOW INDEX FROM xplugintest.table1;
276 SHOW CREATE TABLE xplugintest.table1;
277 -->endsql
278 -->callmacro Drop_collection_index ix
279 -->sql
280 SHOW INDEX FROM xplugintest.table1;
281 -->endsql
282
283 -->callmacro Create_collection_index ix DOUBLE
284 -->sql
285 ANALYZE TABLE xplugintest.table1;
286 SHOW INDEX FROM xplugintest.table1;
287 SHOW CREATE TABLE xplugintest.table1;
288 -->endsql
289 -->callmacro Drop_collection_index ix
290 -->sql
291 SHOW INDEX FROM xplugintest.table1;
292 -->endsql
293
294 -->callmacro Create_collection_index ix DOUBLE UNSIGNED
295 -->sql
296 ANALYZE TABLE xplugintest.table1;
297 SHOW INDEX FROM xplugintest.table1;
298 SHOW CREATE TABLE xplugintest.table1;
299 -->endsql
300 -->callmacro Drop_collection_index ix
301 -->sql
302 SHOW INDEX FROM xplugintest.table1;
303 -->endsql
304
305 -->callmacro Create_collection_index ix DOUBLE(7,4) UNSIGNED
306 -->sql
307 ANALYZE TABLE xplugintest.table1;
308 SHOW INDEX FROM xplugintest.table1;
309 SHOW CREATE TABLE xplugintest.table1;
310 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123.1234","_id":1}');
311 SELECT * FROM xplugintest.table1;
312 DELETE FROM xplugintest.table1;
313 -->endsql
314 -->callmacro Drop_collection_index ix
315 -->sql
316 SHOW INDEX FROM xplugintest.table1;
317 -->endsql
318
319 -->callmacro Create_collection_index ix DECIMAL
320 -->sql
321 ANALYZE TABLE xplugintest.table1;
322 SHOW INDEX FROM xplugintest.table1;
323 SHOW CREATE TABLE xplugintest.table1;
324 -->endsql
325 -->callmacro Drop_collection_index ix
326 -->sql
327 SHOW INDEX FROM xplugintest.table1;
328 -->endsql
329
330 -->callmacro Create_collection_index ix DECIMAL(10) UNSIGNED
331 -->sql
332 ANALYZE TABLE xplugintest.table1;
333 SHOW INDEX FROM xplugintest.table1;
334 SHOW CREATE TABLE xplugintest.table1;
335 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123456.123","_id":1}');
336 SELECT * FROM xplugintest.table1;
337 DELETE FROM xplugintest.table1;
338 -->endsql
339 -->callmacro Drop_collection_index ix
340 -->sql
341 SHOW INDEX FROM xplugintest.table1;
342 -->endsql
343
344 -->callmacro Create_collection_index ix DECIMAL(8,2) UNSIGNED
345 -->sql
346 ANALYZE TABLE xplugintest.table1;
347 SHOW INDEX FROM xplugintest.table1;
348 SHOW CREATE TABLE xplugintest.table1;
349 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"123456.123","_id":1}');
350 SELECT * FROM xplugintest.table1;
351 DELETE FROM xplugintest.table1;
352 -->endsql
353 -->callmacro Drop_collection_index ix
354 -->sql
355 SHOW INDEX FROM xplugintest.table1;
356 -->endsql
357
358 -->callmacro Create_collection_index ix NUMERIC
359 -->sql
360 ANALYZE TABLE xplugintest.table1;
361 SHOW INDEX FROM xplugintest.table1;
362 SHOW CREATE TABLE xplugintest.table1;
363 -->endsql
364 -->callmacro Drop_collection_index ix
365 -->sql
366 SHOW INDEX FROM xplugintest.table1;
367 -->endsql
368
369 -->callmacro Create_collection_index ix NUMERIC(10,2)
370 -->sql
371 ANALYZE TABLE xplugintest.table1;
372 SHOW INDEX FROM xplugintest.table1;
373 SHOW CREATE TABLE xplugintest.table1;
374 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345.123456","_id":1}');
375 SELECT * FROM xplugintest.table1;
376 DELETE FROM xplugintest.table1;
377 -->endsql
378 -->callmacro Drop_collection_index ix
379 -->sql
380 SHOW INDEX FROM xplugintest.table1;
381 -->endsql
382
383 -->callmacro Create_collection_index ix NUMERIC(5)
384 -->sql
385 ANALYZE TABLE xplugintest.table1;
386 SHOW INDEX FROM xplugintest.table1;
387 SHOW CREATE TABLE xplugintest.table1;
388 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12345.123456","_id":1}');
389 SELECT * FROM xplugintest.table1;
390 DELETE FROM xplugintest.table1;
391 -->endsql
392 -->callmacro Drop_collection_index ix
393 -->sql
394 SHOW INDEX FROM xplugintest.table1;
395 -->endsql
396
397 -->callmacro Create_collection_index ix DATE
398 -->sql
399 ANALYZE TABLE xplugintest.table1;
400 SHOW INDEX FROM xplugintest.table1;
401 SHOW CREATE TABLE xplugintest.table1;
402 -->endsql
403 -->callmacro Drop_collection_index ix
404 -->sql
405 SHOW INDEX FROM xplugintest.table1;
406 -->endsql
407
408 -->callmacro Create_collection_index ix TIME
409 -->sql
410 ANALYZE TABLE xplugintest.table1;
411 SHOW INDEX FROM xplugintest.table1;
412 SHOW CREATE TABLE xplugintest.table1;
413 -->endsql
414 -->callmacro Drop_collection_index ix
415 -->sql
416 SHOW INDEX FROM xplugintest.table1;
417 -->endsql
418
419 -->callmacro Create_collection_index ix TIME(2)
420 -->sql
421 ANALYZE TABLE xplugintest.table1;
422 SHOW INDEX FROM xplugintest.table1;
423 SHOW CREATE TABLE xplugintest.table1;
424 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"12:13:44","_id":1}');
425 SELECT * FROM xplugintest.table1;
426 DELETE FROM xplugintest.table1;
427 -->endsql
428 -->callmacro Drop_collection_index ix
429 -->sql
430 SHOW INDEX FROM xplugintest.table1;
431 -->endsql
432
433 -->callmacro Create_collection_index ix TIMESTAMP
434 -->sql
435 ANALYZE TABLE xplugintest.table1;
436 SHOW INDEX FROM xplugintest.table1;
437 SHOW CREATE TABLE xplugintest.table1;
438 -->endsql
439 -->callmacro Drop_collection_index ix
440 -->sql
441 SHOW INDEX FROM xplugintest.table1;
442 -->endsql
443
444 -->callmacro Create_collection_index ix TIMESTAMP(6)
445 -->sql
446 ANALYZE TABLE xplugintest.table1;
447 SHOW INDEX FROM xplugintest.table1;
448 SHOW CREATE TABLE xplugintest.table1;
449 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"2038-01-19 03:14:07.123456","_id":1}');
450 SELECT * FROM xplugintest.table1;
451 DELETE FROM xplugintest.table1;
452 -->endsql
453 -->callmacro Drop_collection_index ix
454 -->sql
455 SHOW INDEX FROM xplugintest.table1;
456 -->endsql
457
458 -->callmacro Create_collection_index ix DATETIME
459 -->sql
460 ANALYZE TABLE xplugintest.table1;
461 SHOW INDEX FROM xplugintest.table1;
462 SHOW CREATE TABLE xplugintest.table1;
463 -->endsql
464 -->callmacro Drop_collection_index ix
465 -->sql
466 SHOW INDEX FROM xplugintest.table1;
467 -->endsql
468
469 -->callmacro Create_collection_index ix DATETIME(6)
470 -->sql
471 ANALYZE TABLE xplugintest.table1;
472 SHOW INDEX FROM xplugintest.table1;
473 SHOW CREATE TABLE xplugintest.table1;
474 -->endsql
475 -->callmacro Drop_collection_index ix
476 -->sql
477 SHOW INDEX FROM xplugintest.table1;
478 -->endsql
479
480 -->callmacro Create_collection_index ix YEAR
481 -->sql
482 ANALYZE TABLE xplugintest.table1;
483 SHOW INDEX FROM xplugintest.table1;
484 SHOW CREATE TABLE xplugintest.table1;
485 -->endsql
486 -->callmacro Drop_collection_index ix
487 -->sql
488 SHOW INDEX FROM xplugintest.table1;
489 -->endsql
490
491 -->callmacro Create_collection_index ix VARCHAR(15)
492 -->sql
493 ANALYZE TABLE xplugintest.table1;
494 SHOW INDEX FROM xplugintest.table1;
495 SHOW CREATE TABLE xplugintest.table1;
496 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"QUITE LOOOONG STRING","_id":1}');
497 SELECT * FROM xplugintest.table1;
498 DELETE FROM xplugintest.table1;
499 -->endsql
500 -->callmacro Drop_collection_index ix
501 -->sql
502 SHOW INDEX FROM xplugintest.table1;
503 -->endsql
504
505 -->callmacro Create_collection_index ix CHAR(25)
506 -->sql
507 ANALYZE TABLE xplugintest.table1;
508 SHOW INDEX FROM xplugintest.table1;
509 SHOW CREATE TABLE xplugintest.table1;
510 -->endsql
511 -->expecterror ER_DATA_TOO_LONG
512 -->sql
513 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"LONG LONG STRING, LONGER THAN INDEX SIZE","_id":1}');
514 -->endsql
515 -->sql
516 SELECT * FROM xplugintest.table1;
517 DELETE FROM xplugintest.table1;
518 -->endsql
519 -->callmacro Drop_collection_index ix
520 -->sql
521 SHOW INDEX FROM xplugintest.table1;
522 -->endsql
523
524 -->callmacro Create_collection_index ix BIT
525 -->sql
526 ANALYZE TABLE xplugintest.table1;
527 SHOW INDEX FROM xplugintest.table1;
528 SHOW CREATE TABLE xplugintest.table1;
529 -->endsql
530 -->callmacro Drop_collection_index ix
531 -->sql
532 SHOW INDEX FROM xplugintest.table1;
533 -->endsql
534
535 -->callmacro Create_collection_index ix BLOB(50)
536 -->sql
537 ANALYZE TABLE xplugintest.table1;
538 SHOW INDEX FROM xplugintest.table1;
539 SHOW CREATE TABLE xplugintest.table1;
540 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"BLOB BLOB BLOB BLOB BLOB VALUE LONG VALUE 0123456789 BLOB01234567890123456789","_id":1}');
541 SELECT * FROM xplugintest.table1;
542 DELETE FROM xplugintest.table1;
543 -->endsql
544 -->callmacro Drop_collection_index ix
545 -->sql
546 SHOW INDEX FROM xplugintest.table1;
547 -->endsql
548
549 -->callmacro Create_collection_index ix TEXT(30)
550 -->sql
551 ANALYZE TABLE xplugintest.table1;
552 SHOW INDEX FROM xplugintest.table1;
553 SHOW CREATE TABLE xplugintest.table1;
554 INSERT INTO xplugintest.table1(doc) VALUES('{"col":"TEXT LONGER THAN INDEX SIZE WHICH EQUALS 30 1234567890","_id":1}');
555 SELECT * FROM xplugintest.table1;
556 DELETE FROM xplugintest.table1;
557 -->endsql
558 -->callmacro Drop_collection_index ix
559 -->sql
560 SHOW INDEX FROM xplugintest.table1;
561 -->endsql
562
563
564 -->echo Create index on two fields
565 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table1", "name":"two_field_index", "unique":true, "fields":[{"field":"$.col_text", "type":"TEXT(10)","required":true},{"field":"$.col_int", "type":"INT","required":true}]}
566 -->recv
567
568 -->sql
569 ANALYZE TABLE xplugintest.table1;
570 SHOW INDEX FROM xplugintest.table1;
571 -->endsql
572
573 #-- Drop two-fields index
574
575 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"two_field_index"}
576 -->recv
577
578
579 -->sql
580 SHOW INDEX FROM xplugintest.table1;
581 -->endsql
582
583
584 -->echo Create two indexes using the same field
585
586 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table1", "name":"index1", "unique":true, "fields":[{"field":"$.col_1", "type":"INT","required":true},{"field":"$.col_2", "type":"INT","required":true}]}
587 -->recv
588
589 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table1", "name":"index2", "unique":true, "fields":{"field":"$.col_2", "type":"INT","required":true}}
590 -->recv
591
592 -->sql
593 ANALYZE TABLE xplugintest.table1;
594 SHOW INDEX FROM xplugintest.table1;
595 -->endsql
596
597 -->echo Drop the first of the indexes, it should keep the shared column
598
599 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"index1"}
600 -->recv
601
602 -->sql
603 SHOW INDEX FROM xplugintest.table1;
604 -->endsql
605
606
607
608 -->echo Drop the second of the indexes, the column should be gone
609
610 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"index2"}
611 -->recv
612
613 -->sql
614 SHOW INDEX FROM xplugintest.table1;
615 -->endsql
616
617
618 -->echo Error scenarios when dropping the index
619
620 #-- drop index for non-existing schema
621 -->stmtadmin drop_collection_index {"schema":"nonexisting", "collection":"table1", "name":"date_index"}
622 -->recv
623 #-- drop index for non-existing collection
624 -->stmtadmin drop_collection_index {"schema": "xplugintest", "collection":"nonexisting", "name":"date_index"}
625 -->recv
626 #-- drop index for non-existing index
627 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"nonexisting"}
628 -->recv
629
630 #-- ERROR scenario: create index of not supported type:
631 -->stmtadmin create_collection_index {"schema":"xplugintest", "collection":"table1", "name":"tblob_index", "unique":true, "fields":{"field":"$.col_blob", "type":"TINYBLOB(10)","required":true}}
632 -->recv
633
634 -->sql
635 SHOW INDEX FROM xplugintest.table1;
636 -->endsql
637
638
639 -->sql
640 #Trying to add FULLTEXT INDEX leads to server crash BUG 21633035
641 ALTER TABLE xplugintest.table1 ADD SUBJECT text GENERATED ALWAYS AS (json_extract( doc, '$.subject' )) STORED;
642 ALTER TABLE xplugintest.table1 ADD book text ;
643 ALTER TABLE xplugintest.table1 ADD INDEX `fulltext_idx_1` (subject(100));
644 ALTER TABLE xplugintest.table1 ADD INDEX `fulltext_idx_2` (subject(100));
645 ALTER TABLE xplugintest.table1 ADD INDEX `fulltext_idx_3` (book(100));
646
647 ANALYZE TABLE xplugintest.table1;
648 SHOW CREATE TABLE xplugintest.table1;
649 SHOW INDEX FROM xplugintest.table1;
650 -->endsql
651
652 -->echo drop primary index
653 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"PRIMARY"}
654 -->recv
655 -->sql
656 SHOW CREATE TABLE xplugintest.table1;
657 -->endsql
658
659
660 #-- Drop fulltext index
661 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"fulltext_idx_1"}
662 -->recv
663
664 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"fulltext_idx_3"}
665 -->recv
666
667 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table1", "name":"fulltext_idx_2"}
668 -->recv
669
670
671 -->sql
672 #-- drop column drop index as well
673 SHOW CREATE TABLE xplugintest.table1;
674 ALTER TABLE xplugintest.table1 DROP COLUMN subject;
675 ANALYZE TABLE xplugintest.table1;
676 SHOW INDEX FROM xplugintest.table1;
677 CREATE TABLE xplugintest.table2 (
678 ID TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
679 Name VARCHAR(255) NOT NULL,
680 LastName VARCHAR(255) NOT NULL,
681 UNIQUE `idx1` (Name),
682 UNIQUE `idx2` (LastName)
683 ) ;
684 -->endsql
685
686 -->echo drop index from data_model=TABLE
687 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table2", "name":"idx1"}
688 -->recv
689
690 -->echo Error can not drop primary index when column defined as auto_increment
691 -->stmtadmin drop_collection_index {"schema":"xplugintest", "collection":"table2", "name":"PRIMARY"}
692 -->expecterror 1075
693 -->recvresult
694
695 -->sql
696 SHOW CREATE TABLE xplugintest.table2;
697 -->endsql
698
699 -->echo drop index using namespace=sql
700 Mysqlx.Sql.StmtExecute {
701 stmt: "ALTER TABLE xplugintest.table2 DROP INDEX `idx2`"
702 namespace: "sql"
703 }
704 -->recvresult
705
706 -->sql
707 ANALYZE TABLE xplugintest.table2;
708 SHOW INDEX FROM xplugintest.table2;
709 -->endsql
710
711
712 ## Cleanup
713 -->sql
714 DROP DATABASE IF EXISTS xplugintest;
715 -->endsql
716 EOF
717
718 --exec $MYSQLXTEST -u x_root --password='' --file=$MYSQL_TMP_DIR/mysqlx-in.xpl 2>&1
719
720 ## Cleanup
721 --source ../include/xplugin_cleanup.inc