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