| Server IP : 172.67.216.182 / Your IP : 104.23.175.121 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/innodb/t/ |
Upload File : |
# Not supported in embedded
--source include/not_embedded.inc
--source include/no_valgrind_without_big.inc
--source include/have_innodb.inc
--source include/have_partition.inc
let $partitioning= 1;
--source suite/innodb/include/innodb_import.inc
let MYSQLD_DATADIR =`SELECT @@datadir`;
--echo #
--echo # DISCARD/IMPORT selected partitions only
--echo #
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY HASH (c1) PARTITIONS 3;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
SELECT c1 FROM t1 ORDER BY c1;
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
perl;
do 'include/innodb-util.inc';
ib_backup_tablespaces("test", "t1#P#p0", "t1#P#p1", "t1#P#p2");
EOF
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY HASH (c1) PARTITIONS 3;
INSERT INTO t1 VALUES (31), (32), (33);
ALTER TABLE t1 DISCARD PARTITION p0,p2 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#P#p0", "t1#P#p2");
ib_restore_tablespaces("test", "t1#P#p0", "t1#P#p2");
EOF
ALTER TABLE t1 IMPORT PARTITION p0, p2 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#P#p1");
ib_restore_tablespaces("test", "t1#P#p1");
EOF
ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
INSERT INTO t1 VALUES (31), (32), (33);
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#P#p0", "t1#P#p1", "t1#P#p2");
ib_restore_tablespaces("test", "t1#P#p0", "t1#P#p1", "t1#P#p2");
EOF
ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
INSERT INTO t1 PARTITION (p1) VALUES (61), (64);
FLUSH TABLES;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t1 ORDER BY c1;
--error ER_PARSE_ERROR
ALTER TABLE t1 DISCARD PARTITION TABLESPACE;
--error ER_PARSE_ERROR
ALTER TABLE t1 IMPORT PARTITION TABLESPACE;
--error ER_UNKNOWN_PARTITION
ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
--error ER_UNKNOWN_PARTITION
ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#P#p1");
ib_restore_tablespaces("test", "t1#P#p1");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
--echo #
--echo # DISCARD/IMPORT selected subpartitions only
--echo #
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6);
INSERT INTO t1 VALUES (101),(102),(103),(104),(105),(106);
SELECT c1 FROM t1 ORDER BY c1;
FLUSH TABLES t1 FOR EXPORT;
SELECT * FROM t1 ORDER BY c1;
perl;
do 'include/innodb-util.inc';
ib_backup_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
UNLOCK TABLES;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (31), (32), (33);
INSERT INTO t1 VALUES (131), (132), (133);
ALTER TABLE t1 DISCARD PARTITION p0sp1,p1sp0 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#P#p0#SP#p0sp1", "t1#P#p1#SP#p1sp0");
ib_restore_tablespaces("test", "t1#P#p0#SP#p0sp1", "t1#P#p1#SP#p1sp0");
EOF
ALTER TABLE t1 IMPORT PARTITION p0sp1, p1sp0 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1, p0sp2 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test",
"t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION p0sp2, p1 TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
INSERT INTO t1 VALUES (31), (32), (33);
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION p1sp2 TABLESPACE;
SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
INSERT INTO t1 PARTITION (p1sp2) VALUES (161), (164);
FLUSH TABLES;
SELECT * FROM t1 PARTITION (p1sp2) ORDER BY c1;
--error ER_TABLESPACE_DISCARDED
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1sp2 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test", "t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test", "t1#P#p1#SP#p1sp2");
EOF
--replace_regex /#p#/#P#/ /#sp#/#SP#/
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
--echo # Removing p0sp2's .ibd file
--remove_file $MYSQLD_DATADIR/test/t1#P#p0#SP#p0sp2.ibd
--error ER_INTERNAL_ERROR
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_restore_tablespaces("test", "t1#P#p0#SP#p0sp2");
EOF
--replace_regex /#p#/#P#/ /#sp#/#SP#/
--error ER_TABLESPACE_EXISTS
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
ALTER TABLE t1 IMPORT PARTITION p0sp2 TABLESPACE;
--replace_regex /#p#/#P#/ /#sp#/#SP#/
ALTER TABLE t1 DISCARD PARTITION all TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
ALTER TABLE t1 DISCARD PARTITION pNonExisting TABLESPACE;
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
ALTER TABLE t1 IMPORT PARTITION pNonExisting TABLESPACE;
--error ER_PARTITION_MGMT_ON_NONPARTITIONED
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
DROP TABLE t1;
--echo # discard partition one by one
CREATE TABLE t1 (c1 INT) ENGINE = InnoDB
PARTITION BY RANGE (c1)
SUBPARTITION BY HASH (c1) SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN MAXVALUE);
ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test","t1#P#p0#SP#p0sp0");
ib_restore_tablespaces("test","t1#P#p0#SP#p0sp0");
EOF
ALTER TABLE t1 IMPORT PARTITION p0sp0 TABLESPACE;
SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 REBUILD PARTITION p0;
SELECT * FROM t1 PARTITION (p0sp0) ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION p1 TABLESPACE;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
ALTER TABLE t1 OPTIMIZE PARTITION p1;
SELECT * FROM t1 PARTITION (p1) ORDER BY c1;
ALTER TABLE t1 CHECK PARTITION p1;
ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 REPAIR PARTITION p0sp0,p1;
SELECT * FROM t1 ORDER BY c1;
ALTER TABLE t1 DISCARD PARTITION p0sp0 TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p1 TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p0sp1 TABLESPACE;
ALTER TABLE t1 DISCARD PARTITION p0sp2 TABLESPACE;
perl;
do 'include/innodb-util.inc';
ib_discard_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
ib_restore_tablespaces("test",
"t1#P#p0#SP#p0sp0", "t1#P#p0#SP#p0sp1", "t1#P#p0#SP#p0sp2",
"t1#P#p1#SP#p1sp0", "t1#P#p1#SP#p1sp1", "t1#P#p1#SP#p1sp2");
EOF
ALTER TABLE t1 IMPORT PARTITION all TABLESPACE;
SELECT * FROM t1 ORDER BY c1;
DROP TABLE t1;
--disable_query_log
call mtr.add_suppression("InnoDB: Cannot calculate statistics for table .* because the \.ibd file is missing");
call mtr.add_suppression("InnoDB: Cannot delete tablespace .* in DISCARD TABLESPACE. Tablespace not found");
call mtr.add_suppression("InnoDB: Cannot delete tablespace .* because it is not found in the tablespace memory cache.");
call mtr.add_suppression("InnoDB: Trying to import a tablespace, but could not open the tablespace file");
call mtr.add_suppression("InnoDB: Operating system error number 2 in a file operation.");
call mtr.add_suppression("InnoDB: The error means the system cannot find the path specified.");
call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT.");
call mtr.add_suppression("InnoDB: Missing \.ibd file for table .test...t1. .. Partition");
--enable_query_log
--remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.ibd
--remove_files_wildcard $MYSQLTEST_VARDIR/tmp t1*.cfg