Debian Codegraph @

mysql-test/suite/innodb/r/change_column_collation.result

raw
1 #
2 # MDEV-26294 Duplicate entries in unique index not detected when
3 # changing collation with INPLACE algorithm
4 #
5 SET NAMES utf8;
6 CREATE TABLE t1 (
7 id INT PRIMARY KEY,
8 msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_swedish_ci UNIQUE
9 ) ENGINE=INNODB;
10 INSERT INTO t1 VALUES (1, 'aaa');
11 INSERT INTO t1 VALUES (2, 'ååå');
12 ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=inplace;
13 ERROR 23000: Duplicate entry 'NULL' for key 'msg'
14 DROP TABLE t1;
15 CREATE TABLE t1 (
16 id INT PRIMARY KEY,
17 msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
18 id_2 INT not null,
19 unique index(msg, id_2)
20 ) ENGINE=INNODB;
21 INSERT INTO t1 VALUES (1, 'aaa', 2);
22 INSERT INTO t1 VALUES (2, 'AAA', 3);
23 ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=inplace;
24 DROP TABLE t1;
25 CREATE TABLE t1 (
26 id INT PRIMARY KEY,
27 msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
28 unique index(msg)
29 ) ENGINE=INNODB;
30 INSERT INTO t1 VALUES (1, 'aaa');
31 INSERT INTO t1 VALUES (2, 'bbb');
32 INSERT INTO t1 VALUES (3, 'ccc');
33 SET DEBUG_SYNC = 'RESET';
34 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL before_apply WAIT_FOR go_ahead';
35 ALTER TABLE t1 MODIFY msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci, ALGORITHM=NOCOPY;
36 connect con1,localhost,root,,;
37 connection con1;
38 SET DEBUG_SYNC = 'now WAIT_FOR before_apply';
39 INSERT INTO t1 VALUES (4, 'AAA');
40 UPDATE t1 set msg = "ddd" where id = 2;
41 DELETE FROM t1 WHERE id= 3;
42 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
43 connection default;
44 ERROR 23000: Duplicate entry 'NULL' for key 'msg'
45 SET DEBUG_SYNC = 'RESET';
46 SELECT * FROM t1;
47 id msg
48 4 AAA
49 1 aaa
50 2 ddd
51 DROP TABLE t1;
52 CREATE TABLE t1 (
53 id INT PRIMARY KEY,
54 f1 INT NOT NULL,
55 f2 INT NOT NULL,
56 f3 INT NOT NULL,
57 msg VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
58 msg_1 VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
59 unique index(f1, msg, f2, msg_1, f3),
60 unique index(f1, msg_1, f2, msg, f3),
61 unique index(f1, msg, f3, msg_1, f2),
62 unique index(f1, msg_1, f3, msg, f2),
63 unique index(f2, msg_1, f1, msg, f3),
64 unique index(f2, msg, f3, msg_1, f1),
65 unique index(f3, f2, msg, msg_1, f1),
66 unique index(f3, msg, msg_1, f1, f2)
67 ) ENGINE=INNODB;
68 INSERT INTO t1 VALUES (1, 1, 1, 1, 'aaa', 'aaa');
69 SET DEBUG_DBUG="+d,create_index_fail";
70 SET DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con1_go WAIT_FOR alter_signal";
71 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;
72 connection con1;
73 SET DEBUG_SYNC="now WAIT_FOR con1_go";
74 BEGIN;
75 SELECT * FROM t1;
76 id f1 f2 f3 msg msg_1
77 1 1 1 1 aaa aaa
78 SET DEBUG_SYNC="now SIGNAL alter_signal";
79 connection default;
80 ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
81 CHECK TABLE t1;
82 Table Op Msg_type Msg_text
83 test.t1 check status OK
84 connection con1;
85 rollback;
86 INSERT INTO t1 VALUES(2, 2, 2, 2, 'bbb', 'bbb');
87 disconnect con1;
88 connection default;
89 SET DEBUG_SYNC=reset;
90 SHOW CREATE TABLE t1;
91 Table Create Table
92 t1 CREATE TABLE `t1` (
93 `id` int(11) NOT NULL,
94 `f1` int(11) NOT NULL,
95 `f2` int(11) NOT NULL,
96 `f3` int(11) NOT NULL,
97 `msg` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
98 `msg_1` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
99 PRIMARY KEY (`id`),
100 UNIQUE KEY `f1` (`f1`,`msg`,`f2`,`msg_1`,`f3`),
101 UNIQUE KEY `f1_2` (`f1`,`msg_1`,`f2`,`msg`,`f3`),
102 UNIQUE KEY `f1_3` (`f1`,`msg`,`f3`,`msg_1`,`f2`),
103 UNIQUE KEY `f1_4` (`f1`,`msg_1`,`f3`,`msg`,`f2`),
104 UNIQUE KEY `f2` (`f2`,`msg_1`,`f1`,`msg`,`f3`),
105 UNIQUE KEY `f2_2` (`f2`,`msg`,`f3`,`msg_1`,`f1`),
106 UNIQUE KEY `f3` (`f3`,`f2`,`msg`,`msg_1`,`f1`),
107 UNIQUE KEY `f3_2` (`f3`,`msg`,`msg_1`,`f1`,`f2`)
108 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
109 INSERT INTO t1 VALUES(3, 3, 3, 3, 'ccc', 'ccc');
110 DROP TABLE t1;
111 CREATE TABLE t1(id INT PRIMARY KEY, msg VARCHAR(100),
112 msg_1 VARCHAR(100) AS (msg) VIRTUAL,
113 msg_2 VARCHAR(100) AS (msg) STORED,
114 UNIQUE(msg), UNIQUE(msg_1),
115 UNIQUE(msg_2))ENGINE=InnoDB;
116 ALTER TABLE t1 MODIFY msg_1 VARCHAR(100) CHARACTER SET utf8
117 COLLATE utf8_unicode_ci, ALGORITHM=inplace;
118 ERROR HY000: This is not yet supported for generated columns
119 ALTER TABLE t1 MODIFY msg_2 VARCHAR(100) CHARACTER SET utf8
120 COLLATE utf8_unicode_ci, ALGORITHM=inplace;
121 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type. Try ALGORITHM=COPY
122 DROP TABLE t1;
123 #
124 # MDEV-29314 Assertion `n_fields > n_cols' failed
125 # in dict_index_t::init_change_cols
126 #
127 CREATE TABLE t (a VARCHAR(16) COLLATE utf8_bin,
128 FULLTEXT (a)) ENGINE=InnoDB COLLATE utf8_unicode_520_ci;
129 ALTER TABLE t MODIFY COLUMN a VARCHAR(512);
130 SHOW CREATE TABLE t;
131 Table Create Table
132 t CREATE TABLE `t` (
133 `a` varchar(512) DEFAULT NULL,
134 FULLTEXT KEY `a` (`a`)
135 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_520_ci
136 DROP TABLE t;
137 #
138 # MDEV-31416 ASAN errors in dict_v_col_t::detach upon
139 # adding key to virtual column
140 #
141 CREATE TABLE t (a INT) ENGINE=InnoDB WITH SYSTEM VERSIONING;
142 SET SYSTEM_VERSIONING_ALTER_HISTORY= KEEP;
143 ALTER TABLE t ADD COLUMN v VARCHAR(128) GENERATED ALWAYS AS (CRC32('MariaDB'));
144 ALTER TABLE t ADD INDEX (v);
145 DROP TABLE t;