diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/change_column_collation.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/change_column_collation.test | 156 |
1 files changed, 156 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/change_column_collation.test b/mysql-test/suite/innodb/t/change_column_collation.test new file mode 100644 index 00000000..edc298fe --- /dev/null +++ b/mysql-test/suite/innodb/t/change_column_collation.test @@ -0,0 +1,156 @@ +--source include/have_innodb.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--source include/count_sessions.inc + +--echo # +--echo # MDEV-26294 Duplicate entries in unique index not detected when +--echo # changing collation with INPLACE algorithm +--echo # + +# Detect the duplicate entry after collation change of column + +SET NAMES utf8; +CREATE TABLE t1 ( + id INT PRIMARY KEY, + msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_swedish_ci UNIQUE +) ENGINE=INNODB; +INSERT INTO t1 VALUES (1, 'aaa'); +INSERT INTO t1 VALUES (2, 'ååå'); +--error ER_DUP_ENTRY +ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=inplace; +DROP TABLE t1; + +# PageBulk insert shouldn't fail like records are not in ascending order + +CREATE TABLE t1 ( + id INT PRIMARY KEY, + msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin, + id_2 INT not null, + unique index(msg, id_2) +) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1, 'aaa', 2); +INSERT INTO t1 VALUES (2, 'AAA', 3); + +ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=inplace; + +DROP TABLE t1; + +# Detect the duplicate entry from concurrent DML + +CREATE TABLE t1 ( + id INT PRIMARY KEY, + msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin, + unique index(msg) +) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1, 'aaa'); +INSERT INTO t1 VALUES (2, 'bbb'); +INSERT INTO t1 VALUES (3, 'ccc'); + +SET DEBUG_SYNC = 'RESET'; +SET DEBUG_SYNC = 'row_log_apply_before SIGNAL before_apply WAIT_FOR go_ahead'; +--send +ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=NOCOPY; + +connect (con1,localhost,root,,); +connection con1; + +SET DEBUG_SYNC = 'now WAIT_FOR before_apply'; +INSERT INTO t1 VALUES (4, 'AAA'); +UPDATE t1 set msg = "ddd" where id = 2; +DELETE FROM t1 WHERE id= 3; +SET DEBUG_SYNC = 'now SIGNAL go_ahead'; + +connection default; + +--error ER_DUP_ENTRY +reap; + +SET DEBUG_SYNC = 'RESET'; +SELECT * FROM t1; +DROP TABLE t1; + +# InnoDB should store the changed collation column into +# change_col_info in index when rollback of alter happens + +CREATE TABLE t1 ( + id INT PRIMARY KEY, + f1 INT NOT NULL, + f2 INT NOT NULL, + f3 INT NOT NULL, + msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin, + msg_1 VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin, + unique index(f1, msg, f2, msg_1, f3), + unique index(f1, msg_1, f2, msg, f3), + unique index(f1, msg, f3, msg_1, f2), + unique index(f1, msg_1, f3, msg, f2), + unique index(f2, msg_1, f1, msg, f3), + unique index(f2, msg, f3, msg_1, f1), + unique index(f3, f2, msg, msg_1, f1), + unique index(f3, msg, msg_1, f1, f2) +) ENGINE=INNODB; + +INSERT INTO t1 VALUES (1, 1, 1, 1, 'aaa', 'aaa'); +SET DEBUG_DBUG="+d,create_index_fail"; +SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal"; +--send +ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, MODIFY msg_1 VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=NOCOPY; + +connection con1; +SET DEBUG_SYNC="now WAIT_FOR con1_go"; +BEGIN; +SELECT * FROM t1; +SET DEBUG_SYNC="now SIGNAL alter_signal"; +connection default; +--error ER_DUP_ENTRY +reap; +CHECK TABLE t1; +connection con1; +rollback; +INSERT INTO t1 VALUES(2, 2, 2, 2, 'bbb', 'bbb'); +disconnect con1; +connection default; +SET DEBUG_SYNC=reset; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES(3, 3, 3, 3, 'ccc', 'ccc'); +DROP TABLE t1; + +# Inplace Collation change is not supported for virtual column +# and stored column + +CREATE TABLE t1(id INT PRIMARY KEY, msg VARCHAR(100), + msg_1 VARCHAR(100) AS (msg) VIRTUAL, + msg_2 VARCHAR(100) AS (msg) STORED, + UNIQUE(msg), UNIQUE(msg_1), + UNIQUE(msg_2))ENGINE=InnoDB; +--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN +ALTER TABLE t1 MODIFY msg_1 VARCHAR(100) CHARACTER SET utf8 + COLLATE utf8_unicode_ci, ALGORITHM=inplace; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +ALTER TABLE t1 MODIFY msg_2 VARCHAR(100) CHARACTER SET utf8 + COLLATE utf8_unicode_ci, ALGORITHM=inplace; +DROP TABLE t1; + +--echo # +--echo # MDEV-29314 Assertion `n_fields > n_cols' failed +--echo # in dict_index_t::init_change_cols +--echo # +CREATE TABLE t (a VARCHAR(16) COLLATE utf8_bin, + FULLTEXT (a)) ENGINE=InnoDB COLLATE utf8_unicode_520_ci; +ALTER TABLE t MODIFY COLUMN a VARCHAR(512); +SHOW CREATE TABLE t; +DROP TABLE t; + +--echo # +--echo # MDEV-31416 ASAN errors in dict_v_col_t::detach upon +--echo # adding key to virtual column +--echo # +CREATE TABLE t (a INT) ENGINE=InnoDB WITH SYSTEM VERSIONING; +SET SYSTEM_VERSIONING_ALTER_HISTORY= KEEP; +ALTER TABLE t ADD COLUMN v VARCHAR(128) GENERATED ALWAYS AS (CRC32('MariaDB')); +ALTER TABLE t ADD INDEX (v); +DROP TABLE t; +--source include/wait_until_count_sessions.inc |