| 1 |
--source include/have_debug_sync.inc |
| 2 |
--source include/have_debug.inc |
| 3 |
--source include/count_sessions.inc |
| 4 |
--source include/have_innodb_max_16k.inc |
| 5 |
|
| 6 |
CREATE TABLE `t` ( |
| 7 |
`a` VARCHAR(100), |
| 8 |
`b` VARCHAR(100), |
| 9 |
`c` VARCHAR(200) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL, |
| 10 |
`h` VARCHAR(10) DEFAULT NULL, |
| 11 |
`i` int |
| 12 |
) ENGINE=InnoDB; |
| 13 |
|
| 14 |
INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 10), DEFAULT, "kk", 1); |
| 15 |
INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2); |
| 16 |
|
| 17 |
CREATE INDEX idx ON t(c(100)); |
| 18 |
|
| 19 |
SET session debug="+d,ib_alter_add_virtual_fail"; |
| 20 |
--error ER_WRONG_KEY_COLUMN |
| 21 |
ALTER TABLE t ADD COLUMN x VARCHAR(200) GENERATED ALWAYS AS (a) VIRTUAL, ADD INDEX(h), |
| 22 |
ALGORITHM = INPLACE; |
| 23 |
--error ER_WRONG_KEY_COLUMN |
| 24 |
ALTER TABLE t DROP COLUMN c, ADD INDEX(h), ALGORITHM = INPLACE; |
| 25 |
SET session debug="-d,ib_alter_add_virtual_fail"; |
| 26 |
DROP TABLE t; |
| 27 |
|
| 28 |
#online test |
| 29 |
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); |
| 30 |
|
| 31 |
INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); |
| 32 |
INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); |
| 33 |
INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); |
| 34 |
INSERT INTO t VALUES (null, null, DEFAULT, "mx"); |
| 35 |
|
| 36 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; |
| 37 |
--send CREATE INDEX idx ON t(c); |
| 38 |
|
| 39 |
connect (con1,localhost,root,,); |
| 40 |
connection con1; |
| 41 |
|
| 42 |
SET DEBUG_SYNC = 'now WAIT_FOR start_create'; |
| 43 |
update t set a=0 where a = 11; |
| 44 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 45 |
|
| 46 |
connection default; |
| 47 |
reap; |
| 48 |
|
| 49 |
--sorted_result |
| 50 |
SELECT c FROM t; |
| 51 |
SHOW CREATE TABLE t; |
| 52 |
SELECT * FROM t; |
| 53 |
|
| 54 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; |
| 55 |
--send ALTER TABLE t ADD COLUMN x INT; |
| 56 |
|
| 57 |
connection con1; |
| 58 |
|
| 59 |
SET DEBUG_SYNC = 'now WAIT_FOR start_create'; |
| 60 |
start transaction; |
| 61 |
update t set a=1 where a = 0; |
| 62 |
ROLLBACK; |
| 63 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 64 |
|
| 65 |
connection default; |
| 66 |
reap; |
| 67 |
|
| 68 |
--sorted_result |
| 69 |
SELECT c FROM t; |
| 70 |
|
| 71 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; |
| 72 |
--send ALTER TABLE t ADD COLUMN x2 INT; |
| 73 |
|
| 74 |
connection con1; |
| 75 |
|
| 76 |
SET DEBUG_SYNC = 'now WAIT_FOR start_create'; |
| 77 |
start transaction; |
| 78 |
DELETE FROM t WHERE a = 0; |
| 79 |
ROLLBACK; |
| 80 |
DELETE FROM t WHERE a = 0; |
| 81 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 82 |
|
| 83 |
connection default; |
| 84 |
reap; |
| 85 |
|
| 86 |
--sorted_result |
| 87 |
SELECT c FROM t; |
| 88 |
|
| 89 |
disconnect con1; |
| 90 |
DROP TABLE t; |
| 91 |
|
| 92 |
SET DEBUG_SYNC = 'RESET'; |
| 93 |
|
| 94 |
|
| 95 |
# Test add virtual column and add index at the same time |
| 96 |
# introduce some error |
| 97 |
|
| 98 |
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); |
| 99 |
|
| 100 |
INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); |
| 101 |
|
| 102 |
INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); |
| 103 |
|
| 104 |
INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); |
| 105 |
|
| 106 |
INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); |
| 107 |
|
| 108 |
CREATE INDEX idx_1 on t(c); |
| 109 |
|
| 110 |
SET SESSION debug="+d,create_index_fail"; |
| 111 |
|
| 112 |
--enable_info |
| 113 |
--error ER_DUP_ENTRY |
| 114 |
ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x); |
| 115 |
SET SESSION debug="-d,create_index_fail"; |
| 116 |
--disable_info |
| 117 |
|
| 118 |
SHOW CREATE TABLE t; |
| 119 |
|
| 120 |
--sorted_result |
| 121 |
SELECT c FROM t; |
| 122 |
|
| 123 |
DROP TABLE t; |
| 124 |
|
| 125 |
|
| 126 |
--echo # |
| 127 |
--echo # Bug#22018532 ASSERTION WHEN ONLINE REAPPLY REBUILD LOG ON |
| 128 |
--echo # MULTIPLE INDEXED VIRTUAL COLUMNS |
| 129 |
--echo # |
| 130 |
|
| 131 |
create table t ( |
| 132 |
a int as (1) virtual, |
| 133 |
b int, |
| 134 |
c int as (1) virtual, |
| 135 |
unique(b), |
| 136 |
unique(c), |
| 137 |
key(a) |
| 138 |
) engine=innodb; |
| 139 |
|
| 140 |
insert ignore into t values(); |
| 141 |
|
| 142 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; |
| 143 |
--send optimize table t |
| 144 |
|
| 145 |
connect (con1,localhost,root,,); |
| 146 |
|
| 147 |
SET DEBUG_SYNC = 'now WAIT_FOR start_create'; |
| 148 |
insert ignore into t values(); |
| 149 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 150 |
|
| 151 |
connection default; |
| 152 |
--echo /* connection default */ optimize table t; |
| 153 |
reap; |
| 154 |
--sorted_result |
| 155 |
SELECT c FROM t; |
| 156 |
SHOW CREATE TABLE t; |
| 157 |
SELECT * FROM t; |
| 158 |
DROP TABLE t; |
| 159 |
|
| 160 |
# Do another test without duplicate error |
| 161 |
|
| 162 |
CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); |
| 163 |
|
| 164 |
INSERT INTO t VALUES (11, 3, DEFAULT, 'mm'); |
| 165 |
INSERT INTO t VALUES (18, 1, DEFAULT, 'mm'); |
| 166 |
INSERT INTO t VALUES (28, 1, DEFAULT, 'mm'); |
| 167 |
INSERT INTO t VALUES (null, null, DEFAULT, 'mm'); |
| 168 |
|
| 169 |
CREATE INDEX idx ON t(c); |
| 170 |
|
| 171 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_rebuild WAIT_FOR go_ahead'; |
| 172 |
--send optimize table t |
| 173 |
|
| 174 |
connection con1; |
| 175 |
SET DEBUG_SYNC = 'now WAIT_FOR start_rebuild'; |
| 176 |
INSERT INTO t VALUES (48, 2, DEFAULT, 'xx'); |
| 177 |
INSERT INTO t VALUES (68, 3, DEFAULT, 'sx'); |
| 178 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 179 |
|
| 180 |
connection default; |
| 181 |
--echo /* connection default */ optimize table t; |
| 182 |
reap; |
| 183 |
|
| 184 |
--sorted_result |
| 185 |
SELECT c FROM t; |
| 186 |
|
| 187 |
disconnect con1; |
| 188 |
|
| 189 |
DROP TABLE t; |
| 190 |
--echo # |
| 191 |
--echo # Bug#22140944 WL#8149: ADD TEST FOR THE ROW0LOG.CC FIX THAT |
| 192 |
--echo # WAS UNRELATED TO Bug#21894654 |
| 193 |
--echo # |
| 194 |
|
| 195 |
CREATE TABLE t1 ( id INT ,a VARCHAR(20), b VARCHAR(20) ,c VARCHAR(40) |
| 196 |
GENERATED ALWAYS AS (CONCAT(a,b)),key(c)); |
| 197 |
INSERT INTO t1 (id,a,b) VALUES (10,'aditya','hello'); |
| 198 |
|
| 199 |
connect (con1,localhost,root); |
| 200 |
connection con1; |
| 201 |
|
| 202 |
SET DEBUG_SYNC= 'ddl_after_scan SIGNAL opened WAIT_FOR go_ddl'; |
| 203 |
send ALTER TABLE t1 ADD CONSTRAINT pk1 PRIMARY KEY (id); |
| 204 |
|
| 205 |
connection default; |
| 206 |
SET DEBUG_SYNC= 'now WAIT_FOR opened'; |
| 207 |
UPDATE t1 SET a = 'fg', id = 20 WHERE id = 10; |
| 208 |
SET DEBUG_SYNC= 'now SIGNAL go_ddl'; |
| 209 |
|
| 210 |
connection con1; |
| 211 |
reap; |
| 212 |
disconnect con1; |
| 213 |
|
| 214 |
connection default; |
| 215 |
SELECT * from t1; |
| 216 |
DROP TABLE t1; |
| 217 |
|
| 218 |
--echo # |
| 219 |
--echo # Bug#22951879 - ASSERTS RELATED TO ONLINE DDL AND GCOL |
| 220 |
--echo # |
| 221 |
|
| 222 |
# Create a table with 2 virtual column, one (vbidxcol) is indexed and |
| 223 |
# the other one (vbcol) is not |
| 224 |
create table ibstd_14 (a int not null, d int not null, b varchar(198) not null, c char(181), vadcol int as (a+length(d)) stored, vbcol char(2) as (substr(b,2,2)) virtual, vbidxcol char(3) as (substr(b,1,3)) virtual , index(d), index(a), index(vbidxcol), index(a,vbidxcol), index(vbidxcol,d), unique key (b(10), a, d), index(c(99), b(31)), index(b(5), c(10), a) , index(a,d)) engine=InnoDB stats_persistent=1 row_format=dynamic charset latin1; |
| 225 |
|
| 226 |
# Do an alter table rebuild table and also create a new index on this |
| 227 |
# non-indexed virtual column |
| 228 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; |
| 229 |
--send alter table ibstd_14 row_format=compressed key_block_size=4,add key kn3 (d,c,vbcol,b); |
| 230 |
|
| 231 |
# Do a concurrent insert, and make sure this newly indexed virtual column |
| 232 |
# is also logged |
| 233 |
connect (con1,localhost,root); |
| 234 |
connection con1; |
| 235 |
SET DEBUG_SYNC = 'now WAIT_FOR start_create'; |
| 236 |
insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6',repeat('oacolaarlruoacuroauurloraarucoooarcooauoolacalllaulrruarrrucruuooclacuoouccarrcoocloccorrrrarourcooalloocooccouruolaorlcaocualolc','1'),repeat('lolrrlalcocroraaulauclaaucolcorcuooaolruaooooluooooouaoorlarucorullalcrrloccououaooaorluorraclrcooouuolocoaolcocaaculruoocucoocoooauuolarcoraraocaoolulolarru','1'),default,default); |
| 237 |
|
| 238 |
insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6', 'aaaa', 'lll', default, default); |
| 239 |
|
| 240 |
# Also do an concurrent update, make sure this is performed |
| 241 |
update ibstd_14 set b='11111' where b='aaaa'; |
| 242 |
|
| 243 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 244 |
|
| 245 |
connection default; |
| 246 |
reap; |
| 247 |
|
| 248 |
select * from ibstd_14; |
| 249 |
|
| 250 |
# This will use the newly added "kn3" index, to check materialized vbcol |
| 251 |
# after log reapply |
| 252 |
--sorted_result |
| 253 |
select d,c,vbcol,b from ibstd_14; |
| 254 |
|
| 255 |
# check the value is inserted into the index |
| 256 |
--sorted_result |
| 257 |
select vbcol from ibstd_14; |
| 258 |
|
| 259 |
drop table ibstd_14; |
| 260 |
|
| 261 |
--echo # |
| 262 |
--echo # Bug#26375771 INNODB: ASSERTION FAILURE: MACH0DATA.IC:308:VAL > 0X7F |
| 263 |
--echo # |
| 264 |
create table ibstd_07 (a int not null, d int not null, b blob not null, c text, vadcol int as (a+length(d)) stored, vbcol char(2) as (substr(b,2,2)) virtual, vbidxcol char(3) as (substr(b,1,3)) virtual , index(d desc), index(a desc), index(vbidxcol desc), index(vbidxcol desc,d asc), unique key (b(101) desc, a asc, d ), index(c(255) desc, b(255) asc), index(b(5) desc, c(10) asc, a ) ) engine=InnoDB default charset=latin1 row_format=redundant; |
| 265 |
|
| 266 |
insert into ibstd_07 values (2, 2, repeat('rocalrulcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc','281'),repeat('ouolrculuouocououooalcoraooaulouuacrolrocooraoaooooolaccralacalooolalocoaacoorarorcurccarocucla','317'), default, default, default); |
| 267 |
|
| 268 |
|
| 269 |
SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; |
| 270 |
--send alter table ibstd_07 add primary key (b(11) desc,a asc, d ); |
| 271 |
|
| 272 |
# Do a concurrent update |
| 273 |
connection con1; |
| 274 |
SET DEBUG_SYNC = 'now WAIT_FOR start_create'; |
| 275 |
update ibstd_07 set c=repeat('0.366710324443464crllooolarlollorlulololucrluacoorrrluroluroocouauacacruloulcurlraorcrclrolcocrrcoccolourrooclrcocruruauallo','70'); |
| 276 |
|
| 277 |
|
| 278 |
SET DEBUG_SYNC = 'now SIGNAL go_ahead'; |
| 279 |
|
| 280 |
connection default; |
| 281 |
reap; |
| 282 |
|
| 283 |
SELECT * FROM ibstd_07; |
| 284 |
|
| 285 |
DROP TABLE ibstd_07; |
| 286 |
|
| 287 |
--echo # |
| 288 |
--echo # Bug#22018745 CORRUPTION IN ONLINE TABLE REBUILD |
| 289 |
--echo # (ROW_FORMAT=REDUNDANT, INDEXED VIRTUAL COLUMN) |
| 290 |
--echo # |
| 291 |
|
| 292 |
CREATE TABLE t ( |
| 293 |
b char(5) PRIMARY KEY, |
| 294 |
v char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, KEY(v) |
| 295 |
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT; |
| 296 |
|
| 297 |
connection con1; |
| 298 |
SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL prepared WAIT_FOR apply'; |
| 299 |
--send OPTIMIZE TABLE t |
| 300 |
connection default; |
| 301 |
|
| 302 |
SET DEBUG_SYNC='now WAIT_FOR prepared'; |
| 303 |
INSERT INTO t SET b='fubar'; |
| 304 |
BEGIN; |
| 305 |
DELETE FROM t; |
| 306 |
ROLLBACK; |
| 307 |
SET DEBUG_SYNC='now SIGNAL apply'; |
| 308 |
|
| 309 |
connection con1; |
| 310 |
reap; |
| 311 |
|
| 312 |
connection default; |
| 313 |
CHECK TABLE t; |
| 314 |
SELECT * FROM t; |
| 315 |
DROP TABLE t; |
| 316 |
|
| 317 |
disconnect con1; |
| 318 |
|
| 319 |
SET DEBUG_SYNC = 'RESET'; |
| 320 |
|
| 321 |
--source include/wait_until_count_sessions.inc |
| 322 |
|
| 323 |
--echo # |
| 324 |
--echo # Bug#24658707 ASSERT: BUF0BUF.CC:2469:BUF_BLOCK_GET_STATE(BLOCK) |
| 325 |
--echo # == BUF_BLOCK_FILE_PAGE# |
| 326 |
--echo # |
| 327 |
|
| 328 |
--connect(con1,localhost,root) |
| 329 |
--connect(con2,localhost,root) |
| 330 |
|
| 331 |
CREATE TABLE t1 ( |
| 332 |
col1 int(11) NOT NULL, |
| 333 |
col2 int(11) DEFAULT NULL |
| 334 |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
| 335 |
|
| 336 |
--connection con1 |
| 337 |
SET DEBUG_SYNC='purge_wait_for_btr_search_latch WAIT_FOR go'; |
| 338 |
--send ALTER TABLE t1 ADD COLUMN col3 INT GENERATED ALWAYS AS (col1 % col2) VIRTUAL, algorithm=inplace; |
| 339 |
|
| 340 |
--connection con2 |
| 341 |
# Allow purge to wait for btr_search_latch |
| 342 |
--sleep 10 |
| 343 |
SET DEBUG_SYNC = 'now signal go'; |
| 344 |
|
| 345 |
--connection con1 |
| 346 |
--reap |
| 347 |
|
| 348 |
SET DEBUG_SYNC = 'RESET'; |
| 349 |
|
| 350 |
DROP TABLE t1; |
| 351 |
|
| 352 |
--connection default |
| 353 |
--disconnect con1 |
| 354 |
--disconnect con2 |
| 355 |
|
| 356 |
--echo # |
| 357 |
--echo # Bug#26330279 - ASSERT ON ROW_PURGE_REMOVE_SEC_IF_POSS_LEAF| INNOBASE/ROW/ROW0PURGE.CC |
| 358 |
--echo # |
| 359 |
|
| 360 |
SET GLOBAL innodb_purge_stop_now=ON; |
| 361 |
CREATE TABLE t1 (col1 INT, |
| 362 |
col2 VARCHAR(100), |
| 363 |
col3 VARCHAR(80) GENERATED ALWAYS AS (SUBSTR(col2,1,70)), |
| 364 |
PRIMARY KEY (col1), UNIQUE KEY uidx(col3(10))) ENGINE = InnoDB; |
| 365 |
|
| 366 |
INSERT INTO t1(col1, col2) VALUES(1, CONCAT(1, REPEAT('z',90))); |
| 367 |
|
| 368 |
REPLACE INTO t1(col1, col2) SELECT col1, col2 FROM t1; |
| 369 |
|
| 370 |
SET GLOBAL innodb_purge_run_now=ON; |
| 371 |
--source include/wait_innodb_all_purged.inc |
| 372 |
|
| 373 |
DROP TABLE t1; |
| 374 |
|
| 375 |
|
| 376 |
--echo # |
| 377 |
--echo # BUG#27319084 - INDEX CORRUPT: INNODB: FIELD 0 LEN IS NNN, SHOULD BE NNN; RECORD |
| 378 |
--echo # |
| 379 |
|
| 380 |
CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, b VARCHAR(198) NOT NULL, c CHAR(84), vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL, vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL, INDEX(vbidxcol ASC)) ROW_FORMAT = DYNAMIC; |
| 381 |
|
| 382 |
INSERT INTO t1(a, d, b, c) VALUES(200, 300, '1.3.1415926535', 'hello world'); |
| 383 |
|
| 384 |
connect (conn1, localhost, root,,); |
| 385 |
|
| 386 |
connection default; |
| 387 |
|
| 388 |
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done'; |
| 389 |
|
| 390 |
--send ALTER TABLE t1 ROW_FORMAT = REDUNDANT |
| 391 |
|
| 392 |
connection conn1; |
| 393 |
|
| 394 |
SET DEBUG_SYNC = 'now WAIT_FOR altered'; |
| 395 |
|
| 396 |
INSERT INTO t1(a, d, b, c) VALUES(201, 300, '3.1415926535', 'hello world'); |
| 397 |
|
| 398 |
UPDATE t1 SET b = '6.1516179' WHERE a = 200; |
| 399 |
|
| 400 |
SET DEBUG_SYNC = 'now SIGNAL dmls_done'; |
| 401 |
|
| 402 |
disconnect conn1; |
| 403 |
|
| 404 |
connection default; |
| 405 |
|
| 406 |
reap; |
| 407 |
|
| 408 |
SET DEBUG_SYNC = 'reset'; |
| 409 |
|
| 410 |
CHECK TABLE t1; |
| 411 |
|
| 412 |
SELECT * FROM t1; |
| 413 |
|
| 414 |
DROP TABLE t1; |
| 415 |
|
| 416 |
|
| 417 |
CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, b VARCHAR(198) NOT NULL, c CHAR(84), vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL, vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL, INDEX(vbidxcol ASC)) ROW_FORMAT = REDUNDANT; |
| 418 |
|
| 419 |
INSERT INTO t1(a, d, b, c) VALUES(200, 300, '1.3.1415926535', 'hello world'); |
| 420 |
|
| 421 |
connect (conn1, localhost, root,,); |
| 422 |
|
| 423 |
connection default; |
| 424 |
|
| 425 |
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done'; |
| 426 |
|
| 427 |
--send ALTER TABLE t1 ROW_FORMAT = DYNAMIC |
| 428 |
|
| 429 |
connection conn1; |
| 430 |
|
| 431 |
SET DEBUG_SYNC = 'now WAIT_FOR altered'; |
| 432 |
|
| 433 |
INSERT INTO t1(a, d, b, c) VALUES(201, 300, '3.1415926535', 'hello world'); |
| 434 |
|
| 435 |
UPDATE t1 SET b = '6.1516179' WHERE a = 200; |
| 436 |
|
| 437 |
SET DEBUG_SYNC = 'now SIGNAL dmls_done'; |
| 438 |
|
| 439 |
disconnect conn1; |
| 440 |
|
| 441 |
connection default; |
| 442 |
|
| 443 |
reap; |
| 444 |
|
| 445 |
SET DEBUG_SYNC = 'reset'; |
| 446 |
|
| 447 |
CHECK TABLE t1; |
| 448 |
|
| 449 |
SELECT * FROM t1; |
| 450 |
|
| 451 |
DROP TABLE t1; |
| 452 |
|
| 453 |
|
| 454 |
--echo # |
| 455 |
--echo # BUG#28448853 - ASSERTION FAILURE: ROW0LOG.CC:2153 ROW_LOG_TABLE_APPLY (THR=0X7F7C7C050030 |
| 456 |
--echo # |
| 457 |
|
| 458 |
CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, b VARCHAR(198) NOT NULL, c CHAR(84), vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL); |
| 459 |
|
| 460 |
INSERT INTO t1(a, d, b, c) VALUES(200, 300, '1.3.1415926535', 'hello world'); |
| 461 |
|
| 462 |
SELECT * FROM t1; |
| 463 |
|
| 464 |
connect (conn1, localhost, root,,); |
| 465 |
|
| 466 |
connection default; |
| 467 |
|
| 468 |
SET DEBUG_SYNC = 'inplace_after_index_build SIGNAL altered WAIT_FOR dmls_done'; |
| 469 |
|
| 470 |
--send ALTER TABLE t1 ROW_FORMAT = COMPRESSED; |
| 471 |
|
| 472 |
connection conn1; |
| 473 |
|
| 474 |
SET DEBUG_SYNC = 'now WAIT_FOR altered'; |
| 475 |
|
| 476 |
START TRANSACTION; |
| 477 |
UPDATE t1 SET b = '6.1516179' WHERE a = 200; |
| 478 |
ROLLBACK; |
| 479 |
|
| 480 |
SET DEBUG_SYNC = 'now SIGNAL dmls_done'; |
| 481 |
|
| 482 |
disconnect conn1; |
| 483 |
|
| 484 |
connection default; |
| 485 |
|
| 486 |
reap; |
| 487 |
|
| 488 |
SET DEBUG_SYNC = 'reset'; |
| 489 |
|
| 490 |
CHECK TABLE t1; |
| 491 |
|
| 492 |
SHOW CREATE TABLE t1; |
| 493 |
|
| 494 |
SELECT * FROM t1; |
| 495 |
|
| 496 |
DROP TABLE t1; |
| 497 |
|
| 498 |
|
| 499 |
|
| 500 |
--echo # |
| 501 |
--echo # Bug#28825718 - ASSERTION FAILURE: TRX0REC.CC:NNN:N_IDX > 0 WHILE DOING REPLACE/INSERT |
| 502 |
--echo # |
| 503 |
|
| 504 |
CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT GENERATED ALWAYS AS(b+1) VIRTUAL) ENGINE=InnoDB; |
| 505 |
|
| 506 |
INSERT INTO t1(a, b) VALUES(1, 1); |
| 507 |
|
| 508 |
connect (con1,localhost,root,,); |
| 509 |
connection con1; |
| 510 |
SET DEBUG_SYNC = 'row_log_apply_after SIGNAL s1 WAIT_FOR s2'; |
| 511 |
SET lock_wait_timeout = 1; |
| 512 |
--send ALTER TABLE t1 ADD UNIQUE INDEX(c, b) |
| 513 |
|
| 514 |
connection default; |
| 515 |
|
| 516 |
SET DEBUG_SYNC = 'now WAIT_FOR s1'; |
| 517 |
SET DEBUG_SYNC = 'row_ins_sec_index_enter SIGNAL s2 WAIT_FOR s3'; |
| 518 |
--send INSERT INTO t1(a, b) VALUES(2, 2) |
| 519 |
|
| 520 |
connection con1; |
| 521 |
--echo /* con1 reap */ ALTER TABLE t1 ADD UNIQUE INDEX(c, b); |
| 522 |
--error ER_LOCK_WAIT_TIMEOUT |
| 523 |
reap; |
| 524 |
|
| 525 |
SET DEBUG_SYNC ='now SIGNAL s3'; |
| 526 |
|
| 527 |
disconnect con1; |
| 528 |
|
| 529 |
connection default; |
| 530 |
reap; |
| 531 |
|
| 532 |
ALTER TABLE t1 ADD KEY(b); |
| 533 |
|
| 534 |
INSERT INTO t1(a, b) VALUES(3, 3); |
| 535 |
|
| 536 |
SELECT * FROM t1; |
| 537 |
|
| 538 |
CHECK TABLE t1; |
| 539 |
|
| 540 |
DROP TABLE t1; |
| 541 |
|
| 542 |
--echo # |
| 543 |
--echo # Bug#28834208 ROW_LOG_TABLE_APPLY_UPDATE*INPLACE_ALTER_TABLE |
| 544 |
--echo # |
| 545 |
|
| 546 |
CREATE TABLE t1 ( |
| 547 |
pkey INT(11) PRIMARY KEY, |
| 548 |
a VARCHAR(100), |
| 549 |
v VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(a,2,2)) VIRTUAL, |
| 550 |
c varchar(100), |
| 551 |
UNIQUE KEY k (v) |
| 552 |
); |
| 553 |
INSERT INTO t1 ( pkey,a,c) VALUES (149,'bata','test'); |
| 554 |
SELECT pkey,a,v FROM t1; |
| 555 |
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL index_built WAIT_FOR S2'; |
| 556 |
--send OPTIMIZE TABLE t1 |
| 557 |
connect (con1,localhost,root,,); |
| 558 |
--echo # Connect to con1 |
| 559 |
--connection con1 |
| 560 |
SET DEBUG_SYNC = 'now WAIT_FOR index_built'; |
| 561 |
UPDATE t1 SET a='tata' WHERE pkey=149; |
| 562 |
SET DEBUG_SYNC ='now signal S2'; |
| 563 |
SELECT pkey,a,v FROM t1; |
| 564 |
disconnect con1; |
| 565 |
--connection default |
| 566 |
--echo # Connect to Default |
| 567 |
reap; |
| 568 |
SELECT * FROM t1; |
| 569 |
DROP TABLE t1; |
| 570 |
|
| 571 |
CREATE TABLE t1 ( |
| 572 |
pkey INT(11) PRIMARY KEY, |
| 573 |
a VARCHAR(100), |
| 574 |
v VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(a,2,2)) VIRTUAL, |
| 575 |
c varchar(100), |
| 576 |
v1 VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(c,2,2)) VIRTUAL, |
| 577 |
KEY k (v), |
| 578 |
KEY v (v1) |
| 579 |
); |
| 580 |
INSERT INTO t1 (pkey, a, c) VALUES (149, 'bata', 'test'); |
| 581 |
INSERT INTO t1 (pkey, a, c) VALUES (150, 'bata', 'test'); |
| 582 |
INSERT INTO t1 (pkey, a, c) VALUES (160, 'bata', 'test'); |
| 583 |
SELECT pkey, a, v, c, v1 FROM t1; |
| 584 |
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL index_built WAIT_FOR S2'; |
| 585 |
--send OPTIMIZE TABLE t1 |
| 586 |
connect (con1,localhost,root,,); |
| 587 |
--echo # Connect to con1 |
| 588 |
--connection con1 |
| 589 |
SET DEBUG_SYNC = 'now WAIT_FOR index_built'; |
| 590 |
UPDATE t1 SET c = 'best' WHERE pkey = 149; |
| 591 |
UPDATE t1 SET a = 'tata' WHERE pkey = 150; |
| 592 |
UPDATE t1 SET a = 'tata', c = 'best' WHERE pkey = 160; |
| 593 |
SET DEBUG_SYNC ='now signal S2'; |
| 594 |
SELECT pkey, a, v, c, v1 FROM t1; |
| 595 |
disconnect con1; |
| 596 |
--connection default |
| 597 |
--echo # Connect to Default |
| 598 |
reap; |
| 599 |
SELECT * FROM t1; |
| 600 |
DROP TABLE t1; |
| 601 |
|
| 602 |
CREATE TABLE t1( |
| 603 |
j SERIAL PRIMARY KEY, |
| 604 |
a VARCHAR(36), |
| 605 |
isa BOOLEAN, |
| 606 |
ab VARCHAR(36) AS (IF(`isa`,`a`,NULL )), |
| 607 |
KEY k(ab) |
| 608 |
); |
| 609 |
INSERT INTO t1 (a, isa) VALUES ('test', 1); |
| 610 |
SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL index_built WAIT_FOR S2'; |
| 611 |
--send OPTIMIZE TABLE t1 |
| 612 |
connect (con1,localhost,root,,); |
| 613 |
--echo # Connect to con1 |
| 614 |
--connection con1 |
| 615 |
SET DEBUG_SYNC = 'now WAIT_FOR index_built'; |
| 616 |
UPDATE t1 SET isa=0 ; |
| 617 |
SET DEBUG_SYNC ='now signal S2'; |
| 618 |
SELECT j,a,isa,ab FROM t1; |
| 619 |
disconnect con1; |
| 620 |
--echo # Connect to Default |
| 621 |
--connection default |
| 622 |
reap; |
| 623 |
SELECT j,a,isa,ab FROM t1; |
| 624 |
UPDATE t1 SET isa = 1 WHERE a = 'test' AND isa = 0; |
| 625 |
SELECT j,a,isa,ab FROM t1; |
| 626 |
DROP TABLE t1; |
| 627 |
|
| 628 |
|
| 629 |
|
| 630 |
# Test for the rollback scenario when both a virtual column and a virtual |
| 631 |
# Index are added. The in-memory dict_index_t object should be handled |
| 632 |
# properly, in case of any unexpected crash |
| 633 |
|
| 634 |
CREATE TABLE t1(a INT); |
| 635 |
|
| 636 |
INSERT INTO t1 VALUES(1), (1); |
| 637 |
|
| 638 |
set session debug = '+d, force_virtual_col_build_fail'; |
| 639 |
|
| 640 |
# Prepare an index which is marked as aborted |
| 641 |
connect (conn1,localhost,root,,); |
| 642 |
connect (conn2,localhost,root,,); |
| 643 |
|
| 644 |
connection default; |
| 645 |
|
| 646 |
SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL rolling_back WAIT_FOR drop_created'; |
| 647 |
--send ALTER TABLE t1 ADD COLUMN (h INT GENERATED ALWAYS AS(a+1) VIRTUAL), ADD INDEX idx(h), ALGORITHM=INPLACE; |
| 648 |
|
| 649 |
connection conn1; |
| 650 |
SET DEBUG_SYNC='now WAIT_FOR rolling_back'; |
| 651 |
SET DEBUG_SYNC='row_search_for_mysql_before_return SIGNAL scanning WAIT_FOR continue_scan'; |
| 652 |
--send SELECT * FROM t1; |
| 653 |
|
| 654 |
connection conn2; |
| 655 |
SET DEBUG_SYNC='now WAIT_FOR scanning'; |
| 656 |
SET DEBUG_SYNC='now SIGNAL drop_created'; |
| 657 |
|
| 658 |
connection default; |
| 659 |
--error ER_GET_ERRNO |
| 660 |
--reap |
| 661 |
|
| 662 |
connection conn2; |
| 663 |
SET DEBUG_SYNC='now SIGNAL continue_scan'; |
| 664 |
|
| 665 |
connection conn1; |
| 666 |
--reap; |
| 667 |
|
| 668 |
disconnect conn1; |
| 669 |
disconnect conn2; |
| 670 |
|
| 671 |
connection default; |
| 672 |
|
| 673 |
set session debug = '-d, force_virtual_col_build_fail'; |
| 674 |
|
| 675 |
INSERT INTO t1 VALUES(1); |
| 676 |
|
| 677 |
CHECK TABLE t1; |
| 678 |
|
| 679 |
DROP TABLE t1; |
| 680 |
|
| 681 |
--source include/wait_until_count_sessions.inc |
| 682 |
|
| 683 |
--echo # |
| 684 |
--echo # Bug 31279528 - CRASH IN MYSQL SERVER, ASSERTION FAILURE: DICT0DICT.CC |
| 685 |
--echo # |
| 686 |
CREATE TABLE t1 (c1 INT); |
| 687 |
|
| 688 |
# Create a secondary index which will cause virtual columns to be added into table |
| 689 |
CREATE INDEX idx ON t1 ( ( c1 = 127 ) , ( c1 + c1 ) , c1 , ( c1 + - -1 ) , (c1 > 26 ) ) ; |
| 690 |
|
| 691 |
SHOW WARNINGS; |
| 692 |
|
| 693 |
SHOW CREATE TABLE t1; |
| 694 |
|
| 695 |
SET SESSION debug= '+d,skip_dd_table_access_check'; |
| 696 |
SELECT ID INTO @tid FROM mysql.tables WHERE NAME='t1'; |
| 697 |
SELECT NAME, ORDINAL_POSITION, TYPE, HIDDEN, GENERATION_EXPRESSION_UTF8 FROM mysql.columns WHERE TABLE_ID=@tid ORDER BY ORDINAL_POSITION; |
| 698 |
|
| 699 |
DROP TABLE t1; |
| 700 |
|
| 701 |
|
| 702 |
--echo # |
| 703 |
--echo # Bug #30556595 FAILING ASSERTION: !CURSOR->INDEX->IS_COMMITTED() |
| 704 |
--echo # ON TABLE WITH GCOLS |
| 705 |
--echo # |
| 706 |
|
| 707 |
--echo # Set binlog_row_image = MINIMAL. |
| 708 |
SET @old_binlog_row_image = @@binlog_row_image; |
| 709 |
SET @@session.binlog_row_image = 'MINIMAL'; |
| 710 |
|
| 711 |
--echo # Testcase-1 |
| 712 |
CREATE TABLE t (pid int PRIMARY KEY, |
| 713 |
uid int, |
| 714 |
vid int AS (uid) VIRTUAL); |
| 715 |
connect(con1,localhost,root,,); |
| 716 |
--echo # Create index on virtual column and stop before applying row logs. |
| 717 |
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl'; |
| 718 |
--send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace |
| 719 |
connection default; |
| 720 |
SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; |
| 721 |
--echo # Insert a record to generate an insert row log for the new index. |
| 722 |
--echo # LOG-1 idx_vid : INSERT [10, 1] |
| 723 |
INSERT INTO t(pid, uid) VALUES (1,10); |
| 724 |
--echo # Update the record changing PK. It should generate 2 row logs for the new index. |
| 725 |
--echo # LOG-2 idx_vid : DELETE [10, 1] |
| 726 |
--echo # LOG-3 idx_vid : INSERT [10, 2] |
| 727 |
UPDATE t SET pid = 2 WHERE pid = 1; |
| 728 |
SELECT * FROM t; |
| 729 |
SET DEBUG_SYNC= 'now SIGNAL resume_ddl'; |
| 730 |
connection con1; |
| 731 |
--reap |
| 732 |
connection default; |
| 733 |
--echo # Insert a record to re-use the delete marked record in idx_vid [10, 1] |
| 734 |
INSERT INTO t(pid, uid) VALUES (1,10); |
| 735 |
SELECT * FROM t; |
| 736 |
disconnect con1; |
| 737 |
DROP TABLE t; |
| 738 |
|
| 739 |
--echo # Testcase-2 |
| 740 |
CREATE TABLE t (pid int PRIMARY KEY, |
| 741 |
uid int, |
| 742 |
vid int AS (uid) VIRTUAL); |
| 743 |
connect(con1,localhost,root,,); |
| 744 |
--echo # Create index on virtual column and stop before applying row logs. |
| 745 |
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl'; |
| 746 |
--send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace |
| 747 |
connection default; |
| 748 |
SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; |
| 749 |
--echo # Start transaction |
| 750 |
BEGIN; |
| 751 |
--echo # Insert a record to generate an insert row log for the new index. |
| 752 |
--echo # LOG-1 idx_vid : INSERT [10, 1] |
| 753 |
INSERT INTO t(pid, uid) VALUES (1,10); |
| 754 |
--echo # Update the record changing PK. It should generate 2 row logs for the new index. |
| 755 |
--echo # LOG-2 idx_vid : DELETE [10, 1] |
| 756 |
--echo # LOG-3 idx_vid : INSERT [10, 2] |
| 757 |
UPDATE t SET pid = 2 WHERE pid = 1; |
| 758 |
SELECT * FROM t; |
| 759 |
--echo # Rollback transaction |
| 760 |
--echo # LOG-4 idx_vid : DELETE [10, 2] |
| 761 |
--echo # LOG-5 idx_vid : INSERT [10, 1] |
| 762 |
--echo # LOG-6 idx_vid : DELETE [10, 1] |
| 763 |
ROLLBACK; |
| 764 |
SET DEBUG_SYNC= 'now SIGNAL resume_ddl'; |
| 765 |
connection con1; |
| 766 |
--reap |
| 767 |
connection default; |
| 768 |
--echo # Insert a record to check valid entry in idx_vid [NULL, 1] |
| 769 |
INSERT INTO t(pid) VALUES (1); |
| 770 |
SELECT * FROM t; |
| 771 |
disconnect con1; |
| 772 |
DROP TABLE t; |
| 773 |
|
| 774 |
--echo # Testcase-3 |
| 775 |
CREATE TABLE t (pid int PRIMARY KEY, |
| 776 |
uid int, |
| 777 |
vid int AS (uid) VIRTUAL); |
| 778 |
INSERT INTO t(pid, uid) VALUES (1,10); |
| 779 |
connect(con1,localhost,root,,); |
| 780 |
--echo # Create index on virtual column and stop before applying row logs. |
| 781 |
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl'; |
| 782 |
--send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace |
| 783 |
connection default; |
| 784 |
SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; |
| 785 |
--echo # Update the record changing PK. It should generate 2 row logs for the new index. |
| 786 |
--echo # LOG-2 idx_vid : DELETE [10, 1] |
| 787 |
--echo # LOG-3 idx_vid : INSERT [10, 2] |
| 788 |
UPDATE t SET pid = 2 WHERE pid = 1; |
| 789 |
SET DEBUG_SYNC= 'now SIGNAL resume_ddl'; |
| 790 |
connection con1; |
| 791 |
--reap |
| 792 |
connection default; |
| 793 |
--echo # Read [2, 10, 10] row using idx_vid index key. |
| 794 |
--sleep 1 |
| 795 |
--skip_if_hypergraph # Hits assertion. Bug#33888263. |
| 796 |
select * from t where VID=10; |
| 797 |
disconnect con1; |
| 798 |
DROP TABLE t; |
| 799 |
|
| 800 |
--echo # Testcase-4 |
| 801 |
CREATE TABLE t (pid int PRIMARY KEY, |
| 802 |
uid int, |
| 803 |
vid int AS (uid) VIRTUAL); |
| 804 |
INSERT INTO t(pid, uid) VALUES (1,10); |
| 805 |
connect(con1,localhost,root,,); |
| 806 |
--echo # Create index on virtual column and stop before applying row logs. |
| 807 |
SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl'; |
| 808 |
--send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace |
| 809 |
connection default; |
| 810 |
SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; |
| 811 |
--echo # Update the record changing PK. It should generate 2 row logs for the new index. |
| 812 |
--echo # LOG-2 idx_vid : DELETE [10, 1] |
| 813 |
--echo # LOG-3 idx_vid : INSERT [10, 2] |
| 814 |
UPDATE t SET pid = 2 WHERE pid = 1; |
| 815 |
SET DEBUG_SYNC= 'now SIGNAL resume_ddl'; |
| 816 |
connection con1; |
| 817 |
--reap |
| 818 |
connection default; |
| 819 |
--echo # Delete a record. |
| 820 |
--skip_if_hypergraph # Hits assertion. Bug#33888263. |
| 821 |
delete from t limit 1; |
| 822 |
disconnect con1; |
| 823 |
DROP TABLE t; |
| 824 |
SET @@session.binlog_row_image = @old_binlog_row_image; |