diff options
Diffstat (limited to 'mysql-test/suite/innodb/t')
24 files changed, 518 insertions, 125 deletions
diff --git a/mysql-test/suite/innodb/t/alter_large_dml.test b/mysql-test/suite/innodb/t/alter_large_dml.test index 5ab3f394115..54f5f171f05 100644 --- a/mysql-test/suite/innodb/t/alter_large_dml.test +++ b/mysql-test/suite/innodb/t/alter_large_dml.test @@ -25,17 +25,11 @@ SET DEBUG_SYNC = 'now WAIT_FOR dml_restart'; ROLLBACK; BEGIN; -INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_16384; -INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_16384; -INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_16384; -INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_16384; -INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_16384; +INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_81920; ROLLBACK; BEGIN; -INSERT INTO t1 SELECT * FROM t1; -INSERT INTO t1 SELECT * FROM t1; -INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT '','','','','','','','' FROM seq_1_to_114688; ROLLBACK; SET DEBUG_SYNC = 'now SIGNAL dml_done'; diff --git a/mysql-test/suite/innodb/t/alter_mdl_timeout.opt b/mysql-test/suite/innodb/t/alter_mdl_timeout.opt new file mode 100644 index 00000000000..9e0e38bd64a --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_mdl_timeout.opt @@ -0,0 +1 @@ +--lock_wait_timeout=2 diff --git a/mysql-test/suite/innodb/t/alter_mdl_timeout.test b/mysql-test/suite/innodb/t/alter_mdl_timeout.test new file mode 100644 index 00000000000..15e7f524fd0 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_mdl_timeout.test @@ -0,0 +1,32 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +create table t1(f1 char(10), f2 char(10) not null, f3 int not null, + f4 int not null, primary key(f3))engine=innodb; +insert into t1 values('a','a', 1, 1), ('b','b', 2, 2), ('c', 'c', 3, 3), ('d', 'd', 4, 4); +SET DEBUG_SYNC="row_merge_after_scan SIGNAL con1_start WAIT_FOR con1_insert"; +SET DEBUG_SYNC="innodb_commit_inplace_alter_table_wait SIGNAL con1_wait WAIT_FOR con1_update"; +send ALTER TABLE t1 ADD UNIQUE INDEX(f1(3), f4), ADD UNIQUE INDEX(f2), ALGORITHM=INPLACE; + +connect(con1,localhost,root,,,); +SET DEBUG_SYNC="now WAIT_FOR con1_start"; +begin; +INSERT INTO t1 VALUES('e','e',5, 5); +SET DEBUG_SYNC="now SIGNAL con1_insert"; +SET DEBUG_SYNC="now WAIT_FOR con1_wait"; +SET DEBUG_SYNC="before_row_upd_sec_new_index_entry SIGNAL con1_update WAIT_FOR alter_rollback"; +SEND UPDATE t1 set f4 = 10 order by f1 desc limit 2; + +connection default; +--error ER_LOCK_WAIT_TIMEOUT +reap; +SET DEBUG_SYNC="now SIGNAL alter_rollback"; + +connection con1; +reap; +commit; + +connection default; +disconnect con1; +DROP TABLE t1; +SET DEBUG_SYNC="RESET"; diff --git a/mysql-test/suite/innodb/t/alter_table.test b/mysql-test/suite/innodb/t/alter_table.test index aca70e61bc6..2b84a37cdce 100644 --- a/mysql-test/suite/innodb/t/alter_table.test +++ b/mysql-test/suite/innodb/t/alter_table.test @@ -80,9 +80,26 @@ ALTER TABLE t ENGINE INNODB; ALTER TABLE t FORCE; DROP TABLE t; -# -# Check that innodb supports transactional=1 -# +--echo # +--echo # MDEV-24763 ALTER TABLE fails to rename a column in SYS_FIELDS +--echo # +CREATE TABLE t1 (a INT, b TEXT, c INT, PRIMARY KEY(b(9)), INDEX(c,a)) +ENGINE=InnoDB; +ALTER TABLE t1 CHANGE COLUMN a u INT; +--replace_column 1 ID +SELECT sf.* FROM information_schema.innodb_sys_fields sf +INNER JOIN information_schema.innodb_sys_indexes si ON sf.index_id=si.index_id +INNER JOIN information_schema.innodb_sys_tables st ON si.table_id=st.table_id +WHERE st.name='test/t1' ORDER BY sf.index_id,sf.pos; +DROP TABLE t1; + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # Check that innodb supports transactional=1 +--echo # create table t1 (a int) transactional=1 engine=aria; create table t2 (a int) transactional=1 engine=innodb; @@ -91,3 +108,8 @@ show create table t2; alter table t1 engine=innodb; alter table t1 add column b int; drop table t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # + diff --git a/mysql-test/suite/innodb/t/alter_varchar_change.test b/mysql-test/suite/innodb/t/alter_varchar_change.test index 65dc38a3411..d1ce5d7403e 100644 --- a/mysql-test/suite/innodb/t/alter_varchar_change.test +++ b/mysql-test/suite/innodb/t/alter_varchar_change.test @@ -361,7 +361,15 @@ DROP PROCEDURE get_table_id; # LEN must increase here create table t (a varchar(100)) engine=innodb; -select name, pos, mtype, prtype, len from information_schema.innodb_sys_columns where name='a'; +select sc.name, sc.pos, sc.mtype, sc.prtype, sc.len +from information_schema.innodb_sys_columns sc +inner join information_schema.innodb_sys_tables st +on sc.table_id=st.table_id where st.name='test/t' and sc.name='a'; alter table t modify a varchar(110), algorithm=inplace; -select name, pos, mtype, prtype, len from information_schema.innodb_sys_columns where name='a'; +select sc.name, sc.pos, sc.mtype, sc.prtype, sc.len +from information_schema.innodb_sys_columns sc +inner join information_schema.innodb_sys_tables st +on sc.table_id=st.table_id where st.name='test/t' and sc.name='a'; drop table t; + +--echo # End of 10.2 tests diff --git a/mysql-test/suite/innodb/t/default_row_format_alter.test b/mysql-test/suite/innodb/t/default_row_format_alter.test index 1f0b0d56bc4..7cd4d672858 100644 --- a/mysql-test/suite/innodb/t/default_row_format_alter.test +++ b/mysql-test/suite/innodb/t/default_row_format_alter.test @@ -97,6 +97,19 @@ SHOW TABLE STATUS LIKE 't1'; DROP TABLE t1; --echo # +--echo # MDEV-24758 heap-use-after-poison in innobase_add_instant_try/rec_copy +--echo # + +CREATE TABLE t1 (pk INT PRIMARY KEY) CHARACTER SET utf8 ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +SET GLOBAL innodb_default_row_format = REDUNDANT; +ALTER TABLE t1 ADD a CHAR(8) DEFAULT ''; +DROP TABLE t1; +SET GLOBAL innodb_default_row_format = @row_format; + +--echo # End of 10.3 tests + +--echo # --echo # MDEV-23295 Assertion fields[i].same(instant.fields[i]) failed --echo # SET GLOBAL innodb_default_row_format = @row_format; @@ -116,4 +129,6 @@ ALTER TABLE t1 DROP b; SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; DROP TABLE t1; +--echo # End of 10.4 tests + SET GLOBAL innodb_default_row_format = @row_format; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 41299e4bc35..a413ea43646 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -782,6 +782,45 @@ ALTER TABLE t1 ADD UNIQUE INDEX ind9 (b), LOCK=SHARED; SET FOREIGN_KEY_CHECKS= 0; ALTER TABLE t1 ADD FOREIGN KEY (a) REFERENCES t1 (pk); DROP TABLE t1; +SET FOREIGN_KEY_CHECKS= 1; + +--echo # +--echo # MDEV-23455 Hangs + Sig11 in unknown location(s) due to single complex FK query +--echo # +let $constr_prefix= aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa; +let $fk_ref= xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx; +let $fk_field= yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy; +let $constr_count= 200; # each 100 constrs is 1 sec of test execution +let $i= 0; + +while ($i < $constr_count) +{ + let $p= $constr_prefix$i; + let $constr= CONSTRAINT $p FOREIGN KEY ($fk_field) REFERENCES t1($fk_ref) ON UPDATE SET NULL; + if ($i) + { + let $constrs= $constrs, $constr; + } + if (!$i) + { + let $constrs= $constr; + } + inc $i; +} +--disable_query_log +--echo Parsing foreign keys 1... +--error ER_CANT_CREATE_TABLE +eval create table t0($fk_field int, $constrs) engine innodb; +--echo Parsing foreign keys 2... +--error ER_CANT_CREATE_TABLE +eval create table t1($fk_field int, $constrs) engine innodb; +--echo Parsing foreign keys 3... +--error ER_CANT_CREATE_TABLE +eval create table t1($fk_ref int, $fk_field int, $constrs) engine innodb; +--echo Parsing foreign keys 4... +eval create table t1($fk_ref int primary key, $fk_field int, $constrs) engine innodb; +drop table t1; +--enable_query_log --echo # End of 10.2 tests diff --git a/mysql-test/suite/innodb/t/innodb-virtual-columns2.test b/mysql-test/suite/innodb/t/innodb-virtual-columns2.test index 474a6354576..13ecffcc896 100644 --- a/mysql-test/suite/innodb/t/innodb-virtual-columns2.test +++ b/mysql-test/suite/innodb/t/innodb-virtual-columns2.test @@ -52,3 +52,23 @@ SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE; INSERT INTO t1 (i) VALUES (1),(2); SELECT * FROM t1 WHERE y BETWEEN 2012 AND 2016 FOR UPDATE; DROP TABLE t1; + +--echo # +--echo # MDEV-23632 ALTER TABLE...ADD KEY creates corrupted index on virtual column +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1,default); +ALTER TABLE t1 ADD COLUMN c INT; +ALTER TABLE t1 ADD KEY(g); +CHECK TABLE t1; +SELECT g FROM t1 FORCE INDEX (g); +DROP TABLE t1; + +CREATE TABLE t1(a INT, b INT, g INT GENERATED ALWAYS AS(b)VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,1,default); +ALTER TABLE t1 ADD COLUMN c INT PRIMARY KEY; # Triggers `new_clustered` +ALTER TABLE t1 ADD KEY(g); +CHECK TABLE t1; +SELECT g FROM t1 FORCE INDEX (g); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index a81e6c3f900..ba5126e4757 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -2592,3 +2592,21 @@ SELECT * FROM t2; DROP TABLE t1; DROP TABLE t2; + +--echo # +--echo # MDEV-24748 Extern field check missing +--echo # in btr_index_rec_validate() +--echo # +CREATE TABLE t1 (pk INT, c1 char(255), +c2 char(255), c3 char(255), c4 char(255), +c5 char(255), c6 char(255), c7 char(255), +c8 char(255), primary key (pk) +) CHARACTER SET utf32 ENGINE=InnoDB; + +INSERT INTO t1 VALUES + (1, 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h'), + (2, 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p'); +CHECK TABLE t1; +ALTER TABLE t1 FORCE; +# Cleanup +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_fail.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_fail.test new file mode 100644 index 00000000000..1d938e12e78 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_fail.test @@ -0,0 +1,11 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +call mtr.add_suppression("InnoDB: Cannot allocate memory for the buffer pool"); +call mtr.add_suppression("InnoDB: Plugin initialization aborted at srv0start.cc.*"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error."); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); +--echo # +--echo # MDEV-25019 memory allocation failures during startup cause server failure in different, confusing ways +--echo # +let restart_parameters=--debug_dbug="+d,ib_buf_chunk_init_fails"; +--source include/restart_mysqld.inc diff --git a/mysql-test/suite/innodb/t/innodb_bug60049-master.opt b/mysql-test/suite/innodb/t/innodb_bug60049-master.opt deleted file mode 100644 index 22a5d4ed221..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug60049-master.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb_fast_shutdown=0 diff --git a/mysql-test/suite/innodb/t/innodb_bug60049.test b/mysql-test/suite/innodb/t/innodb_bug60049.test deleted file mode 100644 index cb05ca297ea..00000000000 --- a/mysql-test/suite/innodb/t/innodb_bug60049.test +++ /dev/null @@ -1,49 +0,0 @@ -# Bug #60049 Verify that purge leaves no garbage in unique secondary indexes -# This test requires a fresh server start-up and a slow shutdown. -# This was a suspected bug (not a bug). - --- source include/not_embedded.inc --- source include/have_innodb.inc --- source include/have_innodb_16k.inc - --- disable_query_log -call mtr.add_suppression('\\[ERROR\\] InnoDB: Table `mysql`.`innodb_(table|index)_stats` not found'); -call mtr.add_suppression('\\[ERROR\\] InnoDB: Fetch of persistent statistics requested for table `mysql`.`gtid_executed`'); - -let $create1 = query_get_value(SHOW CREATE TABLE mysql.innodb_table_stats, Create Table, 1); -let $create2 = query_get_value(SHOW CREATE TABLE mysql.innodb_index_stats, Create Table, 1); -DROP TABLE mysql.innodb_index_stats; -DROP TABLE mysql.innodb_table_stats; --- enable_query_log - -CREATE TABLE t(a INT)ENGINE=InnoDB STATS_PERSISTENT=0; -RENAME TABLE t TO u; -DROP TABLE u; -SELECT @@innodb_fast_shutdown; -let $MYSQLD_DATADIR=`select @@datadir`; - ---source include/shutdown_mysqld.inc - -# Check the tail of ID_IND (SYS_TABLES.ID) -let IBDATA1=$MYSQLD_DATADIR/ibdata1; -perl; -my $file = $ENV{'IBDATA1'}; -open(FILE, "<$file") || die "Unable to open $file"; -# Read DICT_HDR_TABLE_IDS, the root page number of ID_IND (SYS_TABLES.ID). -seek(FILE, 7*16384+38+36, 0) || die "Unable to seek $file"; -die unless read(FILE, $_, 4) == 4; -my $sys_tables_id_root = unpack("N", $_); -print "Last record of ID_IND root page ($sys_tables_id_root):\n"; -# This should be the last record in ID_IND. Dump it in hexadecimal. -seek(FILE, $sys_tables_id_root*16384 + 152, 0) || die "Unable to seek $file"; -read(FILE, $_, 32) || die "Unable to read $file"; -close(FILE); -print unpack("H*", $_), "\n"; -EOF - ---source include/start_mysqld.inc - --- disable_query_log -USE mysql; -eval $create1; -eval $create2; diff --git a/mysql-test/suite/innodb/t/innodb_multi_update.test b/mysql-test/suite/innodb/t/innodb_multi_update.test index 8d5283a9ed5..74a7aea7d13 100644 --- a/mysql-test/suite/innodb/t/innodb_multi_update.test +++ b/mysql-test/suite/innodb/t/innodb_multi_update.test @@ -35,4 +35,5 @@ CREATE TABLE t1(f1 INT) ENGINE=INNODB; INSERT INTO t1 VALUES(1); --error ER_OPERAND_COLUMNS UPDATE (SELECT ((SELECT 1 FROM t1), 1) FROM t1 WHERE (SELECT 1 FROM t1)) x, (SELECT 1) AS d SET d.f1 = 1; +UPDATE (SELECT ((SELECT 1 FROM t1),1) = (1,1) FROM t1 WHERE (SELECT 1 FROM t1)) x, t1 AS d SET d.f1 = 1; DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures-master.opt b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures-master.opt deleted file mode 100644 index 39b205c9b68..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures-master.opt +++ /dev/null @@ -1,2 +0,0 @@ ---innodb-file-per-table ---skip-innodb-doublewrite diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures.test b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures.test deleted file mode 100644 index 5a4978c9b37..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures.test +++ /dev/null @@ -1,9 +0,0 @@ ---source include/big_test.inc -# test takes too long with valgrind ---source include/not_valgrind.inc ---source include/have_debug.inc ---let $num_inserts = 1500 ---let $num_ops = 3500 ---source suite/innodb/include/innodb_simulate_comp_failures.inc -# clean exit ---exit diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small-master.opt b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small-master.opt deleted file mode 100644 index fae32059249..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small-master.opt +++ /dev/null @@ -1,2 +0,0 @@ ---innodb-file-per-table - diff --git a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small.test b/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small.test deleted file mode 100644 index 79a16d36917..00000000000 --- a/mysql-test/suite/innodb/t/innodb_simulate_comp_failures_small.test +++ /dev/null @@ -1,8 +0,0 @@ ---source include/have_debug.inc ---source include/not_valgrind.inc - ---let $num_inserts = 1000 ---let $num_ops = 30 ---source suite/innodb/include/innodb_simulate_comp_failures.inc -# clean exit ---exit diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index 83dca4cb5a6..3872f080d6b 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -1,4 +1,5 @@ --source include/innodb_page_size.inc +--source include/have_sequence.inc --echo # --echo # MDEV-11369: Instant ADD COLUMN for InnoDB @@ -903,3 +904,36 @@ CREATE INDEX i1 ON t1(a) COMMENT 'comment1'; ALTER TABLE t1 DROP INDEX i1, ADD INDEX i1(a) COMMENT 'comment2', ALGORITHM=INSTANT; SHOW CREATE TABLE t1; DROP TABLE t1; + +--echo # +--echo # MDEV-25057 Assertion `n_fields < dtuple_get_n_fields(entry)' +--echo # failed in dtuple_convert_big_rec +--echo # +CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, + a CHAR(255) NOT NULL, + b CHAR(255) NOT NULL, c INT) ENGINE=InnoDB CHARSET utf32; +ALTER TABLE t1 DROP c; +INSERT INTO t1(a, b) SELECT '', '' FROM seq_1_to_16; +SELECT COUNT(*) FROM t1; +# Cleanup +DROP TABLE t1; + +--echo # +--echo # MDEV-25630 Rollback of instant operation adds wrong +--echo # column to secondary index +--echo # +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT, f4 INT, + PRIMARY KEY(f1, f4), + KEY(f2))ENGINE=InnoDB; +CREATE TABLE t2 (f1 INT, f2 INT, PRIMARY KEY(f1), + FOREIGN KEY fk (f2) REFERENCES t2(f1) + )ENGINE=InnoDB; + +ALTER TABLE t1 ADD f5 INT; +SET FOREIGN_KEY_CHECKS=0; +--error ER_FK_FAIL_ADD_SYSTEM +ALTER TABLE t1 DROP COLUMN f3, ADD FOREIGN KEY fk (f1) + REFERENCES x(x); +ALTER TABLE t1 DROP COLUMN f5; +SHOW CREATE TABLE t1; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/innodb/t/instant_alter_charset.test b/mysql-test/suite/innodb/t/instant_alter_charset.test index a5ddd49830c..1d444b88a7f 100644 --- a/mysql-test/suite/innodb/t/instant_alter_charset.test +++ b/mysql-test/suite/innodb/t/instant_alter_charset.test @@ -843,3 +843,17 @@ ALTER TABLE t1 MODIFY a VARCHAR(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; INSERT INTO t1 VALUES ('a'); DROP TABLE t1; + + +--echo # +--echo # MDEV-22775 [HY000][1553] Changing name of primary key column with foreign key constraint fails. +--echo # + +create table t1 (id int primary key) engine=innodb default charset=utf8; +create table t2 (input_id int primary key, id int not null, + key a (id), + constraint a foreign key (id) references t1 (id) +)engine=innodb default charset=utf8; +alter table t1 change id id2 int; +drop table t2; +drop table t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.combinations b/mysql-test/suite/innodb/t/instant_alter_debug.combinations new file mode 100644 index 00000000000..f3bc2cc0c25 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_debug.combinations @@ -0,0 +1,4 @@ +[redundant] +innodb_default_row_format=redundant +[dynamic] +innodb_default_row_format=dynamic diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index fe80de2ca51..b93b9dd8f1b 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -1,6 +1,7 @@ --source include/have_innodb.inc --source include/have_debug.inc --source include/have_debug_sync.inc +--source include/have_sequence.inc SET @save_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency=1; @@ -216,6 +217,15 @@ CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 SET a=0; ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 2, ADD COLUMN c INT; +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE c TEXT DEFAULT(SELECT CONCAT('ALTER TABLE t1 ADD (c', + GROUP_CONCAT(seq SEPARATOR ' INT, c'), ' INT), ALGORITHM=INSTANT;') FROM seq_1_to_130); + EXECUTE IMMEDIATE c; +END; +$$ +DELIMITER ;$$ + connection stop_purge; START TRANSACTION WITH CONSISTENT SNAPSHOT; @@ -233,6 +243,7 @@ connection default; SET DEBUG_SYNC = 'now WAIT_FOR copied'; let $wait_all_purged = 1; --source include/wait_all_purged.inc +let $wait_all_purged = 0; INSERT INTO t1 SET a=1; INSERT INTO t1 SET a=2,b=3,c=4; SET DEBUG_SYNC = 'now SIGNAL logged'; @@ -242,7 +253,7 @@ reap; connection default; SET DEBUG_SYNC = RESET; -SELECT * FROM t1; +SELECT a, b, c FROM t1; ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; connection stop_purge; START TRANSACTION WITH CONSISTENT SNAPSHOT; @@ -260,6 +271,7 @@ connection default; SET DEBUG_SYNC = 'now WAIT_FOR copied'; let $wait_all_purged = 1; --source include/wait_all_purged.inc +let $wait_all_purged = 0; INSERT INTO t1 SET a=1; INSERT INTO t1 SET a=2,c=4; SET DEBUG_SYNC = 'now SIGNAL logged'; @@ -270,7 +282,7 @@ UPDATE t1 SET b = b + 1 WHERE a = 2; connection default; SET DEBUG_SYNC = RESET; -SELECT * FROM t1; +SELECT a, b, c FROM t1; --echo # --echo # MDEV-15872 Crash in online ALTER TABLE...ADD PRIMARY KEY @@ -297,7 +309,7 @@ disconnect ddl; connection default; SET DEBUG_SYNC = RESET; -SELECT * FROM t1; +SELECT a, b, c, d FROM t1; DROP TABLE t1; --echo # @@ -326,44 +338,6 @@ SELECT * FROM t1; DROP TABLE t1; --echo # ---echo # MDEV-17899 Assertion failures on rollback of instant ADD/DROP ---echo # MDEV-18098 Crash after rollback of instant DROP COLUMN ---echo # - -SET @save_dbug = @@SESSION.debug_dbug; -SET debug_dbug='+d,ib_commit_inplace_fail_1'; -CREATE TABLE t1 (a int, b int) ENGINE=InnoDB; -INSERT INTO t1 VALUES (1,2); ---error ER_INTERNAL_ERROR -ALTER TABLE t1 DROP COLUMN b; ---error ER_INTERNAL_ERROR -ALTER TABLE t1 DROP COLUMN b; ---error ER_INTERNAL_ERROR -ALTER TABLE t1 ADD COLUMN c INT; -SELECT * FROM t1; -DROP TABLE t1; - -CREATE TABLE t1 (a int, b int) ENGINE=InnoDB; ---error ER_INTERNAL_ERROR -ALTER TABLE t1 ADD COLUMN c INT; -BEGIN; -INSERT INTO t1 VALUES(1, 1); -ROLLBACK; ---error ER_INTERNAL_ERROR -ALTER TABLE t1 DROP COLUMN b; -INSERT INTO t1 values (1,1); -SELECT * FROM t1; -DROP TABLE t1; - -SET debug_dbug = @save_dbug; - -SELECT variable_value-@old_instant instants -FROM information_schema.global_status -WHERE variable_name = 'innodb_instant_alter_column'; - -SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; - ---echo # --echo # MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col --echo # CREATE TABLE t1 (a TEXT) ENGINE = InnoDB ROW_FORMAT=REDUNDANT; @@ -414,3 +388,134 @@ SELECT * FROM t1; SET DEBUG_SYNC='RESET'; --disconnect con2 DROP TABLE t1; + +--echo # +--echo # MDEV-24653 Assertion block->page.id.page_no() == index->page failed +--echo # in innobase_add_instant_try() +--echo # + +SET @saved_limit = @@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug = 2; + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1),(2),(3),(4); +ALTER TABLE t1 ADD COLUMN b INT; +DELETE FROM t1; +--source include/wait_all_purged.inc +ALTER TABLE t1 ADD COLUMN c INT; + +SELECT * FROM t1; +DROP TABLE t1; +SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit; + +--echo # +--echo # MDEV-24796 Assertion page_has_next... failed +--echo # in btr_pcur_store_position() +--echo # + +CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(1),(2); +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +ALTER TABLE t1 ADD COLUMN d INT; +DELETE FROM t1; +--source include/wait_all_purged.inc +SELECT * FROM t1 WHERE c<>1 ORDER BY c DESC; +DROP TABLE t1; + +SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit; + +--echo # +--echo # MDEV-24620 ASAN heap-buffer-overflow in btr_pcur_restore_position() +--echo # + +CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +connect (stop_purge,localhost,root,,); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +ALTER TABLE t1 ADD c INT; +BEGIN; +DELETE FROM t1; + +connect (dml,localhost,root,,test); +SET DEBUG_SYNC='row_mysql_handle_errors SIGNAL s1 WAIT_FOR s2'; +send UPDATE t1 SET c=1; + +connection default; +SET DEBUG_SYNC='now WAIT_FOR s1'; +COMMIT; + +connection stop_purge; +COMMIT; +disconnect stop_purge; + +connection default; +--source include/wait_all_purged.inc +SET DEBUG_SYNC='now SIGNAL s2'; + +connection dml; +reap; +disconnect dml; + +connection default; +SET DEBUG_SYNC=RESET; +DROP TABLE t1; + +--echo # End of 10.3 tests + +--echo # +--echo # MDEV-17899 Assertion failures on rollback of instant ADD/DROP +--echo # MDEV-18098 Crash after rollback of instant DROP COLUMN +--echo # + +SET @save_dbug = @@SESSION.debug_dbug; +SET debug_dbug='+d,ib_commit_inplace_fail_1'; +CREATE TABLE t1 (a int, b int) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,2); +--error ER_INTERNAL_ERROR +ALTER TABLE t1 DROP COLUMN b; +--error ER_INTERNAL_ERROR +ALTER TABLE t1 DROP COLUMN b; +--error ER_INTERNAL_ERROR +ALTER TABLE t1 ADD COLUMN c INT; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a int, b int) ENGINE=InnoDB; +--error ER_INTERNAL_ERROR +ALTER TABLE t1 ADD COLUMN c INT; +BEGIN; +INSERT INTO t1 VALUES(1, 1); +ROLLBACK; +--error ER_INTERNAL_ERROR +ALTER TABLE t1 DROP COLUMN b; +INSERT INTO t1 values (1,1); +SELECT * FROM t1; +DROP TABLE t1; + +SET debug_dbug = @save_dbug; + +--echo # +--echo # MDEV-24512 Assertion failed in rec_is_metadata() +--echo # in btr_discard_only_page_on_level() +--echo # + +SET @save_limit= @@GLOBAL.innodb_limit_optimistic_insert_debug; +SET GLOBAL innodb_limit_optimistic_insert_debug=2; +CREATE TABLE t1 (c CHAR(1) UNIQUE) ENGINE=InnoDB; + +ALTER TABLE t1 ADD c2 INT NOT NULL DEFAULT 0 FIRST; +--error ER_DUP_ENTRY +INSERT INTO t1 (c) VALUES ('x'),('d'),('r'),('f'),('y'),('u'),('m'),('d'); +SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit; +SELECT * FROM t1; +DROP TABLE t1; + +--echo # End of 10.4 tests + +SET GLOBAL innodb_purge_rseg_truncate_frequency = @save_frequency; + +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; diff --git a/mysql-test/suite/innodb/t/mvcc_secondary.test b/mysql-test/suite/innodb/t/mvcc_secondary.test new file mode 100644 index 00000000000..93c91c40076 --- /dev/null +++ b/mysql-test/suite/innodb/t/mvcc_secondary.test @@ -0,0 +1,26 @@ +--source include/innodb_page_size_small.inc + +--echo # +--echo # MDEV-25459 MVCC read from index on CHAR or VARCHAR wrongly omits rows +--echo # + +CREATE TABLE t1 ( + pk int PRIMARY KEY, c varchar(255) UNIQUE, + d char(255), e varchar(255), f char(255), g char(255) +) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARACTER SET ucs2; + +INSERT INTO t1 VALUES +(1,REPEAT('c',248),REPEAT('a',106),REPEAT('b',220),REPEAT('x',14),''); + +BEGIN; +UPDATE t1 SET c=REPEAT('d',170); + +connect (con1,localhost,root,,); +SELECT pk FROM t1 FORCE INDEX (c); +connection default; +COMMIT; +connection con1; +SELECT pk FROM t1 FORCE INDEX (c); +disconnect con1; +connection default; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test index 8e3ddf95634..13c203b587b 100644 --- a/mysql-test/suite/innodb/t/temporary_table.test +++ b/mysql-test/suite/innodb/t/temporary_table.test @@ -502,3 +502,110 @@ SET FOREIGN_KEY_CHECKS = 0; --error ER_CANT_CREATE_TABLE CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB; + +CREATE TABLE t (c INT) ENGINE=InnoDB; +INSERT INTO t VALUES(0); +CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +INSERT INTO t2 SELECT * FROM t; +COMMIT; +DROP TABLE t, t2; + +CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t VALUES(0); +SAVEPOINT s; +INSERT INTO t VALUES(0,0); +COMMIT; +DROP TABLE t; + +CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t VALUES(0); +SAVEPOINT s; +INSERT INTO t VALUES(0,0); +ROLLBACK; +DROP TABLE t; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB; +SET SESSION tx_read_only=TRUE; +LOCK TABLE test.t READ; +SELECT * FROM t; +INSERT INTO t VALUES(0xADC3); +SET SESSION tx_read_only=FALSE; +DROP TABLE t; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 1); +START TRANSACTION READ ONLY; +UPDATE t1 SET b= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int, c varchar(255)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 1, repeat('a', 200)); +START TRANSACTION READ ONLY; +UPDATE t1 SET b= 2, c=repeat('a', 250); +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +ROLLBACK; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +DELETE FROM t1 WHERE a= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB; +INSERT INTO tmp () VALUES (),(); +SET TX_READ_ONLY= 1; +INSERT INTO tmp SELECT * FROM tmp; +SET TX_READ_ONLY= 0; +DROP TABLE tmp; + +SET sql_mode=''; +SET GLOBAL tx_read_only=TRUE; +CREATE TEMPORARY TABLE t (c INT); +SET SESSION tx_read_only=DEFAULT; +INSERT INTO t VALUES(1); +INSERT INTO t SELECT * FROM t; +SET SESSION tx_read_only=FALSE; +SET GLOBAL tx_read_only=OFF; +DROP TABLE t; + +CREATE TEMPORARY TABLE t(a INT); +SET SESSION tx_read_only=ON; +LOCK TABLE t READ; +SELECT COUNT(*)FROM t; +INSERT INTO t VALUES (0); +SET SESSION tx_read_only=OFF; +DROP TABLE t; + +CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB; +INSERT INTO t VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t SET a = NULL; +ROLLBACK; diff --git a/mysql-test/suite/innodb/t/truncate_foreign.test b/mysql-test/suite/innodb/t/truncate_foreign.test index d9d647e69f0..1c150e5db40 100644 --- a/mysql-test/suite/innodb/t/truncate_foreign.test +++ b/mysql-test/suite/innodb/t/truncate_foreign.test @@ -67,3 +67,16 @@ connection default; SET DEBUG_SYNC = RESET; DROP TABLE child, parent; + +--echo # +--echo # MDEV-24532 Table corruption ER_NO_SUCH_TABLE_IN_ENGINE or +--echo # ER_CRASHED_ON_USAGE after ALTER on table with foreign key +--echo # + +CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a)) ENGINE=InnoDB; +ALTER TABLE t1 ADD FOREIGN KEY (b) REFERENCES t1 (a) ON UPDATE CASCADE; +LOCK TABLE t1 WRITE; +TRUNCATE TABLE t1; +ALTER TABLE t1 ADD c INT; +UNLOCK TABLES; +DROP TABLE t1; |