Debian Codegraph @

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

raw
1 CREATE PROCEDURE show_index_names(table_name varchar(255))
2 BEGIN
3 DECLARE ndb_table_id INT DEFAULT -1;
4 # Load table id
5 SELECT id FROM ndbinfo.dict_obj_info
6 WHERE fq_name = table_name
7 INTO ndb_table_id;
8 # Select all indexes belonging to table id, mask the table id
9 SELECT REPLACE(fq_name, ndb_table_id, '<table_id>') as index_name
10 FROM ndbinfo.dict_obj_info
11 WHERE parent_obj_id = ndb_table_id ORDER BY index_name;
12 END$
13 # Create table with new index name format and show that
14 # indexes are in sys/def/ namespace
15 CREATE TABLE t1_new(
16 a int PRIMARY KEY,
17 b varchar(255) NOT NULL,
18 c int,
19 index b_index (b),
20 unique index c_unique (c)
21 ) ENGINE = NDB;
22 # Indexes are in system namespace, ie. prefixed with sys/def/
23 call show_index_names('test/def/t1_new');
24 index_name
25 sys/def/<table_id>/b_index
26 sys/def/<table_id>/c_unique
27 sys/def/<table_id>/c_unique$unique
28 sys/def/<table_id>/PRIMARY
29 DROP TABLE t1_new;
30 # Create tables with old index format using DBUG keyword
31 set debug='+d,ndb_index_old_name_format';
32 CREATE TABLE t1_old(
33 a int PRIMARY KEY,
34 b varchar(255) NOT NULL,
35 c int,
36 d int DEFAULT NULL,
37 index b_index (b),
38 unique index c_unique (c)
39 ) ENGINE = NDB;
40 CREATE TABLE t2_old(
41 a int PRIMARY KEY,
42 b varchar(255) NOT NULL,
43 c int,
44 d int DEFAULT NULL,
45 index b_index (b),
46 unique index c_unique (c)
47 ) ENGINE = NDB;
48 CREATE TABLE t3_old(
49 a int PRIMARY KEY,
50 b varchar(255) NOT NULL,
51 c int,
52 index b_index (b),
53 unique index c_unique (c)
54 ) ENGINE = NDB;
55 # Remove keyword and let backwards compat code handle it from here on
56 set debug = '-d,ndb_index_old_name_format';
57 # Drop the third table, shows that table with old format can be dropped
58 DROP TABLE t3_old;
59 # Indexes are in tables namespace, ie. prefixed with test/def/
60 call show_index_names('test/def/t1_old');
61 index_name
62 test/def/<table_id>/b_index
63 test/def/<table_id>/c_unique
64 test/def/<table_id>/c_unique$unique
65 test/def/<table_id>/PRIMARY
66 # Check if table can be used for DML
67 INSERT INTO t1_old VALUES (1, "MySQL Server with NDB", 2, 89);
68 INSERT INTO t1_old (a, b, c) VALUES (11, "Barrier effect", 3);
69 INSERT INTO t1_old (a, b, c) VALUES
70 (12, "The third row", 4),
71 (37, "And of course number 37", 7);
72 SELECT * FROM t1_old WHERE a = 1;
73 a b c d
74 1 MySQL Server with NDB 2 89
75 UPDATE t1_old SET b = CONCAT(b, " test") WHERE a = 1;
76 SELECT * FROM t1_old WHERE a = 1;
77 a b c d
78 1 MySQL Server with NDB test 2 89
79 REPLACE t1_old (a, b, c) VALUES (12, "Another layer", 10);
80 SELECT * FROM t1_old WHERE a = 12 ORDER BY a;
81 a b c d
82 12 Another layer 10 NULL
83 DELETE FROM t1_old WHERE a = 11;
84 SELECT COUNT(*) FROM t1_old;
85 COUNT(*)
86 3
87 SELECT b FROM t1_old WHERE b LIKE "MySQL%";
88 b
89 MySQL Server with NDB test
90 DELETE FROM t1_old ORDER BY b DESC;
91 # Create another index, table will now use both new and old index names
92 CREATE INDEX another_b_index ON t1_old (b,c);
93 call show_index_names('test/def/t1_old');
94 index_name
95 sys/def/<table_id>/another_b_index
96 test/def/<table_id>/b_index
97 test/def/<table_id>/c_unique
98 test/def/<table_id>/c_unique$unique
99 test/def/<table_id>/PRIMARY
100 # RENAME TABLE <name> (or ALTER TABLE <name> RENAME) to same database
101 # will preserve the old index name ( since indexes are not recreated)
102 RENAME TABLE t1_old TO t1_old_renamed;
103 # Indexes are still in tables namespace. i.e test/def/
104 call show_index_names('test/def/t1_old_renamed');
105 index_name
106 sys/def/<table_id>/another_b_index
107 test/def/<table_id>/b_index
108 test/def/<table_id>/c_unique
109 test/def/<table_id>/c_unique$unique
110 test/def/<table_id>/PRIMARY
111 RENAME TABLE t1_old_renamed TO t1_old;
112 # RENAME TABLE <name> to _another_ database will recreate the indexes
113 # and thus start to use new index name format.
114 CREATE DATABASE test2;
115 RENAME TABLE t1_old TO test2.t1_old_other_db;
116 # Indexes are now in system namespace. i.e sys/def/
117 call show_index_names('test2/def/t1_old_other_db');
118 index_name
119 sys/def/<table_id>/another_b_index
120 sys/def/<table_id>/b_index
121 sys/def/<table_id>/c_unique
122 sys/def/<table_id>/c_unique$unique
123 sys/def/<table_id>/PRIMARY
124 DROP TABLE test2.t1_old_other_db;
125 DROP DATABASE test2;
126 # Inplace alter table (that adds a colum) will preserve the
127 # old index name format
128 ALTER TABLE t2_old ALGORITHM=inplace, ADD COLUMN e INT DEFAULT NULL;
129 Warnings:
130 Warning 1478 Converted FIXED field 'e' to DYNAMIC to enable online ADD COLUMN
131 call show_index_names('test/def/t2_old');
132 index_name
133 test/def/<table_id>/b_index
134 test/def/<table_id>/c_unique
135 test/def/<table_id>/c_unique$unique
136 test/def/<table_id>/PRIMARY
137 # Forcing a copying alter table will create indexes with new format
138 # This shows there is a way for the user to remove the old index names!
139 ALTER TABLE t2_old FORCE;
140 # All indexes are now in system namespace, i.e sys/def/
141 call show_index_names('test/def/t2_old');
142 index_name
143 sys/def/<table_id>/b_index
144 sys/def/<table_id>/c_unique
145 sys/def/<table_id>/c_unique$unique
146 sys/def/<table_id>/PRIMARY
147 DROP TABLE t2_old;
148 DROP PROCEDURE show_index_names;