Debian Codegraph @

mysql-test/suite/rpl/t/rpl_row_index_choice.test

raw
1 --source include/have_binlog_format_row.inc
2 --source include/have_debug.inc
3 --source include/have_innodb.inc
4 --source include/master-slave.inc
5
6 # Bug#58997: Row-based replication breaks on table with only fulltext index:
7 connection master;
8 CREATE TABLE t1 (a int, b varchar(100), fulltext(b)) engine=MyISAM;
9 INSERT INTO t1 VALUES (1,"a"), (2,"b");
10 UPDATE t1 SET b='A' WHERE a=1;
11 DELETE FROM t1 WHERE a=2;
12
13 sync_slave_with_master;
14
15 connection slave;
16
17 SELECT * FROM t1 ORDER BY a;
18
19 connection master;
20 DROP TABLE t1;
21
22
23 # A utility table used to populate subsequent tables in various ways.
24 connection master;
25 CREATE TABLE t1 (d INT PRIMARY KEY) ENGINE=myisam;
26 INSERT INTO t1 VALUES (0);
27 INSERT INTO t1 SELECT d+1 FROM t1;
28 INSERT INTO t1 SELECT d+2 FROM t1;
29 INSERT INTO t1 SELECT d+4 FROM t1;
30 INSERT INTO t1 SELECT d+8 FROM t1;
31 INSERT INTO t1 SELECT d+16 FROM t1;
32 INSERT INTO t1 SELECT d+32 FROM t1;
33 INSERT INTO t1 SELECT d+64 FROM t1;
34 INSERT INTO t1 SELECT d+128 FROM t1;
35 INSERT INTO t1 SELECT d+256 FROM t1;
36 INSERT INTO t1 SELECT d+512 FROM t1;
37
38 # Test that we pick the better multi-column index, even if the
39 # single-column index is more selective in the first column.
40 CREATE TABLE t2 (a INT, b INT, c INT, d INT,
41 KEY wrong_key(a),
42 KEY expected_key(b,c),
43 KEY wrong_key2(c)) ENGINE=myisam;
44 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d FROM t1;
45
46 sync_slave_with_master;
47 connection slave;
48 ANALYZE TABLE t2;
49 --echo # Slave will crash if using the wrong or no index
50 SET @saved_dbug = @@GLOBAL.debug_dbug;
51 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan";
52
53 connection master;
54 UPDATE t2 SET d=10042 WHERE d=42;
55 DELETE FROM t2 WHERE d=53;
56
57 sync_slave_with_master;
58 connection slave;
59 SET GLOBAL debug_dbug="";
60 SELECT * FROM t2 WHERE d IN (10042,53);
61
62 # Test that we don't pick a unique index with NULLS over a more selective
63 # non-unique index.
64 connection master;
65 DROP TABLE t2;
66 CREATE TABLE t2 (a INT, b INT, c INT, d INT NOT NULL, e INT,
67 UNIQUE wrong_key3(a,e),
68 KEY wrong_key4(b,c),
69 UNIQUE expected_key(d)) ENGINE=myisam;
70 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d, NULL FROM t1;
71
72 sync_slave_with_master;
73 connection slave;
74 ANALYZE TABLE t2;
75 --echo # Slave will crash if using the wrong or no index
76 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan";
77
78 connection master;
79 UPDATE t2 SET d=10042 WHERE d=42;
80 DELETE FROM t2 WHERE d=53;
81
82 sync_slave_with_master;
83 connection slave;
84 SET GLOBAL debug_dbug="";
85 SELECT * FROM t2 WHERE d IN (10042,53);
86
87 connection master;
88 DROP TABLE t2;
89
90 # Test that we pick a reasonable index when there is no rec_per_key[]
91 # information (no ANALYZE TABLE).
92 CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL,
93 KEY wrong_key5(b),
94 UNIQUE expected_key(d),
95 KEY wrong_key6(c)) ENGINE=myisam;
96 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d FROM t1;
97
98 sync_slave_with_master;
99 connection slave;
100 --echo # Slave will crash if using the wrong or no index
101 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan";
102
103 connection master;
104 UPDATE t2 SET d=10042 WHERE d=42;
105 DELETE FROM t2 WHERE d=53;
106
107 sync_slave_with_master;
108 connection slave;
109 SET GLOBAL debug_dbug="";
110 SELECT * FROM t2 WHERE d IN (10042,53);
111
112 connection master;
113 DROP TABLE t2;
114
115
116 # Also test without ANALYZE when we pick the sub-optimal index.
117 # In this case the key on (d) is the best one, but without ANALYZE TABLE we
118 # have no information and will pick the first one on (b).
119 # (This test should be updated if we improve the index selection, of course).
120 CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT NOT NULL,
121 KEY expected_key(b),
122 KEY wrong_key7(d),
123 KEY wrong_key8(c)) ENGINE=myisam;
124 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d FROM t1;
125
126 sync_slave_with_master;
127 connection slave;
128 --echo # Slave will crash if using the wrong or no index
129 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan";
130
131 connection master;
132 UPDATE t2 SET d=10042 WHERE d=42;
133 DELETE FROM t2 WHERE d=53;
134
135 sync_slave_with_master;
136 connection slave;
137 SET GLOBAL debug_dbug="";
138 SELECT * FROM t2 WHERE d IN (10042,53);
139
140 connection master;
141 DROP TABLE t2;
142
143
144 # Test that we pick the primary key for InnoDB, if available.
145 CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT,
146 UNIQUE wrong_key9(d),
147 KEY wrong_key10(a),
148 PRIMARY KEY expected_key(c,b)) ENGINE=innodb;
149 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d FROM t1;
150
151 sync_slave_with_master;
152 connection slave;
153 --echo # Slave will crash if using the wrong or no index
154 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan,slave_crash_if_index_scan";
155
156 connection master;
157 UPDATE t2 SET d=10042 WHERE d=42;
158 DELETE FROM t2 WHERE d=53;
159
160 sync_slave_with_master;
161 connection slave;
162 SET GLOBAL debug_dbug="";
163 SELECT * FROM t2 WHERE d IN (10042,53);
164
165 connection master;
166 DROP TABLE t2;
167
168
169 # Test that we pick a good index for InnoDB when primary key is not available.
170 CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT, e INT,
171 UNIQUE wrong_key11(e),
172 KEY wrong_key12(a),
173 KEY expected_key(c,b)) ENGINE=innodb;
174 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d, IF(d<10, d, NULL) FROM t1;
175
176 sync_slave_with_master;
177 connection slave;
178 ANALYZE TABLE t2;
179 --echo # Slave will crash if using the wrong or no index
180 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan";
181
182 connection master;
183 UPDATE t2 SET d=10042 WHERE d=42;
184 DELETE FROM t2 WHERE d=53;
185
186 sync_slave_with_master;
187 connection slave;
188 SET GLOBAL debug_dbug="";
189 SELECT * FROM t2 WHERE d IN (10042,53);
190
191 connection master;
192 DROP TABLE t2;
193
194
195 # When there is no ANALYZE TABLE, InnoDB will just report "1" for index
196 # cardinality for all indexes in rec_per_key. So currently we cannot choose
197 # index to use intelligently. Just test that we work as expected (select
198 # first index, remember that unique keys are sorted first by server).
199 CREATE TABLE t2 (a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, d INT, e INT,
200 KEY wrong_key13(a),
201 UNIQUE expected_key(e),
202 KEY wrong_key14(c,b)) ENGINE=innodb;
203 INSERT INTO t2 SELECT d DIV 10, d MOD 41, d MOD 37, d, IF(d<10, d, NULL) FROM t1;
204
205 sync_slave_with_master;
206 connection slave;
207 --echo # Slave will crash if using the wrong or no index
208 SET GLOBAL debug_dbug="+d,slave_crash_if_wrong_index,slave_crash_if_table_scan";
209
210 connection master;
211 UPDATE t2 SET d=10042 WHERE d=42;
212 DELETE FROM t2 WHERE d=53;
213
214 sync_slave_with_master;
215 connection slave;
216 SET GLOBAL debug_dbug="";
217 SELECT * FROM t2 WHERE d IN (10042,53);
218
219 connection master;
220 DROP TABLE t2;
221
222
223 # Finally, test behaviour when no indexes are available at all.
224 CREATE TABLE t2 (a INT NOT NULL, d INT) ENGINE=innodb;
225 INSERT INTO t2 SELECT d DIV 10, d FROM t1;
226
227 UPDATE t2 SET d=10042 WHERE d=42;
228 DELETE FROM t2 WHERE d=53;
229
230 sync_slave_with_master;
231 connection slave;
232 SELECT * FROM t2 WHERE d IN (10042,53);
233
234 connection master;
235 DROP TABLE t2;
236
237
238 connection master;
239 DROP TABLE t1;
240 sync_slave_with_master;
241 connection slave;
242 SET @@GLOBAL.debug_dbug = @saved_dbug;
243 --source include/rpl_end.inc