2
# Bug #56680 InnoDB may return wrong results from a case-insensitive index
4
-- source include/have_innodb.inc
7
SET @tx_isolation_orig = @@tx_isolation;
8
# The flag innodb_change_buffering_debug is only available in debug builds.
9
# It instructs InnoDB to try to evict pages from the buffer pool when
10
# change buffering is possible, so that the change buffer will be used
12
-- error 0,ER_UNKNOWN_SYSTEM_VARIABLE
13
SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug;
14
-- error 0,ER_UNKNOWN_SYSTEM_VARIABLE
15
SET GLOBAL innodb_change_buffering_debug = 1;
17
SET GLOBAL tx_isolation='REPEATABLE-READ';
19
CREATE TABLE bug56680(
20
a INT AUTO_INCREMENT PRIMARY KEY,
26
INSERT INTO bug56680 VALUES(0,'x',1);
28
SELECT b FROM bug56680;
30
connect (con1,localhost,root,,);
33
UPDATE bug56680 SET b='X';
36
# This should return the last committed value 'x', but would return 'X'
37
# due to a bug in row_search_for_mysql().
38
SELECT b FROM bug56680;
39
# This would always return the last committed value 'x'.
40
SELECT * FROM bug56680;
48
SELECT b FROM bug56680;
50
# For the rest of this test, use the READ UNCOMMITTED isolation level
51
# to see what exists in the secondary index.
52
SET GLOBAL tx_isolation='READ-UNCOMMITTED';
54
# Create enough rows for the table, so that the insert buffer will be
55
# used for modifying the secondary index page. There must be multiple
56
# index pages, because changes to the root page are never buffered.
58
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
59
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
60
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
61
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
62
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
63
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
64
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
65
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
66
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
67
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
68
INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
71
SELECT b FROM bug56680 LIMIT 2;
73
connect (con1,localhost,root,,);
76
DELETE FROM bug56680 WHERE a=1;
77
# This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
78
INSERT INTO bug56680 VALUES(1,'X',1);
80
# This should force an insert buffer merge, and return 'X' in the first row.
81
SELECT b FROM bug56680 LIMIT 3;
84
SELECT b FROM bug56680 LIMIT 2;
89
SELECT b FROM bug56680 LIMIT 2;
95
SELECT b FROM bug56680 LIMIT 2;
97
CREATE TABLE bug56680_2(
98
a INT AUTO_INCREMENT PRIMARY KEY,
99
b VARCHAR(2) CHARSET latin1 COLLATE latin1_german2_ci,
104
INSERT INTO bug56680_2 SELECT 0,_latin1 0xdf,c FROM bug56680;
107
SELECT HEX(b) FROM bug56680_2 LIMIT 2;
108
DELETE FROM bug56680_2 WHERE a=1;
109
# This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
110
INSERT INTO bug56680_2 VALUES(1,'SS',1);
112
# This should force an insert buffer merge, and return 'SS' in the first row.
113
SELECT HEX(b) FROM bug56680_2 LIMIT 3;
114
CHECK TABLE bug56680_2;
116
DELETE FROM bug56680_2 WHERE a=1;
117
# This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
118
INSERT INTO bug56680_2 VALUES(1,_latin1 0xdf,1);
120
# This should force an insert buffer merge, and return 0xdf in the first row.
121
SELECT HEX(b) FROM bug56680_2 LIMIT 3;
122
CHECK TABLE bug56680_2;
124
DROP TABLE bug56680_2;
128
SET GLOBAL tx_isolation = @tx_isolation_orig;
129
-- error 0, ER_UNKNOWN_SYSTEM_VARIABLE
130
SET GLOBAL innodb_change_buffering_debug = @innodb_change_buffering_debug_orig;