Debian Codegraph @

mysql-test/suite/innodb/r/alter_rename_existing.result

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