mirror of
https://github.com/MariaDB/server.git
synced 2025-02-10 23:45:34 +01:00
9608773f75
This essentially reverts commit 4e89ec6692
and only disables InnoDB persistent statistics for tests where it is
desirable. By design, InnoDB persistent statistics will not be updated
except by ANALYZE TABLE or by STATS_AUTO_RECALC.
The internal transactions that update persistent InnoDB statistics
in background tasks (with innodb_stats_auto_recalc=ON) may cause
nondeterministic query plans or interfere with some tests that deal
with other InnoDB internals, such as the purge of transaction history.
477 lines
12 KiB
Text
477 lines
12 KiB
Text
# Not supported in embedded
|
|
--source include/not_embedded.inc
|
|
|
|
-- source include/innodb_page_size_small.inc
|
|
-- source include/have_sequence.inc
|
|
|
|
call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT\\.");
|
|
call mtr.add_suppression("InnoDB: Cannot save statistics for table `test`\\.`t1` because the \\.ibd file is missing");
|
|
|
|
SET SESSION innodb_strict_mode=1;
|
|
|
|
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 ROW_FORMAT=COMPRESSED ;
|
|
|
|
CREATE TEMPORARY TABLE t (b char(22),c varchar(255));
|
|
INSERT INTO t VALUES
|
|
('Apa', 'Filler........'),
|
|
('Banan', 'Filler........'), ('Cavalry', '..asdasdfaeraf'),
|
|
('Devotion', 'asdfuihknaskdf'), ('Evolution', 'lsjndofiabsoibeg');
|
|
|
|
INSERT INTO t1 (b,c) SELECT b,c FROM t,seq_1_to_128;
|
|
DROP TEMPORARY TABLE t;
|
|
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
|
|
--echo # Restarting server
|
|
-- source include/restart_mysqld.inc
|
|
--echo # Done restarting server
|
|
FLUSH TABLE t1 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
|
|
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
|
|
|
|
# restore session variable
|
|
SET SESSION innodb_strict_mode=1;
|
|
|
|
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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
|
|
|
|
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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
|
|
|
|
INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
|
|
|
|
INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
|
|
|
|
--list_files $MYSQLD_DATADIR/test
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
SELECT COUNT(*) FROM t1;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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
|
|
ROW_FORMAT=COMPRESSED;
|
|
|
|
INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
--list_files $MYSQLD_DATADIR/test
|
|
UNLOCK TABLES;
|
|
|
|
# Move the LSN forward
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX(c2)) ENGINE=InnoDB
|
|
ROW_FORMAT=COMPRESSED;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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.
|
|
let $KEY_BLOCK_SIZE=`SELECT CONCAT('KEY_BLOCK_SIZE=', FLOOR(@@innodb_page_size/1024))`;
|
|
--replace_result $KEY_BLOCK_SIZE KEY_BLOCK_SIZE=16
|
|
eval CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB
|
|
ROW_FORMAT=COMPRESSED $KEY_BLOCK_SIZE;
|
|
|
|
INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/include/innodb-util.pl";
|
|
ib_backup_tablespaces("test", "t1");
|
|
EOF
|
|
UNLOCK TABLES;
|
|
|
|
# Move the LSN forward
|
|
DROP TABLE t1;
|
|
|
|
--replace_result $KEY_BLOCK_SIZE KEY_BLOCK_SIZE=16
|
|
eval CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX x(c2)) ENGINE=InnoDB
|
|
ROW_FORMAT=COMPRESSED $KEY_BLOCK_SIZE;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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}/../innodb/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}/../innodb/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 SELECT seq,1 FROM seq_1_to_16;
|
|
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
|
|
ROW_FORMAT=COMPRESSED;
|
|
|
|
INSERT INTO t1 SELECT seq,1 FROM seq_1_to_16;
|
|
|
|
SHOW CREATE TABLE t1;
|
|
|
|
FLUSH TABLES t1 FOR EXPORT;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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
|
|
ROW_FORMAT=COMPRESSED;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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}/../innodb/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
|
|
ROW_FORMAT=COMPRESSED;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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}/../innodb/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
|
|
ROW_FORMAT=COMPRESSED;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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}/../innodb/include/innodb-util.pl";
|
|
ib_unlink_tablespace("test", "t1");
|
|
EOF
|
|
|
|
DROP TABLE t1;
|
|
|
|
# This should fail because KEY_BLOCK_SIZE is different
|
|
CREATE TABLE t1(
|
|
c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
c2 INT, INDEX idx(c2)) ENGINE=InnoDB
|
|
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/include/innodb-util.pl";
|
|
ib_discard_tablespaces("test", "t1");
|
|
ib_restore_tablespaces("test", "t1");
|
|
EOF
|
|
|
|
# This should fail because KEY_BLOCK_SIZE=1
|
|
# does not match the implicit KEY_BLOCK_SIZE of the exported table.
|
|
# Need better error message for following
|
|
--replace_regex /\(.*\)//
|
|
-- error ER_TABLE_SCHEMA_MISMATCH
|
|
ALTER TABLE t1 IMPORT TABLESPACE;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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=COMPRESSED;
|
|
|
|
ALTER TABLE t1 DISCARD TABLESPACE;
|
|
--error ER_TABLESPACE_DISCARDED
|
|
SELECT * FROM t1;
|
|
|
|
perl;
|
|
do "$ENV{MTR_SUITE_DIR}/../innodb/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}/../innodb/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");
|
|
|
|
# cleanup
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/t1.cfg
|
|
--remove_file $MYSQLTEST_VARDIR/tmp/t1.ibd
|