1# 2# Bug #16963396 INNODB: USE OF LARGE EXTERNALLY-STORED FIELDS MAKES 3# CRASH RECOVERY LOSE DATA 4# 5# 6# Uncompressed Table - Insert Operation - Crash Test 7# Fresh insert with blobs 8# 9CREATE TABLE t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB; 10INSERT INTO t1 (a, b) VALUES (1, repeat('^', 40000)); 11INSERT INTO t1 (a, b) VALUES (2, '2'); 12INSERT INTO t1 (a, b) VALUES (3, '3'); 13INSERT INTO t1 (a, b) VALUES (4, '4'); 14INSERT INTO t1 (a, b) VALUES (5, '5'); 15begin; 16INSERT INTO t1 (a, b) VALUES (6, REPEAT('a', 4*1024*1024)); 17# Kill and restart 18SELECT a, right(b, 50) FROM t1; 19a right(b, 50) 201 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 212 2 223 3 234 4 245 5 25# 26# Uncompressed Table - UPDATE Operation - Crash Test 27# Update of non-blob column so that blob is needed. 28# 29begin; 30UPDATE t1 set b = REPEAT('a', 4*1024*1024) where a = 5 ; 31# Kill and restart 32SELECT a, right(b, 50) FROM t1; 33a right(b, 50) 341 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 352 2 363 3 374 4 385 5 39# 40# Uncompressed Table - UPDATE Operation - Crash Test 41# Update of blob column to blob. 42# 43begin; 44UPDATE t1 set b = REPEAT('$', 50000) where a = 1 ; 45# Kill and restart 46SELECT a, right(b, 50) FROM t1; 47a right(b, 50) 481 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 492 2 503 3 514 4 525 5 53DROP TABLE t1; 54# 55# Uncompressed Table - Rollback of UPDATE operation 56# Update moves offpage data to inline data. 57# 58create table t2 (f1 bigint primary key, f2 longblob, f3 longblob, 59index(f2(10), f3(10))) engine=innodb; 60insert into t2 values (10, repeat('.', 40000), repeat('?', 40000)); 61start transaction; 62update t2 set f2 = '='; 63select f1, right(f2, 20), right(f3, 20) from t2; 64f1 right(f2, 20) right(f3, 20) 6510 = ???????????????????? 66update t2 set f3 = '&'; 67select f1, right(f2, 20), right(f3, 20) from t2; 68f1 right(f2, 20) right(f3, 20) 6910 = & 70set session debug='d,ib_blob_update_rollback'; 71rollback; 72ERROR HY000: Lost connection to MySQL server during query 73select f1, right(f2, 20), right(f3, 20) from t2; 74f1 right(f2, 20) right(f3, 20) 7510 .................... ???????????????????? 76check table t2; 77Table Op Msg_type Msg_text 78test.t2 check status OK 79# 80# Uncompressed Table - Rollback of UPDATE operation 81# 82insert into t2 values (20, repeat('.', 40000), repeat('?', 40000)); 83select f1, right(f2, 20), right(f3, 20) from t2; 84f1 right(f2, 20) right(f3, 20) 8510 .................... ???????????????????? 8620 .................... ???????????????????? 87start transaction; 88update t2 set f2 = repeat('$', 60000) where f1 = 20; 89select f1, right(f2, 20), right(f3, 20) from t2; 90f1 right(f2, 20) right(f3, 20) 9110 .................... ???????????????????? 9220 $$$$$$$$$$$$$$$$$$$$ ???????????????????? 93SET GLOBAL innodb_log_checkpoint_now=ON; 94# Kill the server 95# Starting server with --innodb-force-recovery-crash=99 96# restart 97select f1, right(f2, 20), right(f3, 20) from t2; 98f1 right(f2, 20) right(f3, 20) 9910 .................... ???????????????????? 10020 .................... ???????????????????? 101check table t2; 102Table Op Msg_type Msg_text 103test.t2 check status OK 104drop table t2; 105# 106# Compressed Table - Insert Operation - Crash Test 107# fresh insert with BLOBs 108# 109set global innodb_compression_level = 0; 110create table t3 (f1 bigint primary key, f2 longblob, f3 longblob, 111index(f2(10), f3(10))) engine=innodb row_format=compressed; 112# Kill and restart 113select f1, length(f2), length(f3) from t3; 114f1 length(f2) length(f3) 115select f1, right(f2, 30), right(f3, 20) from t3; 116f1 right(f2, 30) right(f3, 20) 117check table t3; 118Table Op Msg_type Msg_text 119test.t3 check status OK 120# 121# Compressed Table - Update Operation - Crash Test 122# update of a non-BLOB column so that BLOB is needed 123# 124set global innodb_compression_level = 0; 125insert into t3 values (2, repeat('!', 30), repeat('+', 30)); 126begin; 127UPDATE t3 set f2 = repeat('>', 40000) where f1 = 2; 128# Kill and restart 129select f1, length(f2), length(f3) from t3; 130f1 length(f2) length(f3) 1312 30 30 132select f1, right(f2, 30), right(f3, 20) from t3; 133f1 right(f2, 30) right(f3, 20) 1342 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ++++++++++++++++++++ 135check table t3; 136Table Op Msg_type Msg_text 137test.t3 check status OK 138# 139# Compressed Table - Update Operation - Crash Test 140# update blob to blob 141# 142set global innodb_compression_level = 0; 143insert into t3 values (3, repeat('%', 40000), repeat('~', 40000)); 144begin; 145UPDATE t3 set f2 = concat(f2, repeat(',', 10)) where f1 = 3; 146# Kill and restart 147select f1, length(f2), length(f3) from t3; 148f1 length(f2) length(f3) 1492 30 30 1503 40000 40000 151select f1, right(f2, 30), right(f3, 20) from t3; 152f1 right(f2, 30) right(f3, 20) 1532 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ++++++++++++++++++++ 1543 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ~~~~~~~~~~~~~~~~~~~~ 155check table t3; 156Table Op Msg_type Msg_text 157test.t3 check status OK 158DROP TABLE t3; 159