diff options
Diffstat (limited to 'mysql-test/suite/parts/t/partition_special_innodb.test')
| -rw-r--r-- | mysql-test/suite/parts/t/partition_special_innodb.test | 234 |
1 files changed, 234 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/t/partition_special_innodb.test b/mysql-test/suite/parts/t/partition_special_innodb.test new file mode 100644 index 00000000..836b7048 --- /dev/null +++ b/mysql-test/suite/parts/t/partition_special_innodb.test @@ -0,0 +1,234 @@ +################################################################################ +# t/partition_special_innodb.test # +# # +# Purpose: # +# different Tests # +# InnoDB branch # +# # +#------------------------------------------------------------------------------# +# Original Author: HH # +# Original Date: 2006-08-01 # +# Change Author: MattiasJ # +# Change Date: 2008-08-20 # +# Change: added test for bug#34604 # +################################################################################ + +# +# NOTE: PLEASE DO NOT ADD NOT INNODB SPECIFIC TESTCASES HERE ! +# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN +# THE SOURCED FILES ONLY. +# +# Please read the README at the end of inc/partition.pre before changing +# any of the variables. +# + +#------------------------------------------------------------------------------# +# General not engine specific settings and requirements + +##### Options, for debugging support ##### +let $debug= 0; + +# The server must support partitioning. +--source include/have_partition.inc + +#------------------------------------------------------------------------------# +# Engine specific settings and requirements + +##### Storage engine to be tested +--source include/have_innodb.inc +let $engine= 'InnoDB'; + +#------------------------------------------------------------------------------# +# Execute the tests to be applied to all storage engines +--source suite/parts/inc/partition_key_4col.inc +--source suite/parts/inc/partition_key_8col.inc +--source suite/parts/inc/partition_key_16col.inc +--source suite/parts/inc/partition_key_32col.inc + +#------------------------------------------------------------------------------# +# Execute storage engine specific tests + +--echo # Bug#34604 - Assertion 'inited==RND' failed in handler::ha_rnd_end + +CREATE TABLE t1 ( + a INT AUTO_INCREMENT, + b VARCHAR(255), + PRIMARY KEY (a)) +ENGINE = InnoDB +PARTITION BY HASH (a) +PARTITIONS 2; + +connect (con1, localhost, root,,); +SET autocommit=OFF; +START TRANSACTION; +INSERT INTO t1 VALUES (NULL, 'first row t2'); + +connect (con2, localhost, root,,); +SET autocommit=OFF; +SET SESSION lock_wait_timeout= 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 AUTO_INCREMENT = 10; + +--disconnect con2 +--disconnect con1 +--connection default +DROP TABLE t1; + +--echo # +--echo # Bug#53676: Unexpected errors and possible table corruption on +--echo # ADD PARTITION and LOCK TABLE +CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; +INSERT INTO t1 VALUES (2, 2), (3, 3), (4, 4), (5, 5); + +LOCK TABLE t1 READ; + +--connect (con2,localhost,root,,) +SET lock_wait_timeout = 0; +--echo # First attempt: lock wait timeout (as expected) +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +--echo # Second attempt: says that partition already exists +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +--echo # Check that we only can select, not insert/update/delete. +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (NULL, 6), (NULL, 7), (10, 10), (11, 11); +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i = 5 WHERE f = 2; +--error ER_LOCK_WAIT_TIMEOUT +DELETE FROM t1 WHERE i = 10; +--sorted_result +SELECT * FROM t1; + +--connection default +UNLOCK TABLES; +LOCK TABLE t1 READ; + +--connection con2 +--echo # Third attempt: says that the table does not exist +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t1 ADD PARTITION PARTITIONS 2; +--echo # Check table returns the same (not after fixing bug#56172!) +CHECK TABLE t1; + +--connection default +UNLOCK TABLES; +# Avoid a MDL conflict between purge and the DROP TABLE below +SET GLOBAL innodb_max_purge_lag_wait=0; + +--connection con2 +DROP TABLE t1; + +# End of Test1 + +# Test2 + +--connection default + +CREATE TABLE t2 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; + +LOCK TABLE t2 READ; + +--connection con2 +SET lock_wait_timeout = 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t2 ADD PARTITION PARTITIONS 2; +SET lock_wait_timeout = 2; +send ALTER TABLE t2 ADD PARTITION PARTITIONS 2; + +--connection default +UNLOCK TABLES; + +--connection con2 +--reap + +--connect (con3,localhost,root,,) +CHECK TABLE t2; +SELECT * FROM t2; +DROP TABLE t2; + +# End of Test2 +# Test #3 + +--connection default + +CREATE TABLE t3 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; + +LOCK TABLE t3 READ; + +--connection con2 +SET lock_wait_timeout = 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t3 ADD PARTITION PARTITIONS 2; + +--connection con3 +SET lock_wait_timeout = 0; +--error ER_LOCK_WAIT_TIMEOUT +ALTER TABLE t3 ADD PARTITION PARTITIONS 2; + +--connect (con4,localhost,root,,) +send RENAME TABLE t3 TO t4; + +--connection default +UNLOCK TABLES; + +--connection con4 +--reap + +--connect (con5,localhost,root,,) +--echo # SHOW TABLES returns the table (not renamed): +SHOW TABLES; +--echo # Connection 5 attempts to read from the table (table does not exist): +--error ER_NO_SUCH_TABLE +SELECT * FROM t3; +DROP TABLE t4; + +--disconnect con5 +--disconnect con4 +--disconnect con3 +--disconnect con2 +--connection default +# End of Test #3 + +# MDEV-21693 ALGORITHM=INSTANT does not work for partitioned tables +CREATE TABLE t1( + f1 INT, f2 VARCHAR(10) CHARSET ascii, + f3 CHAR(150) CHARSET ascii, + f4 TEXT CHARSET ascii)ENGINE=InnoDB + PARTITION BY RANGE(f1) (PARTITION p1 VALUES LESS THAN(10), + PARTITION p2 VALUES LESS THAN (100)); +ALTER TABLE t1 convert to charset ascii collate ascii_bin, ALGORITHM=INSTANT; +DROP TABLE t1; + +--echo # Test WRITE LOCK. +CREATE TABLE t1 ( i INT NOT NULL AUTO_INCREMENT PRIMARY KEY, f INT ) + ENGINE = InnoDB PARTITION BY HASH(i) PARTITIONS 2; +INSERT INTO t1 VALUES (3, 3), (4, 4); + +LOCK TABLE t1 WRITE; + +--connect (con2,localhost,root,,) +SET lock_wait_timeout = 0; +--echo # Check that we only can select, not insert/update/delete. +--error ER_LOCK_WAIT_TIMEOUT +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (10, 10), (11, 11); +--error ER_LOCK_WAIT_TIMEOUT +UPDATE t1 SET i = 5 WHERE f = 2; +--error ER_LOCK_WAIT_TIMEOUT +DELETE FROM t1 WHERE i = 10; +--error ER_LOCK_WAIT_TIMEOUT +SELECT * FROM t1; + +--connection default +UNLOCK TABLES; +# Avoid a MDL conflict between purge and the DROP TABLE below +SET GLOBAL innodb_max_purge_lag_wait=0; + +--connection con2 +DROP TABLE t1; + +--disconnect con2 +--connection default |