mirror of
https://github.com/MariaDB/server.git
synced 2025-07-05 02:48:14 +02:00
78153cf641
to tables in the system tablespace This is a regression caused by MDEV-11585, which accidentally changed Tablespace::is_undo_tablespace() in an incorrect way, causing the InnoDB system tablespace to be reported as a dedicated undo tablespace, for which the change buffer is not applicable. Tablespace::is_undo_tablespace(): Remove. There were only 2 calls from the function buf_page_io_complete(). Replace those calls as appropriate. Also, merge changes to tablespace import/export tests from MySQL 5.7, and clean up the tests a little further, allowing them to be run with any innodb_page_size. Remove duplicated error injection instrumentation for the import/export tests. In MySQL 5.7, the error injection label buf_page_is_corrupt_failure was renamed to buf_page_import_corrupt_failure. fil_space_extend_must_retry(): Correct a debug assertion (tablespaces can be extended during IMPORT), and remove a TODO comment about compressed temporary tables that was already addressed in MDEV-11816. dict_build_tablespace_for_table(): Correct a comment that no longer holds after MDEV-11816, and assert that ROW_FORMAT=COMPRESSED can only be used in .ibd files.
887 lines
20 KiB
Text
887 lines
20 KiB
Text
# Not supported in embedded
|
|
--source include/not_embedded.inc
|
|
|
|
-- source include/have_innodb.inc
|
|
|
|
call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
|
|
call mtr.add_suppression("Index for table 't2' is corrupt; try to repair it");
|
|
FLUSH TABLES;
|
|
|
|
let $MYSQLD_TMPDIR = `SELECT @@tmpdir`;
|
|
let $MYSQLD_DATADIR = `SELECT @@datadir`;
|
|
|
|
CREATE TABLE t1
|
|
(a INT AUTO_INCREMENT PRIMARY KEY,
|
|
b char(22),
|
|
c varchar(255),
|
|
KEY (b))
|
|
ENGINE = InnoDB;
|
|
|
|
insert into t1 (b, c) values ('Apa', 'Filler........'),
|
|
('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
|
|
('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
|
|
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
SELECT COUNT(*) FROM t1;
|
|
SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
|
|
SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
|
|
|
|
CREATE TABLE t2(a INT PRIMARY KEY) ENGINE=InnoDB ROW_FORMAT=COMPACT;
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--source include/restart_mysqld.inc
|
|
FLUSH TABLE t1, t2 FOR EXPORT;
|
|
--echo # List before copying files
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_TMPDIR/t1.cfg
|
|
--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_TMPDIR/t1.ibd
|
|
--move_file $MYSQLD_DATADIR/test/t2.cfg $MYSQLD_TMPDIR/t2.cfg
|
|
--copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_TMPDIR/t2.ibd
|
|
UNLOCK TABLES;
|
|
INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a;
|
|
SELECT COUNT(*) FROM t1;
|
|
SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
|
|
SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
|
|
--echo # Restarting server
|
|
-- source include/restart_mysqld.inc
|
|
--echo # Done restarting server
|
|
--echo # List before t1 DISCARD
|
|
--list_files $MYSQLD_DATADIR/test
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--echo # List after t1 DISCARD
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--copy_file $MYSQLD_TMPDIR/t1.cfg $MYSQLD_DATADIR/test/t1.cfg
|
|
--copy_file $MYSQLD_TMPDIR/t1.ibd $MYSQLD_DATADIR/test/t1.ibd
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
ALTER TABLE t1 ENGINE InnoDB;
|
|
SELECT COUNT(*) FROM t1;
|
|
SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
|
|
SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
|
|
--list_files $MYSQLD_DATADIR/test
|
|
SELECT COUNT(*) FROM t1;
|
|
SELECT * FROM t1 ORDER BY b,a DESC LIMIT 3;
|
|
SELECT * FROM t1 ORDER BY a DESC LIMIT 3;
|
|
DROP TABLE t1;
|
|
--remove_file $MYSQLD_TMPDIR/t1.cfg
|
|
--remove_file $MYSQLD_TMPDIR/t1.ibd
|
|
|
|
ALTER TABLE t2 ROW_FORMAT=DYNAMIC;
|
|
ALTER TABLE t2 DISCARD TABLESPACE;
|
|
--echo # List after t2 DISCARD
|
|
--list_files $MYSQLD_DATADIR/test
|
|
--move_file $MYSQLD_TMPDIR/t2.ibd $MYSQLD_DATADIR/test/t2.ibd
|
|
--move_file $MYSQLD_TMPDIR/t2.cfg $MYSQLD_DATADIR/test/t2.cfg
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
--remove_file $MYSQLD_DATADIR/test/t2.cfg
|
|
--replace_regex /(FSP_SPACE_FLAGS=0x)[0-9a-f]+(,.*0x)[0-9a-f]+(.*)/\1*\2*\3/
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t2 IMPORT TABLESPACE;
|
|
DROP TABLE t2;
|
|
--remove_file $MYSQLD_DATADIR/test/t2.ibd
|
|
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
SELECT @@innodb_file_per_table;
|
|
|
|
let MYSQLD_DATADIR =`SELECT @@datadir`;
|
|
|
|
# Try importing when tablespace already exists
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
--error ER_TABLESPACE_EXISTS
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
SELECT * FROM t1;
|
|
DROP TABLE t1;
|
|
|
|
# Export/import on the same instance, with --innodb-file-per-table=1
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
SELECT COUNT(*) FROM t1;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
UNLOCK TABLES;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Export/import on the same instance, with --innodb-file-per-table=1
|
|
# Insert some more records to move the LSN forward and then drop the
|
|
# table and restore
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
SELECT COUNT(*) FROM t1;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
--list_files $MYSQLD_DATADIR/test
|
|
UNLOCK TABLES;
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
# Move the LSN forward
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Export/import on the same instance, with --innodb-file-per-table=1
|
|
# Insert some more records to move the LSN forward and then drop the
|
|
# table and restore, this time the table has a secondary index too.
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX(c2)) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
SELECT COUNT(*) FROM t1 WHERE c2 = 1;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
--list_files $MYSQLD_DATADIR/test
|
|
UNLOCK TABLES;
|
|
|
|
# Move the LSN forward
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX(c2)) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
SELECT COUNT(*) FROM t1 WHERE c2 = 1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Export/import on the same instance, with --innodb-file-per-table=1
|
|
# Insert some more records to move the LSN forward and then drop the
|
|
# table and restore, this time the table has a secondary index too.
|
|
# Rename the index on the create so that the IMPORT fails, drop index
|
|
# Create with proper name and then do an IMPORT.
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
SELECT COUNT(*) FROM t1 WHERE c2 = 1;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
UNLOCK TABLES;
|
|
|
|
# Move the LSN forward
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX x(c2)) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
# This is really a name mismatch error, need better error codes.
|
|
-- error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
ALTER TABLE t1 DROP INDEX x;
|
|
ALTER TABLE t1 ADD INDEX idx(c2);
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Export/import on the same instance, with --innodb-file-per-table=0
|
|
# This should fail because it is not supported
|
|
SET GLOBAL innodb_file_per_table = 0;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
SELECT COUNT(*) FROM t1;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
|
|
# This should fail, InnoDB should return a warning
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
|
|
UNLOCK TABLES;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Tests that check for schema mismatch during IMPORT
|
|
#
|
|
|
|
SET GLOBAL innodb_file_per_table = 1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
UNLOCK TABLES;
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Table without the secondary index
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
# This should fail because of a missing secondary index
|
|
-- error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Table with an additional column
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT,
|
|
c3 INT, INDEX idx(c2)) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
# This should fail because the table has an additional column
|
|
-- error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# Change the column type of c2
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 BIGINT, INDEX idx(c2)) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
# This should fail because c2 is now a BIGINT and not INT
|
|
-- error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# This should be OK.
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Row format tests [EXPORT REDUNDANT - IMPORT COMPACT & DYNAMIC]
|
|
#
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
UNLOCK TABLES;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# This should be OK.
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Row format tests [EXPORT COMPACT - IMPORT REDUNDANT & DYNAMIC]
|
|
#
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
UNLOCK TABLES;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# This should be OK.
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
#
|
|
# Row format tests [EXPORT DYNAMIC- IMPORT REDUNDANT & DYNAMIC]
|
|
#
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
|
|
INSERT INTO t1(c2) VALUES(1);
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
INSERT INTO t1(c2) SELECT c2 FROM t1;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
UNLOCK TABLES;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
--error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# This should be OK.
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
CHECK TABLE t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl";
|
|
ib_cleanup("test", "t1");
|
|
EOF
|
|
|
|
SHOW CREATE TABLE t1;
|
|
SELECT * FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
call mtr.add_suppression("Got error -1 when reading table '.*'");
|
|
call mtr.add_suppression("InnoDB: Error: tablespace id and flags in file '.*'.*");
|
|
call mtr.add_suppression("InnoDB: The table .* doesn't have a corresponding tablespace, it was discarded");
|
|
FLUSH TABLES;
|
|
|
|
# cleanup
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
|