1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
#
# Show what happens during ALTER TABLE when an existing file
# exists in the target location.
#
# Bug #19218794: IF TABLESPACE EXISTS, CAN'T CREATE TABLE,
# BUT CAN ALTER ENGINE=INNODB
#
CREATE TABLE t1 (a SERIAL, b CHAR(10)) ENGINE=Memory;
INSERT INTO t1(b) VALUES('one'), ('two'), ('three');
#
# Create a file called MYSQLD_DATADIR/test/t1.ibd
# Directory listing of test/*.ibd
#
t1.ibd
ALTER TABLE t1 ENGINE = InnoDB;
ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
#
# Move the file to InnoDB as t2
#
ALTER TABLE t1 RENAME TO t2, ENGINE = INNODB;
SHOW CREATE TABLE t2;
Table Create Table
t2 CREATE TABLE `t2` (
`a` bigint unsigned NOT NULL AUTO_INCREMENT,
`b` char(10) DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT * from t2;
a b
1 one
2 two
3 three
#
# Try to rename t2 to t1 with an existing t1 in the way.
#
ALTER TABLE t2 RENAME TO t1;
ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
#
# Create another t1, but in the system tablespace.
#
SET GLOBAL innodb_file_per_table=OFF;
CREATE TABLE t1 (a SERIAL, b CHAR(20)) ENGINE=InnoDB;
INSERT INTO t1(b) VALUES('one'), ('two'), ('three');
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` bigint unsigned NOT NULL AUTO_INCREMENT,
`b` char(20) DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 System
#
# ALTER TABLE from system tablespace to system tablespace
#
ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INPLACE;
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=COPY;
#
# Try to move t1 from the system tablespace to a file-per-table
# while a blocking t1.ibd file exists.
#
SET GLOBAL innodb_file_per_table=ON;
#
# Move using TABLESPACE=innodb_file_per_table
#
SET GLOBAL innodb_file_per_table=OFF;
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
ERROR HY000: Tablespace 'test/t1' exists.
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
#
# ALTER TABLE t1 from system tablespace to general tablespace
#
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';
ALTER TABLE t1 TABLESPACE s1;
#
# Try to move t1 from a general tablespace to a file-per-table
# while a blocking t1.ibd file exists.
#
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
ERROR HY000: Tablespace 'test/t1' exists.
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
#
# Delete the blocking file called MYSQLD_DATADIR/test/t1.ibd
# Move t1 to file-per-table using ALGORITHM=INPLACE with no blocking t1.ibd.
#
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE t1 ADD COLUMN e INT, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
Table Create Table
t1 CREATE TABLE `t1` (
`a` bigint unsigned NOT NULL AUTO_INCREMENT,
`b` char(20) DEFAULT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
`e` int DEFAULT NULL,
UNIQUE KEY `a` (`a`)
) /*!50100 TABLESPACE `s1` */ ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 General
SET GLOBAL innodb_file_per_table=OFF;
#
# Move t1 back to the system tablespace using ALGORITHM=INPLACE.
#
ALTER TABLE t1 TABLESPACE=innodb_system, ALGORITHM=INPLACE;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 System
#
# Move t1 to a general tablespace using ALGORITHM=INPLACE.
#
ALTER TABLE t1 TABLESPACE=s1, ALGORITHM=INPLACE;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 General
#
# Move t1 to a file-per-table tablespace using ALGORITHM=INPLACE.
#
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 Single
#
# Move t1 to a general tablespace using ALGORITHM=COPY.
#
ALTER TABLE t1 TABLESPACE=s1, ALGORITHM=COPY;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 General
#
# Move t1 back to the system tablespace using ALGORITHM=COPY.
#
ALTER TABLE t1 TABLESPACE=innodb_system, ALGORITHM=COPY;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 System
#
# Move t1 to a file-per-table tablespace using ALGORITHM=COPY.
#
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 Single
DROP TABLE t1;
#
# Rename t2.ibd to t1.ibd.
#
ALTER TABLE t2 RENAME TO t1;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
name space_type
test/t1 Single
SELECT * from t1;
a b
1 one
2 two
3 three
#
# Rename this file-per-table from test.t1 to test2.t2.
#
CREATE DATABASE test2;
ALTER TABLE test.t1 RENAME TO test2.t2;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test%';
name space_type
#
# Also try the RENAME TABLE syntax.
#
CREATE DATABASE test3;
RENAME TABLE test2.t2 TO test3.t3;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test%';
name space_type
#
# Cleanup
#
DROP TABLE test3.t3;
DROP TABLESPACE s1;
DROP DATABASE test2;
DROP DATABASE test3;
|
