Debian Codegraph @

mysql-test/suite/ndb/r/ndb_use_dropped_index_rebuild.result

raw
1 result_format: 2
2 ## The MySQL Server supports opening tables with missing indexes.
3 ## This means that MySQL knows about an index which does
4 ## not exist in NDB but it still allows opening the table with
5 ## the index unavailable.
6 ##
7 ## This test makes sure that a table with missing index which
8 ## has been opened in MySQL detects when the index becomes available
9 ## in NDB again.
10 ##
11 call mtr.add_suppression(".*Got error 126 when reading table '.*t1'");
12
13 CREATE TABLE t1 (
14 pk INT PRIMARY KEY,
15 a INT,
16 b VARCHAR(255),
17 c DECIMAL(10,2),
18 INDEX idx_a (a)
19 ) ENGINE=NDBCLUSTER;
20
21 INSERT INTO t1 VALUES
22 (1, 10, 'value1', 10.50),
23 (2, 20, 'value2', 20.25),
24 (3, 30, 'value3', 30.75);
25
26 ## Take backup in order to be able to restore the index
27
28 ## Read from table when index idx_a is available
29 ## thus opening the table and cache it
30 SELECT a FROM t1 WHERE a = 10;
31 a
32 10
33 EXPLAIN SELECT a FROM t1 WHERE a = 10;
34 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
35 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref idx_a idx_a 5 const 3 100.00 NULL
36 Warnings:
37 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 10)
38 ## Other queries work
39 SELECT pk FROM t1 ORDER BY pk LIMIT 1;
40 pk
41 1
42 SELECT pk FROM t1 WHERE pk = 1;
43 pk
44 1
45
46 ## Check index exist in NDB
47
48 ##########################################
49 ## Use ndb_drop_index to drop the index idx_a.
50 ## Check index does NOT exist in NDB
51
52 ## Select from the table again, expect error since NDB
53 ## detects that table version has changed
54 SELECT a FROM t1 WHERE a = 10;
55 ERROR HY000: Table definition has changed, please retry transaction
56
57 ## Query using the index fails
58 SELECT a FROM t1 WHERE a = 10;
59 ERROR HY000: Incorrect key file for table 't1'; try to repair it
60
61 ## Other queries work
62 SELECT pk FROM t1 ORDER BY pk LIMIT 1;
63 pk
64 1
65 SELECT pk FROM t1 WHERE pk = 1;
66 pk
67 1
68
69 ###################################
70 ## Restore index from backup
71 ####################################
72 ## Check that index 'idx_a' exist in NDB again
73
74 ## Query using the index works again
75 SELECT a FROM t1 WHERE a = 10;
76 a
77 10
78
79 ## Other queries work
80 SELECT pk FROM t1 ORDER BY pk LIMIT 1;
81 pk
82 1
83 SELECT pk FROM t1 WHERE pk = 1;
84 pk
85 1
86
87 ## Explain shows that MySQL still want to use idx_a
88 EXPLAIN SELECT a FROM t1 WHERE a = 10;
89 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
90 1 SIMPLE t1 p0,p1,p2,p3,p4,p5,p6,p7 ref idx_a idx_a 5 const 3 100.00 NULL
91 Warnings:
92 Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = 10)
93
94 DROP TABLE t1;
95