diff options
author | Sergei Golubchik <serg@mariadb.org> | 2022-05-25 22:22:03 +0200 |
---|---|---|
committer | Nikita Malyavin <nikitamalyavin@gmail.com> | 2022-10-17 15:24:43 +0300 |
commit | 0ff98baf7387413c94279d0c552196167e36539a (patch) | |
tree | 10ed4e30437d89c85c7c2c4bfb8dfad9d4408099 | |
parent | 6e6c06829acc964c1fef1a9642b86769e7bb3be4 (diff) | |
download | mariadb-git-0ff98baf7387413c94279d0c552196167e36539a.tar.gz |
tests: move around, add new
two new tests:
* alter table times out because of a long concurrent trx
* alter table adds a column in the middle
-rw-r--r-- | mysql-test/include/binlog_combinations.combinations (renamed from mysql-test/main/alter_table_online_binlog.combinations) | 3 | ||||
-rw-r--r-- | mysql-test/include/binlog_combinations.inc | 3 | ||||
-rw-r--r-- | mysql-test/include/have_log_bin_off.require | 2 | ||||
-rw-r--r-- | mysql-test/main/alter_table.result | 2 | ||||
-rw-r--r-- | mysql-test/main/alter_table.test | 10 | ||||
-rw-r--r-- | mysql-test/main/alter_table_online.combinations | 2 | ||||
-rw-r--r-- | mysql-test/main/alter_table_online.result | 72 | ||||
-rw-r--r-- | mysql-test/main/alter_table_online.test | 104 | ||||
-rw-r--r-- | mysql-test/main/alter_table_online_binlog.inc | 14 |
9 files changed, 132 insertions, 80 deletions
diff --git a/mysql-test/main/alter_table_online_binlog.combinations b/mysql-test/include/binlog_combinations.combinations index 630977f9ead..5bd64366c9d 100644 --- a/mysql-test/main/alter_table_online_binlog.combinations +++ b/mysql-test/include/binlog_combinations.combinations @@ -1,4 +1,3 @@ -[standalone] +[nobinlog] [binlog] --log-bin=master-bin - diff --git a/mysql-test/include/binlog_combinations.inc b/mysql-test/include/binlog_combinations.inc new file mode 100644 index 00000000000..b9fff3411b3 --- /dev/null +++ b/mysql-test/include/binlog_combinations.inc @@ -0,0 +1,3 @@ +# +# Adds standalone and binlog combinations +# diff --git a/mysql-test/include/have_log_bin_off.require b/mysql-test/include/have_log_bin_off.require deleted file mode 100644 index 979dbe75f80..00000000000 --- a/mysql-test/include/have_log_bin_off.require +++ /dev/null @@ -1,2 +0,0 @@ -Variable_name Value -log_bin OFF diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index fae913369c0..4a647c5fd64 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -1755,7 +1755,6 @@ affected rows: 0 info: Records: 0 Duplicates: 0 Warnings: 1 Warnings: Note 1831 Duplicate index `i3`. This is deprecated and will be disallowed in a future release -ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; affected rows: 2 info: Records: 2 Duplicates: 0 Warnings: 1 @@ -1772,7 +1771,6 @@ ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; ERROR 0A000: LOCK=NONE/SHARED is not supported for this operation. Try LOCK=EXCLUSIVE ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; affected rows: 0 -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; ALTER ONLINE TABLE m1 ADD COLUMN c int; ERROR 0A000: LOCK=NONE is not supported for this operation. Try LOCK=SHARED ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= SHARED; diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index 1d801e29348..43fdf4ee38e 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -1529,12 +1529,6 @@ ALTER TABLE t1 DROP INDEX i1, DROP INDEX i2, DROP INDEX i3, DROP INDEX i4; ALTER TABLE t1 ADD INDEX i1(b), ALGORITHM= INPLACE, LOCK= NONE; ALTER TABLE t1 ADD INDEX i2(b), ALGORITHM= INPLACE, LOCK= SHARED; ALTER TABLE t1 ADD INDEX i3(b), ALGORITHM= INPLACE, LOCK= EXCLUSIVE; ---disable_info ---disable_warnings ---error 0,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD INDEX i4(b), ALGORITHM= COPY, LOCK= NONE; ---enable_warnings ---enable_info ALTER TABLE t1 ADD INDEX i5(b), ALGORITHM= COPY, LOCK= SHARED; ALTER TABLE t1 ADD INDEX i6(b), ALGORITHM= COPY, LOCK= EXCLUSIVE; @@ -1543,10 +1537,6 @@ ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= SHARED; ALTER TABLE m1 ENABLE KEYS, ALGORITHM= INPLACE, LOCK= EXCLUSIVE; ---disable_info ---error 0,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE m1 ENABLE KEYS, ALGORITHM= COPY, LOCK= NONE; ---enable_info --error ER_ALTER_OPERATION_NOT_SUPPORTED ALTER ONLINE TABLE m1 ADD COLUMN c int; # This works because the lock will be SNW for the copy phase. diff --git a/mysql-test/main/alter_table_online.combinations b/mysql-test/main/alter_table_online.combinations deleted file mode 100644 index ae144432c68..00000000000 --- a/mysql-test/main/alter_table_online.combinations +++ /dev/null @@ -1,2 +0,0 @@ -[innodb] -[rocksdb] diff --git a/mysql-test/main/alter_table_online.result b/mysql-test/main/alter_table_online.result index 57a4cc4735f..5dbf73c9073 100644 --- a/mysql-test/main/alter_table_online.result +++ b/mysql-test/main/alter_table_online.result @@ -1,10 +1,11 @@ +set default_storage_engine= innodb; connect con2, localhost, root,,; connection default; # # Test insert # # Insert and add column -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (5); connection con2; set debug_sync= 'now WAIT_FOR ended'; @@ -21,8 +22,30 @@ a b 123 NULL 456 NULL 789 NULL +# long transaction and add column +create or replace table t1 (a int); +insert t1 values (5); +connection con2; +set debug_sync= 'now WAIT_FOR ended'; +connection default; +set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end'; +set session lock_wait_timeout=1; +alter table t1 add b int NULL, algorithm= copy, lock= none; +connection con2; +start transaction; +insert into t1 values (123), (456), (789); +set debug_sync= 'now SIGNAL end'; +connection default; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +select * from t1; +a +5 +set session lock_wait_timeout=default; +connection con2; +rollback; +connection default; # Insert and add NOT NULL column without default value -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (5); connection con2; set debug_sync= 'now WAIT_FOR ended'; @@ -44,7 +67,7 @@ a b 456 0 789 0 # Insert and add a column with a default value -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (5); connection con2; set debug_sync= 'now WAIT_FOR ended'; @@ -65,7 +88,7 @@ a b # Test update # # Update and add a column -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 22); insert t1 values (3, 44); connection con2; @@ -82,11 +105,29 @@ select * from t1; a b c 1 55 1 3 44 1 +# Update and add a column in the middle +create or replace table t1 (a int primary key, b int); +insert t1 values (1, 22); +insert t1 values (3, 44); +connection con2; +set debug_sync= 'now WAIT_FOR ended'; +connection default; +set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end'; +alter table t1 add c int default(1) after a, +algorithm= copy, lock= none; +connection con2; +update t1 set b= 55 where a = 1; +set debug_sync= 'now SIGNAL end'; +connection default; +select * from t1; +a c b +1 1 55 +3 1 44 # # Test primary key change # # Drop key, add key -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 22); insert t1 values (3, 44); connection con2; @@ -104,7 +145,7 @@ a b 3 44 1 55 # Drop key, add key. Two updates -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); connection con2; @@ -125,7 +166,7 @@ a b # # Various tests, see below # -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); insert t1 values (3, 33); @@ -193,6 +234,7 @@ a b 9 99 # # MYISAM. Only Inserts can be tested. +# (everything else is a table lock disallowing concurrent reads) # create or replace table t1 (a int) engine=myisam; insert t1 values (5); @@ -212,7 +254,7 @@ a b 456 NULL 789 NULL # Test incompatible changes -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 22); insert t1 values (3, 44); connection con2; @@ -233,7 +275,7 @@ a b # Test log read after EXCLUSIVE lock # Transaction is started before ALTER, and UPDATE is made. # Then more UPDATEs. -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); insert t1 values (3, 33); @@ -265,7 +307,7 @@ a b # # Test progress report. # -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); insert t1 values (3, 33); @@ -364,7 +406,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) -) ENGINE=*SUBSTITUTED* DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select *, UNIX_TIMESTAMP(row_start), UNIX_TIMESTAMP(row_end) from t1 for system_time all; a b UNIX_TIMESTAMP(row_start) UNIX_TIMESTAMP(row_end) 1 55 1.000000 2147483647.999999 @@ -388,7 +430,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) -) ENGINE=*SUBSTITUTED* DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING +) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING select *, UNIX_TIMESTAMP(row_start), UNIX_TIMESTAMP(row_end) from t1 for system_time all; a b UNIX_TIMESTAMP(row_start) UNIX_TIMESTAMP(row_end) 1 55 1.000000 3.000000 @@ -412,7 +454,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) NOT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`a`) -) ENGINE=*SUBSTITUTED* DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 select * from t1; a b 1 88 @@ -422,9 +464,9 @@ a b # # Test ROLLBACK TO SAVEPOINT # -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (1), (2); -create or replace table t2 (a int) engine=innodb; +create or replace table t2 (a int); insert t2 values (1), (2); connection con2; begin; diff --git a/mysql-test/main/alter_table_online.test b/mysql-test/main/alter_table_online.test index 3a858ab87e1..d0cbf958004 100644 --- a/mysql-test/main/alter_table_online.test +++ b/mysql-test/main/alter_table_online.test @@ -1,30 +1,18 @@ --- source include/have_debug_sync.inc --- source include/not_embedded.inc --- source alter_table_online_binlog.inc - --- disable_query_log --- if ($MTR_COMBINATION_INNODB) { --- source include/have_innodb.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc +--source include/binlog_combinations.inc +--source include/have_innodb.inc set default_storage_engine= innodb; --- } --- if ($MTR_COMBINATION_ROCKSDB) { --- source include/have_rocksdb.inc -set default_storage_engine= rocksdb; --- } --- enable_query_log - --- let $default_engine= `select @@default_storage_engine` --connect (con2, localhost, root,,) --connection default - --echo # --echo # Test insert --echo # --echo # Insert and add column -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (5); --connection con2 @@ -46,8 +34,37 @@ set debug_sync= 'now SIGNAL end'; --reap select * from t1; +--echo # long transaction and add column +create or replace table t1 (a int); +insert t1 values (5); + +--connection con2 +--send +set debug_sync= 'now WAIT_FOR ended'; + +--connection default +set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end'; +set session lock_wait_timeout=1; +--send +alter table t1 add b int NULL, algorithm= copy, lock= none; + +--connection con2 +--reap +start transaction; +insert into t1 values (123), (456), (789); +set debug_sync= 'now SIGNAL end'; + +--connection default +--error ER_LOCK_WAIT_TIMEOUT +--reap +select * from t1; +set session lock_wait_timeout=default; +--connection con2 +rollback; +--connection default + --echo # Insert and add NOT NULL column without default value -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (5); --connection con2 @@ -70,7 +87,7 @@ set debug_sync= 'now SIGNAL end'; select * from t1; --echo # Insert and add a column with a default value -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (5); --connection con2 @@ -97,7 +114,7 @@ select * from t1; --echo # --echo # Update and add a column -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 22); insert t1 values (3, 44); @@ -121,12 +138,37 @@ set debug_sync= 'now SIGNAL end'; --reap select * from t1; +--echo # Update and add a column in the middle +create or replace table t1 (a int primary key, b int); +insert t1 values (1, 22); +insert t1 values (3, 44); + +--connection con2 +--send +set debug_sync= 'now WAIT_FOR ended'; + +--connection default +set debug_sync= 'alter_table_copy_end SIGNAL ended WAIT_FOR end'; + +--send +alter table t1 add c int default(1) after a, + algorithm= copy, lock= none; + +--connection con2 +--reap +update t1 set b= 55 where a = 1; +set debug_sync= 'now SIGNAL end'; + +--connection default +--reap +select * from t1; + --echo # --echo # Test primary key change --echo # --echo # Drop key, add key -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 22); insert t1 values (3, 44); @@ -151,7 +193,7 @@ set debug_sync= 'now SIGNAL end'; select * from t1; --echo # Drop key, add key. Two updates -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); @@ -181,7 +223,7 @@ select * from t1; --echo # Various tests, see below --echo # -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); insert t1 values (3, 33); @@ -248,6 +290,7 @@ select * from t1; --echo # --echo # MYISAM. Only Inserts can be tested. +--echo # (everything else is a table lock disallowing concurrent reads) --echo # create or replace table t1 (a int) engine=myisam; @@ -273,7 +316,7 @@ set debug_sync= 'now SIGNAL end'; select * from t1; --echo # Test incompatible changes -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 22); insert t1 values (3, 44); @@ -298,12 +341,11 @@ set debug_sync= 'now SIGNAL end'; --reap select * from t1; - --echo # Test log read after EXCLUSIVE lock --echo # Transaction is started before ALTER, and UPDATE is made. --echo # Then more UPDATEs. -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); insert t1 values (3, 33); @@ -342,7 +384,7 @@ select * from t1; --echo # Test progress report. --echo # -create or replace table t1 (a int primary key, b int) engine=innodb; +create or replace table t1 (a int primary key, b int); insert t1 values (1, 11); insert t1 values (2, 22); insert t1 values (3, 33); @@ -416,7 +458,6 @@ set debug_sync= 'now SIGNAL proceed'; --reap select * from t1; - --echo # --echo # Test system versioning --echo # @@ -448,7 +489,6 @@ set debug_sync= 'now SIGNAL end'; --connection default --reap --- replace_result $default_engine *SUBSTITUTED* show create table t1; select *, UNIX_TIMESTAMP(row_start), UNIX_TIMESTAMP(row_end) from t1 for system_time all; @@ -473,7 +513,6 @@ set debug_sync= 'now SIGNAL end'; --echo # Can't UPDATE versioned -> plain (and can't DELETE) --error ER_DUP_ENTRY --reap --- replace_result $default_engine *SUBSTITUTED* show create table t1; select *, UNIX_TIMESTAMP(row_start), UNIX_TIMESTAMP(row_end) from t1 for system_time all; @@ -497,7 +536,6 @@ set debug_sync= 'now SIGNAL end'; --connection default --echo # INSERT versioned -> plain works fine since it is a single versioned op. --reap --- replace_result $default_engine *SUBSTITUTED* show create table t1; select * from t1; @@ -505,10 +543,10 @@ select * from t1; --echo # Test ROLLBACK TO SAVEPOINT --echo # -create or replace table t1 (a int) engine=innodb; +create or replace table t1 (a int); insert t1 values (1), (2); -create or replace table t2 (a int) engine=innodb; +create or replace table t2 (a int); insert t2 values (1), (2); --connection con2 diff --git a/mysql-test/main/alter_table_online_binlog.inc b/mysql-test/main/alter_table_online_binlog.inc deleted file mode 100644 index 2fe9677a5b7..00000000000 --- a/mysql-test/main/alter_table_online_binlog.inc +++ /dev/null @@ -1,14 +0,0 @@ -# -# Adds standalone and binlog combinations -# --- if ($MTR_COMBINATION_BINLOG) { --- require include/have_log_bin.require --- } - --- if ($MTR_COMBINATION_STANDALONE) { --- require include/have_log_bin_off.require --- } - -disable_query_log; -show variables like 'log_bin'; -enable_query_log; |