1SET DEBUG_SYNC = 'RESET'; 2# 3# Bug#15863023 SUPPORT IN-PLACE EXTENSION OF VARCHAR COLUMN 4# 5create table t1 (a SERIAL PRIMARY KEY, c varchar(2) UNIQUE) ENGINE=InnoDB; 6INSERT INTO t1 (c) VALUES('a'),('bb'); 7SET DEBUG = '+d,ib_rename_column_error'; 8affected rows: 0 9ALTER TABLE t1 CHANGE c b varchar(2), drop index c, add index (b,a); 10ERROR HY000: The table 't1' is full 11SET DEBUG = '-d,ib_rename_column_error'; 12affected rows: 0 13ALTER TABLE t1 CHANGE c b varchar(2), drop index c, add index (b,a); 14affected rows: 0 15info: Records: 0 Duplicates: 0 Warnings: 0 16SET DEBUG = '+d,ib_resize_column_error'; 17affected rows: 0 18ALTER TABLE t1 CHANGE b fail varchar(4); 19ERROR HY000: The table 't1' is full 20SET DEBUG = '-d,ib_resize_column_error'; 21affected rows: 0 22ALTER TABLE t1 CHANGE b c varchar(4); 23affected rows: 0 24info: Records: 0 Duplicates: 0 Warnings: 0 25INSERT IGNORE INTO t1 (c) VALUES('eeeee'),('ffffff'); 26Warnings: 27Warning 1265 Data truncated for column 'c' at row 1 28Warning 1265 Data truncated for column 'c' at row 2 29INSERT INTO t1 (c) VALUES('ccc'),('dddd'); 30SELECT * FROM t1 ORDER BY c; 31a c 321 a 332 bb 345 ccc 356 dddd 363 eeee 374 ffff 38DROP TABLE t1; 39# Bug#15863023 - case1 40SET DEBUG_SYNC = 'RESET'; 41CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b VARCHAR(254)) ENGINE = Innodb; 42INSERT IGNORE INTO t1 (b) VALUES (repeat('a',254)),(repeat('b',254)),(repeat('c',300)); 43Warnings: 44Warning 1265 Data truncated for column 'b' at row 3 45SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 46a LEFT(b,10) LENGTH(b) = 254 471 aaaaaaaaaa 1 482 bbbbbbbbbb 1 493 cccccccccc 1 50SET DEBUG_SYNC = 'innodb_commit_inplace_alter_table_enter SIGNAL s1 WAIT_FOR s2'; 51ALTER TABLE t1 CHANGE b b varchar(255);; 52SET lock_wait_timeout = 1; 53SET DEBUG_SYNC='now WAIT_FOR s1'; 54INSERT INTO t1 (b) VALUES (repeat('d',300)); 55ERROR HY000: Lock wait timeout exceeded; try restarting transaction 56SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 57ERROR HY000: Lock wait timeout exceeded; try restarting transaction 58UPDATE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ; 59ERROR HY000: Lock wait timeout exceeded; try restarting transaction 60SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 61ERROR HY000: Lock wait timeout exceeded; try restarting transaction 62DELETE FROM t1 WHERE b = repeat('y',254); 63ERROR HY000: Lock wait timeout exceeded; try restarting transaction 64SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 65ERROR HY000: Lock wait timeout exceeded; try restarting transaction 66SET DEBUG_SYNC='now SIGNAL s2'; 67SHOW CREATE TABLE t1; 68Table Create Table 69t1 CREATE TABLE `t1` ( 70 `a` int(11) NOT NULL AUTO_INCREMENT, 71 `b` varchar(255) DEFAULT NULL, 72 PRIMARY KEY (`a`) 73) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 74INSERT IGNORE INTO t1 (b) VALUES (repeat('d',300)); 75Warnings: 76Warning 1265 Data truncated for column 'b' at row 1 77SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 78a LEFT(b,10) LENGTH(b) = 254 791 aaaaaaaaaa 1 802 bbbbbbbbbb 1 813 cccccccccc 1 824 dddddddddd 0 83UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ; 84Warnings: 85Warning 1265 Data truncated for column 'b' at row 1 86SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 87a LEFT(b,10) LENGTH(b) = 254 881 yyyyyyyyyy 0 892 bbbbbbbbbb 1 903 cccccccccc 1 914 dddddddddd 0 92DELETE FROM t1 WHERE b = repeat('y',255); 93SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 94a LEFT(b,10) LENGTH(b) = 254 952 bbbbbbbbbb 1 963 cccccccccc 1 974 dddddddddd 0 98DROP TABLE t1; 99# Bug#15863023 - case2 . enlarge varchar with add.drop index/column 100SET DEBUG_SYNC = 'RESET'; 101CREATE TABLE t1 (a INT , b VARCHAR(254)) ENGINE=Innodb; 102INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300)); 103Warnings: 104Warning 1265 Data truncated for column 'b' at row 3 105SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 106a LEFT(b,10) LENGTH(b) = 254 1071 aaaaaaaaaa 1 1082 bbbbbbbbbb 1 1093 cccccccccc 1 110SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2'; 111ALTER TABLE t1 ADD INDEX idx(b),CHANGE b b varchar(255) ,ALGORITHM=INPLACE;; 112SET DEBUG_SYNC='now WAIT_FOR s1'; 113INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300)); 114Warnings: 115Warning 1265 Data truncated for column 'b' at row 1 116SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 117a LEFT(b,10) LENGTH(b) = 254 1181 aaaaaaaaaa 1 1192 bbbbbbbbbb 1 1203 cccccccccc 1 1214 dddddddddd 1 122UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ; 123Warnings: 124Warning 1265 Data truncated for column 'b' at row 1 125SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 126a LEFT(b,10) LENGTH(b) = 254 1271 yyyyyyyyyy 1 1282 bbbbbbbbbb 1 1293 cccccccccc 1 1304 dddddddddd 1 131DELETE FROM t1 WHERE b = repeat('y',254); 132SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 133a LEFT(b,10) LENGTH(b) = 254 1342 bbbbbbbbbb 1 1353 cccccccccc 1 1364 dddddddddd 1 137SET DEBUG_SYNC='now SIGNAL s2'; 138SHOW CREATE TABLE t1; 139Table Create Table 140t1 CREATE TABLE `t1` ( 141 `a` int(11) DEFAULT NULL, 142 `b` varchar(255) DEFAULT NULL, 143 KEY `idx` (`b`) 144) ENGINE=InnoDB DEFAULT CHARSET=latin1 145INSERT IGNORE INTO t1 (a,b) VALUES (5,repeat('d',300)); 146Warnings: 147Warning 1265 Data truncated for column 'b' at row 1 148SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 149a LEFT(b,10) LENGTH(b) = 254 1502 bbbbbbbbbb 1 1513 cccccccccc 1 1524 dddddddddd 1 1535 dddddddddd 0 154UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ; 155Warnings: 156Warning 1265 Data truncated for column 'b' at row 1 157SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 158a LEFT(b,10) LENGTH(b) = 254 1592 yyyyyyyyyy 0 1603 cccccccccc 1 1614 dddddddddd 1 1625 dddddddddd 0 163DELETE FROM t1 WHERE b = repeat('y',255); 164SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 165a LEFT(b,10) LENGTH(b) = 254 1663 cccccccccc 1 1674 dddddddddd 1 1685 dddddddddd 0 169DROP TABLE t1; 170# Bug#15863023 - case3. enlarge varchar other alter operations+charset 171SET DEBUG_SYNC = 'RESET'; 172CREATE TABLE t1 (a INT DEFAULT 100 , b VARCHAR(254) CHARACTER SET latin1, 173c VARCHAR(254) CHARACTER SET utf8) ENGINE=Innodb CHARSET=utf8; 174CREATE INDEX c1 ON t1(c); 175INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300)); 176Warnings: 177Warning 1265 Data truncated for column 'b' at row 3 178SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 179a LEFT(b,10) LENGTH(b) = 254 1801 aaaaaaaaaa 1 1812 bbbbbbbbbb 1 1823 cccccccccc 1 183SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2'; 184ALTER 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;; 185SET DEBUG_SYNC='now WAIT_FOR s1'; 186INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300)); 187Warnings: 188Warning 1265 Data truncated for column 'b' at row 1 189SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 190a LEFT(b,10) LENGTH(b) = 254 c 1911 aaaaaaaaaa 1 NULL 1922 bbbbbbbbbb 1 NULL 1933 cccccccccc 1 NULL 1944 dddddddddd 1 NULL 195UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ; 196Warnings: 197Warning 1265 Data truncated for column 'b' at row 1 198SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 199a LEFT(b,10) LENGTH(b) = 254 c 2001 yyyyyyyyyy 1 NULL 2012 bbbbbbbbbb 1 NULL 2023 cccccccccc 1 NULL 2034 dddddddddd 1 NULL 204DELETE FROM t1 WHERE b = repeat('y',254); 205SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 206a LEFT(b,10) LENGTH(b) = 254 c 2072 bbbbbbbbbb 1 NULL 2083 cccccccccc 1 NULL 2094 dddddddddd 1 NULL 210INSERT IGNORE INTO t1 (b,c) VALUES (repeat('z',300),"Môžem"); 211Warnings: 212Warning 1265 Data truncated for column 'b' at row 1 213SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 214a LEFT(b,10) LENGTH(b) = 254 c 2152 bbbbbbbbbb 1 NULL 2163 cccccccccc 1 NULL 2174 dddddddddd 1 NULL 218100 zzzzzzzzzz 1 Môžem 219SET DEBUG_SYNC='now SIGNAL s2'; 220SHOW CREATE TABLE t1; 221Table Create Table 222t1 CREATE TABLE `t1` ( 223 `a` int(11) DEFAULT '100', 224 `b` varchar(255) CHARACTER SET latin1 DEFAULT NULL, 225 `c` varchar(255) DEFAULT NULL, 226 KEY `idx` (`b`) 227) ENGINE=InnoDB DEFAULT CHARSET=utf8 228INSERT IGNORE INTO t1 (a,b,c) VALUES (5,repeat('d',300),"Môžem"); 229Warnings: 230Warning 1265 Data truncated for column 'b' at row 1 231SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 232a LEFT(b,10) LENGTH(b) = 254 c 2332 bbbbbbbbbb 1 NULL 2343 cccccccccc 1 NULL 2354 dddddddddd 1 NULL 2365 dddddddddd 0 Môžem 237100 zzzzzzzzzz 1 Môžem 238UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ; 239Warnings: 240Warning 1265 Data truncated for column 'b' at row 1 241SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 242a LEFT(b,10) LENGTH(b) = 254 c 2432 yyyyyyyyyy 0 NULL 2443 cccccccccc 1 NULL 2454 dddddddddd 1 NULL 2465 dddddddddd 0 Môžem 247100 zzzzzzzzzz 1 Môžem 248DELETE FROM t1 WHERE b = repeat('y',255); 249SELECT a,LEFT(b,10),LENGTH(b) = 254,c FROM t1 ORDER BY a; 250a LEFT(b,10) LENGTH(b) = 254 c 2513 cccccccccc 1 NULL 2524 dddddddddd 1 NULL 2535 dddddddddd 0 Môžem 254100 zzzzzzzzzz 1 Môžem 255DROP TABLE t1; 256# Bug#15863023 - case4. enlarge varchar other alter operations runs copy 257# if length is more than 255 258SET DEBUG_SYNC = 'RESET'; 259CREATE TABLE t1 (a INT DEFAULT 100 , b VARCHAR(254) CHARACTER SET latin1, 260c VARCHAR(254) CHARACTER SET utf8, d CHAR(10) DEFAULT 'test') ENGINE=Innodb CHARSET=utf8; 261CREATE INDEX c1 ON t1(c); 262INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300)); 263Warnings: 264Warning 1265 Data truncated for column 'b' at row 3 265SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 266a LEFT(b,10) LENGTH(b) = 254 2671 aaaaaaaaaa 1 2682 bbbbbbbbbb 1 2693 cccccccccc 1 270ALTER 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; 271ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 272ALTER TABLE t1 ADD INDEX idx(b),CHANGE d d char(100) ,ALGORITHM=INPLACE; 273ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 274ALTER TABLE t1 ADD INDEX idx1(b),CHANGE d d VARCHAR(100) ,ALGORITHM=INPLACE; 275ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. 276ALTER 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 ; 277DROP TABLE t1; 278# Bug#15863023 - case6 enlarge varchar other 279# alter operations with fk on parent 280SET DEBUG_SYNC = 'RESET'; 281CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(254) ) ENGINE=Innodb; 282CREATE TABLE t2 (a2 INT , b2 VARCHAR(254) , CONSTRAINT fk FOREIGN KEY (a2) REFERENCES t1 (a) ON UPDATE CASCADE ON DELETE CASCADE) ENGINE=Innodb; 283INSERT IGNORE INTO t1 (a,b) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300)); 284Warnings: 285Warning 1265 Data truncated for column 'b' at row 3 286INSERT IGNORE INTO t2 (a2,b2) VALUES (1,repeat('a',254)),(2,repeat('b',254)),(3,repeat('c',300)); 287Warnings: 288Warning 1265 Data truncated for column 'b2' at row 3 289SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1,t2 WHERE t1.a = t2.a2 ORDER BY a; 290a LEFT(b,10) LENGTH(b) = 254 2911 aaaaaaaaaa 1 2922 bbbbbbbbbb 1 2933 cccccccccc 1 294ALTER TABLE t2 CHANGE b2 b2 varchar(255),DROP FOREIGN KEY fk,ADD CONSTRAINT fk FOREIGN KEY (b2) REFERENCES t1 (b),ADD INDEX idx(a2) ,ALGORITHM=INPLACE; 295ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY. 296SET DEBUG_SYNC = 'row_log_apply_before SIGNAL s1 WAIT_FOR s2'; 297ALTER TABLE t1 ADD INDEX idx(a),CHANGE b b varchar(255) ,ALGORITHM=INPLACE;; 298SET DEBUG_SYNC='now WAIT_FOR s1'; 299INSERT IGNORE INTO t1 (a,b) VALUES (4,repeat('d',300)); 300Warnings: 301Warning 1265 Data truncated for column 'b' at row 1 302SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 303a LEFT(b,10) LENGTH(b) = 254 3041 aaaaaaaaaa 1 3052 bbbbbbbbbb 1 3063 cccccccccc 1 3074 dddddddddd 1 308UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('a',254) ; 309Warnings: 310Warning 1265 Data truncated for column 'b' at row 1 311SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 312a LEFT(b,10) LENGTH(b) = 254 3131 yyyyyyyyyy 1 3142 bbbbbbbbbb 1 3153 cccccccccc 1 3164 dddddddddd 1 317DELETE FROM t1 WHERE b = repeat('y',254); 318SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 319a LEFT(b,10) LENGTH(b) = 254 3202 bbbbbbbbbb 1 3213 cccccccccc 1 3224 dddddddddd 1 323SET DEBUG_SYNC='now SIGNAL s2'; 324SET foreign_key_checks = 1; 325SHOW CREATE TABLE t1; 326Table Create Table 327t1 CREATE TABLE `t1` ( 328 `a` int(11) NOT NULL, 329 `b` varchar(255) DEFAULT NULL, 330 PRIMARY KEY (`a`), 331 KEY `idx` (`a`) 332) ENGINE=InnoDB DEFAULT CHARSET=latin1 333INSERT IGNORE INTO t1 (a,b) VALUES (5,repeat('k',300)); 334Warnings: 335Warning 1265 Data truncated for column 'b' at row 1 336SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 337a LEFT(b,10) LENGTH(b) = 254 3382 bbbbbbbbbb 1 3393 cccccccccc 1 3404 dddddddddd 1 3415 kkkkkkkkkk 0 342UPDATE IGNORE t1 SET b = repeat('y',300) WHERE b = repeat('b',254) ; 343Warnings: 344Warning 1265 Data truncated for column 'b' at row 1 345SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 346a LEFT(b,10) LENGTH(b) = 254 3472 yyyyyyyyyy 0 3483 cccccccccc 1 3494 dddddddddd 1 3505 kkkkkkkkkk 0 351DELETE FROM t1 WHERE b = repeat('y',255); 352SELECT a,LEFT(b,10),LENGTH(b) = 254 FROM t1 ORDER BY a; 353a LEFT(b,10) LENGTH(b) = 254 3543 cccccccccc 1 3554 dddddddddd 1 3565 kkkkkkkkkk 0 357DROP TABLE t2,t1; 358SET DEBUG_SYNC = 'RESET'; 359