Debian Codegraph @

mysql-test/suite/innodb/r/alter_rename_existing_xtra.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 read-only 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 ALTER TABLE t1 ENGINE = InnoDB;
18 ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine
19 #
20 # Move the file to InnoDB as t2
21 #
22 ALTER TABLE t1 RENAME TO t2, ENGINE = INNODB;
23 SHOW CREATE TABLE t2;
24 Table Create Table
25 t2 CREATE TABLE `t2` (
26 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
27 `b` char(10) DEFAULT NULL,
28 UNIQUE KEY `a` (`a`)
29 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
30 SELECT * from t2;
31 a b
32 1 one
33 2 two
34 3 three
35 #
36 # Try to rename t2 to t1 with an existing read-only t1 in the way.
37 #
38 ALTER TABLE t2 RENAME TO t1;
39 ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
40 #
41 # Try again but now with a read-only 'test' directory.
42 #
43 ALTER TABLE t2 RENAME TO t1;
44 ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
45 #
46 # Create another t1, but in the system tablespace.
47 #
48 SET GLOBAL innodb_file_per_table=OFF;
49 CREATE TABLE t1 (a SERIAL, b CHAR(20)) ENGINE=InnoDB;
50 INSERT INTO t1(b) VALUES('one'), ('two'), ('three');
51 SHOW CREATE TABLE t1;
52 Table Create Table
53 t1 CREATE TABLE `t1` (
54 `a` bigint unsigned NOT NULL AUTO_INCREMENT,
55 `b` char(20) DEFAULT NULL,
56 UNIQUE KEY `a` (`a`)
57 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
58 SELECT name, space=0 FROM information_schema.innodb_tables WHERE name = 'test/t1';
59 name space=0
60 test/t1 1
61 #
62 # Try to move t1 from the system tablespace to a file-per-table
63 # while a blocking read-only t1.ibd file exists.
64 #
65 # Move using innodb_file_per_table=ON
66 #
67 SET GLOBAL innodb_file_per_table=ON;
68 #
69 # Move using using innodb_file_per_table=ON and a read-only directory
70 #
71 ALTER TABLE t1 ADD COLUMN e1 INT, ALGORITHM=INPLACE;
72 ALTER TABLE t1 ADD COLUMN e2 INT, ALGORITHM=COPY;
73 #
74 # Move using TABLESPACE=innodb_file_per_table
75 #
76 SET GLOBAL innodb_file_per_table=OFF;
77 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
78 ERROR HY000: Got error 11 - 'InnoDB error' from storage engine
79 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
80 ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine
81 #
82 # Move using TABLESPACE=innodb_file_per_table with a read-only directory
83 #
84 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
85 ERROR HY000: Got error 11 - 'InnoDB error' from storage engine
86 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
87 ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine
88 #
89 # ALTER TABLE t1 from system tablespace to general tablespace
90 #
91 CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd';
92 ALTER TABLE t1 TABLESPACE s1;
93 #
94 # Try to move t1 from a general tablespace to a file-per-table
95 # while a blocking read-only t1.ibd file exists.
96 #
97 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
98 ERROR HY000: Tablespace 'test/t1' exists.
99 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
100 ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 - Tablespace already exists)
101 #
102 # Try again with a read-only 'test' directory.
103 #
104 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE;
105 ERROR HY000: Got error 11 - 'InnoDB error' from storage engine
106 ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY;
107 ERROR HY000: Got error 168 - 'Unknown (generic) error from engine' from storage engine
108 #
109 # Cleanup
110 #
111 DROP TABLE t1, t2;
112 DROP TABLESPACE s1;