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