Debian Codegraph @

mysql-test/suite/innodb/t/alter_rename_existing.test

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