CREATE PROCEDURE show_index_names(table_name varchar(255)) BEGIN DECLARE ndb_table_id INT DEFAULT -1; # Load table id SELECT id FROM ndbinfo.dict_obj_info WHERE fq_name = table_name INTO ndb_table_id; # Select all indexes belonging to table id, mask the table id SELECT REPLACE(fq_name, ndb_table_id, '') as index_name FROM ndbinfo.dict_obj_info WHERE parent_obj_id = ndb_table_id ORDER BY index_name; END$ # Create table with new index name format and show that # indexes are in sys/def/ namespace CREATE TABLE t1_new( a int PRIMARY KEY, b varchar(255) NOT NULL, c int, index b_index (b), unique index c_unique (c) ) ENGINE = NDB; # Indexes are in system namespace, ie. prefixed with sys/def/ call show_index_names('test/def/t1_new'); index_name sys/def//b_index sys/def//c_unique sys/def//c_unique$unique sys/def//PRIMARY DROP TABLE t1_new; # Create tables with old index format using DBUG keyword set debug='+d,ndb_index_old_name_format'; CREATE TABLE t1_old( a int PRIMARY KEY, b varchar(255) NOT NULL, c int, d int DEFAULT NULL, index b_index (b), unique index c_unique (c) ) ENGINE = NDB; CREATE TABLE t2_old( a int PRIMARY KEY, b varchar(255) NOT NULL, c int, d int DEFAULT NULL, index b_index (b), unique index c_unique (c) ) ENGINE = NDB; CREATE TABLE t3_old( a int PRIMARY KEY, b varchar(255) NOT NULL, c int, index b_index (b), unique index c_unique (c) ) ENGINE = NDB; # Remove keyword and let backwards compat code handle it from here on set debug = '-d,ndb_index_old_name_format'; # Drop the third table, shows that table with old format can be dropped DROP TABLE t3_old; # Indexes are in tables namespace, ie. prefixed with test/def/ call show_index_names('test/def/t1_old'); index_name test/def//b_index test/def//c_unique test/def//c_unique$unique test/def//PRIMARY # Check if table can be used for DML INSERT INTO t1_old VALUES (1, "MySQL Server with NDB", 2, 89); INSERT INTO t1_old (a, b, c) VALUES (11, "Barrier effect", 3); INSERT INTO t1_old (a, b, c) VALUES (12, "The third row", 4), (37, "And of course number 37", 7); SELECT * FROM t1_old WHERE a = 1; a b c d 1 MySQL Server with NDB 2 89 UPDATE t1_old SET b = CONCAT(b, " test") WHERE a = 1; SELECT * FROM t1_old WHERE a = 1; a b c d 1 MySQL Server with NDB test 2 89 REPLACE t1_old (a, b, c) VALUES (12, "Another layer", 10); SELECT * FROM t1_old WHERE a = 12 ORDER BY a; a b c d 12 Another layer 10 NULL DELETE FROM t1_old WHERE a = 11; SELECT COUNT(*) FROM t1_old; COUNT(*) 3 SELECT b FROM t1_old WHERE b LIKE "MySQL%"; b MySQL Server with NDB test DELETE FROM t1_old ORDER BY b DESC; # Create another index, table will now use both new and old index names CREATE INDEX another_b_index ON t1_old (b,c); call show_index_names('test/def/t1_old'); index_name sys/def//another_b_index test/def//b_index test/def//c_unique test/def//c_unique$unique test/def//PRIMARY # RENAME TABLE (or ALTER TABLE RENAME) to same database # will preserve the old index name ( since indexes are not recreated) RENAME TABLE t1_old TO t1_old_renamed; # Indexes are still in tables namespace. i.e test/def/ call show_index_names('test/def/t1_old_renamed'); index_name sys/def//another_b_index test/def//b_index test/def//c_unique test/def//c_unique$unique test/def//PRIMARY RENAME TABLE t1_old_renamed TO t1_old; # RENAME TABLE to _another_ database will recreate the indexes # and thus start to use new index name format. CREATE DATABASE test2; RENAME TABLE t1_old TO test2.t1_old_other_db; # Indexes are now in system namespace. i.e sys/def/ call show_index_names('test2/def/t1_old_other_db'); index_name sys/def//another_b_index sys/def//b_index sys/def//c_unique sys/def//c_unique$unique sys/def//PRIMARY DROP TABLE test2.t1_old_other_db; DROP DATABASE test2; # Inplace alter table (that adds a colum) will preserve the # old index name format ALTER TABLE t2_old ALGORITHM=inplace, ADD COLUMN e INT DEFAULT NULL; Warnings: Warning 1478 Converted FIXED field 'e' to DYNAMIC to enable online ADD COLUMN call show_index_names('test/def/t2_old'); index_name test/def//b_index test/def//c_unique test/def//c_unique$unique test/def//PRIMARY # Forcing a copying alter table will create indexes with new format # This shows there is a way for the user to remove the old index names! ALTER TABLE t2_old FORCE; # All indexes are now in system namespace, i.e sys/def/ call show_index_names('test/def/t2_old'); index_name sys/def//b_index sys/def//c_unique sys/def//c_unique$unique sys/def//PRIMARY DROP TABLE t2_old; DROP PROCEDURE show_index_names;