1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
#
# Bug #16963396 INNODB: USE OF LARGE EXTERNALLY-STORED FIELDS MAKES
# CRASH RECOVERY LOSE DATA
#
#
# Uncompressed Table - Insert Operation - Crash Test
# Fresh insert with blobs
#
CREATE TABLE t1 (a BIGINT PRIMARY KEY, b LONGBLOB) ENGINE=InnoDB;
INSERT INTO t1 (a, b) VALUES (1, repeat('^', 40000));
INSERT INTO t1 (a, b) VALUES (2, '2');
INSERT INTO t1 (a, b) VALUES (3, '3');
INSERT INTO t1 (a, b) VALUES (4, '4');
INSERT INTO t1 (a, b) VALUES (5, '5');
begin;
INSERT INTO t1 (a, b) VALUES (6, REPEAT('a', 4*1024*1024));
SELECT a, right(b, 50) FROM t1;
a right(b, 50)
1 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2 2
3 3
4 4
5 5
#
# Uncompressed Table - UPDATE Operation - Crash Test
# Update of non-blob column so that blob is needed.
#
begin;
UPDATE t1 set b = REPEAT('a', 4*1024*1024) where a = 5 ;
SELECT a, right(b, 50) FROM t1;
a right(b, 50)
1 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2 2
3 3
4 4
5 5
#
# Uncompressed Table - UPDATE Operation - Crash Test
# Update of blob column to blob.
#
connect con1,localhost,root,,;
begin;
UPDATE t1 set b = REPEAT('$', 50000) where a = 1;
connection default;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
UPDATE t1 SET b='five' WHERE a=5;
disconnect con1;
SELECT a, right(b, 50) FROM t1;
a right(b, 50)
1 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
2 2
3 3
4 4
5 five
#
# Uncompressed Table - Rollback of UPDATE operation
# Update moves offpage data to inline data.
#
create table t2 (f1 bigint primary key, f2 longblob, f3 longblob,
index(f2(10), f3(10))) engine=innodb;
insert into t2 values (10, repeat('.', 40000), repeat('?', 40000));
connect con1,localhost,root,,;
begin;
update t2 set f2 = '=';
select f1, right(f2, 20), right(f3, 20) from t2;
f1 right(f2, 20) right(f3, 20)
10 = ????????????????????
update t2 set f3 = '&';
select f1, right(f2, 20), right(f3, 20) from t2;
f1 right(f2, 20) right(f3, 20)
10 = &
connection default;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
DELETE FROM t1 WHERE a=1;
disconnect con1;
select f1, right(f2, 20), right(f3, 20) from t2;
f1 right(f2, 20) right(f3, 20)
10 .................... ????????????????????
check table t2;
Table Op Msg_type Msg_text
test.t2 check status OK
drop table t2;
#
# Compressed Table - Insert Operation - Crash Test
# fresh insert with BLOBs
#
set global innodb_compression_level = 0;
create table t3 (f1 bigint primary key, f2 longblob, f3 longblob,
index(f2(10), f3(10))) engine=innodb row_format=compressed;
connect con1,localhost,root,,;
begin;
INSERT INTO t3 (f1, f2, f3) VALUES (6, repeat('/', 40000), repeat('<', 40000));
connection default;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
DELETE FROM t1 WHERE a=2;
disconnect con1;
select f1, length(f2), length(f3) from t3;
f1 length(f2) length(f3)
select f1, right(f2, 30), right(f3, 20) from t3;
f1 right(f2, 30) right(f3, 20)
check table t3;
Table Op Msg_type Msg_text
test.t3 check status OK
#
# Compressed Table - Update Operation - Crash Test
# update of a non-BLOB column so that BLOB is needed
#
set global innodb_compression_level = 0;
insert into t3 values (2, repeat('!', 30), repeat('+', 30));
connect con1,localhost,root,,;
begin;
UPDATE t3 set f2 = repeat('>', 40000) where f1 = 2;
connection default;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
DELETE FROM t1 WHERE a=3;
disconnect con1;
select f1, length(f2), length(f3) from t3;
f1 length(f2) length(f3)
2 30 30
select f1, right(f2, 30), right(f3, 20) from t3;
f1 right(f2, 30) right(f3, 20)
2 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ++++++++++++++++++++
check table t3;
Table Op Msg_type Msg_text
test.t3 check status OK
#
# Compressed Table - Update Operation - Crash Test
# update blob to blob
#
set global innodb_compression_level = 0;
insert into t3 values (3, repeat('%', 40000), repeat('~', 40000));
connect con1,localhost,root,,;
begin;
UPDATE t3 set f2 = concat(f2, repeat(',', 10)) where f1 = 3;
connection default;
SET GLOBAL innodb_flush_log_at_trx_commit=1;
DELETE FROM t1 WHERE a=4;
select f1, length(f2), length(f3) from t3;
f1 length(f2) length(f3)
2 30 30
3 40000 40000
select f1, right(f2, 30), right(f3, 20) from t3;
f1 right(f2, 30) right(f3, 20)
2 !!!!!!!!!!!!!!!!!!!!!!!!!!!!!! ++++++++++++++++++++
3 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%% ~~~~~~~~~~~~~~~~~~~~
check table t3;
Table Op Msg_type Msg_text
test.t3 check status OK
DROP TABLE t1,t3;
|
