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