diff options
Diffstat (limited to 'mysql-test/suite/innodb/t')
65 files changed, 3186 insertions, 107 deletions
diff --git a/mysql-test/suite/innodb/t/alter_algorithm2.test b/mysql-test/suite/innodb/t/alter_algorithm2.test index cf538e30196..cdece2398f0 100644 --- a/mysql-test/suite/innodb/t/alter_algorithm2.test +++ b/mysql-test/suite/innodb/t/alter_algorithm2.test @@ -45,15 +45,19 @@ DROP TABLE t1; DROP PROCEDURE p1; DROP PROCEDURE p2; +SET @save_allowed= @@GLOBAL.innodb_instant_alter_column_allowed; +SET GLOBAL innodb_instant_alter_column_allowed=never; + CREATE TABLE t1(id INT PRIMARY KEY, col1 INT UNSIGNED NOT NULL UNIQUE)ENGINE=InnoDB; INSERT INTO t1 VALUES(1,1),(2,2),(3,3); SET ALTER_ALGORITHM=INSTANT; ---error ER_ALTER_OPERATION_NOT_SUPPORTED +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP COLUMN col1; ---error ER_ALTER_OPERATION_NOT_SUPPORTED +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 DROP COLUMN col1, ALGORITHM=NOCOPY; ALTER TABLE t1 DROP COLUMN col1, ALGORITHM=DEFAULT; ALTER TABLE t1 DROP PRIMARY KEY, ALGORITHM=DEFAULT; DROP TABLE t1; --disable_info +SET GLOBAL innodb_instant_alter_column_allowed=@save_allowed; diff --git a/mysql-test/suite/innodb/t/alter_kill.test b/mysql-test/suite/innodb/t/alter_kill.test index daaac4bc0ee..2313d63db69 100644 --- a/mysql-test/suite/innodb/t/alter_kill.test +++ b/mysql-test/suite/innodb/t/alter_kill.test @@ -2,6 +2,7 @@ # The embedded server does not support restarting in mysql-test-run. -- source include/not_embedded.inc -- source include/no_valgrind_without_big.inc +-- source include/innodb_checksum_algorithm.inc let MYSQLD_DATADIR=`select @@datadir`; let PAGE_SIZE=`select @@innodb_page_size`; @@ -51,20 +52,29 @@ open(FILE, "+<$file") || die "Unable to open $file"; binmode FILE; my $ps= $ENV{PAGE_SIZE}; my $page; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS sysseek(FILE, 3*$ps, 0) || die "Unable to seek $file\n"; die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; substr($page,4,4)=pack("N",0xc001cafe); my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} sysseek(FILE, 3*$ps, 0) || die "Unable to rewind $file\n"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file"; EOF --- echo # Restart mysqld -- source include/start_mysqld.inc --error ER_NO_SUCH_TABLE_IN_ENGINE @@ -84,21 +94,29 @@ open(FILE, "+<$file") || die "Unable to open $file"; binmode FILE; my $ps= $ENV{PAGE_SIZE}; my $page; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS sysseek(FILE, 3*$ps, 0) || die "Unable to seek $file\n"; die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; substr($page,4,4)=pack("N",3); my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} sysseek(FILE, 3*$ps, 0) || die "Unable to rewind $file\n"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file"; EOF --- echo # Restart the server after uncorrupting the file. - -- source include/start_mysqld.inc INSERT INTO bug16720368 VALUES(9,1); diff --git a/mysql-test/suite/innodb/t/alter_not_null_debug.test b/mysql-test/suite/innodb/t/alter_not_null_debug.test index 058f0fc02a5..7a965fd413a 100644 --- a/mysql-test/suite/innodb/t/alter_not_null_debug.test +++ b/mysql-test/suite/innodb/t/alter_not_null_debug.test @@ -68,5 +68,32 @@ reap; --disable_info SELECT * FROM t1; DROP TABLE t1; -disconnect con1; SET DEBUG_SYNC='RESET'; + +--echo # +--echo # MDEV-21539 Assertion ...prtype... in row_log_table_apply_convert_mrec +--echo # +CREATE TABLE t1 (f VARCHAR(8) CHARACTER SET latin1 COLLATE latin1_swedish_ci) +ENGINE=InnoDB; + +connection con1; +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done'; +send ALTER TABLE t1 MODIFY f VARCHAR(256) COLLATE latin1_german2_ci NOT NULL; + +--echo # session default +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR scanned'; +INSERT INTO t1 VALUES('one'); +SET DEBUG_SYNC = 'now SIGNAL insert_done'; + +connection con1; +reap; +disconnect con1; +connection default; +SET DEBUG_SYNC=RESET; + +ALTER TABLE t1 CHANGE f eins VARCHAR(257) COLLATE latin1_german1_ci NOT NULL, +ALGORITHM=INSTANT; + +SELECT * FROM t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_rename_files.test b/mysql-test/suite/innodb/t/alter_rename_files.test index 3ed1cb5d9fa..27408320f7d 100644 --- a/mysql-test/suite/innodb/t/alter_rename_files.test +++ b/mysql-test/suite/innodb/t/alter_rename_files.test @@ -11,7 +11,7 @@ SET GLOBAL innodb_log_checkpoint_now=TRUE; # Start an ALTER TABLE and stop it before renaming the files SET DEBUG_SYNC='commit_cache_rebuild SIGNAL ready WAIT_FOR finish'; ---send ALTER TABLE t1 ADD PRIMARY KEY(x) +--send ALTER TABLE t1 FORCE; connect (con1,localhost,root,,); diff --git a/mysql-test/suite/innodb/t/alter_table.test b/mysql-test/suite/innodb/t/alter_table.test index 5050abdc087..aca70e61bc6 100644 --- a/mysql-test/suite/innodb/t/alter_table.test +++ b/mysql-test/suite/innodb/t/alter_table.test @@ -79,3 +79,15 @@ ALTER TABLE t ENGINE INNODB; --error ER_TABLESPACE_DISCARDED ALTER TABLE t FORCE; DROP TABLE t; + +# +# Check that innodb supports transactional=1 +# + +create table t1 (a int) transactional=1 engine=aria; +create table t2 (a int) transactional=1 engine=innodb; +show create table t1; +show create table t2; +alter table t1 engine=innodb; +alter table t1 add column b int; +drop table t1,t2; diff --git a/mysql-test/suite/innodb/t/alter_varchar_change.test b/mysql-test/suite/innodb/t/alter_varchar_change.test index 23928badb24..65dc38a3411 100644 --- a/mysql-test/suite/innodb/t/alter_varchar_change.test +++ b/mysql-test/suite/innodb/t/alter_varchar_change.test @@ -307,6 +307,30 @@ SHOW CREATE TABLE t1; DROP TABLE t1; CREATE TABLE t1(f1 INT NOT NULL, + f2 VARCHAR(128), + INDEX idx(f2(40)))ENGINE=InnoDB; + +CALL get_table_id("test/t1", @tbl_id); +ALTER TABLE t1 MODIFY f2 VARCHAR(300); +CALL get_table_id("test/t1", @tbl1_id); + +SELECT @tbl1_id = @tbl_id; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT NOT NULL, + f2 VARCHAR(128), + INDEX idx(f2(40)))ENGINE=InnoDB ROW_FORMAT=REDUNDANT; + +CALL get_table_id("test/t1", @tbl_id); +ALTER TABLE t1 MODIFY f2 VARCHAR(300); +CALL get_table_id("test/t1", @tbl1_id); + +SELECT @tbl1_id = @tbl_id; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1(f1 INT NOT NULL, f2 VARCHAR(100), INDEX idx(f2(40)))ENGINE=InnoDB; diff --git a/mysql-test/suite/innodb/t/blob-crash.test b/mysql-test/suite/innodb/t/blob-crash.test index b36c9318e27..beb500553ea 100644 --- a/mysql-test/suite/innodb/t/blob-crash.test +++ b/mysql-test/suite/innodb/t/blob-crash.test @@ -29,6 +29,7 @@ begin; INSERT INTO t1 (a, b) VALUES (6, REPEAT('a', 4*1024*1024)); let $shutdown_timeout=0; +let $restart_noprint=2; --source include/restart_mysqld.inc SELECT a, right(b, 50) FROM t1; diff --git a/mysql-test/suite/innodb/t/corrupted_during_recovery.test b/mysql-test/suite/innodb/t/corrupted_during_recovery.test index 67f2f2dde40..5f1de1bedf9 100644 --- a/mysql-test/suite/innodb/t/corrupted_during_recovery.test +++ b/mysql-test/suite/innodb/t/corrupted_during_recovery.test @@ -14,6 +14,7 @@ let INNODB_PAGE_SIZE=`select @@innodb_page_size`; CREATE TABLE t1(a BIGINT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); # Force a redo log checkpoint. +let $restart_noprint=2; --source include/restart_mysqld.inc --source ../include/no_checkpoint_start.inc CREATE TABLE t2(a BIGINT PRIMARY KEY) ENGINE=InnoDB; 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 8f7217bcf0c..1f0b0d56bc4 100644 --- a/mysql-test/suite/innodb/t/default_row_format_alter.test +++ b/mysql-test/suite/innodb/t/default_row_format_alter.test @@ -1,4 +1,5 @@ --source include/have_innodb.inc +--source include/innodb_row_format.inc SET @row_format = @@GLOBAL.innodb_default_row_format; @@ -95,4 +96,24 @@ ALTER TABLE t1 DROP INDEX k1; SHOW TABLE STATUS LIKE 't1'; DROP TABLE t1; +--echo # +--echo # MDEV-23295 Assertion fields[i].same(instant.fields[i]) failed +--echo # +SET GLOBAL innodb_default_row_format = @row_format; +CREATE TABLE t1 (a char(8)) ENGINE=InnoDB DEFAULT CHARSET utf8; +SET GLOBAL innodb_default_row_format= COMPACT; +ALTER TABLE t1 ADD b INT; +SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; +DROP TABLE t1; + +--echo # +--echo # MDEV-23345 Assertion not_redundant() == old.not_redundant() failed +--echo # +SET GLOBAL innodb_default_row_format = @row_format; +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, KEY(b)) ENGINE=InnoDB; +SET GLOBAL innodb_default_row_format= COMPACT; +ALTER TABLE t1 DROP b; +SELECT ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; +DROP TABLE t1; + SET GLOBAL innodb_default_row_format = @row_format; diff --git a/mysql-test/suite/innodb/t/doublewrite.combinations b/mysql-test/suite/innodb/t/doublewrite.combinations new file mode 100644 index 00000000000..4f52013f6fc --- /dev/null +++ b/mysql-test/suite/innodb/t/doublewrite.combinations @@ -0,0 +1,7 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 +--innodb-use-atomic-writes=0 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 +--innodb-use-atomic-writes=0 diff --git a/mysql-test/suite/innodb/t/doublewrite.test b/mysql-test/suite/innodb/t/doublewrite.test index 7c8c1180adb..62e8549f21b 100644 --- a/mysql-test/suite/innodb/t/doublewrite.test +++ b/mysql-test/suite/innodb/t/doublewrite.test @@ -19,11 +19,14 @@ call mtr.add_suppression("InnoDB: New log files created"); call mtr.add_suppression("InnoDB: Cannot create doublewrite buffer: the first file in innodb_data_file_path must be at least (3|6|12)M\\."); call mtr.add_suppression("InnoDB: Database creation was aborted"); call mtr.add_suppression("Plugin 'InnoDB' (init function returned error|registration as a STORAGE ENGINE failed)"); +call mtr.add_suppression("InnoDB: A bad Space ID was found in datafile.*"); +call mtr.add_suppression("InnoDB: Checksum mismatch in datafile: .*"); --enable_query_log --source include/restart_mysqld.inc let INNODB_PAGE_SIZE=`select @@innodb_page_size`; let MYSQLD_DATADIR=`select @@datadir`; +let ALGO=`select @@innodb_checksum_algorithm`; let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; show variables like 'innodb_doublewrite'; @@ -75,14 +78,22 @@ perl; use IO::Handle; do "$ENV{MTR_SUITE_DIR}/include/crc32.pl"; my $polynomial = 0x82f63b78; # CRC-32C +my $algo = $ENV{ALGO}; +die "Unsupported innodb_checksum_algorithm=$algo\n" unless $algo =~ /crc32/; my $fname= "$ENV{'MYSQLD_DATADIR'}test/t1.ibd"; my $page_size = $ENV{INNODB_PAGE_SIZE}; my $page; +do "$ENV{MTR_SUITE_DIR}/../innodb/include/crc32.pl"; open(FILE, "+<", $fname) or die; sysread(FILE, $page, $page_size)==$page_size||die "Unable to read $name\n"; +my $page1 = $page; +substr($page1, 34, 4) = pack("N", 0); +my $polynomial0 = 0x82f63b78; # CRC-32C +my $ck0 = mycrc32(substr($page1, 0, ($page_size-4)), 0, $polynomial0); +substr($page1, ($page_size - 4), 4) = pack("N", $ck0); sysseek(FILE, 0, 0)||die "Unable to seek $fname\n"; -die unless syswrite(FILE, chr(0) x $page_size, $page_size) == $page_size; +die unless syswrite(FILE, $page1, $page_size) == $page_size; close FILE; open(FILE, "+<", "$ENV{MYSQLD_DATADIR}ibdata1")||die "cannot open ibdata1\n"; @@ -105,12 +116,21 @@ for (my $d = $d1; $d < $d2 + 64; $d++) $badflags |= ($flags & 15 << 6) << 7; # PAGE_SSIZE substr ($_, 54, 4) = pack("N", $badflags); - # Replace the innodb_checksum_algorithm=crc32 checksum - my $ck= pack("N", - mycrc32(substr($_, 4, 22), 0, $polynomial) ^ - mycrc32(substr($_, 38, $page_size - 38 - 8), 0, $polynomial)); - substr ($_, 0, 4) = $ck; - substr ($_, $page_size - 8, 4) = $ck; + if ($algo =~ /full_crc32/) + { + my $ck = mycrc32(substr($_, 0, $page_size - 4), 0, $polynomial); + substr($_, $page_size - 4, 4) = pack("N", $ck); + } + else + { + # Replace the innodb_checksum_algorithm=crc32 checksum + my $ck= pack("N", + mycrc32(substr($_, 4, 22), 0, $polynomial) ^ + mycrc32(substr($_, 38, $page_size - 38 - 8), 0, + $polynomial)); + substr ($_, 0, 4) = $ck; + substr ($_, $page_size - 8, 4) = $ck; + } syswrite(FILE, $_, $page_size)==$page_size||die; close(FILE); exit 0; @@ -153,10 +173,12 @@ set global innodb_buf_flush_list_now = 1; perl; use IO::Handle; my $fname= "$ENV{'MYSQLD_DATADIR'}test/t1.ibd"; +my $page_size = $ENV{INNODB_PAGE_SIZE}; open(FILE, "+<", $fname) or die; -FILE->autoflush(1); -binmode FILE; -print FILE chr(0) x ($ENV{'INNODB_PAGE_SIZE'}/2); +sysread(FILE, $page, $page_size)==$page_size||die "Unable to read $name\n"; +substr($page, 28, 4) = pack("N", 1000); +sysseek(FILE, 0, 0)||die "Unable to seek $fname\n"; +die unless syswrite(FILE, $page, $page_size) == $page_size; close FILE; EOF diff --git a/mysql-test/suite/innodb/t/foreign-keys.test b/mysql-test/suite/innodb/t/foreign-keys.test index dda87359df9..b93f82c93ef 100644 --- a/mysql-test/suite/innodb/t/foreign-keys.test +++ b/mysql-test/suite/innodb/t/foreign-keys.test @@ -145,7 +145,6 @@ SET lock_wait_timeout=0; UPDATE t2 SET pk=10 WHERE pk=1; PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; DEALLOCATE PREPARE stmt; -FLUSH TABLE t2; SET debug_sync='now SIGNAL go'; connection default; diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test index 380d4520d87..18cd0fa15e4 100644 --- a/mysql-test/suite/innodb/t/foreign_key.test +++ b/mysql-test/suite/innodb/t/foreign_key.test @@ -784,4 +784,26 @@ DROP TABLE t1; --echo # End of 10.2 tests +# MDEV-21792 Server aborts upon attempt to create foreign key on spatial field +# Fail to create foreign key for spatial fields +--error ER_CANT_CREATE_TABLE +CREATE TABLE t1 (a GEOMETRY, INDEX(a(8)), + FOREIGN KEY (a) REFERENCES x (xx)) ENGINE=InnoDB; + +--echo # +--echo # MDEV-23675 Assertion `pos < table->n_def' in dict_table_get_nth_col +--echo # +CREATE TABLE t1 (pk int PRIMARY KEY, a INT, b INT, c INT, KEY(c), +FOREIGN KEY fx (b) REFERENCES t1 (c)) +ENGINE=InnoDB; +INSERT INTO t1 VALUES (1,0,10,10); +ALTER TABLE t1 DROP a, ALGORITHM=INSTANT; +SET FOREIGN_KEY_CHECKS= 0; +DROP INDEX fx ON t1; +INSERT INTO t1 VALUES (2,11,11); +DROP TABLE t1; +SET FOREIGN_KEY_CHECKS=DEFAULT; + +-- echo # End of 10.4 tests + --source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/foreign_key_debug.test b/mysql-test/suite/innodb/t/foreign_key_debug.test index 75d05801b5f..166cfb7c84e 100644 --- a/mysql-test/suite/innodb/t/foreign_key_debug.test +++ b/mysql-test/suite/innodb/t/foreign_key_debug.test @@ -54,12 +54,11 @@ send ALTER TABLE t1 ADD FOREIGN KEY(pk) REFERENCES t2(pk) ON UPDATE CASCADE; connect con1, localhost, root; SET debug_sync='now WAIT_FOR ready'; -SET lock_wait_timeout=1; # change to 0 in 10.3 +SET lock_wait_timeout=0; --error ER_LOCK_WAIT_TIMEOUT UPDATE t2 SET pk=10 WHERE pk=1; PREPARE stmt FROM 'UPDATE t2 SET pk=10 WHERE pk=1'; DEALLOCATE PREPARE stmt; -FLUSH TABLE t2; SET debug_sync='now SIGNAL go'; connection default; diff --git a/mysql-test/suite/innodb/t/full_crc32_import.opt b/mysql-test/suite/innodb/t/full_crc32_import.opt new file mode 100644 index 00000000000..ac859973988 --- /dev/null +++ b/mysql-test/suite/innodb/t/full_crc32_import.opt @@ -0,0 +1 @@ +--innodb_checksum_algorithm=full_crc32 diff --git a/mysql-test/suite/innodb/t/full_crc32_import.test b/mysql-test/suite/innodb/t/full_crc32_import.test new file mode 100644 index 00000000000..2c11a2423e6 --- /dev/null +++ b/mysql-test/suite/innodb/t/full_crc32_import.test @@ -0,0 +1,203 @@ +-- source include/have_innodb.inc +# This test is slow on buildbot. +--source include/big_test.inc + +FLUSH TABLES; + +let $MYSQLD_TMPDIR = `SELECT @@tmpdir`; +let $MYSQLD_DATADIR = `SELECT @@datadir`; + +--echo # Treating compact format as dynamic format after import stmt + +CREATE TABLE t1 +(a int AUTO_INCREMENT PRIMARY KEY, + b blob, + c blob, + KEY (b(200))) ENGINE=InnoDB ROW_FORMAT=COMPACT; + +INSERT INTO t1 (b, c) values (repeat("ab", 200), repeat("bc", 200)); +INSERT INTO t1 (b, c) values (repeat("bc", 200), repeat("cd", 200)); +INSERT INTO t1 (b, c) values (repeat("cd", 200), repeat("ef", 200)); +INSERT INTO t1 (b, c) values (repeat("de", 200), repeat("fg", 200)); +INSERT INTO t1 (b, c) values (repeat("ef", 200), repeat("gh", 200)); +INSERT INTO t1 (b, c) values (repeat("fg", 200), repeat("hi", 200)); +INSERT INTO t1 (b, c) values (repeat("gh", 200), repeat("ij", 200)); +INSERT INTO t1 (b, c) values (repeat("hi", 200), repeat("jk", 200)); +INSERT INTO t1 (b, c) values (repeat("ij", 200), repeat("kl", 200)); +INSERT INTO t1 (b, c) values (repeat("jk", 200), repeat("lm", 200)); +INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; +INSERT INTO t1 (b, c) SELECT b,c FROM t1 ORDER BY a; +SELECT COUNT(*) FROM t1; + +FLUSH TABLE t1 FOR EXPORT; +--echo # List before copying files +let MYSQLD_DATADIR =`SELECT @@datadir`; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF + +UNLOCK TABLES; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DISCARD TABLESPACE; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +--remove_file $MYSQLD_DATADIR/test/t1.cfg +--disable_warnings +ALTER TABLE t1 IMPORT TABLESPACE; +--enable_warnings +SHOW CREATE TABLE t1; +UPDATE t1 set b = repeat("de", 100) where b = repeat("cd", 200); +--replace_column 9 # +explain SELECT a FROM t1 where b = repeat("de", 100); +SELECT a FROM t1 where b = repeat("de", 100); +SELECT COUNT(*) FROM t1; +DELETE FROM t1; +--source include/wait_all_purged.inc +CHECK TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 +(c1 int AUTO_INCREMENT PRIMARY KEY, + c2 POINT NOT NULL, + c3 LINESTRING NOT NULL, + SPATIAL INDEX idx1(c2)) ENGINE=InnoDB ROW_FORMAT=COMPACT; + +INSERT INTO t1(c2,c3) VALUES( + ST_GeomFromText('POINT(10 10)'), + ST_GeomFromText('LINESTRING(5 5,20 20,30 30)')); + +INSERT INTO t1(c2,c3) VALUES( + ST_GeomFromText('POINT(20 20)'), + ST_GeomFromText('LINESTRING(5 15,20 10,30 20)')); + +INSERT INTO t1(c2,c3) VALUES( + ST_GeomFromText('POINT(30 30)'), + ST_GeomFromText('LINESTRING(10 5,20 24,30 32)')); + +INSERT INTO t1(c2,c3) VALUES( + ST_GeomFromText('POINT(40 40)'), + ST_GeomFromText('LINESTRING(15 5,25 20,35 30)')); + +INSERT INTO t1(c2,c3) VALUES( + ST_GeomFromText('POINT(50 10)'), + ST_GeomFromText('LINESTRING(15 15,24 10,31 20)')); + +INSERT INTO t1(c2,c3) VALUES( + ST_GeomFromText('POINT(60 50)'), + ST_GeomFromText('LINESTRING(10 15,20 44,35 32)')); + +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; +INSERT INTO t1(c2, c3) SELECT c2, c3 FROM t1; + +FLUSH TABLE t1 FOR EXPORT; +--echo # List before copying files +let MYSQLD_DATADIR =`SELECT @@datadir`; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF + +UNLOCK TABLES; +ALTER TABLE t1 ROW_FORMAT=DYNAMIC; +ALTER TABLE t1 DISCARD TABLESPACE; +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +--remove_file $MYSQLD_DATADIR/test/t1.cfg +--disable_warnings +ALTER TABLE t1 IMPORT TABLESPACE; +--enable_warnings +SHOW CREATE TABLE t1; +UPDATE t1 SET C2 = ST_GeomFromText('POINT(0 0)'); +SELECT COUNT(*) FROM t1; +DELETE FROM t1; +CHECK TABLE t1; +--source include/wait_all_purged.inc +DROP TABLE t1; + +SET @save_algo = @@GLOBAL.innodb_compression_algorithm; +--error 0,ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_compression_algorithm=2; +CREATE TABLE t1(a SERIAL) PAGE_COMPRESSED=1 ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); + +FLUSH TABLE t1 FOR EXPORT; +--echo # List before copying files +let MYSQLD_DATADIR =`SELECT @@datadir`; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF + +UNLOCK TABLES; +SET GLOBAL innodb_compression_algorithm=0; +ALTER TABLE t1 FORCE; +ALTER TABLE t1 DISCARD TABLESPACE; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; +INSERT INTO t1 VALUES(2); +SELECT * FROM t1; + + +--error 0,ER_WRONG_VALUE_FOR_VAR +SET GLOBAL innodb_compression_algorithm=3; +FLUSH TABLE t1 FOR EXPORT; +--echo # List before copying files +let MYSQLD_DATADIR =`SELECT @@datadir`; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_backup_tablespaces("test", "t1"); +EOF + +UNLOCK TABLES; +SET GLOBAL innodb_compression_algorithm=0; +ALTER TABLE t1 FORCE; +ALTER TABLE t1 DISCARD TABLESPACE; + +--list_files $MYSQLD_DATADIR/test +perl; +do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; +ib_discard_tablespaces("test", "t1"); +ib_restore_tablespaces("test", "t1"); +EOF + +ALTER TABLE t1 IMPORT TABLESPACE; +INSERT INTO t1 VALUES(3); +SELECT * FROM t1; +DROP TABLE t1; + +SET GLOBAL innodb_compression_algorithm=@save_algo; diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.combinations b/mysql-test/suite/innodb/t/ibuf_not_empty.combinations new file mode 100644 index 00000000000..729380593f3 --- /dev/null +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test index 43f09bf6828..a3f4ad9ac5c 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.test +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test @@ -61,15 +61,24 @@ open(FILE, "+<$file") || die "Unable to open $file"; binmode FILE; my $ps= $ENV{PAGE_SIZE}; my $page; -sysseek(FILE, $ps, 0) || die "Unable to seek $file\n"; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; # Clean the change buffer bitmap. substr($page,38,$ps - 38 - 8) = chr(0) x ($ps - 38 - 8); my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ - mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ + mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} sysseek(FILE, $ps, 0) || die "Unable to rewind $file\n"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file"; @@ -93,12 +102,21 @@ my $ps= $ENV{PAGE_SIZE}; my $pages=5; my $page; die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS substr($page,46,4)=pack("N", $pages); my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ - mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ + mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} sysseek(FILE, 0, 0) || die "Unable to rewind $file\n"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; truncate(FILE, $ps * $pages); diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test index df2cd1707dc..f72935ebc3c 100644 --- a/mysql-test/suite/innodb/t/innodb-alter.test +++ b/mysql-test/suite/innodb/t/innodb-alter.test @@ -207,7 +207,7 @@ CREATE TABLE tu ( ) ENGINE=InnoDB; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -# Instant ADD COLUMN (adding after the visible FTS_DOC_ID) +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE tu ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; DROP TABLE tu; @@ -218,7 +218,7 @@ CREATE TABLE tv ( ) ENGINE=InnoDB; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL FIRST, LOCK=NONE; -# Instant ADD COLUMN (adding after the visible FTS_DOC_ID) +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE tv ADD COLUMN c CHAR(1) NOT NULL, LOCK=NONE; DROP TABLE tv; diff --git a/mysql-test/suite/innodb/t/innodb-index-online.test b/mysql-test/suite/innodb/t/innodb-index-online.test index 4cdbdb7c584..5e21fa896a4 100644 --- a/mysql-test/suite/innodb/t/innodb-index-online.test +++ b/mysql-test/suite/innodb/t/innodb-index-online.test @@ -225,10 +225,7 @@ SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2e_created WAIT_FOR dml2_done'; # Ensure that the ALTER TABLE will be executed even with some concurrent DML. SET lock_wait_timeout = 10; --send -# FIXME: MDEV-13668 -#ALTER TABLE t1 CHANGE c2 c22 INT, DROP INDEX c2d, ADD INDEX c2e(c22), -ALTER TABLE t1 DROP INDEX c2d, ADD INDEX c2e(c2), -ALGORITHM = INPLACE; +ALTER TABLE t1 CHANGE c2 c22 INT, DROP INDEX c2d, ADD INDEX c2e(c22, c3(10)), ALGORITHM = NOCOPY; # Generate some log (delete-mark, delete-unmark, insert etc.) # while the index creation is blocked. Some of this may run diff --git a/mysql-test/suite/innodb/t/innodb-index.test b/mysql-test/suite/innodb/t/innodb-index.test index 474b0e08935..9350672bee9 100644 --- a/mysql-test/suite/innodb/t/innodb-index.test +++ b/mysql-test/suite/innodb/t/innodb-index.test @@ -1159,7 +1159,9 @@ AND support IN ('YES', 'DEFAULT', 'ENABLED'); if ($have_debug) { # Initiate shutdown in order to issue a redo log checkpoint and to discard # the redo log record that was emitted due to '+d,fil_names_write_bogus'. +--let $restart_noprint=2 --source include/restart_mysqld.inc +--let $restart_noprint=0 } SELECT * FROM t1; diff --git a/mysql-test/suite/innodb/t/innodb-rollback.test b/mysql-test/suite/innodb/t/innodb-rollback.test new file mode 100644 index 00000000000..1d3e05b021f --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-rollback.test @@ -0,0 +1,17 @@ +--source include/have_innodb.inc + +# +# MDEV-18632: wsrep_is_wsrep_xid: Conditional jump or move depends on uninitialised value +# +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +--connect (con1,localhost,root,,test) +ALTER TABLE t1; +--connect (con2,localhost,root,,test) +--error ER_SP_DOES_NOT_EXIST +SELECT f() FROM t1; + +# Cleanup +--disconnect con2 +--disconnect con1 +--connection default +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/innodb-wl5522-debug.test b/mysql-test/suite/innodb/t/innodb-wl5522-debug.test index b2809aa0669..7159b164065 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5522-debug.test +++ b/mysql-test/suite/innodb/t/innodb-wl5522-debug.test @@ -17,6 +17,7 @@ --source include/default_charset.inc --source include/have_sequence.inc +let $restart_noprint=2; call mtr.add_suppression("InnoDB: Operating system error number .* in a file operation."); call mtr.add_suppression("InnoDB: The error means the system cannot find the path specified."); call mtr.add_suppression("InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them."); @@ -1013,20 +1014,6 @@ do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; ib_restore_tablespaces("test", "t1"); EOF -# Test failure after importing the cluster index -SET SESSION debug_dbug="+d,ib_import_set_max_rowid_failure"; - ---error ER_NOT_KEYFILE -ALTER TABLE t1 IMPORT TABLESPACE; - -SET SESSION debug_dbug=@saved_debug_dbug; - -# Left over from the failed IMPORT -perl; -do "$ENV{MTR_SUITE_DIR}/include/innodb-util.pl"; -ib_unlink_tablespace("test", "t1"); -EOF - DROP TABLE t1; --disable_query_log diff --git a/mysql-test/suite/innodb/t/innodb-wl5522.test b/mysql-test/suite/innodb/t/innodb-wl5522.test index 2b836bfc985..0b1c7d216b2 100644 --- a/mysql-test/suite/innodb/t/innodb-wl5522.test +++ b/mysql-test/suite/innodb/t/innodb-wl5522.test @@ -3,6 +3,7 @@ -- source include/have_innodb.inc -- source include/have_sequence.inc +-- source include/innodb_checksum_algorithm.inc call mtr.add_suppression("InnoDB: Unable to import tablespace .* because it already exists. Please DISCARD the tablespace before IMPORT\\."); call mtr.add_suppression("Index for table 't2' is corrupt; try to repair it"); @@ -10,6 +11,7 @@ FLUSH TABLES; let $MYSQLD_TMPDIR = `SELECT @@tmpdir`; let $MYSQLD_DATADIR = `SELECT @@datadir`; +let $checksum_algorithm = `SELECT @@innodb_checksum_algorithm`; CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, @@ -78,8 +80,19 @@ ALTER TABLE t2 DISCARD TABLESPACE; --error ER_TABLE_SCHEMA_MISMATCH ALTER TABLE t2 IMPORT TABLESPACE; --remove_file $MYSQLD_DATADIR/test/t2.cfg + +let $error_code = ER_TABLE_SCHEMA_MISMATCH; + +if ($checksum_algorithm == "full_crc32") { + let $error_code = 0; +} + +if ($checksum_algorithm == "strict_full_crc32") { + let $error_code = 0; +} + --replace_regex /(FSP_SPACE_FLAGS=0x)[0-9a-f]+(,.*0x)[0-9a-f]+(.*)/\1*\2*\3/ ---error ER_TABLE_SCHEMA_MISMATCH +--error $error_code ALTER TABLE t2 IMPORT TABLESPACE; DROP TABLE t2; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 37708a4536d..a81e6c3f900 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -1321,8 +1321,7 @@ drop table t1; # Test for testable InnoDB status variables. This test # uses previous ones(pages_created, rows_deleted, ...). ---replace_result 511 ok 512 ok 2047 ok 513 ok 514 ok 515 ok -SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; +SELECT IF(variable_value BETWEEN 488 AND 512, 'OK', variable_value) FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_buffer_pool_pages_total'; SELECT variable_value FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_page_size'; SELECT variable_value - @innodb_rows_deleted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_deleted'; SELECT variable_value - @innodb_rows_inserted_orig FROM information_schema.global_status WHERE LOWER(variable_name) = 'innodb_rows_inserted'; diff --git a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test index d1e17c7622a..d770e719601 100644 --- a/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test +++ b/mysql-test/suite/innodb/t/innodb_buffer_pool_resize_debug.test @@ -80,6 +80,7 @@ select @@global.innodb_adaptive_hash_index; connection con2; # - analyze table +set use_stat_tables=never; --send analyze table t2 connection con3; diff --git a/mysql-test/suite/innodb/t/innodb_bug68148.test b/mysql-test/suite/innodb/t/innodb_bug68148.test index 2741c3cba3d..ab4e0311656 100644 --- a/mysql-test/suite/innodb/t/innodb_bug68148.test +++ b/mysql-test/suite/innodb/t/innodb_bug68148.test @@ -28,7 +28,6 @@ SET FOREIGN_KEY_CHECKS=0; DROP INDEX `idx_1` ON `main`; SHOW TABLES; ---echo # restart and see if we can still access the main table --source include/restart_mysqld.inc ALTER TABLE `main` ADD INDEX `idx_1` (`ref_id1`); diff --git a/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test b/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test index 83a12431802..34c9336e517 100644 --- a/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test +++ b/mysql-test/suite/innodb/t/innodb_bulk_create_index_debug.test @@ -5,6 +5,7 @@ # Test Restart & Crash Recovery. -- source include/big_test.inc -- source include/innodb_page_size_small.inc +let $restart_noprint=2; # Test Row Format: REDUNDANT. let $row_format = REDUNDANT; diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index 23bba81c6bd..d495186db25 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -850,6 +850,7 @@ set @optimizer_switch_saved=@@optimizer_switch; SET SESSION optimizer_switch='derived_merge=off'; SET SESSION sort_buffer_size = 1024*36; +--replace_column 9 # EXPLAIN SELECT COUNT(*) FROM (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY) diff --git a/mysql-test/suite/innodb/t/innodb_stats.test b/mysql-test/suite/innodb/t/innodb_stats.test index b9f71f8fa6f..09515ec9720 100644 --- a/mysql-test/suite/innodb/t/innodb_stats.test +++ b/mysql-test/suite/innodb/t/innodb_stats.test @@ -9,6 +9,9 @@ DROP TABLE IF EXISTS test_innodb_stats; +set @save_use_stat_tables= @@use_stat_tables; +set @@use_stat_tables= COMPLEMENTARY; + CREATE TABLE test_innodb_stats ( a INT, KEY a_key (a) @@ -59,3 +62,4 @@ CREATE TABLE test_innodb_stats ( -- disable_query_log DROP TABLE test_innodb_stats; +set @@use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/suite/innodb/t/innodb_stats_fetch.test b/mysql-test/suite/innodb/t/innodb_stats_fetch.test index 8544509ccad..549ad65feff 100644 --- a/mysql-test/suite/innodb/t/innodb_stats_fetch.test +++ b/mysql-test/suite/innodb/t/innodb_stats_fetch.test @@ -11,6 +11,9 @@ -- vertical_results +set @save_use_stat_tables = @@use_stat_tables; +set @@use_stat_tables = COMPLEMENTARY; + CREATE TABLE test_ps_fetch (a INT, b INT, c INT, d INT, PRIMARY KEY (a, b), INDEX idx (c, d)) ENGINE=INNODB STATS_PERSISTENT=1; @@ -77,3 +80,4 @@ table_rows, avg_row_length, max_data_length, index_length FROM information_schema.tables WHERE table_name = 'test_ps_fetch'; DROP TABLE test_ps_fetch; +set @@use_stat_tables = @save_use_stat_tables; diff --git a/mysql-test/suite/innodb/t/instant_alter.test b/mysql-test/suite/innodb/t/instant_alter.test index 6d5c4e96d26..83dca4cb5a6 100644 --- a/mysql-test/suite/innodb/t/instant_alter.test +++ b/mysql-test/suite/innodb/t/instant_alter.test @@ -5,7 +5,7 @@ --echo # SET @saved_allowance = @@GLOBAL.innodb_instant_alter_column_allowed; -SET GLOBAL innodb_instant_alter_column_allowed = add_last; +SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; call mtr.add_suppression("Cannot add field `.*` in table `test`.`.*` because after adding it, the row size is"); @@ -200,9 +200,19 @@ SELECT clust_index_size FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS WHERE name = 'test/t2'; connection default; +if ($redundant_4k) +{ +--error ER_TOO_BIG_ROWSIZE +ALTER TABLE t2 DROP p; +SET innodb_strict_mode = OFF; +} --enable_info ALTER TABLE t2 DROP p; --disable_info +if ($redundant_4k) +{ +SET innodb_strict_mode = ON; +} SELECT * FROM t2; # datetime @@ -426,15 +436,470 @@ ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT; ALTER TABLE t1 MODIFY a INT DEFAULT 0; --disable_info ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; -DROP TABLE t1; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +SET GLOBAL innodb_instant_alter_column_allowed = add_last; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +SET GLOBAL innodb_instant_alter_column_allowed = add_drop_reorder; +ALTER TABLE t1 MODIFY b TEXT FIRST, ALGORITHM=INSTANT; SET GLOBAL innodb_instant_alter_column_allowed = add_last; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 MODIFY a INT DEFAULT 1, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ADD d TEXT AFTER a, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 MODIFY a INT DEFAULT 0, ALGORITHM=INSTANT; +--enable_info +ALTER TABLE t1 MODIFY a INT DEFAULT 0; +--disable_info +ALTER TABLE t1 MODIFY a INT DEFAULT NULL, ALGORITHM=INSTANT; +DROP TABLE t1; +SET GLOBAL innodb_instant_alter_column_allowed = DEFAULT; + +# MDEV-15562 Instant DROP/ADD/reorder columns + +eval CREATE TABLE t1 (a INT, b INT UNIQUE) $engine; +INSERT INTO t1 (a) VALUES (NULL), (NULL); +ALTER TABLE t1 DROP a, ADD COLUMN a INT; +DELETE FROM t1; +BEGIN;INSERT INTO t1 SET a=NULL;ROLLBACK; +DELETE FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (a INT PRIMARY KEY, t VARCHAR(33101) NOT NULL) $engine; +INSERT INTO t1 VALUES(347,''); +ALTER TABLE t1 DROP COLUMN t, ALGORITHM=INSTANT; +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (a INT) $engine; +INSERT INTO t1() VALUES(); +ALTER TABLE t1 ADD COLUMN b INT FIRST, ADD COLUMN c INT AFTER b; +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (t TEXT) $engine; +SET @t=REPEAT('x',@@innodb_page_size / 2); +INSERT INTO t1 VALUES (@t),(@t),(@t),(@t),(@t),(@t),(NULL),(@t),(@t),(@t),(@t); +ALTER TABLE t1 ADD COLUMN a INT FIRST; +UPDATE t1 SET a = 0; +DROP TABLE t1; + +eval CREATE TABLE t1 (t TEXT) $engine; +INSERT INTO t1 SET t = @x; +ALTER TABLE t1 DROP COLUMN t, ADD COLUMN i INT NOT NULL DEFAULT 1; +ALTER TABLE t1 ADD COLUMN t TEXT; +SELECT * FROM t1; +DROP TABLE t1; + +# +# MDEV-17735 Assertion failure in row_parse_int() on first ADD/DROP COLUMN +# when an AUTO_INCREMENT column is not in PRIMARY KEY +# +eval CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, KEY(a)) $engine; +INSERT INTO t1 SET a=NULL; +ALTER TABLE t1 DROP COLUMN b; +ALTER TABLE t1 ADD COLUMN c INT NOT NULL DEFAULT 42; +INSERT INTO t1 SET a=NULL; +UPDATE t1 SET a=a+2; +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (i INT) $engine; +INSERT INTO t1 SET i=1; +ALTER TABLE t1 ADD COLUMN b BIT FIRST; +ALTER TABLE t1 ADD COLUMN v INT AS (i) VIRTUAL; +SELECT * FROM t1; +DROP TABLE t1; + +# MDEV-17721 Corrupted data dictionary after instant DROP COLUMN +eval CREATE TABLE t1 (ts TIMESTAMP) $engine; +ALTER TABLE t1 ADD COLUMN f VARCHAR(8), ADD COLUMN dt DATETIME; +ALTER TABLE t1 ADD COLUMN b BIT, DROP COLUMN f, ADD COLUMN t TIME FIRST; +ALTER TABLE t1 ADD COLUMN ts2 TIMESTAMP; +DROP TABLE t1; + +eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) $engine; +INSERT INTO t1 VALUES (4,4,4); +ALTER TABLE t1 DROP f1, DROP f2, ADD f4 INT, ADD f5 INT; +DELETE FROM t1; +ALTER TABLE t1 DROP COLUMN f4; +DROP TABLE t1; + +eval CREATE TABLE t1 (f1 INT, f2 INT, f3 INT) $engine; +ALTER TABLE t1 DROP f2, ADD COLUMN f4 INT; +ALTER TABLE t1 DROP f4; +ALTER TABLE t1 DROP f1; +DROP TABLE t1; + +# MDEV-17901 Crash after instant DROP COLUMN of AUTO_INCREMENT column +eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) $engine; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 () VALUES (),(); +SELECT * FROM t1; +# Adding AUTO_INCREMENT column will always require rebuild. +ALTER TABLE t1 ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST, ADD KEY(id); +SELECT * FROM t1; +DROP TABLE t1; + +# MDEV-18149 Crash after DROP COLUMN of AUTO_INCREMENT column on nonempty table +eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, f INT, KEY(id)) $engine; +INSERT INTO t1 SET f=NULL; +ALTER TABLE t1 DROP COLUMN id; +INSERT INTO t1 SET f=NULL; +SELECT * FROM t1; +DROP TABLE t1; + +# MDEV-18076/MDEV-18077 Crash on AUTO_INCREMENT column after instant DROP +eval CREATE TABLE t1(f INT, k INT NOT NULL AUTO_INCREMENT, KEY(k)) $engine; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 VALUES (1); +DROP TABLE t1; + +eval CREATE TABLE t1(pk INT PRIMARY KEY, f INT, k INT AUTO_INCREMENT, KEY(k)) +$engine; +ALTER TABLE t1 DROP COLUMN f; +INSERT INTO t1 (pk) VALUES (1); +DROP TABLE t1; + +# MDEV-17763 Assertion `len == 20U' failed in rec_convert_dtuple_to_rec_comp +# upon DROP COLUMN +eval CREATE TABLE t1 ( + pk INT PRIMARY KEY, + f1 INT, + f2 CHAR(255), + f3 BIGINT, + f4 INT, + f5 CHAR(255), + f6 CHAR(255), + f7 CHAR(255) NOT NULL, + f8 INT, + f9 CHAR(10) +) $engine; + +INSERT INTO t1 VALUES + (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'), + (2, 2, 'b', 2, 2, 'b', 'b', 'b', 2, 'b'), + (3, 3, 'c', 3, 3, 'c', 'c', 'c', 3, 'c'), + (4, 4, 'd', 4, 4, 'd', 'd', 'd', 4, 'd'), + (5, 5, 'e', 5, 5, 'e', 'e', 'e', 5, 'e'), + (6, 6, 'f', 6, 6, 'f', 'f', 'f', 6, 'f'), + (7, 7, 'g', 7, 7, 'g', 'g', 'g', 7, 'g'), + (8, 8, 'h', 8, 8, 'h', 'h', 'h', 8, 'h'), + (9, 9, 'i', 9, 9, 'i', 'i', 'i', 9, 'i'), + (10, 0, 'j', 0, 0, 'j', 'j', 'j', 0, 'j'), + (11, 1, 'k', 1, 1, 'k', 'k', 'k', 1, 'k'), + (12, 2, 'l', 2, 2, 'l', 'l', 'l', 2, 'l'), + (13, 3, 'm', 3, 3, 'm', 'm', 'm', 3, 'm'), + (14, 4, 'n', 4, 4, 'n', 'n', 'n', 4, 'n'), + (15, 5, 'o', 5, 5, 'o', 'o', 'o', 5, 'o'); + +DELETE FROM t1 WHERE pk=1; +--source include/wait_all_purged.inc +INSERT INTO t1 VALUES + (1, 1, 'a', 1, 1, 'a', 'a', 'a', 1, 'a'); +ALTER TABLE t1 DROP COLUMN f1; +DROP TABLE t1; + +# MDEV-17820 Assertion failures on DROP COLUMN +eval CREATE TABLE t1 ( + pk INT PRIMARY KEY, + f1 INT, f2 CHAR(32) NOT NULL, + f3 INT NOT NULL, f4 INT NOT NULL, f5 INT, f6 CHAR(32) NOT NULL, + f7 CHAR(32), f8 CHAR(32) +) $engine; + +INSERT INTO t1 VALUES + (1,9,'',2,88,88,'','',''),(2,48,'',8,68,92,'','',''), + (3,41,'',56,84,37,'','',''),(4,NULL,'',6,6,NULL,'','',''), + (5,52,'',37,44,20,'','',''),(6,44,'',53,4,NULL,'','',''), + (7,24,'',54,8,54,'','',''),(8,80,'',3,52,20,'','',''), + (9,71,'',34,32,NULL,'','',''),(10,14,'',6,64,88,'','',''), + (11,48,'',8,25,42,'','',''),(12,16,'',8,7,NULL,'','',''), + (13,NULL,'',22,0,95,'','',''),(14,4,'',72,48,NULL,'','',''), + (15,4,'',5,64,2,'','',''),(16,NULL,'',9,40,30,'','',''), + (17,92,'',48,2,NULL,'','',''),(18,36,'',48,51,7,'','',''), + (19,NULL,'',80,96,NULL,'','',''),(20,96,'',9,80,NULL,'','',''), + (21,50,'',16,40,NULL,'','',''),(22,NULL,'',7,84,8,'','',''), + (23,28,'',93,80,NULL,'','',''),(24,31,'',40,38,NULL,'','',''), + (25,85,'',8,5,88,'','',''),(26,66,'',8,32,4,'','',''), + (51,52,'',6,92,15,'','',''),(52,77,'',24,24,28,'','',''), + (53,8,'',75,31,NULL,'','',''),(54,48,'',5,8,1,'','',''), + (55,90,'',56,12,5,'','',''),(56,92,'',4,9,88,'','',''), + (57,83,'',23,40,72,'','',''),(58,7,'',4,40,32,'','',''), + (59,28,'',2,3,32,'','',''),(60,16,'',80,4,NULL,'','',''), + (61,44,'',88,24,NULL,'','',''),(62,4,'',5,25,3,'','',''), + (63,NULL,'',7,24,76,'','',''),(64,0,'',13,40,73,'','',''), + (101,NULL,'',1,49,75,'','',''),(102,34,'',10,17,20,'','',''), + (103,8,'',2,2,NULL,'','',''),(104,12,'',44,48,52,'','',''), + (105,8,'',4,19,38,'','',''),(106,20,'',6,80,9,'','',''), + (107,72,'',72,16,56,'','',''),(108,76,'',98,24,21,'','',''), + (109,67,'',16,91,NULL,'','',''),(110,72,'',72,3,48,'','',''), + (151,8,'',3,86,NULL,'','',''),(152,NULL,'',52,72,0,'','',''), + (153,NULL,'',46,30,92,'','',''),(154,80,'',1,40,48,'','',''), + (155,24,'',68,68,8,'','',''),(156,85,'',85,72,60,'','',''), + (157,7,'',7,12,6,'','',''),(158,NULL,'',48,48,80,'','',''), + (159,12,'',0,36,0,'','',''),(160,2,'',6,52,NULL,'','',''), + (201,0,'',1,3,NULL,'','',''),(202,NULL,'',3,53,14,'','',''), + (203,84,'',6,20,NULL,'','',''),(204,38,'',25,13,88,'','',''), + (205,1,'',2,69,5,'','',''),(206,7,'',60,22,NULL,'','',''), + (207,NULL,'',5,4,NULL,'','',''),(251,7,'',0,4,40,'','',''), + (252,4,'',16,8,NULL,'','',''),(253,14,'',60,12,99,'','',''), + (254,84,'',68,16,5,'','',''),(255,3,'',70,36,61,'','',''), + (256,7,'',18,48,NULL,'','',''),(257,NULL,'',68,53,NULL,'','',''), + (258,29,'',52,16,64,'','',''),(259,NULL,'',80,92,40,'','',''), + (301,68,'',1,48,48,'','',''),(302,2,'',1,1,32,'','',''), + (303,44,'',60,96,16,'','',''),(304,32,'',52,64,32,'','',''), + (305,88,'',37,72,NULL,'','',''),(306,5,'',35,60,20,'','',''), + (307,35,'',4,48,NULL,'','',''),(308,4,'',92,44,80,'','',''), + (351,48,'',60,4,40,'','',''),(352,7,'',9,61,13,'','',''), + (353,0,'',5,93,53,'','',''),(354,7,'',1,20,NULL,'','',''), + (355,84,'',5,48,96,'','',''),(356,NULL,'',39,92,36,'','',''), + (357,88,'',9,76,44,'','',''),(358,66,'',34,67,80,'','',''), + (359,8,'',8,52,NULL,'','',''),(360,3,'',53,83,NULL,'','',''), + (361,23,'',44,9,48,'','',''),(362,4,'',0,54,48,'','',''), + (363,75,'',66,76,52,'','',''); + +ALTER TABLE t1 ADD COLUMN x VARCHAR(255) DEFAULT ' foobar '; +UPDATE t1 SET f1 = 0; +ALTER TABLE t1 DROP COLUMN x; +DROP TABLE t1; + +eval CREATE TABLE t1 (f1 VARCHAR(1), f2 VARCHAR(2)) $engine; +ALTER TABLE t1 MODIFY f2 VARCHAR (8) FIRST; +DROP TABLE t1; + +# MDEV-18035 Failing assertion on DELETE +eval CREATE TABLE t1 (a INT UNIQUE, b INT UNIQUE, PRIMARY KEY(a,b)) $engine; +ALTER TABLE t1 DROP PRIMARY KEY; +ALTER TABLE t1 CHANGE COLUMN a a INT; +DELETE FROM t1 WHERE a = NULL OR a IS NULL; +DROP TABLE t1; + +# MDEV-18048 Failing assertion on ALTER +eval CREATE TABLE t1 (a INT, b INT, c INT NOT NULL, d INT, +e INT, f INT, g INT, h INT, j INT) $engine; +ALTER TABLE t1 MODIFY COLUMN c INT, MODIFY COLUMN a INT AFTER b; +DROP TABLE t1; + +# MDEV-18649 Failing assertion on ALTER for ROW_FORMAT=REDUNDANT +eval CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) $engine; +INSERT INTO t1 VALUES (0,0); +ALTER TABLE t1 MODIFY a INT AFTER b; +# Exploit MDEV-17468 to force the table definition to be reloaded +ALTER TABLE t1 ADD COLUMN v INT AS (a) VIRTUAL; +ALTER TABLE t1 MODIFY b INT NOT NULL AFTER a; +DROP TABLE t1; + +# MDEV-18033/MDEV-18034 Failing assertion on ALTER +eval CREATE TABLE t1 (a INT NOT NULL) $engine; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 ADD COLUMN b INT; +ALTER TABLE t1 MODIFY COLUMN a INT NULL; +DROP TABLE t1; + +# MDEV-18160/MDEV-18162 Failing assertion on ALTER +eval CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT) $engine; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; + +eval CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT) $engine; +INSERT INTO t1 SET a=1; +ALTER TABLE t1 DROP c; +ALTER TABLE t1 DROP b, ADD v INT AS (a); +DROP TABLE t1; + +# MDEV-18218 Assertion `0' failed in btr_page_reorganize_low upon DROP COLUMN +eval CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, b BLOB NOT NULL) $engine; +INSERT INTO t1 VALUES (1,10,REPEAT('foobar',2000)); +ALTER TABLE t1 DROP COLUMN b; +INSERT INTO t1 VALUES (2,20); +# this evicts and reloads the table definition until MDEV-17468 is fixed +ALTER TABLE t1 ADD COLUMN vpk INT AS (pk); +# this would load wrong metadata from the previous DROP COLUMN b, causing a crash +ALTER TABLE t1 DROP COLUMN i; +DROP TABLE t1; + +# MDEV-18315 Assertion instant.fields[i].col->same_format(*fields[i].col) +# failed in dict_index_t::instant_add_field +eval CREATE TABLE t1 (a INT, b INT) $engine; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD f DATE AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; + +# MDEV-18316 Assertion is_added() failed in dict_col_t::instant_value +eval CREATE TABLE t1 (a INT, b INT) $engine; +INSERT INTO t1 VALUES (1,1); +ALTER TABLE t1 ADD COLUMN f INT AFTER a; +ALTER TABLE t1 DROP b, DROP f; +DROP TABLE t1; + +# MDEV-18579 Assertion !ctx->online || num_fts_index == 0 +eval CREATE TABLE t1(t TEXT NOT NULL, FULLTEXT(t)) $engine; +ALTER TABLE t1 MODIFY COLUMN t TEXT; +DROP TABLE t1; + +# MDEV-18598 Assertions and wrong results after MDEV-15563 extending INT +eval CREATE TABLE t1 (f TINYINT, g SMALLINT UNSIGNED) $engine; +INSERT INTO t1 VALUES(127,6502),(-128,33101); +--enable_info +ALTER TABLE t1 MODIFY f SMALLINT DEFAULT 12345, +MODIFY g BIGINT UNSIGNED DEFAULT 1234567; +--disable_info +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (f BIT(8)) $engine; +INSERT INTO t1 VALUES (b'10000000'),(b'00000001'); +--enable_info +ALTER TABLE t1 MODIFY f BIT(16); +--disable_info +INSERT INTO t1 VALUES (b'1000000010101111'),(b'10000000'); +SELECT HEX(f) FROM t1; +--error ER_WARN_DATA_OUT_OF_RANGE +ALTER TABLE t1 MODIFY f SMALLINT; +--enable_info +ALTER TABLE t1 MODIFY f SMALLINT UNSIGNED; +--disable_info +SELECT * FROM t1; +--error ER_DATA_TOO_LONG +ALTER TABLE t1 MODIFY f BIT; +--error ER_DATA_TOO_LONG +ALTER TABLE t1 MODIFY f BIT(15); +DELETE FROM t1 LIMIT 3; +--enable_info +ALTER TABLE t1 MODIFY f BIT(15); +ALTER TABLE t1 MODIFY f BIT(8); +--disable_info +SELECT HEX(f) FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (b BIT NOT NULL) $engine DEFAULT CHARSET utf16; +INSERT INTO t1 SET b=b'1'; +--enable_info +ALTER TABLE t1 CHANGE b c BIT NOT NULL; +--disable_info +SELECT HEX(c) FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (c VARCHAR(10) NOT NULL DEFAULT 'scary') $engine; +INSERT INTO t1() VALUES(); +--enable_info +ALTER TABLE t1 ADD f TINYINT NOT NULL DEFAULT -42; +ALTER TABLE t1 MODIFY f MEDIUMINT NOT NULL DEFAULT 64802, +MODIFY c VARCHAR(20) NOT NULL DEFAULT 'gory', +ADD d DATETIME; +--disable_info +INSERT INTO t1() VALUES(); +INSERT INTO t1 (c,f,d) VALUES ('fury', -8388608, now()); +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (t TINYINT PRIMARY KEY, m MEDIUMINT UNIQUE) $engine; +SELECT table_id INTO @table_id1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +INSERT INTO t1 VALUES (-42, -123456); +--enable_info +ALTER TABLE t1 CHANGE t s SMALLINT; +SELECT table_id INTO @table_id2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 CHANGE m i INT, ALGORITHM=INSTANT; +ALTER TABLE t1 CHANGE m i INT; +SELECT table_id INTO @table_id3 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS +WHERE name = 'test/t1'; +--disable_info +SELECT @table_id1 = @table_id2, @table_id2 = @table_id3; +INSERT IGNORE INTO t1 VALUES (0, -123456); +REPLACE INTO t1 VALUES(-42, 123456); +INSERT IGNORE INTO t1 VALUES(32768, 2147483648); +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (a SERIAL, b INT, c TINYINT UNIQUE) $engine; +INSERT INTO t1 (c) VALUES(1),(2),(3); +--enable_info +ALTER TABLE t1 MODIFY c BIGINT; +--disable_info +UPDATE t1 SET b=1 WHERE c=2; +UPDATE t1 SET c=4 WHERE a=3; +UPDATE t1 SET b=2 WHERE c>3; +--error ER_DUP_ENTRY +UPDATE t1 SET c=c+1; +SELECT * FROM t1; +DROP TABLE t1; + +# MDEV-18719 Assertion (c.prtype ^ o->prtype) & ... failed on ALTER TABLE +eval CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(1)) $engine; +INSERT INTO t1 VALUES(1,'a'); +ALTER TABLE t1 MODIFY b VARCHAR(256), ADD COLUMN c INT; +INSERT INTO t1 VALUES(2,'bah',3); +SELECT * FROM t1; +DROP TABLE t1; + +# MDEV-21088 Table cannot be loaded after instant ADD/DROP COLUMN +eval CREATE TABLE t1(a CHAR(5) CHARACTER SET utf8 PRIMARY KEY) $engine; +INSERT INTO t1 VALUES('barf'); +ALTER TABLE t1 ADD b INT FIRST, ALGORITHM=INSTANT; +# this evicts and reloads the table definition until MDEV-17468 is fixed +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +DROP TABLE t1; + +# MDEV-21148 Assertion failure index->n_core_fields + n_add >= index->n_fields +eval CREATE TABLE t1 (a INT, b TEXT, PRIMARY KEY(b(9))) $engine; +ALTER TABLE t1 ADD COLUMN c TEXT FIRST; +ALTER TABLE t1 ADD COLUMN d TEXT GENERATED ALWAYS AS (SUBSTR(b,1,499)) FIRST; +DROP TABLE t1; + +eval CREATE TABLE t1(a CHAR(5), b INT, c CHAR(1), d CHAR(1), PRIMARY KEY(a,b)) +DEFAULT CHARACTER SET utf8 $engine; +INSERT INTO t1 SET a='fubar',b=42; +ALTER TABLE t1 DROP c, DROP d, ALGORITHM=INSTANT; +# this evicts and reloads the table definition until MDEV-17468 is fixed +ALTER TABLE t1 ADD vb INT AS (b); +SELECT * FROM t1; +DROP TABLE t1; + +eval CREATE TABLE t1 (a INT, b INT, PRIMARY KEY(a,b)) $engine; +ALTER TABLE t1 MODIFY b INT FIRST; +DROP TABLE t1; dec $format; let $redundant_4k= 0; } disconnect analyze; + +--echo # +--echo # MDEV-22465: DROP COLUMN is wrongly claimed to be ALGORITHM=INSTANT +--echo # + +CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +ALTER TABLE t1 DROP b, ALGORITHM=NOCOPY; +DROP TABLE t1; + 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= @saved_frequency; SET GLOBAL innodb_instant_alter_column_allowed = @saved_allowance; + +--echo # +--echo # MDEV-18266: Changing an index comment unnecessarily rebuilds index +--echo # + +CREATE TABLE t1(a INT, b INT) ENGINE=INNODB; +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; diff --git a/mysql-test/suite/innodb/t/instant_alter_bugs.test b/mysql-test/suite/innodb/t/instant_alter_bugs.test index a7ccefb19af..cdfe6472c06 100644 --- a/mysql-test/suite/innodb/t/instant_alter_bugs.test +++ b/mysql-test/suite/innodb/t/instant_alter_bugs.test @@ -1,5 +1,8 @@ --source include/have_innodb.inc +SET @save_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; + --echo # --echo # MDEV-17821 Assertion `!page_rec_is_supremum(rec)' failed --echo # in btr_pcur_store_position @@ -125,7 +128,7 @@ INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8) VALUES INSERT INTO t1 (f1,f2,f3,f4,f5,f6,f7,f8) VALUES ('impact', 'b', 'h', 185, 'fj', 7, 7, 3); ALTER TABLE t1 ADD COLUMN filler VARCHAR(255) DEFAULT ''; -SELECT * FROM t1 INTO OUTFILE 'load.data'; +SELECT * INTO OUTFILE 'load.data' FROM t1; UPDATE IGNORE t1 SET pk = 0; LOAD DATA INFILE 'load.data' REPLACE INTO TABLE t1; HANDLER t1 OPEN AS h; @@ -205,3 +208,249 @@ SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; --echo # End of 10.3 tests + + +create table t ( + a varchar(9), + b int, + c int, + row_start bigint unsigned generated always as row start invisible, + row_end bigint unsigned generated always as row end invisible, + period for system_time (row_start, row_end) +) engine=innodb row_format=compressed with system versioning; +insert into t values (repeat('a', 9), 1, 1); +set @@system_versioning_alter_history = keep; +alter table t modify a varchar(10), algorithm=instant; +alter table t change b bb int, algorithm=instant; +alter table t modify c int without system versioning, algorithm=instant; +set @@system_versioning_alter_history = error; +check table t; +drop table t; + +--echo # +--echo # MDEV-18219 Assertion `index->n_core_null_bytes <= ...' failed +--echo # in rec_init_offsets after instant DROP COLUMN +--echo # +CREATE TABLE t1 (a INT, b INT NOT NULL) ENGINE=InnoDB; +INSERT INTO t1 VALUES +(0,9),(2,7),(3,1),(3,4),(8,4),(3,7),(6,1),(3,8),(1,2),(4,1),(0,8),(5,3), +(1,3),(1,6),(2,1),(8,7),(6,0),(1,9),(9,4),(0,6),(9,3),(0,9),(9,4),(2,4), +(2,7),(7,8),(8,2),(2,5),(6,1),(4,5),(5,3),(6,8),(4,9),(5,7),(7,5),(5,1), +(8,8),(5,7),(3,8),(0,1),(8,4),(8,3),(9,7),(4,8),(1,1),(0,4),(2,6),(8,5), +(8,8),(8,7),(6,7),(1,7),(9,6),(3,6),(1,9),(0,3),(5,3),(2,4),(0,6),(2,0), +(6,5),(1,6),(2,4),(9,1),(3,0),(6,4),(1,3),(0,8),(3,5),(3,1),(8,9),(9,9), +(7,9),(4,5),(2,2),(3,8),(0,8),(7,1),(2,0),(1,5),(7,3),(4,4),(3,9),(7,2), +(6,2),(0,4),(2,0),(1,5),(5,7),(4,5),(3,7),(6,0),(2,1),(5,0),(1,0),(2,0), +(8,4),(5,7),(3,5),(0,5),(7,6),(5,9),(1,2),(4,2),(8,5),(8,7),(2,8),(1,8), +(4,3),(1,6),(7,8),(3,7),(4,6),(1,1),(3,0),(1,6),(2,0),(3,4),(4,8),(3,9), +(8,0),(4,9),(4,0),(3,9),(6,4),(7,4),(5,8),(4,7),(7,3),(5,9),(2,3),(7,3), +(0,4),(5,9),(9,8),(4,2),(3,6),(2,6),(1,8),(7,0),(0,0),(2,3),(1,2),(3,3), +(2,7),(6,0),(9,0),(6,9),(4,6),(9,8),(0,7),(9,1),(9,6),(4,3),(7,7),(7,7), +(4,1),(4,7),(7,3),(2,8),(5,8),(8,9),(3,9),(7,7),(0,8),(4,9),(3,2),(5,0), +(1,7),(0,3),(2,9),(9,7),(7,5),(6,9),(8,5),(3,6),(1,1),(2,8),(7,9),(4,9), +(6,6),(5,9),(5,3),(9,8),(3,3),(5,6),(0,9),(3,9),(7,9),(7,3),(5,2),(1,4), +(4,4),(8,2),(2,2),(8,3),(9,1),(4,9),(9,8),(1,8),(1,8),(9,1),(1,1),(3,0), +(4,6),(9,3),(3,3),(5,2),(0,1),(3,4),(3,2),(1,3),(4,4),(7,0),(4,6),(7,2), +(4,5),(8,7),(7,8),(8,1),(3,5),(0,6),(3,5),(2,1),(4,4),(3,4),(2,1),(4,1); +INSERT INTO t1 SELECT * FROM t1; +ALTER TABLE t1 DROP a; +# Exploit MDEV-17468 to force the table definition to be reloaded +ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; +DROP TABLE t1; + +--echo # +--echo # MDEV-19030 Assertion index->n_core_null_bytes <= ... failed +--echo # in rec_init_offsets after instant DROP COLUMN +--echo # +CREATE TABLE t1 (a INT, b INT NOT NULL DEFAULT 0) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (),(),(),(); +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +ALTER TABLE t1 FORCE; +INSERT INTO t1 SELECT * FROM t1; +ALTER TABLE t1 DROP a, ADD a SMALLINT NOT NULL; +INSERT INTO t1 SELECT * FROM t1; +INSERT INTO t1 SELECT * FROM t1; +# Exploit MDEV-17468 to force the table definition to be reloaded +ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL; +DROP TABLE t1; + +--echo # +--echo # MDEV-18623 Assertion after DROP FULLTEXT INDEX and removing NOT NULL +--echo # +CREATE TABLE t1 (c TEXT NOT NULL, FULLTEXT INDEX ftidx(c)) ENGINE=InnoDB +ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DROP INDEX ftidx; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t1 MODIFY c TEXT NULL, ALGORITHM=INSTANT; +ALTER TABLE t1 MODIFY c TEXT NULL; +DROP TABLE t1; + +--echo # +--echo # MDEV-20048 dtuple_get_nth_field(): Assertion 'n < tuple->n_fields' +--echo # failed on ROLLBACK after instant DROP COLUMN +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 ADD b TEXT, ALGORITHM=INSTANT; +SET @b = REPEAT('b', @@innodb_page_size / 2 + 1); +INSERT INTO t1 VALUES(2, @b), (3, @b); +BEGIN; +DELETE FROM t1 WHERE a=2; + +# Stop purge so that it doesn't remove the delete-marked entry. +connect (purge_control,localhost,root); +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; + +COMMIT; + +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +BEGIN; +INSERT INTO t1 VALUES (2); + +connection purge_control; +SELECT * FROM t1; +disconnect purge_control; +connection default; +ROLLBACK; + +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-20479: assertion failure in dict_table_get_nth_col() after INSTANT DROP COLUMN +--echo # + +CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +ALTER TABLE t1 ADD COLUMN (b INT, c INT, d INT, e INT NOT NULL DEFAULT 0); +ALTER TABLE t1 ADD UNIQUE INDEX(e); +ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e; +DROP TABLE t1; + +--echo # +--echo # MDEV-20565 Assertion on CHANGE COLUMN...SYSTEM VERSIONING +--echo # +set @@system_versioning_alter_history = keep; +CREATE TABLE t (a INT WITHOUT SYSTEM VERSIONING, b INT) ENGINE=InnoDB +WITH SYSTEM VERSIONING; +ALTER TABLE t CHANGE COLUMN a alpha INT WITH SYSTEM VERSIONING, +ALGORITHM=INSTANT; +DROP TABLE t; + +CREATE TABLE t (alpha INT, b INT) ENGINE=InnoDB WITH SYSTEM VERSIONING; +ALTER TABLE t CHANGE COLUMN alpha a INT WITHOUT SYSTEM VERSIONING, +ALGORITHM=INSTANT; +DROP TABLE t; +set @@system_versioning_alter_history = error; + +--echo # +--echo # MDEV-20117 Assertion 0 failed in row_sel_get_clust_rec_for_mysql +--echo # + +# This is not repeating the bug itself, but demonstrating that both +# parts of the fix are needed. +# To repeat the original bug, we should be somehow able to empty +# the table of user records while purgeable undo log records exist. +CREATE TABLE t (b INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t SET b=1; +ALTER TABLE t ADD COLUMN a INT FIRST, ALGORITHM=INSTANT; +DELETE FROM t; +ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; +# If page_cur_delete_rec() emptied the page (and wrongly reset the +# page type) during the previous ALTER TABLE, the following would hit +# an assertion failure because of root page type mismatch. +ALTER TABLE t DROP COLUMN c, ALGORITHM=INSTANT; +SELECT * FROM t; +DROP TABLE t; + +# The following is nondeterministically repeating the bug in a +# different scenario: the table is empty at the time the ALTER TABLE +# is invoked, apparently because purge already processed the records +# for the DELETE, but not the record for the UPDATE. +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c INT, d INT, e INT) ENGINE=InnoDB; +INSERT INTO t1 SET a=1; +INSERT INTO t1 SET a=2; +BEGIN; +UPDATE t1 SET b=1; +DELETE FROM t1; +COMMIT; + +ALTER TABLE t1 DROP b, DROP c, DROP d, DROP e; +--source include/wait_all_purged.inc +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-20190 Instant operation fails when add column and collation +--echo # change on non-indexed column +--echo # + +CREATE TABLE t1 (a CHAR)ENGINE=INNODB; +ALTER TABLE t1 DEFAULT COLLATE= latin1_general_cs; +ALTER TABLE t1 ADD COLUMN b INT NOT NULL, MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR NOT NULL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin1_general_cs; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +CREATE TABLE t1 (a CHAR NOT NULL) CHARSET latin2 COLLATE latin2_bin +ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +ALTER TABLE t1 DEFAULT COLLATE = latin2_general_ci; +ALTER TABLE t1 MODIFY a CHAR, ALGORITHM=INSTANT; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-21645 SIGSEGV in innobase_get_computed_value +--echo # +CREATE TABLE t1 (a INT PRIMARY KEY, b INT, va INTEGER GENERATED ALWAYS AS (a)) +ENGINE=InnoDB; +INSERT INTO t1 SET a=1, b=NULL; +ALTER TABLE t1 MODIFY COLUMN b INT FIRST; +ALTER TABLE t1 ADD UNIQUE INDEX (va); +DROP TABLE t1; + +--echo # +--echo # MDEV-22651 Assertion dict_col_get_fixed_size... +--echo # in dict_table_t::init_instant() +--echo # +--source include/have_innodb.inc +CREATE TABLE t (i INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t SET i=1; +ALTER TABLE t ADD e CHAR(255) CHARACTER SET UTF32 FIRST, ALGORITHM=INSTANT; +DROP TABLE t; + +--echo # +--echo # MDEV-23499 Assertion c.same_type(*o) failed +--echo # +CREATE TABLE t (pk SERIAL, b TEXT CHARACTER SET utf8) ENGINE=InnoDB; +ALTER TABLE t MODIFY b TEXT CHARACTER SET utf8mb4 FIRST; +DROP TABLE t; + +--echo # +--echo # MDEV-23672 Assertion `v.v_indexes.empty()' failed in dict_table_t::instant_column +--echo # +create table t1 ( + col_int integer, + col_text text not null, + col_int_g integer generated always as (col_int) unique, + col_text_g text generated always as (substr(col_text,1,499)) ) +engine innodb row_format = redundant; +insert into t1 (col_int,col_text) values (0, 'a'), (null, 'b'); +alter table t1 modify column col_text text null, algorithm = instant; +insert into t1 (col_int,col_text) values (1, null), (null, null); +update t1 set col_text= 'd'; +select * from t1; +check table t1; +drop table t1; + +SET GLOBAL innodb_purge_rseg_truncate_frequency=@save_frequency; diff --git a/mysql-test/suite/innodb/t/instant_alter_charset.test b/mysql-test/suite/innodb/t/instant_alter_charset.test new file mode 100644 index 00000000000..a5ddd49830c --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_charset.test @@ -0,0 +1,845 @@ +--source include/innodb_row_format.inc + +--let $row_format= `SELECT @@GLOBAL.innodb_default_row_format` +set names utf8; + +create table no_rebuild ( + a char(150) charset utf8mb3 collate utf8mb3_general_ci +) engine=innodb; +create table rebuild ( + a varchar(150) charset ascii +) engine=innodb; + +set @id = (select table_id from information_schema.innodb_sys_tables + where name = 'test/no_rebuild'); +select name, prtype, len from information_schema.innodb_sys_columns + where table_id = @id; +select c.prtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id + where t.name = 'test/rebuild' and c.name = 'a'; +alter table no_rebuild + change a a char(150) charset utf8mb3 collate utf8mb3_spanish_ci, + algorithm=inplace; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table rebuild + change a a varchar(150) charset latin1 not null default 'asdf', + algorithm=inplace; +alter table rebuild + change a a varchar(150) charset latin1 not null default 'asdf', + algorithm=copy; +select name, prtype, len from information_schema.innodb_sys_columns + where table_id = @id; +select c.prtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id + where t.name = 'test/rebuild' and c.name = 'a'; + +drop table no_rebuild, rebuild; + +create table supported_types ( + id int primary key auto_increment, + a varchar(150) charset ascii, + b text(150) charset ascii, + c text charset ascii, + d tinytext charset ascii, + e mediumtext charset ascii, + f longtext charset ascii +) engine=innodb; + +alter table supported_types + convert to charset ascii collate ascii_bin, + algorithm=instant; + +drop table supported_types; + +create table various_cases ( + a char(150) charset ascii, + b varchar(150) as (a) virtual, + c char(150) as (a) persistent +) engine=innodb; + +alter table various_cases + change a a char(150) charset ascii collate ascii_bin, + algorithm=inplace; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table various_cases + change a a varchar(222), + algorithm=inplace; + +alter table various_cases + change b b varchar(150) as (a) virtual, + algorithm=inplace; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table various_cases + change c c char(150) as (a) persistent, + algorithm=inplace; + +# Can not grow storage in bytes from CHAR +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table various_cases + modify a char(150) charset utf8mb4, + algorithm=instant; + +drop table various_cases; + + +create table all_texts ( + a tinytext charset ascii, + b text charset ascii, + c mediumtext charset ascii, + d longtext charset ascii, + footer int +) engine=innodb; + +alter table all_texts + convert to charset ascii collate ascii_bin, + algorithm=instant; + +drop table all_texts; + + +create table all_binaries ( + a tinyblob, + b blob, + c mediumblob, + d longblob, + e varbinary(150), + f binary(150) +) engine=innodb; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_binaries modify a tinytext, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_binaries modify b text, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_binaries modify c mediumtext, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_binaries modify d longtext, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_binaries modify e varchar(150), algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_binaries modify f char(150), algorithm=instant; + +drop table all_binaries; + +create table all_strings ( + a tinytext, + b text, + c mediumtext, + d longtext, + e varchar(150), + f char(150) +) engine=innodb; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify a tinyblob, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify b blob, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify c mediumblob, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify d longblob, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify e varbinary(150), algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify f binary(150), algorithm=instant; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify a tinytext charset binary, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify b text charset binary, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify c mediumtext charset binary, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify d longtext charset binary, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify e varchar(150) charset binary, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table all_strings modify f char(150) charset binary, algorithm=instant; + +drop table all_strings; + +create table key_part_change ( + a char(150) charset ascii, + b char(150) charset ascii, + c char(150) charset ascii, + unique key ab (a,b) +) engine=innodb; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table key_part_change + modify a char(150) charset utf8mb4, + drop index ab, + add unique key ab(a,c), + algorithm=instant; + +drop table key_part_change; + +create table key_part_change_and_rename ( + a char(100) charset ascii, + b char(100) charset ascii, + unique key ab (a,b) +) engine=innodb; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table key_part_change_and_rename + change a b char(100) charset utf8mb4, + change b a char(100) charset utf8mb4, + drop index ab, + add unique key ab(a,b), + algorithm=instant; + +drop table key_part_change_and_rename; + +create table enum_and_set ( + a enum('one', 'two') charset utf8mb3, + b set('three', 'four') charset utf8mb3 +) engine=innodb; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table enum_and_set + modify a enum('one', 'two') charset utf8mb4, + algorithm=instant; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table enum_and_set + modify b enum('three', 'four') charset utf8mb4, + algorithm=instant; + +drop table enum_and_set; + +create table compressed ( + a varchar(255) compressed charset utf8mb3 +) engine=innodb; + +insert into compressed values ('AAA'), ('bbb'), ('CCC'); + +alter table compressed + modify a varchar(255) compressed charset utf8mb4, + algorithm=instant; + +select * from compressed; +check table compresed; + +drop table compressed; + +create table key_part_bug ( + id int primary key auto_increment, + a varchar(150) charset utf8mb3 unique key +) engine=innodb; + +alter table key_part_bug + modify a varchar(150) charset utf8mb4, + algorithm=instant; + +drop table key_part_bug; + + +create table latin1_swedish_special_case ( + copy1 varchar(150) charset ascii collate ascii_general_ci, + copy2 char(150) charset ascii collate ascii_general_ci, + instant1 varchar(150) charset ascii collate ascii_general_ci, + instant2 char(150) charset ascii collate ascii_general_ci +) engine=innodb; + +select c.name, c.prtype, c.mtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id + where t.name = 'test/latin1_swedish_special_case'; +alter table latin1_swedish_special_case + modify copy1 varchar(150) charset latin1 collate latin1_swedish_ci, + modify copy2 char(150) charset latin1 collate latin1_swedish_ci, + algorithm=copy; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table latin1_swedish_special_case + modify instant1 varchar(150) charset latin1 collate latin1_swedish_ci, + modify instant2 char(150) charset latin1 collate latin1_swedish_ci, + algorithm=instant; +alter table latin1_swedish_special_case + modify instant1 varchar(150) charset latin1 collate latin1_swedish_ci, + modify instant2 char(150) charset latin1 collate latin1_swedish_ci, + algorithm=copy; +select c.name, c.prtype, c.mtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id + where t.name = 'test/latin1_swedish_special_case'; +alter table latin1_swedish_special_case + modify copy1 varchar(150) charset latin1 collate latin1_general_ci, + modify copy2 char(150) charset latin1 collate latin1_general_ci, + algorithm=copy; +alter table latin1_swedish_special_case + modify instant1 varchar(150) charset latin1 collate latin1_general_ci, + modify instant2 char(150) charset latin1 collate latin1_general_ci, + algorithm=instant; +select c.name, c.prtype, c.mtype, c.len from information_schema.innodb_sys_columns as c inner join information_schema.innodb_sys_tables t on c.table_id = t.table_id + where t.name = 'test/latin1_swedish_special_case'; + +drop table latin1_swedish_special_case; + +create table regression (a varchar(100) charset utf8mb3 primary key, b int) engine=innodb; +alter table regression convert to character set utf8mb4; +drop table regression; + +# ROW_FORMAT=DYNAMIC limitation: +# size in bytes cannot be increased from less of equal that 255 to more than 255 +create table boundary_255 ( + a varchar(50) charset ascii, + b varchar(200) charset ascii, + c varchar(300) charset ascii +) engine=innodb; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table boundary_255 + modify a varchar(50) charset utf8mb3, + algorithm=instant; +alter table boundary_255 + modify a varchar(50) charset utf8mb3, + algorithm=copy; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table boundary_255 + modify b varchar(200) charset utf8mb3, + algorithm=instant; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table boundary_255 + modify c varchar(300) charset utf8mb3, + algorithm=instant; + +drop table boundary_255; + +create table boundary_255 ( + a varchar(70) charset utf8mb3 +) engine=innodb; + +if ($row_format == 'redundant') { +alter table boundary_255 + modify a varchar(70) charset utf8mb4, + algorithm=instant; +} +if ($row_format != 'redundant') { +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table boundary_255 + modify a varchar(70) charset utf8mb4, + algorithm=instant; +} + +drop table boundary_255; + + +create table t ( + a char(10) collate utf8mb3_general_ci, + b char(70) collate utf8mb3_general_ci, + c char(100) collate utf8mb3_general_ci, + + aa char(10) collate utf8mb3_general_ci unique, + bb char(70) collate utf8mb3_general_ci unique, + cc char(100) collate utf8mb3_general_ci unique, + + d char(10) collate utf8mb3_general_ci, + dd char(10) collate utf8mb3_general_ci unique +) engine=innodb; +insert into t values + (repeat('a', 10), repeat('a', 70), repeat('a', 100), + repeat('a', 10), repeat('a', 70), repeat('a', 100), + repeat('a', 10), repeat('a', 10) +); +if ($row_format != 'redundant') { +alter table t modify a char(10) collate utf8mb4_general_ci, algorithm=instant; +check table t; +alter table t modify b char(70) collate utf8mb4_general_ci, algorithm=instant; +check table t; +alter table t modify c char(100) collate utf8mb4_general_ci, algorithm=instant; +check table t; + +alter table t modify aa char(10) collate utf8mb4_general_ci, algorithm=instant; +check table t; +alter table t modify bb char(70) collate utf8mb4_general_ci, algorithm=instant; +check table t; +alter table t modify cc char(100) collate utf8mb4_general_ci, algorithm=instant; +check table t; + +alter table t modify d char(10) collate utf8mb4_spanish_ci, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify dd char(10) collate utf8mb4_spanish_ci, algorithm=instant; +} +if ($row_format == 'redundant') { +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify a char(10) collate utf8mb4_general_ci, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify b char(70) collate utf8mb4_general_ci, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify c char(100) collate utf8mb4_general_ci, algorithm=instant; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify aa char(10) collate utf8mb4_general_ci, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify bb char(70) collate utf8mb4_general_ci, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify cc char(100) collate utf8mb4_general_ci, algorithm=instant; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify d char(10) collate utf8mb4_spanish_ci, algorithm=instant; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify dd char(10) collate utf8mb4_spanish_ci, algorithm=instant; +} +select * from t; +drop table t; + + +create table fully_compatible ( + id int auto_increment unique key, + from_charset char(255), + from_collate char(255), + to_charset char(255), + to_collate char(255) +); + +insert into fully_compatible (from_charset, from_collate, to_charset, to_collate) values + ('utf8mb3', 'utf8mb3_general_ci', 'utf8mb4', 'utf8mb4_general_ci'), + ('utf8mb3', 'utf8mb3_bin', 'utf8mb4', 'utf8mb4_bin'), + ('utf8mb3', 'utf8mb3_unicode_ci', 'utf8mb4', 'utf8mb4_unicode_ci'), + ('utf8mb3', 'utf8mb3_icelandic_ci', 'utf8mb4', 'utf8mb4_icelandic_ci'), + ('utf8mb3', 'utf8mb3_latvian_ci', 'utf8mb4', 'utf8mb4_latvian_ci'), + ('utf8mb3', 'utf8mb3_romanian_ci', 'utf8mb4', 'utf8mb4_romanian_ci'), + ('utf8mb3', 'utf8mb3_slovenian_ci', 'utf8mb4', 'utf8mb4_slovenian_ci'), + ('utf8mb3', 'utf8mb3_polish_ci', 'utf8mb4', 'utf8mb4_polish_ci'), + ('utf8mb3', 'utf8mb3_estonian_ci', 'utf8mb4', 'utf8mb4_estonian_ci'), + ('utf8mb3', 'utf8mb3_spanish_ci', 'utf8mb4', 'utf8mb4_spanish_ci'), + ('utf8mb3', 'utf8mb3_swedish_ci', 'utf8mb4', 'utf8mb4_swedish_ci'), + ('utf8mb3', 'utf8mb3_turkish_ci', 'utf8mb4', 'utf8mb4_turkish_ci'), + ('utf8mb3', 'utf8mb3_czech_ci', 'utf8mb4', 'utf8mb4_czech_ci'), + ('utf8mb3', 'utf8mb3_danish_ci', 'utf8mb4', 'utf8mb4_danish_ci'), + ('utf8mb3', 'utf8mb3_lithuanian_ci', 'utf8mb4', 'utf8mb4_lithuanian_ci'), + ('utf8mb3', 'utf8mb3_slovak_ci', 'utf8mb4', 'utf8mb4_slovak_ci'), + ('utf8mb3', 'utf8mb3_spanish2_ci', 'utf8mb4', 'utf8mb4_spanish2_ci'), + ('utf8mb3', 'utf8mb3_roman_ci', 'utf8mb4', 'utf8mb4_roman_ci'), + ('utf8mb3', 'utf8mb3_persian_ci', 'utf8mb4', 'utf8mb4_persian_ci'), + ('utf8mb3', 'utf8mb3_esperanto_ci', 'utf8mb4', 'utf8mb4_esperanto_ci'), + ('utf8mb3', 'utf8mb3_hungarian_ci', 'utf8mb4', 'utf8mb4_hungarian_ci'), + ('utf8mb3', 'utf8mb3_sinhala_ci', 'utf8mb4', 'utf8mb4_sinhala_ci'), + ('utf8mb3', 'utf8mb3_german2_ci', 'utf8mb4', 'utf8mb4_german2_ci'), + ('utf8mb3', 'utf8mb3_croatian_mysql561_ci', 'utf8mb4', 'utf8mb4_croatian_mysql561_ci'), + ('utf8mb3', 'utf8mb3_unicode_520_ci', 'utf8mb4', 'utf8mb4_unicode_520_ci'), + ('utf8mb3', 'utf8mb3_vietnamese_ci', 'utf8mb4', 'utf8mb4_vietnamese_ci'), + ('utf8mb3', 'utf8mb3_croatian_ci', 'utf8mb4', 'utf8mb4_croatian_ci'), + ('utf8mb3', 'utf8mb3_myanmar_ci', 'utf8mb4', 'utf8mb4_myanmar_ci'), + ('utf8mb3', 'utf8mb3_thai_520_w2', 'utf8mb4', 'utf8mb4_thai_520_w2'), + ('utf8mb3', 'utf8mb3_general_nopad_ci', 'utf8mb4', 'utf8mb4_general_nopad_ci'), + ('utf8mb3', 'utf8mb3_nopad_bin', 'utf8mb4', 'utf8mb4_nopad_bin'), + ('utf8mb3', 'utf8mb3_unicode_nopad_ci', 'utf8mb4', 'utf8mb4_unicode_nopad_ci'), + ('utf8mb3', 'utf8mb3_unicode_520_nopad_ci', 'utf8mb4', 'utf8mb4_unicode_520_nopad_ci') +; + +let $data_size = `select count(*) from fully_compatible`; +let $counter = 1; + +while ($counter <= $data_size) { + let $from_charset = `select from_charset from fully_compatible where id = $counter`; + let $from_collate = `select from_collate from fully_compatible where id = $counter`; + let $to_charset = `select to_charset from fully_compatible where id = $counter`; + let $to_collate = `select to_collate from fully_compatible where id = $counter`; + + eval create table tmp ( + a varchar(50) charset $from_charset collate $from_collate, + b varchar(50) charset $from_charset collate $from_collate primary key + ) engine=innodb; + + insert into tmp values ('AAA', 'AAA'), ('bbb', 'bbb'); + + eval alter table tmp + change a a varchar(50) charset $to_charset collate $to_collate, + modify b varchar(50) charset $to_charset collate $to_collate, + algorithm=instant; + + check table tmp; + + drop table tmp; + + inc $counter; +} + +drop table fully_compatible; + + +create table compatible_without_index ( + id int auto_increment unique key, + from_charset char(255), + from_collate char(255), + to_charset char(255), + to_collate char(255) +); + +insert into compatible_without_index (from_charset, from_collate, to_charset, to_collate) values + + ('utf8mb3', 'utf8mb3_general_ci', 'utf8mb4', 'utf8mb4_vietnamese_ci'), + ('utf8mb3', 'utf8mb3_bin', 'utf8mb4', 'utf8mb4_vietnamese_ci'), + ('utf8mb3', 'utf8mb3_general_nopad_ci', 'utf8mb4', 'utf8mb4_vietnamese_ci'), + ('utf8mb3', 'utf8mb3_nopad_bin', 'utf8mb4', 'utf8mb4_vietnamese_ci'), + + ('ascii', 'ascii_general_ci', 'ascii', 'ascii_bin'), + ('utf8mb3', 'utf8mb3_roman_ci', 'utf8mb3', 'utf8mb3_lithuanian_ci'), + ('utf8mb4', 'utf8mb4_thai_520_w2', 'utf8mb4', 'utf8mb4_persian_ci'), + ('utf8mb3', 'utf8mb3_myanmar_ci', 'utf8mb4', 'utf8mb4_german2_ci'), + ('utf8mb3', 'utf8mb3_general_ci', 'utf8mb3', 'utf8mb3_unicode_ci'), + ('latin1', 'latin1_general_cs', 'latin1', 'latin1_general_ci'), + + ('utf16', 'utf16_general_ci', 'utf16', 'utf16_german2_ci') +; + +let $data_size = `select count(*) from compatible_without_index`; +let $counter = 1; + +while ($counter <= $data_size) { + let $from_charset = `select from_charset from compatible_without_index where id = $counter`; + let $from_collate = `select from_collate from compatible_without_index where id = $counter`; + let $to_charset = `select to_charset from compatible_without_index where id = $counter`; + let $to_collate = `select to_collate from compatible_without_index where id = $counter`; + + eval create table tmp ( + a varchar(50) charset $from_charset collate $from_collate, + b varchar(50) charset $from_charset collate $from_collate unique key, + c varchar(50) charset $from_charset collate $from_collate primary key + ) engine=innodb; + + eval alter table tmp + change a a varchar(50) charset $to_charset collate $to_collate, + algorithm=instant; + + --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON + eval alter table tmp + modify b varchar(50) charset $to_charset collate $to_collate, + algorithm=instant; + + --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON + eval alter table tmp + modify c varchar(50) charset $to_charset collate $to_collate, + algorithm=instant; + + drop table tmp; + + inc $counter; +} + +drop table compatible_without_index; + + +create table fully_incompatible ( + id int auto_increment unique key, + from_charset char(255), + from_collate char(255), + to_charset char(255), + to_collate char(255) +); + +insert into fully_incompatible (from_charset, from_collate, to_charset, to_collate) values + ('ascii', 'ascii_general_ci', 'utf8mb3', 'utf8mb3_general_ci'), + ('ascii', 'ascii_general_ci', 'utf8mb4', 'utf8mb4_general_ci'), + ('ascii', 'ascii_general_ci', 'latin1', 'latin1_general_ci'), + ('ascii', 'ascii_bin', 'latin1', 'latin1_bin'), + ('ascii', 'ascii_nopad_bin', 'latin1', 'latin1_nopad_bin'), + ('ascii', 'ascii_general_ci', 'latin2', 'latin2_general_ci'), + ('ascii', 'ascii_general_ci', 'latin7', 'latin7_general_ci'), + ('ascii', 'ascii_bin', 'koi8u', 'koi8u_bin'), + ('ascii', 'ascii_bin', 'ujis', 'ujis_bin'), + ('ascii', 'ascii_bin', 'big5', 'big5_bin'), + ('ascii', 'ascii_bin', 'gbk', 'gbk_bin'), + + ('ascii', 'ascii_general_ci', 'utf8mb3', 'utf8mb3_swedish_ci'), + ('ascii', 'ascii_bin', 'latin1', 'latin1_swedish_ci'), + ('ascii', 'ascii_general_nopad_ci', 'latin1', 'latin1_swedish_ci'), + ('ascii', 'ascii_nopad_bin', 'latin1', 'latin1_swedish_ci'), + + ('ascii', 'ascii_general_ci', 'koi8u', 'koi8u_bin'), + ('ascii', 'ascii_general_nopad_ci', 'koi8u', 'koi8u_bin'), + ('ascii', 'ascii_nopad_bin', 'koi8u', 'koi8u_bin'), + + ('ascii', 'ascii_general_ci', 'latin1', 'latin1_swedish_ci'), + ('ascii', 'ascii_bin', 'utf8mb3', 'utf8mb3_swedish_ci'), + ('ascii', 'ascii_general_nopad_ci', 'utf8mb3', 'utf8mb3_swedish_ci'), + ('ascii', 'ascii_nopad_bin', 'utf8mb3', 'utf8mb3_swedish_ci'), + + ('ascii', 'ascii_general_ci', 'utf8mb4', 'utf8mb4_danish_ci'), + ('ascii', 'ascii_bin', 'utf8mb4', 'utf8mb4_danish_ci'), + ('ascii', 'ascii_general_nopad_ci', 'utf8mb4', 'utf8mb4_danish_ci'), + ('ascii', 'ascii_nopad_bin', 'utf8mb4', 'utf8mb4_danish_ci'), + + ('ascii', 'ascii_general_ci', 'gbk', 'gbk_chinese_ci'), + ('ascii', 'ascii_general_ci', 'gbk', 'gbk_chinese_nopad_ci'), + + ('ascii', 'ascii_general_ci', 'ujis', 'ujis_japanese_ci'), + ('ascii', 'ascii_general_ci', 'big5', 'big5_chinese_ci'), + ('ascii', 'ascii_general_ci', 'latin2', 'latin2_croatian_ci'), + ('ascii', 'ascii_general_ci', 'latin7', 'latin7_estonian_cs'), + + ('ucs2', 'ucs2_general_ci', 'utf16', 'utf16_general_ci'), + ('ucs2', 'ucs2_unicode_ci', 'utf16', 'utf16_unicode_ci'), + ('ucs2', 'ucs2_icelandic_ci', 'utf16', 'utf16_icelandic_ci'), + ('ucs2', 'ucs2_latvian_ci', 'utf16', 'utf16_latvian_ci'), + ('ucs2', 'ucs2_romanian_ci', 'utf16', 'utf16_romanian_ci'), + ('ucs2', 'ucs2_slovenian_ci', 'utf16', 'utf16_slovenian_ci'), + ('ucs2', 'ucs2_polish_ci', 'utf16', 'utf16_polish_ci'), + ('ucs2', 'ucs2_estonian_ci', 'utf16', 'utf16_estonian_ci'), + ('ucs2', 'ucs2_spanish_ci', 'utf16', 'utf16_spanish_ci'), + ('ucs2', 'ucs2_general_ci', 'utf16', 'utf16_general_ci'), + ('ucs2', 'ucs2_myanmar_ci', 'utf16', 'utf16_thai_520_w2'), + ('ucs2', 'ucs2_general_ci', 'utf16', 'utf16_unicode_nopad_ci'), + ('ucs2', 'ucs2_general_mysql500_ci', 'utf16', 'utf16_spanish2_ci'), + + ('utf8mb4', 'utf8mb4_general_ci', 'utf8mb3', 'utf8mb3_general_ci'), + ('utf8mb4', 'utf8mb4_general_ci', 'ascii', 'ascii_general_ci'), + ('utf8mb3', 'utf8mb3_general_ci', 'ascii', 'ascii_general_ci'), + ('utf8mb3', 'utf8mb3_general_ci', 'latin1', 'latin1_general_ci'), + ('utf16', 'utf16_general_ci', 'utf32', 'utf32_general_ci'), + ('latin1', 'latin1_general_ci', 'ascii', 'ascii_general_ci'), + ('ascii', 'ascii_general_ci', 'swe7', 'swe7_swedish_ci'), + ('eucjpms', 'eucjpms_japanese_nopad_ci', 'geostd8', 'geostd8_general_ci'), + ('latin1', 'latin1_general_ci', 'utf16', 'utf16_general_ci') +; + +let $data_size = `select count(*) from fully_incompatible`; +let $counter = 1; + +while ($counter <= $data_size) { + let $from_charset = `select from_charset from fully_incompatible where id = $counter`; + let $from_collate = `select from_collate from fully_incompatible where id = $counter`; + let $to_charset = `select to_charset from fully_incompatible where id = $counter`; + let $to_collate = `select to_collate from fully_incompatible where id = $counter`; + + eval create table tmp ( + a varchar(150) charset $from_charset collate $from_collate, + b text(150) charset $from_charset collate $from_collate, + unique key b_idx (b(150)) + ) engine=innodb; + + --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON + eval alter table tmp + change a a varchar(150) charset $to_charset collate $to_collate, + algorithm=instant; + + --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON + eval alter table tmp + modify b text charset $to_charset collate $to_collate, + algorithm=instant; + + drop table tmp; + + inc $counter; +} + +drop table fully_incompatible; + +--echo # +--echo # MDEV-19284 INSTANT ALTER with ucs2-to-utf16 conversion produces bad data +--echo # + +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2, PRIMARY KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('a'),(0xD800); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET utf16; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET utf16; +--enable_info ONCE +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-19285 INSTANT ALTER from ascii_general_ci to latin1_general_ci produces currupt data +--echo # + +CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ascii COLLATE ascii_general_ci, PRIMARY KEY(a)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('a'),(0xC0),('b'); +--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD +ALTER TABLE t1 ALGORITHM=COPY, MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 ALGORITHM=INSTANT, MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci; +--enable_info ONCE +ALTER IGNORE TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci; +SELECT HEX(a) FROM t1; +DROP TABLE t1; + + + +--echo # +--echo # MDEV-19524 Server crashes in Bitmap<64u>::is_clear_all / Field_longstr::csinfo_change_allows_instant_alter +--echo # + +CREATE TABLE t1 (a VARCHAR(1), UNIQUE(a)) ENGINE=InnoDB; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 MODIFY a INT, ADD b INT, ADD UNIQUE (b), ALGORITHM=INSTANT; +DROP TABLE t1; + + +--echo # +--echo # MDEV-17301 Change of COLLATE unnecessarily requires ALGORITHM=COPY +--echo # + +create table t ( + a char(10) collate latin1_general_ci primary key, + b char(10) collate latin1_general_ci, + c char(10) collate latin1_general_ci, + unique key b_key(b) +) engine=innodb; + +insert into t values + ('aaa', 'aaa', 'aaa'), ('ccc', 'ccc', 'ccc'), ('bbb', 'bbb', 'bbb'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify a char(10) collate latin1_general_cs, algorithm=inplace; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify b char(10) collate latin1_general_cs, algorithm=instant; +alter table t modify b char(10) collate latin1_general_cs, algorithm=nocopy; +check table t; + +alter table t modify c char(10) collate latin1_general_cs, algorithm=instant; +check table t; + +drop table t; + +create table t ( + a varchar(10) collate latin1_general_ci primary key, + b varchar(10) collate latin1_general_ci, + c varchar(10) collate latin1_general_ci, + unique key b_key(b) +) engine=innodb; + +insert into t values + ('aaa', 'aaa', 'aaa'), ('ccc', 'ccc', 'ccc'), ('bbb', 'bbb', 'bbb'); + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify a varchar(10) collate latin1_general_cs, algorithm=inplace; + +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify b varchar(10) collate latin1_general_cs, algorithm=instant; +alter table t modify b varchar(10) collate latin1_general_cs, algorithm=nocopy; +check table t; + +alter table t modify c varchar(10) collate latin1_general_cs, algorithm=instant; +check table t; + +drop table t; + +--echo # +--echo # MDEV-20726: InnoDB: Assertion failure in file data0type.cc line 67 +--echo # + +CREATE TABLE t ( + id int(10) unsigned NOT NULL PRIMARY KEY, + a text CHARSET utf8mb3, + KEY a_idx(a(1)) +) ENGINE=InnoDB; + +INSERT INTO t VALUES (1, 'something in the air'); +ALTER TABLE t MODIFY a text CHARSET utf8mb4; + +DROP TABLE t; + + +--echo # +--echo # MDEV-22899: Assertion `field->col->is_binary() || field->prefix_len % field->col->mbmaxlen == 0' failed in dict_index_add_to_cache +--echo # + +CREATE TABLE t1 ( + a text CHARACTER SET utf8 DEFAULT NULL, + KEY a_key (a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a text CHARACTER SET utf8 DEFAULT NULL, + b int, + KEY a_key (b, a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a char(200) CHARACTER SET utf8 DEFAULT NULL, + KEY a_key (a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a char(200) CHARACTER SET utf8 DEFAULT NULL, + b int, + KEY a_key (b, a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a varchar(200) CHARACTER SET utf8 DEFAULT NULL, + KEY a_key (a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a varchar(200) CHARACTER SET utf8 DEFAULT NULL, + b int, + KEY a_key (b, a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a varchar(2000) CHARACTER SET utf8 DEFAULT NULL, + KEY a_key (a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +CREATE TABLE t1 ( + a varchar(2000) CHARACTER SET utf8 DEFAULT NULL, + b int, + KEY a_key (b, a(1)) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1 VALUES (); +ALTER TABLE t1 MODIFY a text DEFAULT NULL; +DROP TABLE t1; + +--echo # +--echo # MDEV-23245 Still getting assertion failure in file data0type.cc line 67 +--echo # + +CREATE TABLE Foo +( + Bar char(2) CHARACTER SET utf8, + KEY Bar (Bar(1)) +) ENGINE = InnoDB; +ALTER TABLE Foo MODIFY Bar char(2) CHARACTER SET utf8mb4; +INSERT INTO Foo VALUES ('a'); +DROP TABLE Foo; + +CREATE TABLE Foo +( + Bar varchar(2) CHARACTER SET utf8, + KEY Bar (Bar(1)) +) ENGINE = InnoDB; +ALTER TABLE Foo MODIFY Bar varchar(2) CHARACTER SET utf8mb4; +INSERT INTO Foo VALUES ('a'); +DROP TABLE Foo; + +CREATE TABLE Foo +( + Bar text CHARACTER SET utf8, + KEY Bar (Bar(1)) +) ENGINE = InnoDB; +ALTER TABLE Foo MODIFY Bar text CHARACTER SET utf8mb4; +INSERT INTO Foo VALUES ('a'); +DROP TABLE Foo; + +CREATE TABLE t1 (a VARCHAR(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci, +PRIMARY KEY (a(1))) +ENGINE=InnoDB; +SHOW CREATE TABLE t1; +ALTER TABLE t1 MODIFY a VARCHAR(2) +CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; +INSERT INTO t1 VALUES ('a'); +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_crash.test b/mysql-test/suite/innodb/t/instant_alter_crash.test index 9edf861d2a2..f9b18aa589f 100644 --- a/mysql-test/suite/innodb/t/instant_alter_crash.test +++ b/mysql-test/suite/innodb/t/instant_alter_crash.test @@ -44,9 +44,39 @@ DELETE FROM t1; ROLLBACK; --source include/wait_all_purged.inc +INSERT INTO t2 VALUES +(16,1551,'Omnium enim rerum'),(128,1571,' principia parva sunt'); + +connect ddl, localhost, root; +SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever'; +--send +ALTER TABLE t2 DROP COLUMN c3, ADD COLUMN c5 TEXT DEFAULT 'naturam abhorrere'; + +connection default; +SET DEBUG_SYNC='now WAIT_FOR ddl'; +SET GLOBAL innodb_flush_log_at_trx_commit=1; +SET debug_dbug='+d,dict_sys_mutex_avoid'; +UPDATE t1 SET c2=c2+1; + +--source include/kill_mysqld.inc +disconnect ddl; +--source include/start_mysqld.inc + +SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; + +SELECT * FROM t1; +SELECT * FROM t2; +BEGIN; +INSERT INTO t1 SET id=1; +DELETE FROM t2; +ROLLBACK; +--source include/wait_all_purged.inc + INSERT INTO t2 VALUES (64,42,'De finibus bonorum'), (347,33101,' et malorum'); connect ddl, localhost, root; +ALTER TABLE t2 DROP COLUMN c3; SET DEBUG_SYNC='innodb_alter_inplace_before_commit SIGNAL ddl WAIT_FOR ever'; --send ALTER TABLE t2 ADD COLUMN (c4 TEXT NOT NULL DEFAULT ' et malorum'); @@ -102,16 +132,39 @@ for (my $offset= 0x65; $offset; my $n_fields= unpack("n", substr($page,$offset-4,2)) >> 1 & 0x3ff; my $start= 0; my $name; - for (my $i= 0; $i < $n_fields; $i++) { - my $end= unpack("C", substr($page, $offset-7-$i, 1)); - print ",\n " if $i; - print "$fields[$i]="; - if ($end & 0x80) { - print "NULL(", ($end & 0x7f) - $start, " bytes)" - } else { - print "0x", unpack("H*", substr($page,$offset+$start,$end-$start)) + if (unpack("C", substr($page,$offset-3,1)) & 1) { + for (my $i= 0; $i < $n_fields; $i++) { + my $end= unpack("C", substr($page, $offset-7-$i, 1)); + print ",\n " if $i; + print "$fields[$i]="; + if ($end & 0x80) { + print "NULL(", ($end & 0x7f) - $start, " bytes)" + } else { + print "0x", unpack("H*", substr($page,$offset+$start,$end-$start)) + } + $start= $end & 0x7f; + } + } else { + for (my $i= 0; $i < $n_fields; $i++) { + my $end= unpack("n", substr($page, $offset-8-2*$i, 2)); + print ",\n " if $i; + if ($i > 2 && !(~unpack("C",substr($page,$offset-6,1)) & 0x30)) { + if ($i == 3) { + print "BLOB="; + $start += 8; # skip the space_id,page_number + } else { + print "$fields[$i - 1]="; + } + } else { + print "$fields[$i]="; + } + if ($end & 0x8000) { + print "NULL(", ($end & 0x7fff) - $start, " bytes)" + } else { + print "0x", unpack("H*", substr($page,$offset+$start,($end-$start) & 0x3fff)) + } + $start= $end & 0x3fff; } - $start= $end & 0x7f; } print ")\n"; } diff --git a/mysql-test/suite/innodb/t/instant_alter_debug.test b/mysql-test/suite/innodb/t/instant_alter_debug.test index cec7a05725b..fe80de2ca51 100644 --- a/mysql-test/suite/innodb/t/instant_alter_debug.test +++ b/mysql-test/suite/innodb/t/instant_alter_debug.test @@ -5,6 +5,10 @@ SET @save_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency; SET GLOBAL innodb_purge_rseg_truncate_frequency=1; +SET @old_instant= +(SELECT variable_value FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'); + CREATE TABLE t1 ( pk INT AUTO_INCREMENT PRIMARY KEY, c1 INT, @@ -222,7 +226,8 @@ connection ddl; SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged'; send ALTER TABLE t1 FORCE; -disconnect stop_purge; +connection stop_purge; +COMMIT; connection default; SET DEBUG_SYNC = 'now WAIT_FOR copied'; @@ -238,6 +243,34 @@ reap; connection default; SET DEBUG_SYNC = RESET; SELECT * FROM t1; +ALTER TABLE t1 DROP b, ALGORITHM=INSTANT; +connection stop_purge; +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +DELETE FROM t1; + +connection ddl; +SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged'; +send ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 2 AFTER a, FORCE; + +disconnect stop_purge; + +connection default; +SET DEBUG_SYNC = 'now WAIT_FOR copied'; +let $wait_all_purged = 1; +--source include/wait_all_purged.inc +INSERT INTO t1 SET a=1; +INSERT INTO t1 SET a=2,c=4; +SET DEBUG_SYNC = 'now SIGNAL logged'; + +connection ddl; +reap; +UPDATE t1 SET b = b + 1 WHERE a = 2; + +connection default; +SET DEBUG_SYNC = RESET; +SELECT * FROM t1; --echo # --echo # MDEV-15872 Crash in online ALTER TABLE...ADD PRIMARY KEY @@ -292,6 +325,42 @@ CHECK TABLE t1; 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 # @@ -316,6 +385,32 @@ SET DEBUG_SYNC='now SIGNAL update'; --reap --connection default +DROP TABLE t1; + +--echo # +--echo # MDEV-21658 Error on online ADD PRIMARY KEY after instant DROP/reorder +--echo # + +CREATE TABLE t1 (a INT, b INT, c INT, col INT) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (); +ALTER TABLE t1 DROP b, DROP c, DROP col; +ALTER TABLE t1 ADD COLUMN col INT; +ALTER TABLE t1 DROP a, DROP col, ADD COLUMN b INT; + +--connection con2 +SET SQL_MODE= ''; +SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR dml'; +send ALTER TABLE t1 ADD PRIMARY KEY(b); + +--connection default +SET DEBUG_SYNC = 'now WAIT_FOR scanned'; +UPDATE t1 SET b = 1; +SET DEBUG_SYNC = 'now SIGNAL dml'; +--connection con2 +reap; +--connection default +SELECT * FROM t1; + SET DEBUG_SYNC='RESET'; --disconnect con2 DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_extend.combinations b/mysql-test/suite/innodb/t/instant_alter_extend.combinations new file mode 100644 index 00000000000..1465bf59ad7 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_extend.combinations @@ -0,0 +1,5 @@ +[latin1] +character-set-server=latin1 + +[utf8] +character-set-server=utf8 diff --git a/mysql-test/suite/innodb/t/instant_alter_extend.test b/mysql-test/suite/innodb/t/instant_alter_extend.test new file mode 100644 index 00000000000..7258ba6d238 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_extend.test @@ -0,0 +1,258 @@ +--source include/have_innodb.inc +--source include/innodb_row_format.inc +--source include/maybe_debug.inc + +-- echo # +-- echo # MDEV-15563: Instant ROW_FORMAT=REDUNDANT column type change&extension +-- echo # (reverted in MDEV-18627) +-- echo # + +# Use character-set-server in test db +create database best; +use best; + +set default_storage_engine=innodb; +set @bigval= repeat('0123456789', 30); + +delimiter ~~; +create procedure check_table(table_name varchar(255)) +begin + select table_id into @table_id + from information_schema.innodb_sys_tables + where name = concat('best/', table_name); + select name, mtype, hex(prtype) as prtype, len + from information_schema.innodb_sys_columns + where table_id = @table_id; +end~~ +delimiter ;~~ + + +--echo # VARCHAR -> CHAR, VARBINARY -> BINARY conversion +set @bigval= repeat('0123456789', 20); + +create table t (a varchar(300)); +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t modify a char(255), algorithm=instant; +alter table t modify a char(255), algorithm=copy; + +create or replace table t (a varchar(200)); +insert into t values (@bigval); +insert into t values ('z'); +if ($have_debug) { +--disable_query_log +# This should not be reachable. +set @save_debug= @@SESSION.debug_dbug; +set debug_dbug= '+d,ib_instant_error'; +--enable_query_log +} +--enable_info +alter table t modify a char(200); +--disable_info +select count(a) from t where a = @bigval; +select a, length(a) from t where a = 'z'; + +check table t extended; +call check_table('t'); + +--echo # CHAR enlargement +--enable_info +alter table t modify a char(220); +--disable_info +select count(a) from t where a = @bigval; +select a, length(a) from t where a = 'z'; + +check table t extended; +call check_table('t'); + +--enable_info +ALTER TABLE t CHANGE COLUMN a a CHAR(230) BINARY; +ALTER TABLE t ADD COLUMN b INT FIRST; +ALTER TABLE t DROP b; +--disable_info + +check table t extended; +call check_table('t'); + +--echo # Convert from VARCHAR to a bigger CHAR +--enable_info +alter table t modify a varchar(200); +alter table t modify a char(255); +--disable_info +select count(a) from t where a = @bigval; +select a, length(a) from t where a = 'z'; + +select * from t; +check table t extended; +call check_table('t'); + +--echo # BINARY/VARBINARY test +create or replace table t (a varbinary(300)); +insert into t values(NULL); +--enable_info +alter table t modify a binary(255); +--disable_info + +create or replace table t (a varbinary(200)); +insert into t values (@bigval); +insert into t values ('z'); +--enable_info +alter table t modify a binary(200); +--disable_info +select count(a) from t where a = @bigval; +select length(a) from t where left(a, 1) = 'z'; + +check table t extended; +call check_table('t'); + +--echo # BINARY enlargement +--enable_info +alter table t modify a binary(220); +--disable_info + +check table t extended; +call check_table('t'); + +--echo # Convert from VARBINARY to a bigger BINARY +--enable_info +alter table t modify a varbinary(220); +alter table t modify a binary(255); +--disable_info +select count(a) from t where a = @bigval; +select a, length(a) from t where a = 'z'; + +select * from t; +check table t extended; +call check_table('t'); + + +--echo # Integer conversions +create or replace table t (x tinyint); +insert into t values (127); +--enable_info +alter table t modify x smallint; +--disable_info +select * from t; +check table t extended; +call check_table('t'); + +update t set x= 32767; +--enable_info +alter table t modify x mediumint; +--disable_info +select * from t; +check table t extended; +call check_table('t'); + +update t set x= 8388607; +--enable_info +alter table t modify x int; +--disable_info +select * from t; +check table t extended; +call check_table('t'); + +update t set x= 2147483647; +--enable_info +alter table t modify x bigint; +--disable_info +select * from t; +check table t extended; +call check_table('t'); + +if ($have_debug) { +--disable_query_log +# This should not be reachable. +set debug_dbug= @save_debug; +--enable_query_log +} + +--echo # Check IMPORT TABLESPACE +--let $MYSQLD_DATADIR= `select @@datadir` +create or replace table t2 (x int); +alter table t2 discard tablespace; + +create or replace table t1 (x tinyint); +insert into t1 set x= 42; +alter table t1 modify x int; +flush tables t1 for export; +--move_file $MYSQLD_DATADIR/best/t1.cfg $MYSQLD_DATADIR/best/t2.cfg +--copy_file $MYSQLD_DATADIR/best/t1.ibd $MYSQLD_DATADIR/best/t2.ibd +unlock tables; + +alter table t2 import tablespace; + +select * from t2; +check table t2 extended; +call check_table('t2'); + +--echo # Check innobase_col_to_mysql() len < flen +create or replace table t1 (x mediumint); +insert into t1 values (1); +insert into t1 values (1); +--enable_info +alter table t1 add column y int first, modify x int; +--error ER_DUP_ENTRY +alter table t1 add column z int first, add primary key (x); +--disable_info + +--echo # Check assertion in wrong instant operation +create or replace table t1 (a varchar(26) not null) default character set utf8mb4; +insert into t1 values ('abcdef'), (repeat('x',26)); +--enable_info +alter ignore table t1 modify a varchar(25) not null; +--disable_info +select * from t1; + +--echo # Check row_mysql_store_col_in_innobase_format() +create or replace table t1(x int primary key, a varchar(20)); +insert into t1 (x) values (1); +update t1 set a= 'foo' where x = 2; + +--echo # +--echo # MDEV-18124 PK on inplace-enlarged type fails +--echo # +create or replace table t1 (x int, y int); +insert into t1 (x, y) values (11, 22); +--enable_info +alter table t1 modify x bigint; +alter table t1 add primary key (x); +--disable_info +select * from t1; +check table t1; + +create or replace table t1 (a varchar(10), y int); +insert into t1 (a, y) values ("0123456789", 33); +--enable_info +alter table t1 modify a char(15); +alter table t1 add primary key (a); +--disable_info +select * from t1; +check table t1; + +create or replace table t1 (x int primary key, y int); +insert into t1 (x, y) values (44, 55); +--enable_info +alter table t1 modify x bigint; +--disable_info +select * from t1; +check table t1; + +create or replace table t1 (x int primary key, y int); +insert into t1 values (66, 77); +--enable_info +alter table t1 add column z int; +alter table t1 drop column y; +--disable_info +select * from t1; +check table t1; + +create or replace table t1 (x integer, a varchar(20)); +--enable_info +insert into t1 (x, a) values (73, 'a'); +alter table t1 add index idx3 (a); +alter table t1 modify a char(20); +--disable_info +select * from t1; +check table t1; + +drop database best; diff --git a/mysql-test/suite/innodb/t/instant_alter_import.test b/mysql-test/suite/innodb/t/instant_alter_import.test index 5bd10a7994e..fb187debb51 100644 --- a/mysql-test/suite/innodb/t/instant_alter_import.test +++ b/mysql-test/suite/innodb/t/instant_alter_import.test @@ -43,10 +43,42 @@ flush tables t2 for export; --move_file $MYSQLD_DATADIR/test/t2.cfg $MYSQLD_DATADIR/test/t1.cfg --copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd unlock tables; +--copy_file $MYSQLD_DATADIR/test/t1.cfg $MYSQLD_DATADIR/test/t1b.cfg +--copy_file $MYSQLD_DATADIR/test/t1.ibd $MYSQLD_DATADIR/test/t1b.ibd --echo # The instant ADD COLUMN has to be removed from the metadata. alter table t1 import tablespace; select * from t1; +--echo # Remove metadata for instant DROP COLUMN, then import +alter table t1 drop x, add column x int first, algorithm instant; +select * from t1; +alter table t1 discard tablespace; + +--move_file $MYSQLD_DATADIR/test/t1b.cfg $MYSQLD_DATADIR/test/t1.cfg +--move_file $MYSQLD_DATADIR/test/t1b.ibd $MYSQLD_DATADIR/test/t1.ibd +alter table t1 import tablespace; +select * from t1; + +--echo # Import a data file that contains instant DROP COLUMN metadata +alter table t2 drop x; +alter table t1 drop x, force; +alter table t1 discard tablespace; + +flush tables t2 for export; +--move_file $MYSQLD_DATADIR/test/t2.cfg $MYSQLD_DATADIR/test/t1.cfg +--copy_file $MYSQLD_DATADIR/test/t2.ibd $MYSQLD_DATADIR/test/t1.ibd +unlock tables; + +--error ER_TABLE_SCHEMA_MISMATCH +alter table t1 import tablespace; +--error ER_TABLESPACE_DISCARDED +select * from t1; +--remove_file $MYSQLD_DATADIR/test/t1.cfg +--error ER_INTERNAL_ERROR +alter table t1 import tablespace; +--error ER_TABLESPACE_DISCARDED +select * from t1; + drop table t2; drop table t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_index_rename.test b/mysql-test/suite/innodb/t/instant_alter_index_rename.test new file mode 100644 index 00000000000..af66c1027cc --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_index_rename.test @@ -0,0 +1,229 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_sequence.inc + +delimiter |; +create function get_index_id(tbl_id int, index_name char(100)) + returns int +begin + declare res int; + select index_id into res from information_schema.innodb_sys_indexes where + name=index_name and table_id = tbl_id; + return res; +end| + +delimiter ;| + +create table t ( + pk int primary key, + a int, + b int, + c int, + unique index a_key (a), + key c_key (c) +) engine=innodb stats_persistent=1; + +insert into t values (1, 1, 1, 1); + +set @table_id = (select table_id from information_schema.innodb_sys_tables where name='test/t'); + +set @a_key_id = get_index_id(@table_id, 'a_key'); +set @c_key_id = get_index_id(@table_id, 'c_key'); +set @primary_id = get_index_id(@table_id, 'primary'); + +select distinct(index_name) from mysql.innodb_index_stats where table_name = 't'; +alter table t + drop index a_key, + add unique index a_key_strikes_back (a); +select distinct(index_name) from mysql.innodb_index_stats where table_name = 't'; + +check table t; +select @a_key_id = get_index_id(@table_id, 'a_key_strikes_back'), + @c_key_id = get_index_id(@table_id, 'c_key'), + @primary_id = get_index_id(@table_id, 'primary'); + +set @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_strikes_back'); +set @c_key_id = get_index_id(@table_id, 'c_key'); +set @primary_id = get_index_id(@table_id, 'primary'); + +alter table t + drop index a_key_strikes_back, + add unique index a_key_returns (a), + drop primary key, + add primary key (pk), + add unique index b_key (b); + +check table t; +select @a_key_strikes_back_id = get_index_id(@table_id, 'a_key_returns'), + @c_key_id = get_index_id(@table_id, 'c_key'), + @primary_id = get_index_id(@table_id, 'primary'); + +set @a_key_returns_id = get_index_id(@table_id, 'a_key_returns'); +set @b_key_id = get_index_id(@table_id, 'b_key'); +set @c_key_id = get_index_id(@table_id, 'c_key'); +set @primary_id = get_index_id(@table_id, 'primary'); + +alter table t + drop key c_key, + add key c_key2 (c); + +check table t; +select @a_key_returns_id = get_index_id(@table_id, 'a_key_returns'), + @b_key_id = get_index_id(@table_id, 'b_key'), + @c_key_id = get_index_id(@table_id, 'c_key2'), + @primary_id = get_index_id(@table_id, 'primary'); + +drop table t; + +create table errors ( + a int, + unique key a_key (a), + b int +) engine=innodb; + +--error ER_CANT_DROP_FIELD_OR_KEY +alter table errors + drop key a_key, + drop key a_key, + add unique key a_key2 (a); + +--error ER_CANT_DROP_FIELD_OR_KEY +alter table errors + drop key a_key, + drop key a_key2, + add unique key a_key2 (a); + +--error ER_CANT_DROP_FIELD_OR_KEY +alter table errors + add key b_key (b), + drop key b_key, + add key bb_key (b); + +--error ER_CANT_DROP_FIELD_OR_KEY +alter table errors + drop key a_key, + add key a_key2 (a), + drop key a_key, + add key a_key2 (a); + +drop table errors; + +--disable_query_log +call mtr.add_suppression("Flagged corruption of `a_key` in table `test`.`corrupted` in dict_set_index_corrupted"); +--enable_query_log + +create table corrupted ( + a int, + key a_key (a) +) engine=innodb; + +insert into corrupted values (1); + +select * from corrupted; + +SET @save_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,dict_set_index_corrupted'; +check table corrupted; +SET debug_dbug = @save_dbug; + +--error ER_INDEX_CORRUPT +select * from corrupted; + +--error ER_INDEX_CORRUPT +alter table corrupted + drop key a_key, + add key a_key2 (a); + +alter table corrupted + drop key a_key; + +select * from corrupted; + +check table corrupted; + +drop table corrupted; + +create table t ( + a int, + unique key a_key (a) +) engine=innodb stats_persistent=1; + +SET @save_dbug = @@SESSION.debug_dbug; +SET debug_dbug = '+d,ib_rename_index_fail1'; +-- error ER_LOCK_DEADLOCK +alter table t + drop key a_key, + add unique key a_key2 (a), + algorithm=instant; +SET debug_dbug = @save_dbug; + +--error ER_WRONG_NAME_FOR_INDEX +alter table t + drop key a_key, + add unique key `GEN_CLUST_INDEX` (a), + algorithm=instant; + +show create table t; + +drop table t; + + +create table rename_column_and_index ( + a int, + unique index a_key(a) +) engine=innodb; + +insert into rename_column_and_index values (1), (3); + +alter table rename_column_and_index + change a aa int, + drop key a_key, + add unique key aa_key(aa), + algorithm=instant; + +show create table rename_column_and_index; +check table rename_column_and_index; +drop table rename_column_and_index; + + +--echo # +--echo # MDEV-19189: ASAN memcpy-param-overlap in fill_alter_inplace_info upon adding indexes +--echo # + +CREATE TABLE t1 (f1 INT, f2 INT, f3 INT); +ALTER TABLE t1 ADD FOREIGN KEY f (f2) REFERENCES xx(f2); +ALTER TABLE t1 ADD FOREIGN KEY (f2) REFERENCES t1(f2), ADD KEY (f3), ADD KEY (f1); +DROP TABLE t1; + +--echo # +--echo # MDEV-21669 InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB +--echo # +CREATE TABLE t1 (col_int INTEGER, col_char CHAR(20), col_varchar VARCHAR(500)) ENGINE=InnoDB; +SET @table_id = (SELECT table_id FROM information_schema.innodb_sys_tables WHERE name='test/t1'); +ALTER TABLE t1 ADD KEY idx3 (col_varchar(9)), ADD KEY idX2 (col_char(9)); +SET @idx3_key_id = get_index_id(@table_id, 'iDx3'); +ALTER TABLE t1 DROP KEY iDx3, ADD KEY Idx3 (col_varchar(9)); +SELECT @idx3_key_id = get_index_id(@table_id, 'Idx3'); +CHECK TABLE t1 EXTENDED ; +DROP TABLE t1; + +DROP FUNCTION get_index_id; + +--echo # +--echo # MDEV-23356 InnoDB: Failing assertion: field->col->mtype == type, crash or ASAN failures in row_sel_convert_mysql_key_to_innobase, InnoDB indexes are inconsistent after INDEX changes +--echo # + +CREATE TABLE t1 (a INT, b INT, c CHAR(8), + KEY ind1(c), KEY ind2(b)) ENGINE=InnoDB STATS_PERSISTENT=1; + +INSERT INTO t1 SELECT 1, 1, 'a' FROM seq_1_to_100; + +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; + +ALTER TABLE t1 DROP INDEX ind2, ADD INDEX ind3(b), + DROP INDEX ind1, ADD INDEX ind2(c); + +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; + +UPDATE t1 SET a = 1 WHERE c = 'foo'; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_limit.test b/mysql-test/suite/innodb/t/instant_alter_limit.test new file mode 100644 index 00000000000..b7fb3ee55c1 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_limit.test @@ -0,0 +1,76 @@ +--source include/innodb_page_size.inc + +SET @old_instant= +(SELECT variable_value FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'); + +CREATE TABLE t(a INT PRIMARY KEY, b INT, c INT, d INT, e INT) +ENGINE=InnoDB; +INSERT INTO t VALUES(1,2,3,4,5); +SET innodb_strict_mode = OFF; +--disable_query_log +call mtr.add_suppression("\\[Warning\\] InnoDB: Cannot add .* in table `test`\\.`t` because after adding it, the row size"); +let $n=253; +while ($n) { +dec $n; +ALTER TABLE t DROP b, DROP c, DROP d, DROP e, +ADD COLUMN b INT FIRST, ADD COLUMN c INT, ADD COLUMN d INT AFTER b, +ADD COLUMN e INT AFTER c, ALGORITHM=INSTANT; +} +--enable_query_log +SELECT * FROM t; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t DROP b, DROP c, DROP d, DROP e, +ADD COLUMN b INT, ALGORITHM=INSTANT; +ALTER TABLE t CHANGE COLUMN b beta INT AFTER a, ALGORITHM=INSTANT; +ALTER TABLE t DROP e, DROP c, DROP d, ALGORITHM=INSTANT; +SELECT * FROM t; +ALTER TABLE t DROP COLUMN beta, ALGORITHM=INSTANT; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +ALTER TABLE t ADD COLUMN b INT NOT NULL, ALGORITHM=INSTANT; + +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; + +ALTER TABLE t ADD COLUMN b INT NOT NULL; + +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; + +SELECT * FROM t; +ALTER TABLE t ADD COLUMN (c CHAR(255) NOT NULL, d BIGINT NOT NULL), +ALGORITHM=INSTANT; + +--disable_query_log +let $n=253; +while ($n) { +dec $n; +ALTER TABLE t DROP b, DROP c, DROP d, +ADD COLUMN (b INT NOT NULL, c CHAR(255) NOT NULL, d BIGINT NOT NULL); +} +--enable_query_log + +UPDATE t SET b=b+1,d=d+1,c='foo'; +SELECT * FROM t; + +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; + +DROP TABLE t; + +--echo # +--echo # MDEV-21787 Alter table failure tries to access uninitialized column +--echo # +CREATE TABLE t1(f1 INT PRIMARY KEY, f2 TEXT GENERATED ALWAYS AS (SUBSTR(f4, 1, 400)), f3 VARCHAR(500), f4 TEXT)ENGINE=InnoDB ROW_FORMAT=Compact; +ALTER TABLE t1 ADD UNIQUE KEY (f2(9)); +let $error_code = 0; +let $innodb_page_size = `SELECT @@INNODB_PAGE_SIZE`; +if ($innodb_page_size == 4k) { + let $error_code= ER_TOO_BIG_ROWSIZE; +} +--error $error_code +ALTER TABLE t1 ADD COLUMN f5 TEXT, ALGORITHM=INPLACE; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/instant_alter_null.test b/mysql-test/suite/innodb/t/instant_alter_null.test new file mode 100644 index 00000000000..69fb1ae4495 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_null.test @@ -0,0 +1,57 @@ +--source include/have_innodb.inc + +create table t (a int NOT NULL) engine=innodb row_format= compressed; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t modify a int NULL, algorithm=instant; +drop table t; + +create table t (a int NOT NULL) engine=innodb row_format= dynamic; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t modify a int NULL, algorithm=instant; +drop table t; + +create table t (a int NOT NULL) engine=innodb row_format= compact; +--error ER_ALTER_OPERATION_NOT_SUPPORTED +alter table t modify a int NULL, algorithm=instant; +drop table t; + +create table t ( + id int primary key, + a int NOT NULL default 0, + b int NOT NULL default 0, + c int NOT NULL default 0, + index idx (a,b,c) +) engine=innodb row_format=redundant; + +--error ER_BAD_NULL_ERROR +insert into t (id, a) values (0, NULL); +--error ER_BAD_NULL_ERROR +insert into t (id, b) values (0, NULL); +--error ER_BAD_NULL_ERROR +insert into t (id, c) values (0, NULL); + +insert into t values (1,1,1,1); + +set @id = (select table_id from information_schema.innodb_sys_tables +where name = 'test/t'); + +--replace_column 1 TABLE_ID +select * from information_schema.innodb_sys_columns where table_id=@id; + +alter table t modify a int NULL, algorithm=instant; +insert into t values (2, NULL, 2, 2); + +alter table t modify b int NULL, algorithm=nocopy; +insert into t values (3, NULL, NULL, 3); + +alter table t modify c int NULL, algorithm=inplace; +insert into t values (4, NULL, NULL, NULL); + +--replace_column 1 TABLE_ID +select * from information_schema.innodb_sys_columns where table_id=@id; + +select * from t; + +check table t; + +drop table t; diff --git a/mysql-test/suite/innodb/t/instant_alter_purge.test b/mysql-test/suite/innodb/t/instant_alter_purge.test new file mode 100644 index 00000000000..5ccce2ad1d6 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_alter_purge.test @@ -0,0 +1,76 @@ +--source include/have_innodb.inc +--source include/maybe_debug.inc +if ($have_debug) { +--source include/have_debug_sync.inc +} + +SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency; +SET GLOBAL innodb_purge_rseg_truncate_frequency=1; + +--echo # +--echo # MDEV-17793 Crash in purge after instant DROP and emptying the table +--echo # + +connect (prevent_purge,localhost,root); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +CREATE TABLE t1 (f1 INT, f2 INT) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (); +ALTER TABLE t1 DROP f2, ADD COLUMN f2 INT; +ALTER TABLE t1 DROP f1; +DELETE FROM t1; + +connection prevent_purge; +COMMIT; +START TRANSACTION WITH CONSISTENT SNAPSHOT; +connection default; + +ALTER TABLE t1 ADD COLUMN extra TINYINT UNSIGNED NOT NULL DEFAULT 42; +let $wait_all_purged= 1; +--source include/wait_all_purged.inc +ALTER TABLE t1 DROP extra; +disconnect prevent_purge; +let $wait_all_purged= 0; +--source include/wait_all_purged.inc +DROP TABLE t1; + +--echo # +--echo # MDEV-17813 Crash in instant ALTER TABLE due to purge +--echo # concurrently emptying table +--echo # +CREATE TABLE t1 (f2 INT) ENGINE=InnoDB; +INSERT INTO t1 SET f2=1; +ALTER TABLE t1 ADD COLUMN f1 INT; + +connect (purge_control,localhost,root); +START TRANSACTION WITH CONSISTENT SNAPSHOT; + +connection default; +DELETE FROM t1; + +if ($have_debug) { +SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL go WAIT_FOR do'; +} +send ALTER TABLE t1 ADD COLUMN f3 INT; + +connection purge_control; +if ($have_debug) { +SET DEBUG_SYNC='now WAIT_FOR go'; +} +COMMIT; +SET GLOBAL innodb_max_purge_lag_wait= 0; +if ($have_debug) { +--source include/wait_all_purged.inc +SET DEBUG_SYNC='now SIGNAL do'; +} +disconnect purge_control; + +connection default; +reap; +if ($have_debug) { +SET DEBUG_SYNC=RESET; +} +DROP TABLE t1; + +SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; diff --git a/mysql-test/suite/innodb/t/instant_alter_rollback.test b/mysql-test/suite/innodb/t/instant_alter_rollback.test index b68a6ad3880..cfece7e0738 100644 --- a/mysql-test/suite/innodb/t/instant_alter_rollback.test +++ b/mysql-test/suite/innodb/t/instant_alter_rollback.test @@ -8,28 +8,49 @@ FLUSH TABLES; --echo # --echo # MDEV-11369: Instant ADD COLUMN for InnoDB +--echo # MDEV-15562: Instant DROP COLUMN or changing the order of columns --echo # connect to_be_killed, localhost, root; +SET @old_instant= +(SELECT variable_value FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'); CREATE TABLE empty (id INT PRIMARY KEY, c2 INT UNIQUE) ENGINE=InnoDB; CREATE TABLE once LIKE empty; CREATE TABLE twice LIKE empty; +CREATE TABLE thrice LIKE empty; INSERT INTO once SET id=1,c2=1; INSERT INTO twice SET id=1,c2=1; +INSERT INTO thrice SET id=1,c2=1; ALTER TABLE empty ADD COLUMN (d1 INT DEFAULT 15); ALTER TABLE once ADD COLUMN (d1 INT DEFAULT 20); ALTER TABLE twice ADD COLUMN (d1 INT DEFAULT 20); +ALTER TABLE thrice ADD COLUMN (d1 INT DEFAULT 20); ALTER TABLE twice ADD COLUMN (d2 INT NOT NULL DEFAULT 10, d3 VARCHAR(15) NOT NULL DEFAULT 'var och en char'); +ALTER TABLE thrice ADD COLUMN +(d2 INT NOT NULL DEFAULT 10, + d3 TEXT NOT NULL DEFAULT 'con'); +ALTER TABLE thrice DROP c2, DROP d3, CHANGE d2 d3 INT NOT NULL FIRST; + +SELECT variable_value-@old_instant instants +FROM information_schema.global_status +WHERE variable_name = 'innodb_instant_alter_column'; BEGIN; INSERT INTO empty set id=0,c2=42; UPDATE once set c2=c2+1; UPDATE twice set c2=c2+1; +UPDATE thrice set d3=d3+1; INSERT INTO twice SET id=2,c2=0,d3=''; +INSERT INTO thrice SET id=2,d3=0; +DELETE FROM empty; +DELETE FROM once; +DELETE FROM twice; +DELETE FROM thrice; connection default; SET GLOBAL innodb_flush_log_at_trx_commit=1; @@ -48,4 +69,5 @@ SET GLOBAL innodb_purge_rseg_truncate_frequency=@saved_frequency; SELECT * FROM empty; SELECT * FROM once; SELECT * FROM twice; -DROP TABLE empty, once, twice; +SELECT * FROM thrice; +DROP TABLE empty, once, twice, thrice; diff --git a/mysql-test/suite/innodb/t/instant_auto_inc.test b/mysql-test/suite/innodb/t/instant_auto_inc.test new file mode 100644 index 00000000000..4aea81c1c49 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_auto_inc.test @@ -0,0 +1,13 @@ +--source include/have_innodb.inc + +create table t(id int primary key, a int) engine=InnoDB; +insert into t (id, a) values (1, 1); +alter table t modify column id int auto_increment; +check table t; +insert into t (a) values (2); +alter table t modify column id int, algorithm=instant; +check table t; +insert into t (id, a) values (3, 3); +select * from t; +check table t; +drop table t; diff --git a/mysql-test/suite/innodb/t/instant_drop.test b/mysql-test/suite/innodb/t/instant_drop.test new file mode 100644 index 00000000000..566ac02b314 --- /dev/null +++ b/mysql-test/suite/innodb/t/instant_drop.test @@ -0,0 +1,108 @@ +--source include/have_innodb.inc + +create table t1(f1 int not null, f2 int not null, f3 int not null)engine=innodb; +insert into t1 values(1, 2, 3),(4, 5, 6); +alter table t1 drop column f2, algorithm=instant; +select * from t1; +insert into t1 values(1,2); +select * from t1; +alter table t1 add column f4 int not null default 5, algorithm=instant; +select * from t1; +alter table t1 drop column f1, algorithm=instant; +select * from t1; +insert into t1 values(7, 9); +select * from t1; +alter table t1 add column f5 blob default repeat('aaa', 950), drop column f4, algorithm=instant; +select * from t1; +select f3 from t1; +update t1 set f3 = 10 where f3 > 2; +select * from t1; +delete from t1 where f3 = 10; +show create table t1; +select f3 from t1; +update t1 set f5 = 'world'; +select * from t1; +drop table t1; + +create table t1(f1 int, f2 int not null, index idx(f2))engine=innodb; +insert into t1 values(1, 2); +alter table t1 drop column f1, add column f3 varchar(100) default 'thiru', algorithm=instant; +select * from t1 force index (idx); +alter table t1 drop column f3, algorithm=instant; +select * from t1; +begin; +insert into t1 values(10); +select * from t1; +update t1 set f2 = 100; +select * from t1; +delete from t1 where f2 = 100; +select * from t1; +rollback; +select * from t1; +show create table t1; +drop table t1; + +create table t1(f1 int, f2 int not null)engine=innodb; +insert into t1 values(1, 2); +alter table t1 drop column f2, algorithm=instant; +insert into t1 values(NULL); +select * from t1; +drop table t1; + +create table t1(f1 int not null, f2 int not null)engine=innodb; +insert into t1 values(1, 2); +alter table t1 add column f5 int default 10, algorithm=instant; +alter table t1 add column f3 int not null default 100, algorithm=instant; +alter table t1 add column f4 int default 100, drop column f3, algorithm=instant; +insert into t1 values(2, 3, 20, 100); +select * from t1; +drop table t1; + +create table t1(f1 int not null, f2 int not null) engine=innodb; +insert into t1 values(1, 1); +alter table t1 drop column f2, add column f3 int default 3, algorithm=instant; +select * from t1; +update t1 set f3 = 19; +select * from t1; +alter table t1 drop column f1, add column f5 tinyint default 10 first, +algorithm=instant; +insert into t1 values(4, 10); +select * from t1; + +create table t2(f1 int, f2 int not null) engine=innodb; +insert into t2(f1, f2) values(1, 2); +alter table t2 drop column f2, add column f4 varchar(100) default repeat('a', 20), add column f5 int default 10, algorithm=instant; +select * from t2; +show create table t2; +alter table t2 add column f6 char(100) default repeat('a', 99), algorithm=instant; + +create table t3(f1 int, f2 int not null)engine=innodb; +insert into t3 values(1, 2); +alter table t3 drop column f2, add column f3 int default 1, add column f4 int default 4, algorithm=instant; + +create table t4(a varchar(1), b int, c int, primary key(a,b))engine=innodb; +insert into t4 values('4',5,6); +alter table t4 drop column c; + +--source include/restart_mysqld.inc +select * from t1; +alter table t1 add column f6 int default 9,drop column f5, algorithm = instant; +insert into t1 values(4, 9); +alter table t1 force, algorithm=inplace; +select * from t1; + +select * from t2; +alter table t2 force, algorithm=inplace; +select * from t2; +show create table t2; + +select * from t3; +alter table t3 add column f5 char(100) default repeat('a', 99), algorithm=instant; + +select * from t4; +alter table t4 add column d varchar(5) default 'fubar'; +insert into t4 values('',0,'snafu'); +--source include/restart_mysqld.inc +select * from t3; +select * from t4; +drop table t1,t2,t3,t4; diff --git a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.combinations b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.combinations new file mode 100644 index 00000000000..729380593f3 --- /dev/null +++ b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test index faed87630b2..8943ee2352f 100644 --- a/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test +++ b/mysql-test/suite/innodb/t/leaf_page_corrupted_during_recovery.test @@ -20,6 +20,7 @@ INSERT INTO t1 VALUES(1, 'sql'), (2, 'server'), (3, 'mariadb'), (8, 'test4'), (9, 'test5'), (10, 'test6'), (11, 'test7'), (12, 'test8'); +let $restart_noprint=2; --source include/restart_mysqld.inc let INNODB_PAGE_SIZE=`select @@innodb_page_size`; @@ -37,7 +38,7 @@ perl; my $file = "$ENV{MYSQLD_DATADIR}/test/t1.ibd"; open(FILE, "+<$file") || die "Unable to open $file"; binmode FILE; -seek (FILE, $ENV{INNODB_PAGE_SIZE} * 19, SEEK_SET) or die "seek"; +seek (FILE, $ENV{INNODB_PAGE_SIZE} * 19 + 38, SEEK_SET) or die "seek"; print FILE "junk"; close FILE or die "close"; EOF diff --git a/mysql-test/suite/innodb/t/log_data_file_size.test b/mysql-test/suite/innodb/t/log_data_file_size.test index f01e013ddfa..fe75b9ab236 100644 --- a/mysql-test/suite/innodb/t/log_data_file_size.test +++ b/mysql-test/suite/innodb/t/log_data_file_size.test @@ -57,6 +57,7 @@ for (my $d = $d1; $d < $d2 + 64; $d++) { close FILE; open(FILE, ">$ENV{MYSQLTEST_VARDIR}/log/start_mysqld.txt") || die; print FILE "--let \$restart_parameters=$restart\n" if $restart; +print FILE "--let \$restart_noprint=2\n"; print FILE "--source include/start_mysqld.inc\n"; close FILE; open(FILE, "+<", "$ENV{'MYSQLD_DATADIR'}test/ibd4.ibd") or die; diff --git a/mysql-test/suite/innodb/t/missing_tablespaces.test b/mysql-test/suite/innodb/t/missing_tablespaces.test index 98efc51d7b8..b4da9745ba4 100644 --- a/mysql-test/suite/innodb/t/missing_tablespaces.test +++ b/mysql-test/suite/innodb/t/missing_tablespaces.test @@ -16,6 +16,7 @@ USE `..................................................`; CREATE TABLE `..................................................` (ID INT) ENGINE=INNODB; +let $restart_noprint=2; --source include/shutdown_mysqld.inc --remove_file $MYSQLTEST_VARDIR/mysqld.1/data/@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e/@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e@002e.ibd diff --git a/mysql-test/suite/innodb/t/page_id_innochecksum.test b/mysql-test/suite/innodb/t/page_id_innochecksum.test index a7e61cdbe5f..106da09df6d 100644 --- a/mysql-test/suite/innodb/t/page_id_innochecksum.test +++ b/mysql-test/suite/innodb/t/page_id_innochecksum.test @@ -1,5 +1,6 @@ --source include/have_innodb.inc --source include/not_embedded.inc +--source include/innodb_checksum_algorithm.inc --echo # Set the environmental variables let MYSQLD_BASEDIR= `SELECT @@basedir`; let MYSQLD_DATADIR= `SELECT @@datadir`; @@ -9,6 +10,7 @@ create table t1(f1 int not null)engine=innodb; insert into t1 values(1), (2), (3); let $resultlog=$MYSQLTEST_VARDIR/tmp/result.log; +let $restart_noprint=2; --source include/shutdown_mysqld.inc --echo # Change the page offset perl; @@ -21,22 +23,32 @@ my $page_size = $ENV{INNODB_PAGE_SIZE}; sysopen IBD_FILE, "$ENV{MYSQLD_DATADIR}/test/t1.ibd", O_RDWR || die "Cannot open t1.ibd\n"; -sysread(IBD_FILE, $_, 38) || die "Cannot read t1.ibd\n"; -my $space = unpack("x[34]N", $_); +sysread(IBD_FILE, $_, 58) || die "Cannot read t1.ibd\n"; +my ($space,$flags) = unpack("x[34]Nx[16]N", $_); +my $full_crc32 = $flags & 0x10; sysseek(IBD_FILE, $page_size * 3, SEEK_SET) || die "Cannot seek t1.ibd\n"; -my $head = pack("Nx[18]", 4); # better to have a valid page number -my $body = chr(0) x ($page_size - 38 - 8); +my $page= pack("NNx[18]NNN", 0, 4, 1, 0, $space).(chr(0) x ($page_size - 38)); -# Calculate innodb_checksum_algorithm=crc32 for the unencrypted page. -# The following bytes are excluded: -# bytes 0..3 (the checksum is stored there) -# bytes 26..37 (encryption key version, post-encryption checksum, tablespace id) -# bytes $page_size-8..$page_size-1 (checksum, LSB of FIL_PAGE_LSN) +# Calculate innodb_checksum_algorithm=crc32 or full_crc32. my $polynomial = 0x82f63b78; # CRC-32C -my $ck = mycrc32($head, 0, $polynomial) ^ mycrc32($body, 0, $polynomial); +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $page_size-4), 0, $polynomial); + substr($page, $page_size-4, 4) = pack("N", $ck); +} +else +{ + # The following bytes are excluded: + # 0..3 (the checksum is stored there) + # 26..37 (encryption key version, post-encryption checksum, tablespace id) + # $page_size-8..$page_size-1 (checksum, LSB of FIL_PAGE_LSN) + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ + mycrc32(substr($page, 38, $page_size-38-8), 0, $polynomial)); + substr($page,0,4)=$ck; + substr($page,$page_size-8,4)=$ck; +} -my $page= pack("N",$ck).$head.pack("NNN",1,$ck,$space).$body.pack("Nx[4]",$ck); die unless syswrite(IBD_FILE, $page, $page_size) == $page_size; close IBD_FILE; EOF diff --git a/mysql-test/suite/innodb/t/purge_thread_shutdown.test b/mysql-test/suite/innodb/t/purge_thread_shutdown.test index 8a9a834454c..5be29b7a6a3 100644 --- a/mysql-test/suite/innodb/t/purge_thread_shutdown.test +++ b/mysql-test/suite/innodb/t/purge_thread_shutdown.test @@ -32,7 +32,9 @@ delete from t1 where a=3; error ER_WRONG_VALUE_FOR_VAR; set global innodb_fast_shutdown=0; -let $me=`select connection_id()`; +# Get id with space prefix to ensure that replace_result doesn't replace +# the error code +let $me=`select concat(' ', connection_id())`; replace_result $me ID; error ER_CONNECTION_KILLED; eval kill $me; diff --git a/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test b/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test index f1d1537237e..645ae8c7855 100644 --- a/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test +++ b/mysql-test/suite/innodb/t/redo_log_during_checkpoint.test @@ -12,6 +12,7 @@ # is independent of the page size. --source include/have_innodb_16k.inc +let $restart_noprint=2; SET GLOBAL innodb_page_cleaner_disabled_debug = 1; SET GLOBAL innodb_dict_stats_disabled_debug = 1; SET GLOBAL innodb_master_thread_disabled_debug = 1; diff --git a/mysql-test/suite/innodb/t/restart.test b/mysql-test/suite/innodb/t/restart.test index d7582306492..a7a7855ba7b 100644 --- a/mysql-test/suite/innodb/t/restart.test +++ b/mysql-test/suite/innodb/t/restart.test @@ -14,8 +14,8 @@ let page_size= `select @@innodb_page_size`; --echo # FIXME: Unlike MySQL, maybe MariaDB should not read the .ibd files --echo # of tables with .isl file or DATA DIRECTORY attribute. -call mtr.add_suppression("\\[ERROR\\] InnoDB: Invalid flags 0x7a207879 in .*td\\.ibd"); --echo # FIXME: This is much more noisy than MariaDB 10.1! +call mtr.add_suppression("\\[ERROR\\] InnoDB: Tablespace flags are invalid in datafile: .*test.t[rcd]\\.ibd"); call mtr.add_suppression("\\[ERROR\\] InnoDB: Operating system error number .* in a file operation\\."); call mtr.add_suppression("\\[ERROR\\] InnoDB: The error means the system cannot find the path specified\\."); call mtr.add_suppression("\\[ERROR\\] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them\\."); diff --git a/mysql-test/suite/innodb/t/row_format_redundant.test b/mysql-test/suite/innodb/t/row_format_redundant.test index b23093009bb..e8869b886c8 100644 --- a/mysql-test/suite/innodb/t/row_format_redundant.test +++ b/mysql-test/suite/innodb/t/row_format_redundant.test @@ -95,6 +95,9 @@ do "$ENV{MTR_SUITE_DIR}/include/crc32.pl"; my $ps= $ENV{INNODB_PAGE_SIZE}; my $file= "$ENV{bugdir}/ibdata1"; open(FILE, "+<", $file) || die "Unable to open $file\n"; +die "Unable to read $file" unless sysread(FILE, $_, $ps) == $ps; +my $full_crc32 = unpack("N",substr($_,54,4)) & 0x10; # FIL_SPACE_FLAGS; +sysseek(FILE, 0, 0) || die "Unable to seek $file"; # Read DICT_HDR_TABLES, the root page number of CLUST_IND (SYS_TABLES.NAME). sysseek(FILE, 7*$ps+38+32, 0) || die "Unable to seek $file"; die "Unable to read $file" unless sysread(FILE, $_, 4) == 4; @@ -121,10 +124,16 @@ for (my $offset= 0x65; $offset; } } my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ +if ($full_crc32) { + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} else { + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} + sysseek(FILE, $sys_tables_root*$ps, 0) || die "Unable to seek $file"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file\n"; diff --git a/mysql-test/suite/innodb/t/system_tables.test b/mysql-test/suite/innodb/t/system_tables.test index 90cb8c59fbd..172628790f8 100644 --- a/mysql-test/suite/innodb/t/system_tables.test +++ b/mysql-test/suite/innodb/t/system_tables.test @@ -9,4 +9,4 @@ insert envois3 values ('2008-08-11 22:43:00'); --source include/restart_mysqld.inc select convert_tz(starttime,'UTC','Europe/Moscow') starttime from envois3; drop table envois3; -alter table mysql.time_zone_name engine=MyISAM; +alter table mysql.time_zone_name engine=Aria; diff --git a/mysql-test/suite/innodb/t/table_flags.combinations b/mysql-test/suite/innodb/t/table_flags.combinations new file mode 100644 index 00000000000..729380593f3 --- /dev/null +++ b/mysql-test/suite/innodb/t/table_flags.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/innodb/t/table_flags.opt b/mysql-test/suite/innodb/t/table_flags.opt new file mode 100644 index 00000000000..c44c611ed60 --- /dev/null +++ b/mysql-test/suite/innodb/t/table_flags.opt @@ -0,0 +1 @@ +--innodb-checksum-algorithm=crc32 diff --git a/mysql-test/suite/innodb/t/table_flags.test b/mysql-test/suite/innodb/t/table_flags.test index 13e1fc01dc0..79b2c3dd77a 100644 --- a/mysql-test/suite/innodb/t/table_flags.test +++ b/mysql-test/suite/innodb/t/table_flags.test @@ -16,6 +16,7 @@ call mtr.add_suppression("InnoDB: Operating system error number .* in a file ope call mtr.add_suppression("InnoDB: The error means the system cannot find the path specified"); call mtr.add_suppression("InnoDB: If you are installing InnoDB, remember that you must create directories yourself"); call mtr.add_suppression("InnoDB: adjusting FSP_SPACE_FLAGS of file "); +call mtr.add_suppression("InnoDB: Parent table of FTS auxiliary table .* not found."); FLUSH TABLES; --enable_query_log @@ -34,6 +35,7 @@ let bugdir= $MYSQLTEST_VARDIR/tmp/table_flags; if ($have_debug) { --let $d=$d --debug=d,create_and_drop_garbage } +--let $restart_noprint=1 --let $restart_parameters=$d --innodb-stats-persistent=0 --source include/restart_mysqld.inc @@ -56,6 +58,8 @@ do "$ENV{MTR_SUITE_DIR}/include/crc32.pl"; my $ps= $ENV{INNODB_PAGE_SIZE}; my $file= "$ENV{bugdir}/ibdata1"; open(FILE, "+<", $file) || die "Unable to open $file\n"; +die "Unable to read $file" unless sysread(FILE, $_, 58) == 58; +my $full_crc32 = unpack("N",substr($_,54,4)) & 0x10; # FIL_SPACE_FLAGS # Read DICT_HDR_TABLES, the root page number of CLUST_IND (SYS_TABLES.NAME). sysseek(FILE, 7*$ps+38+32, 0) || die "Unable to seek $file"; die "Unable to read $file" unless sysread(FILE, $_, 4) == 4; @@ -129,10 +133,18 @@ for (my $offset= 0x65; $offset; print ")\n"; } my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps-4), 0, $polynomial); + substr($page, $ps-4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} sysseek(FILE, $sys_tables_root*$ps, 0) || die "Unable to seek $file"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file\n"; @@ -226,3 +238,16 @@ call mtr.add_suppression("ERROR HY000: Can't create table `test`.`t1`"); CREATE TABLE t1(f1 INT, f2 VARCHAR(1), KEY k1(f2), FULLTEXT KEY(f2), FOREIGN KEY (f2) REFERENCES t1(f3))ENGINE=InnoDB; + +--echo # +--echo # MDEV-23199 page_compression flag is missing +--echo # for full_crc32 tablespace +--echo # + +CREATE TABLE t1(f1 BIGINT PRIMARY KEY)ENGINE=InnoDB; +INSERT INTO t1 VALUES(1); +ALTER TABLE t1 PAGE_COMPRESSED = 1; +INSERT INTO t1 VALUES(2); +let $shutdown_timeout = 0; +--source include/restart_mysqld.inc +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/trx_id_future.combinations b/mysql-test/suite/innodb/t/trx_id_future.combinations new file mode 100644 index 00000000000..729380593f3 --- /dev/null +++ b/mysql-test/suite/innodb/t/trx_id_future.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/innodb/t/trx_id_future.test b/mysql-test/suite/innodb/t/trx_id_future.test index e65dc537fd8..b897800fa91 100644 --- a/mysql-test/suite/innodb/t/trx_id_future.test +++ b/mysql-test/suite/innodb/t/trx_id_future.test @@ -14,6 +14,7 @@ INSERT INTO t1 VALUES(1); let MYSQLD_DATADIR=`select @@datadir`; --source include/wait_all_purged.inc +let $restart_noprint=2; --source include/shutdown_mysqld.inc perl; @@ -29,6 +30,8 @@ binmode FILE; my $ps= $ENV{PAGE_SIZE}; my $page; +die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; +my $full_crc32 = unpack("N",substr($page,54,4)) & 0x10; # FIL_SPACE_FLAGS sysseek(FILE, 3*$ps, 0) || die "Unable to seek $file\n"; die "Unable to read $file" unless sysread(FILE, $page, $ps) == $ps; #In this case the first record should be at offset 135 @@ -37,10 +40,18 @@ die unless unpack("n", substr($page, 99, 2)) == 135; substr($page,135+6,6) = "\xff" x 6; my $polynomial = 0x82f63b78; # CRC-32C -my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ +if ($full_crc32) +{ + my $ck = mycrc32(substr($page, 0, $ps - 4), 0, $polynomial); + substr($page, $ps - 4, 4) = pack("N", $ck); +} +else +{ + my $ck= pack("N",mycrc32(substr($page, 4, 22), 0, $polynomial) ^ mycrc32(substr($page, 38, $ps - 38 - 8), 0, $polynomial)); -substr($page,0,4)=$ck; -substr($page,$ps-8,4)=$ck; + substr($page,0,4)=$ck; + substr($page,$ps-8,4)=$ck; +} sysseek(FILE, 3*$ps, 0) || die "Unable to rewind $file\n"; syswrite(FILE, $page, $ps)==$ps || die "Unable to write $file\n"; close(FILE) || die "Unable to close $file"; diff --git a/mysql-test/suite/innodb/t/undo_truncate.opt b/mysql-test/suite/innodb/t/undo_truncate.opt deleted file mode 100644 index 1c897ab6cfc..00000000000 --- a/mysql-test/suite/innodb/t/undo_truncate.opt +++ /dev/null @@ -1 +0,0 @@ ---innodb-log-buffer-size=2m diff --git a/mysql-test/suite/innodb/t/undo_truncate.test b/mysql-test/suite/innodb/t/undo_truncate.test index 9b91c78e35a..af6ed2b4372 100644 --- a/mysql-test/suite/innodb/t/undo_truncate.test +++ b/mysql-test/suite/innodb/t/undo_truncate.test @@ -1,9 +1,5 @@ --source include/have_innodb.inc -# With 32k, truncation could happen on shutdown after the test, -# and the mtr.add_suppression() would not filter out the warning. -# With 64k, no truncation seems to happen. -# --source include/innodb_page_size.inc ---source include/innodb_page_size_small.inc +--source include/innodb_page_size.inc --source include/have_undo_tablespaces.inc SET @save_undo_logs = @@GLOBAL.innodb_undo_logs; diff --git a/mysql-test/suite/innodb/t/undo_truncate_recover.combinations b/mysql-test/suite/innodb/t/undo_truncate_recover.combinations new file mode 100644 index 00000000000..729380593f3 --- /dev/null +++ b/mysql-test/suite/innodb/t/undo_truncate_recover.combinations @@ -0,0 +1,5 @@ +[strict_crc32] +--innodb-checksum-algorithm=strict_crc32 + +[strict_full_crc32] +--innodb-checksum-algorithm=strict_full_crc32 diff --git a/mysql-test/suite/innodb/t/undo_truncate_recover.test b/mysql-test/suite/innodb/t/undo_truncate_recover.test index f3034c16988..64b1f6f23a1 100644 --- a/mysql-test/suite/innodb/t/undo_truncate_recover.test +++ b/mysql-test/suite/innodb/t/undo_truncate_recover.test @@ -28,6 +28,7 @@ while ($i) { --enable_query_log commit; +let $checksum_algorithm = `SELECT @@GLOBAL.innodb_checksum_algorithm`; let SEARCH_PATTERN = ib_undo_trunc; begin; update t1 set c = 'MariaDB'; @@ -36,11 +37,18 @@ eval set global debug_dbug = '+d,$SEARCH_PATTERN'; commit; drop table t1; call mtr.add_suppression("InnoDB: innodb_undo_tablespaces=0 disables dedicated undo log tablespaces"); -# FIXME: remove this work-around, and generate less log! -call mtr.add_suppression("InnoDB: The redo log transaction size "); SET GLOBAL innodb_fast_shutdown=0; --source include/shutdown_mysqld.inc --source include/search_pattern_in_file.inc -# FIXME: remove this work-around, and generate less log! ---let $restart_parameters= --innodb-buffer-pool-size=16m --innodb-undo-tablespaces=1 +--let $restart_parameters= --innodb-undo-tablespaces=1 +--let $restart_noprint=1 +if ($checksum_algorithm == "strict_full_crc32") +{ + let $restart_parameters= $restart_parameters --innodb_checksum_algorithm=strict_crc32; +} + +if ($checksum_algorithm == "strict_crc32") +{ + let $restart_parameters= $restart_parameters --innodb_checksum_algorithm=strict_full_crc32; +} --source include/start_mysqld.inc |