Debian Codegraph @

mysql-test/suite/ndb/t/ndb_index_name_format.test

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