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)); 17SELECT a, right(b, 50) FROM t1; 18a right(b, 50) 191 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 202 2 213 3 224 4 235 5 24# 25# Uncompressed Table - UPDATE Operation - Crash Test 26# Update of non-blob column so that blob is needed. 27# 28begin; 29UPDATE t1 set b = REPEAT('a', 4*1024*1024) where a = 5 ; 30SELECT a, right(b, 50) FROM t1; 31a right(b, 50) 321 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 332 2 343 3 354 4 365 5 37# 38# Uncompressed Table - UPDATE Operation - Crash Test 39# Update of blob column to blob. 40# 41connect con1,localhost,root,,; 42begin; 43UPDATE t1 set b = REPEAT('$', 50000) where a = 1; 44connection default; 45SET GLOBAL innodb_flush_log_at_trx_commit=1; 46UPDATE t1 SET b='five' WHERE a=5; 47disconnect con1; 48SELECT a, right(b, 50) FROM t1; 49a right(b, 50) 501 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ 512 2 523 3 534 4 545 five 55# 56# Uncompressed Table - Rollback of UPDATE operation 57# Update moves offpage data to inline data. 58# 59create table t2 (f1 bigint primary key, f2 longblob, f3 longblob, 60index(f2(10), f3(10))) engine=innodb; 61insert into t2 values (10, repeat('.', 40000), repeat('?', 40000)); 62connect con1,localhost,root,,; 63begin; 64update t2 set f2 = '='; 65select f1, right(f2, 20), right(f3, 20) from t2; 66f1 right(f2, 20) right(f3, 20) 6710 = ???????????????????? 68update t2 set f3 = '&'; 69select f1, right(f2, 20), right(f3, 20) from t2; 70f1 right(f2, 20) right(f3, 20) 7110 = & 72connection default; 73SET GLOBAL innodb_flush_log_at_trx_commit=1; 74DELETE FROM t1 WHERE a=1; 75disconnect con1; 76select f1, right(f2, 20), right(f3, 20) from t2; 77f1 right(f2, 20) right(f3, 20) 7810 .................... ???????????????????? 79check table t2; 80Table Op Msg_type Msg_text 81test.t2 check status OK 82drop table t2; 83# 84# Compressed Table - Insert Operation - Crash Test 85# fresh insert with BLOBs 86# 87set global innodb_compression_level = 0; 88create table t3 (f1 bigint primary key, f2 longblob, f3 longblob, 89index(f2(10), f3(10))) engine=innodb row_format=compressed; 90connect con1,localhost,root,,; 91begin; 92INSERT INTO t3 (f1, f2, f3) VALUES (6, repeat('/', 40000), repeat('<', 40000)); 93connection default; 94SET GLOBAL innodb_flush_log_at_trx_commit=1; 95DELETE FROM t1 WHERE a=2; 96disconnect con1; 97select f1, length(f2), length(f3) from t3; 98f1 length(f2) length(f3) 99select f1, right(f2, 30), right(f3, 20) from t3; 100f1 right(f2, 30) right(f3, 20) 101check table t3; 102Table Op Msg_type Msg_text 103test.t3 check status OK 104# 105# Compressed Table - Update Operation - Crash Test 106# update of a non-BLOB column so that BLOB is needed 107# 108set global innodb_compression_level = 0; 109insert into t3 values (2, repeat('!', 30), repeat('+', 30)); 110connect con1,localhost,root,,; 111begin; 112UPDATE t3 set f2 = repeat('>', 40000) where f1 = 2; 113connection default; 114SET GLOBAL innodb_flush_log_at_trx_commit=1; 115DELETE FROM t1 WHERE a=3; 116disconnect con1; 117select f1, length(f2), length(f3) from t3; 118f1 length(f2) length(f3) 1192 30 30 120select f1, right(f2, 30), right(f3, 20) from t3; 121f1 right(f2, 30) right(f3, 20) 1222 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ++++++++++++++++++++ 123check table t3; 124Table Op Msg_type Msg_text 125test.t3 check status OK 126# 127# Compressed Table - Update Operation - Crash Test 128# update blob to blob 129# 130set global innodb_compression_level = 0; 131insert into t3 values (3, repeat('%', 40000), repeat('~', 40000)); 132connect con1,localhost,root,,; 133begin; 134UPDATE t3 set f2 = concat(f2, repeat(',', 10)) where f1 = 3; 135connection default; 136SET GLOBAL innodb_flush_log_at_trx_commit=1; 137DELETE FROM t1 WHERE a=4; 138select f1, length(f2), length(f3) from t3; 139f1 length(f2) length(f3) 1402 30 30 1413 40000 40000 142select f1, right(f2, 30), right(f3, 20) from t3; 143f1 right(f2, 30) right(f3, 20) 1442 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ++++++++++++++++++++ 1453 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ~~~~~~~~~~~~~~~~~~~~ 146check table t3; 147Table Op Msg_type Msg_text 148test.t3 check status OK 149DROP TABLE t1,t3; 150