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(20) 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_uca1400_ai_ci
28 SELECT * from t2;
29 a b
30 1 one
31 2 two
32 3 three
33 ALTER TABLE t2 RENAME TO t1;
34 ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 "Tablespace already exists")
35 #
36 # Create another t1, but in the system tablespace.
37 #
38 SET GLOBAL innodb_file_per_table=OFF;
39 Warnings:
40 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
41 CREATE TABLE t1 (a SERIAL, b CHAR(20)) ENGINE=InnoDB;
42 INSERT INTO t1(b) VALUES('one'), ('two'), ('three');
43 SHOW CREATE TABLE t1;
44 Table Create Table
45 t1 CREATE TABLE `t1` (
46 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
47 `b` char(20) DEFAULT NULL,
48 UNIQUE KEY `a` (`a`)
49 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
50 SELECT name, space=0 FROM information_schema.innodb_sys_tables WHERE name = 'test/t1';
51 name space=0
52 test/t1 1
53 #
54 # ALTER TABLE from system tablespace to system tablespace
55 #
56 ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INPLACE;
57 ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=COPY;
58 #
59 # Try to move t1 from the system tablespace to a file-per-table
60 # while a blocking t1.ibd file exists.
61 #
62 SET GLOBAL innodb_file_per_table=ON;
63 Warnings:
64 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release
65 ALTER TABLE t1 FORCE, ALGORITHM=INPLACE;
66 ERROR HY000: Tablespace for table 'test/#sql-ib' exists. Please DISCARD the tablespace before IMPORT
67 ALTER TABLE t1 FORCE, ALGORITHM=COPY;
68 ERROR HY000: Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME' (errno: 184 "Tablespace already exists")
69 #
70 # Delete the blocking file called MYSQLD_DATADIR/test/t1.ibd
71 # Move t1 to file-per-table using ALGORITHM=INPLACE with no blocking t1.ibd.
72 #
73 ALTER TABLE t1 FORCE, ALGORITHM=INPLACE;
74 SHOW CREATE TABLE t1;
75 Table Create Table
76 t1 CREATE TABLE `t1` (
77 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
78 `b` char(20) DEFAULT NULL,
79 `c` int(11) DEFAULT NULL,
80 `d` int(11) DEFAULT NULL,
81 UNIQUE KEY `a` (`a`)
82 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
83 SELECT name, space=0 FROM information_schema.innodb_sys_tables WHERE name = 'test/t1';
84 name space=0
85 test/t1 0
86 DROP TABLE t1;
87 #
88 # Rename t2.ibd to t1.ibd.
89 #
90 ALTER TABLE t2 RENAME TO t1;
91 SELECT name, space=0 FROM information_schema.innodb_sys_tables WHERE name = 'test/t1';
92 name space=0
93 test/t1 0
94 SELECT * from t1;
95 a b
96 1 one
97 2 two
98 3 three
99 DROP TABLE t1;
100 Warnings:
101 Warning 1287 '@@innodb_file_per_table' is deprecated and will be removed in a future release