| 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 |
|