Debian Codegraph @

mysql-test/suite/innodb_zip/t/bug56680.test

raw
1 #Want to skip this test from daily Valgrind execution
2 --source include/no_valgrind_without_big.inc
3 #
4 # Bug #56680 InnoDB may return wrong results from a case-insensitive index
5 #
6 -- source include/have_innodb_max_16k.inc
7
8 -- disable_query_log
9 SET @transaction_isolation_orig = @@transaction_isolation;
10 SET @innodb_file_per_table_orig = @@innodb_file_per_table;
11 # The flag innodb_change_buffering_debug is only available in debug builds.
12 # It instructs InnoDB to try to evict pages from the buffer pool when
13 # change buffering is possible, so that the change buffer will be used
14 # whenever possible.
15 -- error 0,ER_UNKNOWN_SYSTEM_VARIABLE
16 SET @innodb_change_buffering_debug_orig = @@innodb_change_buffering_debug;
17 -- error 0,ER_UNKNOWN_SYSTEM_VARIABLE
18 SET GLOBAL innodb_change_buffering_debug = 1;
19 -- enable_query_log
20 SET GLOBAL transaction_isolation='REPEATABLE-READ';
21 SET GLOBAL innodb_file_per_table=on;
22
23 CREATE TABLE bug56680(
24 a INT AUTO_INCREMENT PRIMARY KEY,
25 b CHAR(1),
26 c INT,
27 INDEX(b))
28 ENGINE=InnoDB STATS_PERSISTENT=0;
29
30 INSERT INTO bug56680 VALUES(0,'x',1);
31 BEGIN;
32 SELECT b FROM bug56680;
33
34 connect (con1,localhost,root,,);
35 connection con1;
36 BEGIN;
37 UPDATE bug56680 SET b='X';
38
39 connection default;
40 # This should return the last committed value 'x', but would return 'X'
41 # due to a bug in row_search_for_mysql().
42 SELECT b FROM bug56680;
43 # This would always return the last committed value 'x'.
44 SELECT * FROM bug56680;
45
46 connection con1;
47 ROLLBACK;
48 disconnect con1;
49
50 connection default;
51
52 SELECT b FROM bug56680;
53
54 # For the rest of this test, use the READ UNCOMMITTED isolation level
55 # to see what exists in the secondary index.
56 SET GLOBAL transaction_isolation='READ-UNCOMMITTED';
57
58 # Create enough rows for the table, so that the insert buffer will be
59 # used for modifying the secondary index page. There must be multiple
60 # index pages, because changes to the root page are never buffered.
61
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;
69 INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
70 INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
71 INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
72 INSERT INTO bug56680 SELECT 0,b,c FROM bug56680;
73
74 BEGIN;
75 SELECT b FROM bug56680 LIMIT 2;
76
77 connect (con1,localhost,root,,);
78 connection con1;
79 BEGIN;
80 DELETE FROM bug56680 WHERE a=1;
81 # This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
82 INSERT INTO bug56680 VALUES(1,'X',1);
83
84 # This should force an insert buffer merge, and return 'X' in the first row.
85 SELECT b FROM bug56680 LIMIT 3;
86
87 connection default;
88 SELECT b FROM bug56680 LIMIT 2;
89 CHECK TABLE bug56680;
90
91 connection con1;
92 ROLLBACK;
93 SELECT b FROM bug56680 LIMIT 2;
94 CHECK TABLE bug56680;
95
96 connection default;
97 disconnect con1;
98
99 SELECT b FROM bug56680 LIMIT 2;
100
101 CREATE TABLE bug56680_2(
102 a INT AUTO_INCREMENT PRIMARY KEY,
103 b VARCHAR(2) CHARSET latin1 COLLATE latin1_german2_ci,
104 c INT,
105 INDEX(b))
106 ENGINE=InnoDB STATS_PERSISTENT=0;
107
108 INSERT INTO bug56680_2 SELECT 0,_latin1 0xdf,c FROM bug56680;
109
110 BEGIN;
111 SELECT HEX(b) FROM bug56680_2 LIMIT 2;
112 DELETE FROM bug56680_2 WHERE a=1;
113 # This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
114 INSERT INTO bug56680_2 VALUES(1,'SS',1);
115
116 # This should force an insert buffer merge, and return 'SS' in the first row.
117 SELECT HEX(b) FROM bug56680_2 LIMIT 3;
118 CHECK TABLE bug56680_2;
119
120 # Test this with compressed tables.
121 ALTER TABLE bug56680_2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
122
123 SELECT HEX(b) FROM bug56680_2 LIMIT 2;
124 DELETE FROM bug56680_2 WHERE a=1;
125 # This should be buffered, if innodb_change_buffering_debug = 1 is in effect.
126 INSERT INTO bug56680_2 VALUES(1,_latin1 0xdf,1);
127
128 # This should force an insert buffer merge, and return 0xdf in the first row.
129 SELECT HEX(b) FROM bug56680_2 LIMIT 3;
130 CHECK TABLE bug56680_2;
131
132 DROP TABLE bug56680_2;
133 DROP TABLE bug56680;
134
135 -- disable_query_log
136 SET GLOBAL transaction_isolation = @transaction_isolation_orig;
137 SET GLOBAL innodb_file_per_table = @innodb_file_per_table_orig;
138 -- error 0, ER_UNKNOWN_SYSTEM_VARIABLE
139 SET GLOBAL innodb_change_buffering_debug = @innodb_change_buffering_debug_orig;