Debian Codegraph @

mysql-test/suite/innodb/t/innodb-alter-varchar-debug.test

raw
1 --source include/have_debug.inc
2 --source include/have_debug_sync.inc
3 --source include/not_valgrind.inc
4
5 --source include/count_sessions.inc
6
7 SET DEBUG_SYNC = 'RESET';
8
9 --echo #
10 --echo # Bug#15863023 SUPPORT IN-PLACE EXTENSION OF VARCHAR COLUMN
11 --echo #
12 create table t1 (a SERIAL PRIMARY KEY, c varchar(2) UNIQUE) ENGINE=InnoDB;
13 INSERT INTO t1 (c) VALUES('a'),('bb');
14 --enable_info
15 SET DEBUG = '+d,ib_rename_column_error';
16 --error ER_RECORD_FILE_FULL
17 ALTER TABLE t1 CHANGE c b varchar(2), drop index c, add index (b,a);
18 SET DEBUG = '-d,ib_rename_column_error';
19 ALTER TABLE t1 CHANGE c b varchar(2), drop index c, add index (b,a);
20 SET DEBUG = '+d,ib_resize_column_error';
21 --error ER_RECORD_FILE_FULL
22 ALTER TABLE t1 CHANGE b fail varchar(4);
23 SET DEBUG = '-d,ib_resize_column_error';
24 ALTER TABLE t1 CHANGE b c varchar(4);
25 --disable_info
26 INSERT IGNORE INTO t1 (c) VALUES('eeeee'),('ffffff');
27 INSERT INTO t1 (c) VALUES('ccc'),('dddd');
28 SELECT * FROM t1 ORDER BY c;
29 DROP TABLE t1;
30
31 --echo # Bug#15863023 - case1
32 SET DEBUG_SYNC = 'RESET';
33 CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(254) charset latin1) ENGINE = Innodb;
34 INSERT IGNORE INTO t1 (b) VALUES (repeat('a',254)),(repeat('b',254)),(repeat('c',300));
35 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
36 connect (con1,localhost,root,,);
37 connection con1;
38 SET DEBUG_SYNC = 'innodb_commit_inplace_alter_table_enter SIGNAL s1 WAIT_FOR s2';
39 --send ALTER TABLE t1 CHANGE b b varchar(255) charset latin1;
40 connection default;
41 # DML no go through in paralle as whole operation happen during
42 # commit_inplace_alter_table, under an exclusive MDL
43 SET lock_wait_timeout = 1;
44 SET DEBUG_SYNC='now WAIT_FOR s1';
45 --error ER_LOCK_WAIT_TIMEOUT
46 INSERT INTO t1 (b) VALUES (repeat('d',300));
47 --error ER_LOCK_WAIT_TIMEOUT
48 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
49 --error ER_LOCK_WAIT_TIMEOUT
50 UPDATE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
51 --error ER_LOCK_WAIT_TIMEOUT
52 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
53 --error ER_LOCK_WAIT_TIMEOUT
54 DELETE FROM t1 WHERE b = repeat('y',254);
55 --error ER_LOCK_WAIT_TIMEOUT
56 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
57 SET DEBUG_SYNC='now SIGNAL s2';
58
59 connection con1;
60 reap;
61 SHOW CREATE TABLE t1;
62 disconnect con1;
63
64 connection default;
65 INSERT IGNORE INTO t1 (b) VALUES (repeat('d',300));
66 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
67 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
68 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
69 DELETE FROM t1 WHERE b = repeat('y',255);
70 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
71 DROP TABLE t1;
72
73
74 --echo # Bug#15863023 - case2 . enlarge varchar with add.drop index/column
75 SET DEBUG_SYNC = 'RESET';
76 CREATE TABLE t1 (a INT , b VARCHAR(254) charset latin1) ENGINE=Innodb;
77 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
78 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
79 connect (con1,localhost,root,,);
80 connection con1;
81 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2';
82 --send ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(255) charset latin1 ,ALGORITHM=INPLACE;
83 connection default;
84 # DML go through
85 SET DEBUG_SYNC='now WAIT_FOR s1';
86 INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300));
87 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
88 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
89 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
90 DELETE FROM t1 WHERE b = repeat('y',254);
91 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
92 SET DEBUG_SYNC='now SIGNAL s2';
93
94 connection con1;
95 reap;
96 SHOW CREATE TABLE t1;
97 disconnect con1;
98
99 connection default;
100 INSERT IGNORE INTO t1 (a,b) VALUES (5,repeat('d',300));
101 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
102 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ;
103 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
104 DELETE FROM t1 WHERE b = repeat('y',255);
105 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
106 DROP TABLE t1;
107
108
109 --echo # Bug#15863023 - case3. enlarge varchar other alter operations+charset
110 SET DEBUG_SYNC = 'RESET';
111 CREATE TABLE t1 (a INT DEFAULT 100 , b VARCHAR(254) CHARACTER SET latin1,
112 c VARCHAR(254) CHARACTER SET utf8) ENGINE=Innodb CHARSET=utf8;
113 CREATE INDEX c1 ON t1(c);
114 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
115 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
116 connect (con1,localhost,root,,);
117 connection con1;
118 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2';
119 --send ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(255) CHARACTER SET latin1,CHANGE c c varchar(255) CHARACTER SET utf8,DROP INDEX c1 ,ALGORITHM=INPLACE;
120 connection default;
121 # DML go through
122 SET DEBUG_SYNC='now WAIT_FOR s1';
123 INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300));
124 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
125 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
126 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
127 DELETE FROM t1 WHERE b = repeat('y',254);
128 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
129 INSERT IGNORE INTO t1 (b,c) VALUES (repeat('z',300),"Môžem");
130 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
131 SET DEBUG_SYNC='now SIGNAL s2';
132
133 connection con1;
134 reap;
135 SHOW CREATE TABLE t1;
136 disconnect con1;
137
138 connection default;
139 INSERT IGNORE INTO t1 (a,b,c) VALUES (5,repeat('d',300),"Môžem");
140 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
141 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ;
142 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
143 DELETE FROM t1 WHERE b = repeat('y',255);
144 SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a;
145 DROP TABLE t1;
146
147
148 --echo # Bug#15863023 - case4. enlarge varchar other alter operations runs copy
149 --echo # if length is more than 255
150 SET DEBUG_SYNC = 'RESET';
151 CREATE TABLE t1 (a INT DEFAULT 100 , b VARCHAR(254) CHARACTER SET latin1,
152 c VARCHAR(254) CHARACTER SET utf8, d CHAR(10) DEFAULT 'test') ENGINE=Innodb CHARSET=utf8;
153 CREATE INDEX c1 ON t1(c);
154 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
155 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
156 # increase size > 255
157 --ERROR ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
158 ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(256) CHARACTER SET latin1,CHANGE c c varchar(256) CHARACTER SET utf8,DROP INDEX c1 ,ALGORITHM=INPLACE;
159 # can not enlarge change size of char inplace
160 --ERROR ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
161 ALTER TABLE t1 ADD INDEX idx(b),CHANGE d d char(100) ,ALGORITHM=INPLACE;
162 # can not change column type inplace
163 --ERROR ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
164 ALTER TABLE t1 ADD INDEX idx1(b),CHANGE d d VARCHAR(100) ,ALGORITHM=INPLACE;
165 ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(355) CHARACTER SET latin1,CHANGE c c varchar(255) CHARACTER SET utf8,DROP INDEX c1 ;
166 DROP TABLE t1;
167
168
169 --echo # Bug#15863023 - case6 enlarge varchar other
170 --echo # alter operations with fk on parent
171 SET DEBUG_SYNC = 'RESET';
172 CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(254) charset latin1, KEY(b)) ENGINE=Innodb;
173 CREATE TABLE t2 (a2 INT , b2 VARCHAR(254) charset latin1, CONSTRAINT fk FOREIGN KEY (a2) REFERENCES t1 (a) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=Innodb;
174 INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
175 INSERT IGNORE INTO t2 (a2,b2) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300));
176 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1,t2 WHERE t1.a = t2.a2 ORDER BY a;
177 connect (con1,localhost,root,,);
178 connection con1;
179 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
180 ALTER TABLE t2 CHANGE b2 b2 varchar(255) charset latin1, DROP FOREIGN KEY fk,ADD CONSTRAINT fk FOREIGN KEY (b2) REFERENCES t1 (b),ADD INDEX idx(a2) ,ALGORITHM=INPLACE;
181 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2';
182 --send ALTER TABLE t1 ADD INDEX idx(a),CHANGE b b varchar(255) charset latin1, ALGORITHM=INPLACE;
183 connection default;
184 # DML go through
185 SET DEBUG_SYNC='now WAIT_FOR s1';
186 INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300));
187 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
188 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ;
189 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
190 DELETE FROM t1 WHERE b = repeat('y',254);
191 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
192 SET DEBUG_SYNC='now SIGNAL s2';
193
194 connection con1;
195 reap;
196 SET foreign_key_checks = 1;
197 SHOW CREATE TABLE t1;
198 disconnect con1;
199
200 connection default;
201 INSERT IGNORE INTO t1 (a,b) VALUES (5,repeat('k',300));
202 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
203 UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ;
204 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
205 DELETE FROM t1 WHERE b = repeat('y',255);
206 SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a;
207 DROP TABLE t2,t1;
208
209 SET DEBUG_SYNC = 'RESET';
210
211 --source include/wait_until_count_sessions.inc