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
|
--echo #
--echo # Show what happens during ALTER TABLE when an existing file
--echo # exists in the target location.
--echo #
--echo # Bug #19218794: IF TABLESPACE EXISTS, CAN'T CREATE TABLE,
--echo # BUT CAN ALTER ENGINE=INNODB
--echo #
--disable_query_log
LET $MYSQLD_DATADIR = `select @@datadir`;
SET @old_innodb_file_per_table = @@innodb_file_per_table;
--enable_query_log
CREATE TABLE t1 (a SERIAL, b CHAR(10)) ENGINE=Memory;
INSERT INTO t1(b) VALUES('one'), ('two'), ('three');
--echo #
--echo # Create a file called MYSQLD_DATADIR/test/t1.ibd
--exec echo "This is not t1.ibd" > $MYSQLD_DATADIR/test/t1.ibd
--echo # Directory listing of test/*.ibd
--echo #
--list_files $MYSQLD_DATADIR/test/ *.ibd
--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
--error ER_ERROR_ON_RENAME
ALTER TABLE t1 ENGINE = InnoDB;
--echo #
--echo # Move the file to InnoDB as t2
--echo #
ALTER TABLE t1 RENAME TO t2, ENGINE = INNODB;
SHOW CREATE TABLE t2;
SELECT * from t2;
--echo #
--echo # Try to rename t2 to t1 with an existing t1 in the way.
--echo #
--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
--error ER_ERROR_ON_RENAME
ALTER TABLE t2 RENAME TO t1;
--echo #
--echo # Create another t1, but in the system tablespace.
--echo #
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;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
--echo #
--echo # ALTER TABLE from system tablespace to system tablespace
--echo #
ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INPLACE;
ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=COPY;
--echo #
--echo # Try to move t1 from the system tablespace to a file-per-table
--echo # while a blocking t1.ibd file exists.
--echo #
SET GLOBAL innodb_file_per_table=ON;
--echo #
--echo # Move using TABLESPACE=innodb_file_per_table
--echo #
SET GLOBAL innodb_file_per_table=OFF;
--replace_regex /$MYSQLD_DATADIR/MYSQLD_DATADIR/
--error ER_TABLESPACE_EXISTS
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
--error ER_ERROR_ON_RENAME
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
--echo #
--echo # ALTER TABLE t1 from system tablespace to general tablespace
--echo #
CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';
ALTER TABLE t1 TABLESPACE s1;
--echo #
--echo # Try to move t1 from a general tablespace to a file-per-table
--echo # while a blocking t1.ibd file exists.
--echo #
--replace_regex /$MYSQLD_DATADIR/MYSQLD_DATADIR/
--error ER_TABLESPACE_EXISTS
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/
--error ER_ERROR_ON_RENAME
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
--echo #
--echo # Delete the blocking file called MYSQLD_DATADIR/test/t1.ibd
--remove_file $MYSQLD_DATADIR/test/t1.ibd
--echo # Move t1 to file-per-table using ALGORITHM=INPLACE with no blocking t1.ibd.
--echo #
SET GLOBAL innodb_file_per_table=ON;
ALTER TABLE t1 ADD COLUMN e INT, ALGORITHM=INPLACE;
SHOW CREATE TABLE t1;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
SET GLOBAL innodb_file_per_table=OFF;
--echo #
--echo # Move t1 back to the system tablespace using ALGORITHM=INPLACE.
--echo #
ALTER TABLE t1 TABLESPACE=innodb_system, ALGORITHM=INPLACE;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
--echo #
--echo # Move t1 to a general tablespace using ALGORITHM=INPLACE.
--echo #
ALTER TABLE t1 TABLESPACE=s1, ALGORITHM=INPLACE;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
--echo #
--echo # Move t1 to a file-per-table tablespace using ALGORITHM=INPLACE.
--echo #
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
--echo #
--echo # Move t1 to a general tablespace using ALGORITHM=COPY.
--echo #
ALTER TABLE t1 TABLESPACE=s1, ALGORITHM=COPY;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
--echo #
--echo # Move t1 back to the system tablespace using ALGORITHM=COPY.
--echo #
ALTER TABLE t1 TABLESPACE=innodb_system, ALGORITHM=COPY;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
--echo #
--echo # Move t1 to a file-per-table tablespace using ALGORITHM=COPY.
--echo #
ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
DROP TABLE t1;
--echo #
--echo # Rename t2.ibd to t1.ibd.
--echo #
ALTER TABLE t2 RENAME TO t1;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test/t1';
SELECT * from t1;
--echo #
--echo # Rename this file-per-table from test.t1 to test2.t2.
--echo #
CREATE DATABASE test2;
ALTER TABLE test.t1 RENAME TO test2.t2;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test%';
--echo #
--echo # Also try the RENAME TABLE syntax.
--echo #
CREATE DATABASE test3;
RENAME TABLE test2.t2 TO test3.t3;
SELECT name, space_type FROM information_schema.innodb_tables WHERE name = 'test%';
--echo #
--echo # Cleanup
--echo #
DROP TABLE test3.t3;
DROP TABLESPACE s1;
DROP DATABASE test2;
DROP DATABASE test3;
--disable_query_log
call mtr.add_suppression("\\[ERROR\\] .*MY-\\d+.* Cannot rename '.*' to '.*' for space ID .* because the target file exists. Remove the target file and try again");
SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table;
--enable_query_log
|
