[ Avaa Bypassed ]

Upload:

Command:

hmhc3928@3.135.184.218: ~ $ 
--echo #
--echo # This test shows DISCARD/IMPORT of a remote tablespace.
--echo #

# Not supported in embedded
--source include/not_embedded.inc

-- source include/have_innodb.inc

--disable_query_log
# These values can change during the test
LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;

# This warning is expected in the log
call mtr.add_suppression("tablespace is set as discarded");

# Set up some variables
LET $MYSQL_DATA_DIR = `select @@datadir`;
LET $data_directory_clause = DATA DIRECTORY='$MYSQL_TMP_DIR/alt_dir';
--enable_query_log

SET default_storage_engine=InnoDB;

SET GLOBAL innodb_file_per_table=ON;

--disable_warnings
DROP TABLE IF EXISTS t5980;
--enable_warnings

--echo #
--echo # CREATE TABLE ... DATA DIRECTORY
--echo # combined with  WL#5522 - Transportable Tablespace
--echo # Create the tablespace in MYSQL_TMP_DIR/alt_dir
--echo # InnoDB will create the sub-directories if needed.
--echo # Test that DISCARD and IMPORT work correctly.
--echo #

--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
INSERT INTO t5980 VALUES (1, "Create the tablespace");
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Check that DATA DIRECTORY shows up in the SHOW CREATE TABLE  results.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980;


--echo #
--echo # Backup the cfg and ibd files.
--echo #
FLUSH TABLES t5980 FOR EXPORT;
SELECT * FROM t5980;
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak
UNLOCK TABLES;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Do some DDL and DML.
--echo #
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;
SELECT * FROM t5980;
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;

--echo #
--echo # Make a second backup of the cfg and ibd files.
--echo #
FLUSH TABLES t5980 FOR EXPORT;
SELECT * FROM t5980;
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2
UNLOCK TABLES;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # DROP the table and make sure all files except the backups are gone.
--echo #
DROP TABLE t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # CREATE the table again.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
INSERT INTO t5980 VALUES (1, "Create the tablespace a second time");
SELECT * FROM t5980;

--echo #
--echo # DISCARD existing tablespace so backed-up .ibd which can be imported/restored
--echo #
ALTER TABLE t5980 DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restore the second backup of cfg and ibd files.
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2 $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2 $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--echo "### files in MYSQL_TMP_DIR/alt_dir/test"
--list_files $MYSQL_TMP_DIR/alt_dir/test/

--echo #
--echo # Try to Import the second backup.  These backups have extra DDL and
--echo # do not match the current frm file.
--echo #
--error ER_TABLE_SCHEMA_MISMATCH
ALTER TABLE t5980 IMPORT TABLESPACE;
CHECK TABLE t5980;
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd 
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restore the first backup of cfg and ibd files.
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Import the tablespace and do some DDL and DML.
--echo #
ALTER TABLE t5980 IMPORT TABLESPACE;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
CHECK TABLE t5980;
SELECT COUNT(*) FROM t5980;
SELECT * FROM t5980;
INSERT INTO t5980 VALUES (2,'Inserted record after IMPORT');
SELECT * FROM t5980;
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;
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;

--echo #
--echo # Show that the system tables have this table in them correctly.
--echo #
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo #
--echo # Drop the imported table and show that the system tables are updated.
--echo #
DROP TABLE t5980;
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # CREATE the table a third time.
--echo #
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980 (a int KEY, b text) $data_directory_clause;
INSERT INTO t5980 VALUES (1, "Create the tablespace a third time");
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restart the server
--echo # This test makes sure that you can still execute the FLUSH TABLES command
--echo # after restarting the server and the tablespace can still be found.
--echo #
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
SELECT * FROM t5980;
FLUSH TABLES t5980 FOR EXPORT;
SELECT * FROM t5980;
UNLOCK TABLES;

--echo #
--echo # Restart the server again.  This test makes sure that you can
--echo # still DISCARD a remote table after restarting the server.
--echo #
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
ALTER TABLE t5980 DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restore the backup of *.ibd and *.cfg files
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak  $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak  $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Import the tablespace and check it out.
--echo #
ALTER TABLE t5980 IMPORT TABLESPACE;
SELECT * FROM t5980;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # DISCARD the tablespace again
--echo #
ALTER TABLE t5980 DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restart the engine while the tablespace is in the discarded state
--echo #
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
CHECK TABLE t5980;

--echo #
--echo # Relocate this discarded file to the default directory
--echo # instead of the remote directory it was discarded from.
--echo # Put cfg and idb files into the default directory.
--echo # Delete the isl file and the remote cfg file.
--echo # Restart the engine again.
--echo # The tablespace is still in the discarded state.
--echo #
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak  $MYSQL_DATA_DIR/test/t5980.ibd
--copy_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak  $MYSQL_DATA_DIR/test/t5980.cfg
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
--echo # Restarting ...
--source include/restart_mysqld.inc
SET GLOBAL innodb_file_per_table=ON;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
CHECK TABLE t5980;

--echo #
--echo # Try to import the tablespace.  It can only be imported from
--echo # the location it was discarded from.
--echo # The error message for 1810 (IO_READ_ERROR) refers to a local path
--echo # so do not display it.
--echo #
--disable_result_log
--error ER_NO_SUCH_TABLE
ALTER TABLE t5980 IMPORT TABLESPACE;
--enable_result_log
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980;
CHECK TABLE t5980;

--echo #
--echo # Restore the ibd and cfg files to the remote directory.
--echo # Delete the ibd and cfg files from the default directory.
--echo # The isl file is missing, but is no longer needed since the
--echo # remote location is in the data dictionary.
--echo # Import the tablespace and check it out.
--echo #
--copy_file $MYSQL_DATA_DIR/test/t5980.ibd  $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd
--copy_file $MYSQL_DATA_DIR/test/t5980.cfg  $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg
--remove_file $MYSQL_DATA_DIR/test/t5980.ibd
--remove_file $MYSQL_DATA_DIR/test/t5980.cfg
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test
ALTER TABLE t5980 IMPORT TABLESPACE;
INSERT INTO t5980 VALUES (2, "Insert this record after IMPORT");
SELECT * FROM t5980;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980;

--echo #
--echo # Show that the system tables have this table in them correctly.
--echo #
SELECT name,n_cols,file_format,row_format
       FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format
       FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

DROP TABLE t5980;
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.cfg.bak2
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980.ibd.bak2

--echo #
--echo # Create a local and remote tablespaces, discard two and make
--echo # the other two missing upon restart, and try some DDL and DML
--echo # on these discarded and missing tablespaces.
--echo #

SET GLOBAL innodb_file_per_table=ON;
CREATE TABLE t5980a (a int, b text) engine=InnoDB;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980b (a int, b text) engine=InnoDB $data_directory_clause;
CREATE TABLE t5980c (a int, b text) engine=InnoDB;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
eval CREATE TABLE t5980d (a int, b text) engine=InnoDB $data_directory_clause;

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;
SELECT * FROM t5980b;
SELECT * FROM t5980c;
SELECT * FROM t5980d;

SHOW CREATE TABLE t5980a;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980b;
SHOW CREATE TABLE t5980c;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Shutdown the server, remove two tablespaces, restart server.
--echo #
--source include/shutdown_mysqld.inc
--remove_file $MYSQL_DATA_DIR/test/t5980c.ibd
--remove_file $MYSQL_DATA_DIR/test/t5980d.isl
--remove_file $MYSQL_TMP_DIR/alt_dir/test/t5980d.ibd
--source include/start_mysqld.inc

FLUSH TABLES t5980a, t5980b FOR EXPORT;
UNLOCK TABLES;

ALTER TABLE t5980a DISCARD TABLESPACE;
ALTER TABLE t5980b DISCARD TABLESPACE;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980c;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980d;

SHOW CREATE TABLE t5980a;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980b;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980c;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Discarded and missing tablespaces cannot be TRUNCATED
--echo #

--error ER_TABLESPACE_DISCARDED
TRUNCATE TABLE t5980a;
--error ER_TABLESPACE_DISCARDED
TRUNCATE TABLE t5980b;
--error ER_NO_SUCH_TABLE
TRUNCATE TABLE t5980c;
--error ER_NO_SUCH_TABLE
TRUNCATE TABLE t5980d;

--echo #
--echo # Discarded tablespaces can be RENAMED but they remain discarded
--echo #

RENAME TABLE t5980a TO t5980aa;
RENAME TABLE t5980b TO t5980bb;

--echo #
--echo # Missing tablespaces cannot be RENAMED
--echo #

--error ER_ERROR_ON_RENAME
RENAME TABLE t5980c TO t5980cc;
--error ER_ERROR_ON_RENAME
RENAME TABLE t5980d TO t5980dd;

--error ER_NO_SUCH_TABLE
SELECT * FROM t5980a;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980b;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980aa;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980bb;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980c;
--error ER_NO_SUCH_TABLE
SELECT * FROM t5980d;

SHOW CREATE TABLE t5980aa;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980bb;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980c;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t5980d;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo #
--echo # Discarded tablespaces cannot be ALTERED with ALGORITHM=COPY.
--echo #

--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=COPY;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=COPY;

--echo #
--echo # Discarded tablespaces can be ALTERED with ALGORITHM=INPLACE.
--echo #

# NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
# And adding a PRIMARY KEY will also add NOT NULL implicitly!
SET @old_sql_mode = @@sql_mode;
SET @@sql_mode = 'STRICT_TRANS_TABLES';
ALTER TABLE t5980aa ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
ALTER TABLE t5980bb ADD PRIMARY KEY(a), ALGORITHM=INPLACE;
SET @@sql_mode = @old_sql_mode;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Missing tablespaces cannot be ALTERED.
--echo #

--error ER_NO_SUCH_TABLE
ALTER TABLE t5980c ADD PRIMARY KEY(a);
--error ER_NO_SUCH_TABLE
ALTER TABLE t5980d ADD PRIMARY KEY(a);

--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980aa;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980bb;

SHOW CREATE TABLE t5980aa;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980bb;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Restart the server to check if the discarded flag is persistent
--echo #
--source include/shutdown_mysqld.inc
--source include/start_mysqld.inc

--echo #
--echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
--echo #

--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980aa VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980bb VALUES (1, "Inserted into Discarded Local tablespace after ALTER ADD PRIMARY KEY, ALGORITHM=INPLACE");

--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980aa;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980bb;

RENAME TABLE t5980aa TO t5980a;
RENAME TABLE t5980bb TO t5980b;

SHOW CREATE TABLE t5980a;
--replace_result $MYSQL_TMP_DIR MYSQL_TMP_DIR
SHOW CREATE TABLE t5980b;

SELECT name,n_cols,file_format,row_format FROM information_schema.innodb_sys_tables
       WHERE name LIKE 'test%' ORDER BY name;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Discard tablespaces again and try another ALTER TABLE ROW_FORMAT.
--echo #

ALTER TABLE t5980a DISCARD TABLESPACE;
ALTER TABLE t5980b DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;

--echo #
--echo # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
--echo #

--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=COPY;

--echo #
--echo # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
--echo #

ALTER TABLE t5980a ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE t5980b ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;

--echo #
--echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
--echo #

--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE");
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Discard tablespaces again and try ALTER TABLE ADD COLUMN.
--echo #

ALTER TABLE t5980a DISCARD TABLESPACE;
ALTER TABLE t5980b DISCARD TABLESPACE;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;

--echo #
--echo # ALTER TABLE ALGORITHM=COPY cannot use a discarded tablespace.
--echo #

--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=COPY;
--error ER_TABLESPACE_DISCARDED
ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=COPY;

--echo #
--echo # ALTER TABLE ALGORITHM=INPLACE can use a discarded tablespace.
--echo #

ALTER TABLE t5980a ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;
ALTER TABLE t5980b ADD COLUMN c CHAR(20), ALGORITHM=INPLACE;

--echo #
--echo # Discarded tablespaces that were ALTERED IN_PLACE are still discarded.
--echo #

--error ER_TABLESPACE_DISCARDED
DELETE FROM t5980a;
--error ER_TABLESPACE_DISCARDED
UPDATE t5980a SET c="Tablespace is DISCARDED";
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980a VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column");
--error ER_TABLESPACE_DISCARDED
INSERT INTO t5980b VALUES (1, "Inserted into discarded local tablespace after ALTER ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE", "new column");
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980a;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t5980b;
--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $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;
SELECT name,file_format,row_format FROM information_schema.innodb_sys_tablespaces
       WHERE name LIKE 'test%' ORDER BY space;
--replace_result ./ MYSQL_DATA_DIR/  $MYSQL_DATA_DIR MYSQL_DATA_DIR  $MYSQL_TMP_DIR MYSQL_TMP_DIR
SELECT path FROM information_schema.innodb_sys_datafiles
       WHERE path LIKE '%test%' ORDER BY space;

--echo ### files in MYSQL_DATA_DIR/test
--list_files $MYSQL_DATA_DIR/test
--echo ### files in MYSQL_TMP_DIR/alt_dir/test
--list_files $MYSQL_TMP_DIR/alt_dir/test

--echo #
--echo # Cleanup
--echo #

--rmdir $MYSQL_TMP_DIR/alt_dir/test
--rmdir $MYSQL_TMP_DIR/alt_dir

-- disable_query_log
eval set global innodb_file_per_table=$innodb_file_per_table_orig;
call mtr.add_suppression("InnoDB: Could not find a valid tablespace file for");
call mtr.add_suppression("InnoDB: Tablespace open failed for");
call mtr.add_suppression("InnoDB: Failed to find tablespace for table ");
call mtr.add_suppression("InnoDB: Table test/t5980.* does not have an .ibd file in the database directory.");
call mtr.add_suppression("Trying to import a tablespace, but could not open the tablespace file");
call mtr.add_suppression("Cannot delete tablespace .+ because it is not found in the tablespace memory cache");
call mtr.add_suppression("Cannot delete tablespace .+ in DISCARD TABLESPACE. Tablespace not found");
call mtr.add_suppression("doesn't have a corresponding tablespace, it was discarded.");
-- enable_query_log

Filemanager

DIR : / usr/ share/ mysql-test/ suite/ innodb/ t/
Name Type Size Permission Actions
add_foreign_key.test File 1.35 KB 0644
alter_rename_existing.test File 3.02 KB 0644
analyze_table.test File 803 B 0644
autoinc_debug.test File 2.14 KB 0644
blob-update-debug.test File 525 B 0644
blob_redo-master.opt File 57 B 0644
blob_redo.test File 2.35 KB 0644
bulk_create_index_online.test File 1.15 KB 0644
checksum.test File 644 B 0644
create-index.test File 1.29 KB 0644
create_isl_with_direct-master.opt File 31 B 0644
create_isl_with_direct.test File 1010 B 0644
disabled.def File 404 B 0644
dropdb.test File 251 B 0644
end_range_check.test File 1.82 KB 0644
events-merge-tmp-path-master.opt File 149 B 0644
events-merge-tmp-path.test File 1.65 KB 0644
flush-hang.test File 1.51 KB 0644
foreign_key.test File 2.18 KB 0644
ibuf_not_empty-master.opt File 30 B 0644
ibuf_not_empty.test File 3.2 KB 0644
import.test File 763 B 0644
import_tablespace_schema_missmatch.test File 3.6 KB 0644
import_update_stats.test File 1.46 KB 0644
index_length.test File 1.31 KB 0644
index_tree_operation.test File 2.36 KB 0644
innodb-2byte-collation-master.opt File 48 B 0644
innodb-2byte-collation.test File 2.95 KB 0644
innodb-ac-non-locking-select.test File 3 KB 0644
innodb-alter-autoinc.test File 2.63 KB 0644
innodb-alter-debug.test File 2.69 KB 0644
innodb-alter-discard.test File 1.74 KB 0644
innodb-alter-nullable.test File 1.98 KB 0644
innodb-alter-tempfile.test File 2.46 KB 0644
innodb-alter.test File 15.98 KB 0644
innodb-analyze.test File 1.65 KB 0644
innodb-autoinc-18274.test File 661 B 0644
innodb-autoinc-44030-master.opt File 81 B 0644
innodb-autoinc-44030.test File 1.61 KB 0644
innodb-autoinc-56228-master.opt File 29 B 0644
innodb-autoinc-56228.test File 784 B 0644
innodb-autoinc-master.opt File 81 B 0644
innodb-autoinc-optimize.test File 482 B 0644
innodb-autoinc.test File 25.87 KB 0644
innodb-blob.test File 6.14 KB 0644
innodb-bug-14068765.test File 3.41 KB 0644
innodb-bug-14084530.test File 1.31 KB 0644
innodb-bug12552164.test File 1.43 KB 0644
innodb-bug14219515.test File 472 B 0644
innodb-change-buffer-recovery-master.opt File 62 B 0644
innodb-change-buffer-recovery.test File 2.27 KB 0644
innodb-consistent-master.opt File 29 B 0644
innodb-consistent.test File 1.24 KB 0644
innodb-double-write.test File 11.92 KB 0644
innodb-index-debug-master.opt File 30 B 0644
innodb-index-debug.test File 3.14 KB 0644
innodb-index-online-delete.test File 1.06 KB 0644
innodb-index-online-fk.test File 14.48 KB 0644
innodb-index-online-master.opt File 128 B 0644
innodb-index-online-norebuild.test File 1.8 KB 0644
innodb-index-online-purge.test File 2.07 KB 0644
innodb-index-online.test File 13.08 KB 0644
innodb-index.test File 16.93 KB 0644
innodb-index_ucs2.test File 124 B 0644
innodb-lock-inherit-read_commited.test File 2.58 KB 0644
innodb-lock.test File 4.27 KB 0644
innodb-log-file-size-1.test File 10.53 KB 0644
innodb-log-file-size.test File 7.33 KB 0644
innodb-master.opt File 136 B 0644
innodb-multiple-tablespaces.test File 18.88 KB 0644
innodb-read-view.test File 3.63 KB 0644
innodb-replace.test File 555 B 0644
innodb-semi-consistent-master.opt File 29 B 0644
innodb-semi-consistent.test File 1.83 KB 0644
innodb-status-output.test File 3.23 KB 0644
innodb-system-table-view.test File 6.46 KB 0644
innodb-table-online-master.opt File 128 B 0644
innodb-table-online.test File 11.35 KB 0644
innodb-tablespace.test File 16.16 KB 0644
innodb-timeout.test File 3.63 KB 0644
innodb-truncate.test File 2.03 KB 0644
innodb-ucs2.test File 11.41 KB 0644
innodb-update-insert.test File 860 B 0644
innodb-use-sys-malloc-master.opt File 56 B 0644
innodb-use-sys-malloc.test File 1.52 KB 0644
innodb-wl5522-1.test File 27.19 KB 0644
innodb-wl5522-debug.test File 35.31 KB 0644
innodb-wl5522.test File 19.19 KB 0644
innodb-wl5980-alter.test File 18.11 KB 0644
innodb-wl5980-debug.test File 2.58 KB 0644
innodb-wl5980-discard.test File 25.47 KB 0644
innodb-wl5980-linux.test File 7.54 KB 0644
innodb-wl5980-windows.test File 8.83 KB 0644
innodb-wl6445-1.test File 24.83 KB 0644
innodb-wl6445-2.test File 9.04 KB 0644
innodb-wl6445.test File 2.3 KB 0644
innodb.test File 79.03 KB 0644
innodb_autoinc_lock_mode_zero-master.opt File 29 B 0644
innodb_autoinc_lock_mode_zero.test File 1.1 KB 0644
innodb_autoinc_reset.test File 519 B 0644
innodb_blob_unrecoverable_crash.test File 1.56 KB 0644
innodb_buffer_pool_load-master.opt File 30 B 0644
innodb_buffer_pool_load.test File 3.94 KB 0644
innodb_bug-13628249.test File 3.95 KB 0644
innodb_bug11754376.test File 406 B 0644
innodb_bug11766634-master.opt File 16 B 0644
innodb_bug11766634.test File 1.82 KB 0644
innodb_bug11789106.test File 536 B 0644
innodb_bug11933790.test File 969 B 0644
innodb_bug12400341-master.opt File 75 B 0644
innodb_bug12400341.test File 2.57 KB 0644
innodb_bug12429573.test File 1.4 KB 0644
innodb_bug12661768.test File 1.99 KB 0644
innodb_bug13635833.test File 1.6 KB 0644
innodb_bug13867871.test File 5.7 KB 0644
innodb_bug14006907.test File 1.38 KB 0644
innodb_bug14007109.test File 1.19 KB 0644
innodb_bug14007649.test File 1.1 KB 0644
innodb_bug14147491-master.opt File 62 B 0644
innodb_bug14147491.test File 3.28 KB 0644
innodb_bug14169459.test File 1.96 KB 0644
innodb_bug14676111.test File 3.84 KB 0644
innodb_bug14704286.test File 2.03 KB 0644
innodb_bug21704.test File 1.66 KB 0644
innodb_bug30423.test File 6.51 KB 0644
innodb_bug30919-master.opt File 38 B 0644
innodb_bug30919.test File 2.27 KB 0644
innodb_bug34053.test File 1.41 KB 0644
innodb_bug34300.test File 852 B 0644
innodb_bug35220.test File 416 B 0644
innodb_bug38231.test File 1.64 KB 0644
innodb_bug39438-master.opt File 26 B 0644
innodb_bug39438.test File 2.19 KB 0644
innodb_bug40360.test File 340 B 0644
innodb_bug40565.test File 327 B 0644
innodb_bug41904.test File 328 B 0644
innodb_bug42101-nonzero-master.opt File 30 B 0644
innodb_bug42101-nonzero.test File 653 B 0644
innodb_bug42101.test File 577 B 0644
innodb_bug42419.test File 2.19 KB 0644
innodb_bug44032.test File 543 B 0644
innodb_bug44369.test File 530 B 0644
innodb_bug44571.test File 788 B 0644
innodb_bug45357.test File 326 B 0644
innodb_bug46000.test File 941 B 0644
innodb_bug46676.test File 602 B 0644
innodb_bug47167-master.opt File 16 B 0644
innodb_bug47167.test File 1.41 KB 0644
innodb_bug47621.test File 1.79 KB 0644
innodb_bug47622.test File 1.58 KB 0644
innodb_bug47777.test File 915 B 0644
innodb_bug48024.test File 863 B 0644
innodb_bug49164.test File 928 B 0644
innodb_bug51378.test File 2.53 KB 0644
innodb_bug51920.test File 988 B 0644
innodb_bug52199.test File 210 B 0644
innodb_bug52663.test File 1003 B 0644
innodb_bug53046.test File 1.27 KB 0644
innodb_bug53290.test File 909 B 0644
innodb_bug53592.test File 2.47 KB 0644
innodb_bug53674-master.opt File 65 B 0644
innodb_bug53674.test File 259 B 0644
innodb_bug53756-master.opt File 36 B 0644
innodb_bug53756.test File 4.62 KB 0644
innodb_bug54044.test File 606 B 0644
innodb_bug56143.test File 74.16 KB 0644
innodb_bug56716.test File 268 B 0644
innodb_bug56947.test File 472 B 0644
innodb_bug57252.test File 1.27 KB 0644
innodb_bug57255.test File 889 B 0644
innodb_bug57904.test File 908 B 0644
innodb_bug59307.test File 626 B 0644
innodb_bug59410.test File 805 B 0644
innodb_bug59641.test File 1.7 KB 0644
innodb_bug59733.test File 1.98 KB 0644
innodb_bug60049-master.opt File 25 B 0644
innodb_bug60049.test File 1.95 KB 0644
innodb_bug60196-master.opt File 27 B 0644
innodb_bug60196.test File 4.44 KB 0644
innodb_bug60229.test File 1.34 KB 0644
innodb_bug70867.test File 1.64 KB 0644
innodb_copy_col_in_partition.test File 665 B 0644
innodb_corrupt_bit.test File 4.21 KB 0644
innodb_ctype_ldml-master.opt File 80 B 0644
innodb_ctype_ldml.test File 15.32 KB 0644
innodb_deadlock_with_autoinc-master.opt File 29 B 0644
innodb_deadlock_with_autoinc.test File 1.22 KB 0644
innodb_file_format-master.opt File 16 B 0644
innodb_file_format.test File 1.32 KB 0644
innodb_file_limit_check-master.opt File 69 B 0644
innodb_file_limit_check.test File 1.07 KB 0644
innodb_force_recovery.test File 5.7 KB 0644
innodb_gis.test File 277 B 0644
innodb_i_s_innodb_locks.test File 4.54 KB 0644
innodb_i_s_innodb_trx.test File 2.32 KB 0644
innodb_information_schema_buffer.test File 2.39 KB 0644
innodb_io_pf.test File 398 B 0644
innodb_lock_wait_timeout_1-master.opt File 29 B 0644
innodb_lock_wait_timeout_1.test File 8.06 KB 0644
innodb_misc1-master.opt File 55 B 0644
innodb_misc1.test File 31.49 KB 0644
innodb_multi_update.test File 1.4 KB 0644
innodb_mysql-master.opt File 82 B 0644
innodb_mysql.test File 26.75 KB 0644
innodb_mysql_rbk-master.opt File 60 B 0644
innodb_mysql_rbk.test File 849 B 0644
innodb_notembedded.test File 1.13 KB 0644
innodb_page_size_func.test File 12.92 KB 0644
innodb_prefix_index_restart_server.test File 4.4 KB 0644
innodb_replace.test File 5.06 KB 0644
innodb_stats.test File 1.89 KB 0644
innodb_stats_auto_recalc.test File 1.76 KB 0644
innodb_stats_auto_recalc_ddl.test File 1.65 KB 0644
innodb_stats_auto_recalc_lots.test File 887 B 0644
innodb_stats_auto_recalc_on_nonexistent.test File 1.87 KB 0644
innodb_stats_create_on_corrupted.test File 1.27 KB 0644
innodb_stats_create_table.test File 1.43 KB 0644
innodb_stats_del_mark-master.opt File 40 B 0644
innodb_stats_del_mark.test File 3.6 KB 0644
innodb_stats_drop_locked.test File 1.41 KB 0644
innodb_stats_external_pages.test File 2.58 KB 0644
innodb_stats_fetch.test File 2.13 KB 0644
innodb_stats_fetch_corrupted.test File 1.6 KB 0644
innodb_stats_fetch_nonexistent.test File 1.14 KB 0644
innodb_stats_flag_global_off-master.opt File 28 B 0644
innodb_stats_flag_global_off.test File 324 B 0644
innodb_stats_flag_global_on-master.opt File 28 B 0644
innodb_stats_flag_global_on.test File 323 B 0644
innodb_stats_rename_table.test File 1.25 KB 0644
innodb_stats_rename_table_if_exists.test File 1.43 KB 0644
innodb_stats_sample_pages.test File 1.72 KB 0644
innodb_stats_table_flag_auto_recalc.test File 2.19 KB 0644
innodb_stats_table_flag_sample_pages.test File 2.73 KB 0644
innodb_sys_var_valgrind.test File 1.99 KB 0644
innodb_timeout_rollback-master.opt File 58 B 0644
innodb_timeout_rollback.test File 108 B 0644
innodb_trx_weight.test File 3.65 KB 0644
innodb_upd_stats_if_needed_not_inited.test File 1.04 KB 0644
innodb_ut_format_name.test File 298 B 0644
insert_debug.test File 476 B 0644
monitor.test File 13.96 KB 0644
monitor_debug.test File 1.07 KB 0644
portability_wl5980_linux.zip File 473.02 KB 0644
portability_wl5980_windows.zip File 514.24 KB 0644
sp_temp_table.test File 2.51 KB 0644
strict_checksum.test File 2.77 KB 0644
strict_mode.test File 3.22 KB 0644
timestamp.test File 787 B 0644
tmpdir.test File 1.9 KB 0644
undo_space_id.test File 889 B 0644
xa_recovery.test File 1.13 KB 0644