# # This test shows DISCARD/IMPORT of a remote tablespace. # SET default_storage_engine=InnoDB; SET GLOBAL innodb_file_per_table=ON; DROP TABLE IF EXISTS t5980; # # CREATE TABLE ... DATA DIRECTORY # combined with WL#5522 - Transportable Tablespace # Create the tablespace in MYSQL_TMP_DIR/alt_dir # InnoDB will create the sub-directories if needed. # Test that DISCARD and IMPORT work correctly. # CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; INSERT INTO t5980 VALUES (1, "Create the tablespace"); SELECT * FROM t5980; a b 1 Create the tablespace ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.ibd # # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE results. # SHOW CREATE TABLE t5980; Table Create Table t5980 CREATE TABLE `t5980` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' # # Backup the cfg and ibd files. # FLUSH TABLES t5980 FOR EXPORT; SELECT * FROM t5980; a b 1 Create the tablespace UNLOCK TABLES; ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.ibd t5980.ibd.bak # # Do some DDL and DML. # INSERT INTO t5980 VALUES (2,'Remote table has been FLUSHed and UNLOCKed'); START TRANSACTION; INSERT INTO t5980 VALUES (12,'Transactional record inserted'); COMMIT; START TRANSACTION; INSERT INTO t5980 VALUES (13,'Rollback this transactional record'); ROLLBACK; SELECT COUNT(*) FROM t5980; COUNT(*) 3 SELECT * FROM t5980; a b 1 Create the tablespace 2 Remote table has been FLUSHed and UNLOCKed 12 Transactional record inserted ALTER TABLE t5980 DROP PRIMARY KEY; ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL; INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added'); SELECT * FROM t5980; a b c 1 Create the tablespace NULL 2 Remote table has been FLUSHed and UNLOCKed NULL 12 Transactional record inserted NULL 2 Duplicate value since primary key has been dropped third column added # # Make a second backup of the cfg and ibd files. # FLUSH TABLES t5980 FOR EXPORT; SELECT * FROM t5980; a b c 1 Create the tablespace NULL 2 Remote table has been FLUSHed and UNLOCKed NULL 12 Transactional record inserted NULL 2 Duplicate value since primary key has been dropped third column added UNLOCK TABLES; ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 # # DROP the table and make sure all files except the backups are gone. # DROP TABLE t5980; ### files in MYSQL_DATA_DIR/test ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # # CREATE the table again. # CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; INSERT INTO t5980 VALUES (1, "Create the tablespace a second time"); SELECT * FROM t5980; a b 1 Create the tablespace a second time # # DISCARD existing tablespace so backed-up .ibd which can be imported/restored # ALTER TABLE t5980 DISCARD TABLESPACE; SELECT * FROM t5980; ERROR HY000: Tablespace has been discarded for table 't5980' ### files in MYSQL_DATA_DIR/test t5980.frm ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # # Restore the second backup of cfg and ibd files. # "### files in MYSQL_TMP_DIR/alt_dir/test" t5980.cfg t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 # # Try to Import the second backup. These backups have extra DDL and # do not match the current frm file. # ALTER TABLE t5980 IMPORT TABLESPACE; ERROR HY000: Schema mismatch (Number of columns don't match, table has 5 columns but the tablespace meta-data file has 6 columns) CHECK TABLE t5980; Table Op Msg_type Msg_text test.t5980 check Error Tablespace has been discarded for table 't5980' test.t5980 check error Corrupt ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # # Restore the first backup of cfg and ibd files. # ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 # # Import the tablespace and do some DDL and DML. # ALTER TABLE t5980 IMPORT TABLESPACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980' ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 CHECK TABLE t5980; Table Op Msg_type Msg_text test.t5980 check status OK SELECT COUNT(*) FROM t5980; COUNT(*) 1 SELECT * FROM t5980; a b 1 Create the tablespace INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT'); SELECT * FROM t5980; a b 1 Create the tablespace 2 Inserted record after IMPORT START TRANSACTION; INSERT INTO t5980 VALUES (12,'Transactional record inserted'); COMMIT; START TRANSACTION; INSERT INTO t5980 VALUES (13,'Rollback this transactional record'); ROLLBACK; SELECT * FROM t5980; a b 1 Create the tablespace 2 Inserted record after IMPORT 12 Transactional record inserted ALTER TABLE t5980 DROP PRIMARY KEY; ALTER TABLE t5980 ADD COLUMN c VARCHAR(50) DEFAULT NULL; INSERT INTO t5980(a,b,c) VALUES (2,'Duplicate value since primary key has been dropped','third column added'); SELECT * FROM t5980; a b c 1 Create the tablespace NULL 2 Inserted record after IMPORT NULL 12 Transactional record inserted NULL 2 Duplicate value since primary key has been dropped third column added # # Show that the system tables have this table in them correctly. # SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980 6 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980 Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_TMP_DIR/alt_dir/test/t5980.ibd # # Drop the imported table and show that the system tables are updated. # DROP TABLE t5980; SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path ### files in MYSQL_DATA_DIR/test ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # # CREATE the table a third time. # CREATE TABLE t5980 (a int KEY, b text) DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; INSERT INTO t5980 VALUES (1, "Create the tablespace a third time"); SELECT * FROM t5980; a b 1 Create the tablespace a third time ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 # # Restart the server # This test makes sure that you can still execute the FLUSH TABLES command # after restarting the server and the tablespace can still be found. # SET GLOBAL innodb_file_per_table=ON; ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 SELECT * FROM t5980; a b 1 Create the tablespace a third time FLUSH TABLES t5980 FOR EXPORT; SELECT * FROM t5980; a b 1 Create the tablespace a third time UNLOCK TABLES; # # Restart the server again. This test makes sure that you can # still DISCARD a remote table after restarting the server. # SET GLOBAL innodb_file_per_table=ON; SELECT * FROM t5980; a b 1 Create the tablespace a third time ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 ALTER TABLE t5980 DISCARD TABLESPACE; SELECT * FROM t5980; ERROR HY000: Tablespace has been discarded for table 't5980' ### files in MYSQL_DATA_DIR/test t5980.frm ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # # Restore the backup of *.ibd and *.cfg files # ### files in MYSQL_DATA_DIR/test t5980.frm ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 # # Import the tablespace and check it out. # ALTER TABLE t5980 IMPORT TABLESPACE; SELECT * FROM t5980; a b 1 Create the tablespace SHOW CREATE TABLE t5980; Table Create Table t5980 CREATE TABLE `t5980` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' ### files in MYSQL_DATA_DIR/test t5980.frm t5980.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 # # DISCARD the tablespace again # ALTER TABLE t5980 DISCARD TABLESPACE; SELECT * FROM t5980; ERROR HY000: Tablespace has been discarded for table 't5980' ### files in MYSQL_DATA_DIR/test t5980.frm ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # # Restart the engine while the tablespace is in the discarded state # SET GLOBAL innodb_file_per_table=ON; SELECT * FROM t5980; ERROR HY000: Tablespace has been discarded for table 't5980' CHECK TABLE t5980; Table Op Msg_type Msg_text test.t5980 check Error Tablespace has been discarded for table 't5980' test.t5980 check error Corrupt # # Relocate this discarded file to the default directory # instead of the remote directory it was discarded from. # Put cfg and idb files into the default directory. # Delete the isl file and the remote cfg file. # Restart the engine again. # The tablespace is still in the discarded state. # ### files in MYSQL_DATA_DIR/test t5980.cfg t5980.frm t5980.ibd ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg.bak t5980.cfg.bak2 t5980.ibd.bak t5980.ibd.bak2 # Restarting ... SET GLOBAL innodb_file_per_table=ON; SELECT * FROM t5980; ERROR HY000: Tablespace has been discarded for table 't5980' CHECK TABLE t5980; Table Op Msg_type Msg_text test.t5980 check Error Tablespace has been discarded for table 't5980' test.t5980 check error Corrupt # # Try to import the tablespace. It can only be imported from # the location it was discarded from. # The error message for 1810 (IO_READ_ERROR) refers to a local path # so do not display it. # ALTER TABLE t5980 IMPORT TABLESPACE; SELECT * FROM t5980; ERROR HY000: Tablespace has been discarded for table 't5980' CHECK TABLE t5980; Table Op Msg_type Msg_text test.t5980 check Error Tablespace has been discarded for table 't5980' test.t5980 check error Corrupt # # Restore the ibd and cfg files to the remote directory. # Delete the ibd and cfg files from the default directory. # The isl file is missing, but is no longer needed since the # remote location is in the data dictionary. # Import the tablespace and check it out. # ### files in MYSQL_DATA_DIR/test t5980.frm ### files in MYSQL_TMP_DIR/alt_dir/test t5980.cfg t5980.cfg.bak t5980.cfg.bak2 t5980.ibd t5980.ibd.bak t5980.ibd.bak2 ALTER TABLE t5980 IMPORT TABLESPACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980' INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT"); SELECT * FROM t5980; a b 1 Create the tablespace 2 Insert this record after IMPORT SHOW CREATE TABLE t5980; Table Create Table t5980 CREATE TABLE `t5980` ( `a` int(11) NOT NULL, `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' # # Show that the system tables have this table in them correctly. # SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980 5 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980 Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_TMP_DIR/alt_dir/test/t5980.ibd DROP TABLE t5980; # # Create a local and remote tablespaces, discard two and make # the other two missing upon restart, and try some DDL and DML # on these discarded and missing tablespaces. # SET GLOBAL innodb_file_per_table=ON; CREATE TABLE t5980a (a int, b text) engine=InnoDB; CREATE TABLE t5980b (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; CREATE TABLE t5980c (a int, b text) engine=InnoDB; CREATE TABLE t5980d (a int, b text) engine=InnoDB DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir'; INSERT INTO t5980a VALUES (1, "Default location, discarded."); INSERT INTO t5980b VALUES (1, "Remote location, discarded"); INSERT INTO t5980c VALUES (1, "Default location, missing"); INSERT INTO t5980d VALUES (1, "Remote location, missing"); SELECT * FROM t5980a; a b 1 Default location, discarded. SELECT * FROM t5980b; a b 1 Remote location, discarded SELECT * FROM t5980c; a b 1 Default location, missing SELECT * FROM t5980d; a b 1 Remote location, missing SHOW CREATE TABLE t5980a; Table Create Table t5980a CREATE TABLE `t5980a` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t5980b; Table Create Table t5980b CREATE TABLE `t5980b` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' SHOW CREATE TABLE t5980c; Table Create Table t5980c CREATE TABLE `t5980c` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t5980d; Table Create Table t5980d CREATE TABLE `t5980d` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980a 5 Antelope Compact test/t5980b 5 Antelope Compact test/t5980c 5 Antelope Compact test/t5980d 5 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980a Antelope Compact or Redundant test/t5980b Antelope Compact or Redundant test/t5980c Antelope Compact or Redundant test/t5980d Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_DATA_DIR/test/t5980a.ibd MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd MYSQL_DATA_DIR/test/t5980c.ibd MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd ### files in MYSQL_DATA_DIR/test t5980a.frm t5980a.ibd t5980b.frm t5980b.isl t5980c.frm t5980c.ibd t5980d.frm t5980d.isl ### files in MYSQL_TMP_DIR/alt_dir/test t5980b.ibd t5980d.ibd # # Shutdown the server, remove two tablespaces, restart server. # FLUSH TABLES t5980a, t5980b FOR EXPORT; UNLOCK TABLES; ALTER TABLE t5980a DISCARD TABLESPACE; ALTER TABLE t5980b DISCARD TABLESPACE; ### files in MYSQL_DATA_DIR/test t5980a.frm t5980b.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test SELECT * FROM t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' SELECT * FROM t5980b; ERROR HY000: Tablespace has been discarded for table 't5980b' SELECT * FROM t5980c; ERROR 42S02: Table 'test.t5980c' doesn't exist SELECT * FROM t5980d; ERROR 42S02: Table 'test.t5980d' doesn't exist SHOW CREATE TABLE t5980a; Table Create Table t5980a CREATE TABLE `t5980a` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t5980b; Table Create Table t5980b CREATE TABLE `t5980b` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' SHOW CREATE TABLE t5980c; ERROR 42S02: Table 'test.t5980c' doesn't exist SHOW CREATE TABLE t5980d; ERROR 42S02: Table 'test.t5980d' doesn't exist SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980a 5 Antelope Compact test/t5980b 5 Antelope Compact test/t5980c 5 Antelope Compact test/t5980d 5 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980a Antelope Compact or Redundant test/t5980b Antelope Compact or Redundant test/t5980c Antelope Compact or Redundant test/t5980d Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_DATA_DIR/test/t5980a.ibd MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd MYSQL_DATA_DIR/test/t5980c.ibd MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd ### files in MYSQL_DATA_DIR/test t5980a.frm t5980b.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test # # Discarded and missing tablespaces cannot be TRUNCATED # TRUNCATE TABLE t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' TRUNCATE TABLE t5980b; ERROR HY000: Tablespace has been discarded for table 't5980b' TRUNCATE TABLE t5980c; ERROR 42S02: Table 'test.t5980c' doesn't exist TRUNCATE TABLE t5980d; ERROR 42S02: Table 'test.t5980d' doesn't exist # # Discarded tablespaces can be RENAMED but they remain discarded # RENAME TABLE t5980a TO t5980aa; RENAME TABLE t5980b TO t5980bb; # # Missing tablespaces cannot be RENAMED # RENAME TABLE t5980c TO t5980cc; ERROR HY000: Error on rename of './test/t5980c' to './test/t5980cc' (errno: 155 - The table does not exist in engine) RENAME TABLE t5980d TO t5980dd; ERROR HY000: Error on rename of './test/t5980d' to './test/t5980dd' (errno: 155 - The table does not exist in engine) SELECT * FROM t5980a; ERROR 42S02: Table 'test.t5980a' doesn't exist SELECT * FROM t5980b; ERROR 42S02: Table 'test.t5980b' doesn't exist SELECT * FROM t5980aa; ERROR HY000: Tablespace has been discarded for table 't5980aa' SELECT * FROM t5980bb; ERROR HY000: Tablespace has been discarded for table 't5980bb' SELECT * FROM t5980c; ERROR 42S02: Table 'test.t5980c' doesn't exist SELECT * FROM t5980d; ERROR 42S02: Table 'test.t5980d' doesn't exist SHOW CREATE TABLE t5980aa; Table Create Table t5980aa CREATE TABLE `t5980aa` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t5980bb; Table Create Table t5980bb CREATE TABLE `t5980bb` ( `a` int(11) DEFAULT NULL, `b` text ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' SHOW CREATE TABLE t5980c; ERROR 42S02: Table 'test.t5980c' doesn't exist SHOW CREATE TABLE t5980d; ERROR 42S02: Table 'test.t5980d' doesn't exist SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980aa 5 Antelope Compact test/t5980bb 5 Antelope Compact test/t5980c 5 Antelope Compact test/t5980d 5 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980a Antelope Compact or Redundant test/t5980b Antelope Compact or Redundant test/t5980c Antelope Compact or Redundant test/t5980d Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_DATA_DIR/test/t5980a.ibd MYSQL_TMP_DIR/alt_dir/test/t5980b.ibd MYSQL_DATA_DIR/test/t5980c.ibd MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd # # Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY. # ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY; ERROR HY000: Tablespace has been discarded for table 't5980aa' ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY; ERROR HY000: Tablespace has been discarded for table 't5980bb' # # Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE. # SET @old_sql_mode = @@sql_mode; SET @@sql_mode = 'STRICT_TRANS_TABLES'; ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980aa' ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980bb' SET @@sql_mode = @old_sql_mode; ### files in MYSQL_DATA_DIR/test t5980aa.frm t5980bb.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test # # Missing tablespaces cannot be ALTERED. # ALTER TABLE t5980c ADD PRIMARY KEY(a); ERROR 42S02: Table 'test.t5980c' doesn't exist ALTER TABLE t5980d ADD PRIMARY KEY(a); ERROR 42S02: Table 'test.t5980d' doesn't exist SELECT * FROM t5980aa; ERROR HY000: Tablespace has been discarded for table 't5980aa' SELECT * FROM t5980bb; ERROR HY000: Tablespace has been discarded for table 't5980bb' SHOW CREATE TABLE t5980aa; Table Create Table t5980aa CREATE TABLE `t5980aa` ( `a` int(11) NOT NULL DEFAULT '0', `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SHOW CREATE TABLE t5980bb; Table Create Table t5980bb CREATE TABLE `t5980bb` ( `a` int(11) NOT NULL DEFAULT '0', `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980aa 5 Antelope Compact test/t5980bb 5 Antelope Compact test/t5980c 5 Antelope Compact test/t5980d 5 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980c Antelope Compact or Redundant test/t5980d Antelope Compact or Redundant test/t5980aa Antelope Compact or Redundant test/t5980bb Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_DATA_DIR/test/t5980c.ibd MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd MYSQL_DATA_DIR/test/t5980aa.ibd MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd ### files in MYSQL_DATA_DIR/test t5980aa.frm t5980bb.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test # # Restart the server to check if the discarded flag is persistent # # # Discarded tablespaces that were ALTERED IN_PLACE are still discarded. # INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE"); ERROR HY000: Tablespace has been discarded for table 't5980aa' INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE"); ERROR HY000: Tablespace has been discarded for table 't5980bb' SELECT * FROM t5980aa; ERROR HY000: Tablespace has been discarded for table 't5980aa' SELECT * FROM t5980bb; ERROR HY000: Tablespace has been discarded for table 't5980bb' RENAME TABLE t5980aa TO t5980a; RENAME TABLE t5980bb TO t5980b; SHOW CREATE TABLE t5980a; Table Create Table t5980a CREATE TABLE `t5980a` ( `a` int(11) NOT NULL DEFAULT '0', `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a' SHOW CREATE TABLE t5980b; Table Create Table t5980b CREATE TABLE `t5980b` ( `a` int(11) NOT NULL DEFAULT '0', `b` text, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 DATA DIRECTORY='MYSQL_TMP_DIR/alt_dir/' Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b' SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format test/t5980a 5 Antelope Compact test/t5980b 5 Antelope Compact test/t5980c 5 Antelope Compact test/t5980d 5 Antelope Compact SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format test/t5980c Antelope Compact or Redundant test/t5980d Antelope Compact or Redundant test/t5980aa Antelope Compact or Redundant test/t5980bb Antelope Compact or Redundant SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path MYSQL_DATA_DIR/test/t5980c.ibd MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd MYSQL_DATA_DIR/test/t5980aa.ibd MYSQL_TMP_DIR/alt_dir/test/t5980bb.ibd ### files in MYSQL_DATA_DIR/test t5980a.frm t5980b.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test # # Discard tablespaces again and try another ALTER TABLE ROW_FORMAT. # ALTER TABLE t5980a DISCARD TABLESPACE; Warnings: Warning 1812 InnoDB: Tablespace is missing for table 't5980a' ALTER TABLE t5980b DISCARD TABLESPACE; Warnings: Warning 1812 InnoDB: Tablespace is missing for table 't5980b' SELECT * FROM t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' SELECT * FROM t5980b; ERROR HY000: Tablespace has been discarded for table 't5980b' # # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace. # ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY; ERROR HY000: Tablespace has been discarded for table 't5980a' ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY; ERROR HY000: Tablespace has been discarded for table 't5980b' # # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace. # ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a' ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b' # # Discarded tablespaces that were ALTERED IN_PLACE are still discarded. # INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE"); ERROR HY000: Tablespace has been discarded for table 't5980a' INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE"); ERROR HY000: Tablespace has been discarded for table 't5980b' SELECT * FROM t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' SELECT * FROM t5980b; ERROR HY000: Tablespace has been discarded for table 't5980b' ### files in MYSQL_DATA_DIR/test t5980a.frm t5980b.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test # # Discard tablespaces again and try ALTER TABLE ADD COLUMN. # ALTER TABLE t5980a DISCARD TABLESPACE; Warnings: Warning 1812 InnoDB: Tablespace is missing for table 't5980a' ALTER TABLE t5980b DISCARD TABLESPACE; Warnings: Warning 1812 InnoDB: Tablespace is missing for table 't5980b' SELECT * FROM t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' SELECT * FROM t5980b; ERROR HY000: Tablespace has been discarded for table 't5980b' # # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace. # ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY; ERROR HY000: Tablespace has been discarded for table 't5980a' ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY; ERROR HY000: Tablespace has been discarded for table 't5980b' # # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace. # ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980a' ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE; Warnings: Warning 1814 InnoDB: Tablespace has been discarded for table 't5980b' # # Discarded tablespaces that were ALTERED IN_PLACE are still discarded. # DELETE FROM t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' UPDATE t5980a SET c="Tablespace is DISCARDED"; ERROR HY000: Tablespace has been discarded for table 't5980a' INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column"); ERROR HY000: Tablespace has been discarded for table 't5980a' INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column"); ERROR HY000: Tablespace has been discarded for table 't5980b' SELECT * FROM t5980a; ERROR HY000: Tablespace has been discarded for table 't5980a' SELECT * FROM t5980b; ERROR HY000: Tablespace has been discarded for table 't5980b' ### files in MYSQL_DATA_DIR/test t5980a.frm t5980b.frm t5980c.frm t5980d.frm ### files in MYSQL_TMP_DIR/alt_dir/test DROP TABLE t5980a; DROP TABLE t5980b; DROP TABLE t5980c; DROP TABLE t5980d; SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables WHERE name LIKE 'test%' ORDER BY name; name n_cols file_format row_format SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces WHERE name LIKE 'test%' ORDER BY space; name file_format row_format SELECT path FROM information_schema.innodb_sys_datafiles WHERE path LIKE '%test%' ORDER BY space; path ### files in MYSQL_DATA_DIR/test ### files in MYSQL_TMP_DIR/alt_dir/test # # Cleanup #