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--source include/have_innodb.inc 10 11--disable_query_log 12LET $MYSQLD_DATADIR = `select @@datadir`; 13SET @old_innodb_file_per_table = @@innodb_file_per_table; 14--enable_query_log 15 16CREATE TABLE t1 (a SERIAL, b CHAR(10)) ENGINE=Memory; 17INSERT INTO t1(b) VALUES('one'), ('two'), ('three'); 18 19--echo # 20--echo # Create a file called MYSQLD_DATADIR/test/t1.ibd 21--exec echo "This is not t1.ibd" > $MYSQLD_DATADIR/test/t1.ibd 22 23--echo # Directory listing of test/*.ibd 24--echo # 25--list_files $MYSQLD_DATADIR/test/ *.ibd 26 27--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ 28--error ER_ERROR_ON_RENAME 29ALTER TABLE t1 ENGINE = InnoDB; 30 31--echo # 32--echo # Move the file to InnoDB as t2 33--echo # 34ALTER TABLE t1 RENAME TO t2, ENGINE = INNODB; 35SHOW CREATE TABLE t2; 36SELECT * from t2; 37 38--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ 39--error ER_ERROR_ON_RENAME 40ALTER TABLE t2 RENAME TO t1; 41 42--echo # 43--echo # Create another t1, but in the system tablespace. 44--echo # 45SET GLOBAL innodb_file_per_table=OFF; 46CREATE TABLE t1 (a SERIAL, b CHAR(20)) ENGINE=InnoDB; 47INSERT INTO t1(b) VALUES('one'), ('two'), ('three'); 48SHOW CREATE TABLE t1; 49SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 50 51--echo # 52--echo # ALTER TABLE from system tablespace to system tablespace 53--echo # 54ALTER TABLE t1 ADD COLUMN c INT, ALGORITHM=INPLACE; 55ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=COPY; 56 57--echo # 58--echo # Try to move t1 from the system tablespace to a file-per-table 59--echo # while a blocking t1.ibd file exists. 60--echo # 61SET GLOBAL innodb_file_per_table=ON; 62--replace_regex /$MYSQLD_DATADIR/MYSQLD_DATADIR/ 63--error ER_TABLESPACE_EXISTS 64ALTER TABLE t1 ADD COLUMN e1 INT, ALGORITHM=INPLACE; 65--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ 66--error ER_ERROR_ON_RENAME 67ALTER TABLE t1 ADD COLUMN e2 INT, ALGORITHM=COPY; 68 69SET GLOBAL innodb_file_per_table=OFF; 70--replace_regex /$MYSQLD_DATADIR/MYSQLD_DATADIR/ 71--error ER_TABLESPACE_EXISTS 72ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE; 73--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ 74--error ER_ERROR_ON_RENAME 75ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY; 76 77--echo # 78--echo # ALTER TABLE from system tablespace to general tablespace 79--echo # 80CREATE TABLESPACE s1 ADD DATAFILE 's1.ibd'; 81ALTER TABLE t1 TABLESPACE s1; 82 83--echo # 84--echo # Try to move t1 from a general tablespace to a file-per-table 85--echo # while a blocking t1.ibd file exists. 86--echo # 87--replace_regex /$MYSQLD_DATADIR/MYSQLD_DATADIR/ 88--error ER_TABLESPACE_EXISTS 89ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE; 90--replace_regex /Error on rename of '.*' to '.*'/Error on rename of 'OLD_FILE_NAME' to 'NEW_FILE_NAME'/ 91--error ER_ERROR_ON_RENAME 92ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY; 93 94 95--echo # 96--echo # Delete the blocking file called MYSQLD_DATADIR/test/t1.ibd 97--remove_file $MYSQLD_DATADIR/test/t1.ibd 98 99--echo # Move t1 to file-per-table using ALGORITHM=INPLACE with no blocking t1.ibd. 100--echo # 101SET GLOBAL innodb_file_per_table=ON; 102ALTER TABLE t1 ADD COLUMN e INT, ALGORITHM=INPLACE; 103SHOW CREATE TABLE t1; 104SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 105SET GLOBAL innodb_file_per_table=OFF; 106 107--echo # 108--echo # Move t1 back to the system tablespace using ALGORITHM=INPLACE. 109--echo # 110ALTER TABLE t1 TABLESPACE=innodb_system, ALGORITHM=INPLACE; 111SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 112 113--echo # 114--echo # Move t1 to a general tablespace using ALGORITHM=INPLACE. 115--echo # 116ALTER TABLE t1 TABLESPACE=s1, ALGORITHM=INPLACE; 117SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 118 119--echo # 120--echo # Move t1 to a file-per-table tablespace using ALGORITHM=INPLACE. 121--echo # 122ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=INPLACE; 123SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 124 125--echo # 126--echo # Move t1 to a general tablespace using ALGORITHM=COPY. 127--echo # 128ALTER TABLE t1 TABLESPACE=s1, ALGORITHM=COPY; 129SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 130 131--echo # 132--echo # Move t1 back to the system tablespace using ALGORITHM=COPY. 133--echo # 134ALTER TABLE t1 TABLESPACE=innodb_system, ALGORITHM=COPY; 135SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 136 137--echo # 138--echo # Move t1 to a file-per-table tablespace using ALGORITHM=COPY. 139--echo # 140ALTER TABLE t1 TABLESPACE=innodb_file_per_table, ALGORITHM=COPY; 141SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 142 143DROP TABLE t1; 144 145--echo # 146--echo # Rename t2.ibd to t1.ibd. 147--echo # 148ALTER TABLE t2 RENAME TO t1; 149SELECT name, space_type FROM information_schema.innodb_sys_tables WHERE name = 'test/t1'; 150SELECT * from t1; 151 152DROP TABLE t1; 153DROP TABLESPACE s1; 154 155--disable_query_log 156call mtr.add_suppression("\\[ERROR\\] InnoDB: Cannot rename '.*' to '.*' for space ID .* because the target file exists. Remove the target file and try again"); 157SET GLOBAL innodb_file_per_table = @old_innodb_file_per_table; 158--enable_query_log 159