| 1 |
# |
| 2 |
# Test for read before write removal(rbwr) optimization in MySQL Cluster |
| 3 |
# |
| 4 |
# Parameter based testcase using a table containing the different queries |
| 5 |
# and their expected number of roundtrips(regarding rbwr). This allows the |
| 6 |
# same queries to be run with different settings while making sure that |
| 7 |
# the query result is always the same. Of course some settings causes the |
| 8 |
# rbwr optimzation to not apply and the expected number of roundtrips etc. |
| 9 |
# shouldn't be checked. |
| 10 |
# |
| 11 |
-- source include/have_ndb.inc |
| 12 |
|
| 13 |
--disable_query_log |
| 14 |
--disable_result_log |
| 15 |
|
| 16 |
### Setup parameter tables and utility procedures |
| 17 |
create table rbwr_tests ( |
| 18 |
test_id int primary key auto_increment, |
| 19 |
table_name varchar(255), |
| 20 |
query varchar(255), |
| 21 |
execute_count int, |
| 22 |
affected_rows int |
| 23 |
) engine = MEMORY; |
| 24 |
|
| 25 |
### Setup test tables |
| 26 |
# Same table as ndb_update_no_read |
| 27 |
create table t1 ( |
| 28 |
a int not null primary key, |
| 29 |
b int not null, |
| 30 |
c int, |
| 31 |
unique index_b (b) using hash |
| 32 |
) engine = ndb; |
| 33 |
create temporary table data_t1 ( |
| 34 |
a int not null primary key, |
| 35 |
b int not null, |
| 36 |
c int |
| 37 |
); |
| 38 |
insert into data_t1 values |
| 39 |
(1,10,1),(2,9,1),(3,8,1),(4,7,1),(5,6,1), |
| 40 |
(6,5,2),(7,4,2),(8,3,2),(9,2,2),(10,1,2); |
| 41 |
|
| 42 |
# Composite PK |
| 43 |
create table t2 ( |
| 44 |
pk_a int not null, |
| 45 |
pk_b int not null, |
| 46 |
pk_c int not null, |
| 47 |
PRIMARY KEY (pk_a,pk_b,pk_c), |
| 48 |
d int |
| 49 |
) engine = ndb; |
| 50 |
create temporary table data_t2 ( |
| 51 |
a int not null, |
| 52 |
b int not null, |
| 53 |
c int not null, |
| 54 |
d int |
| 55 |
); |
| 56 |
insert into data_t2 values |
| 57 |
(1,10,1,37),(2,9,1,38),(3,8,1,39),(4,7,1,40),(5,6,1,41), |
| 58 |
(6,5,2,42),(7,4,2,43),(8,3,2,44),(9,2,2,45),(10,1,2,46); |
| 59 |
|
| 60 |
# |
| 61 |
create table t3 ( |
| 62 |
pk_a varchar(256) not null, |
| 63 |
b int not null, |
| 64 |
c int not null, |
| 65 |
PRIMARY KEY (pk_a), |
| 66 |
unique index_cb (c,b) using hash, |
| 67 |
d int |
| 68 |
) engine = ndb; |
| 69 |
create temporary table data_t3 ( |
| 70 |
a varchar(256) not null, |
| 71 |
b int not null, |
| 72 |
c int not null, |
| 73 |
d int |
| 74 |
); |
| 75 |
insert into data_t3 values |
| 76 |
('11111',10,1,44),('22222',9,2,45),('33333',8,3,46),('44444',7,4,47),('55555',6,5,48), |
| 77 |
('66666',5,6,44),('77777',4,7,45),('88888',3,8,46),('99999',2,9,47),('00000',1,10,48); |
| 78 |
|
| 79 |
# Composite PK, more rows, random number of rows |
| 80 |
create table t4 ( |
| 81 |
pk_a varchar(256) not null, |
| 82 |
pk_b int not null, |
| 83 |
pk_c datetime not null, |
| 84 |
PRIMARY KEY (pk_a, pk_b, pk_c), |
| 85 |
d int, |
| 86 |
e varchar(255), |
| 87 |
f int, |
| 88 |
unique index_f (f) using hash, |
| 89 |
g bigint, |
| 90 |
h bigint, |
| 91 |
i bigint, |
| 92 |
j bigint, |
| 93 |
unique index_ghij (g,h,i,j) using hash |
| 94 |
) engine = ndb; |
| 95 |
create temporary table data_t4 ( |
| 96 |
a varchar(256) not null, |
| 97 |
b int not null, |
| 98 |
c datetime not null, |
| 99 |
d int, |
| 100 |
e varchar(255), |
| 101 |
f int, |
| 102 |
g bigint, |
| 103 |
h int, |
| 104 |
i bigint, |
| 105 |
j char(11) |
| 106 |
); |
| 107 |
let $i = `select round(rand() * 10)`; |
| 108 |
while ($i) |
| 109 |
{ |
| 110 |
eval insert into data_t4 values ( |
| 111 |
REPEAT('$i', 4), |
| 112 |
$i, |
| 113 |
DATE_ADD("2012-10-15 11:11:11", INTERVAL $i SECOND), |
| 114 |
100000-$i, |
| 115 |
REPEAT(CHAR(64+25*RAND()), 1+254*RAND()), |
| 116 |
$i, |
| 117 |
1000+$i, |
| 118 |
10000+$i, |
| 119 |
100000+$i, |
| 120 |
1000000+$i |
| 121 |
); |
| 122 |
|
| 123 |
dec $i; |
| 124 |
} |
| 125 |
|
| 126 |
#Table with BLOB |
| 127 |
create table t5 ( |
| 128 |
a int not null primary key, |
| 129 |
b int not null, |
| 130 |
c int, |
| 131 |
d text, |
| 132 |
unique index_b (b) using hash |
| 133 |
) engine = ndb; |
| 134 |
create temporary table data_t5 ( |
| 135 |
a int not null primary key, |
| 136 |
b int not null, |
| 137 |
c int, |
| 138 |
d text |
| 139 |
); |
| 140 |
insert into data_t5 values |
| 141 |
(1,10,1,'aaa'),(2,9,1,'bbb'),(3,8,1,'ccc'),(4,7,1,''),(5,6,1,NULL), |
| 142 |
(6,5,2,'foo'),(7,4,2,'xxx'),(8,3,2,'yyy'),(9,2,2,'zzz'),(10,1,3,'xyz'); |
| 143 |
|
| 144 |
#enable_result_log; |
| 145 |
#select * from data_t4; |
| 146 |
#exit; |
| 147 |
|
| 148 |
|
| 149 |
### Add test cases and their expected execute_count results |
| 150 |
|
| 151 |
# Same testcases as ndb_update_no_read |
| 152 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 153 |
("t1", "update t1 set c = 97, b = 98 where a = 1", 2), |
| 154 |
("t1", "delete from t1 where a = 1", 1), |
| 155 |
("t1", "update t1 set c = 97 where b = 2", 1), |
| 156 |
("t1", "update t1 set c = 97, b = 98 where b = 2", 2), |
| 157 |
("t1", "delete from t1 where b = 9", 1), |
| 158 |
("t1", "update t1 set c = 97 where a = 10 or a >= 10", 3), |
| 159 |
("t1", "update t1 set c = 97 where a in (8,10)", 1), |
| 160 |
("t1", "update t1 set c = 97 where a in (7,8) or a >= 10", 4), |
| 161 |
("t1", "update t1 set a = 99, b = 98 where a = 3", 4), |
| 162 |
("t1", "update t1 set a = 99, b = 98 where b = 7", 4), |
| 163 |
("t1", "update t1 set c = 97, b = 98 where a = 5 and b = 6", 2), |
| 164 |
("t1", "delete from t1 where b = 8 and c = 2", 2); |
| 165 |
|
| 166 |
# Using full primary key, one roundtrip |
| 167 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 168 |
("t1", "delete from t1 where a = 1", 1), |
| 169 |
("t1", "update t1 set c = 37 where a = 1", 1), |
| 170 |
("t2", "delete from t2 where pk_a = 1 and pk_b = 2 and pk_c = 3", 1), |
| 171 |
("t2", "update t2 set d = 37 where pk_a = 1 and pk_b = 2 and pk_c = 3", 1), |
| 172 |
("t3", "delete from t3 where pk_a = \"11111\"", 1), |
| 173 |
("t3", "update t3 set d = 37 where pk_a = \"11111\"", 1), |
| 174 |
("t4", "delete from t4 where pk_a = \"16161616\" and pk_b = 16 |
| 175 |
and pk_c = \"2012-10-15 11:11:27\"", 1), |
| 176 |
("t4", "update t4 set d = 37 where pk_a = \"16161616\" and pk_b = 16 |
| 177 |
and pk_c = \"2012-10-15 11:11:27\"", 1); |
| 178 |
|
| 179 |
# Using full primary key with constant parameter in value list, one roundtrip |
| 180 |
set @param37 = 37; |
| 181 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 182 |
("t1", "update t1 set c = @param37 where a = 1", 1), |
| 183 |
("t2", "update t2 set d = @param37 where pk_a = 1 and pk_b = 2 and |
| 184 |
pk_c = 3", 1), |
| 185 |
("t3", "update t3 set d = @param37 where pk_a = \"11111\"", 1), |
| 186 |
("t4", "update t4 set d = @param37 where pk_a = \"16161616\" and pk_b = 16 |
| 187 |
and pk_c = \"2012-10-15 11:11:27\"", 1); |
| 188 |
|
| 189 |
# Using full primary key with constant parameters in value list and where |
| 190 |
# clause, one roundtrip |
| 191 |
set @param1 = 1; |
| 192 |
set @param2 = 2; |
| 193 |
set @param3 = 3; |
| 194 |
set @param11111 = "11111"; |
| 195 |
set @param16161616 = "16161616"; |
| 196 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 197 |
("t1", "delete from t1 where a = @param1", 1), |
| 198 |
("t1", "update t1 set c = @param37 where a = @param1", 1), |
| 199 |
("t2", "delete from t2 where pk_a = @param1 and pk_b = @param2 and |
| 200 |
pk_c = 3", 1), |
| 201 |
("t2", "update t2 set d = @param37 where pk_a = @param1 and pk_b = @param2 and |
| 202 |
pk_c = 3", 1), |
| 203 |
("t3", "delete from t3 where pk_a = @param11111", 1), |
| 204 |
("t3", "update t3 set d = @param37 where pk_a = @param11111", 1), |
| 205 |
("t4", "delete from t4 where pk_a = @param16161616 and pk_b = 16 |
| 206 |
and pk_c = \"2012-10-15 11:11:27\"", 1), |
| 207 |
("t4", "update t4 set d = @param37 where pk_a = @param16161616 and pk_b = 16 |
| 208 |
and pk_c = \"2012-10-15 11:11:27\"", 1); |
| 209 |
|
| 210 |
# Using full unique key, one roundtrip |
| 211 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 212 |
("t1", "delete from t1 where b = 1", 1), |
| 213 |
("t1", "update t1 set c = 37 where b = 1", 1), |
| 214 |
# no unique key in t2 |
| 215 |
("t3", "delete from t3 where c = 2 and b = 9", 1), |
| 216 |
("t3", "update t3 set d = 37 where c = 2 and b = 9", 1), |
| 217 |
# The 4 cases below should supposedly also use only 1 roundtrip |
| 218 |
("t4", "delete from t4 where f = 37", 2), |
| 219 |
("t4", "update t4 set d = 37 where f = 37", 2), |
| 220 |
("t4", "delete from t4 |
| 221 |
where g = 1009 and h = 10009 and i = 100009 and j = \"1000009\"", 2), |
| 222 |
("t4", "update t4 set d = 37 |
| 223 |
where g = 1009 and h = 10009 and i = 100009 and j = \"1000009\"", 2); |
| 224 |
|
| 225 |
# Update (part of) primary key (may require read+delete+insert+commit) |
| 226 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 227 |
# All columns in PK updated |
| 228 |
("t1", "update t1 set a = 97 where a = 1", 4), #PK |
| 229 |
("t1", "update t1 set a = 98 where b = 1", 4), #Unique key |
| 230 |
|
| 231 |
# Update PK as keyop |
| 232 |
("t2", "update t2 set pk_a = 37 where pk_a = 1 and pk_b = 2 and pk_c = 3", 2), #No match |
| 233 |
("t2", "update t2 set pk_a = 38 where pk_a = 9 and pk_b = 2 and pk_c = 2", 4), |
| 234 |
("t2", "update t2 set pk_b = 39 where pk_a = 9 and pk_b = 2 and pk_c = 2", 4), |
| 235 |
|
| 236 |
# Update PK as index-rangeop |
| 237 |
("t2", "update t2 set pk_a = 47 where pk_a = 1 and pk_b = 2", 2), #No match |
| 238 |
("t2", "update t2 set pk_a = 48 where pk_a = 9 and pk_b = 2", 6), |
| 239 |
|
| 240 |
# Update PK on table having BLOB |
| 241 |
("t5", "update t5 set a = 57 where a = 1", 4), #PK |
| 242 |
("t5", "update t5 set a = 58 where b = 1", 4), #Unique key |
| 243 |
|
| 244 |
# Update PK on table having BLOB, non existing rows |
| 245 |
("t5", "update t5 set a = 57 where a = 99", 2), #PK |
| 246 |
("t5", "update t5 set a = 58 where b = 99", 2) #Unique key |
| 247 |
; |
| 248 |
|
| 249 |
# Update or delete of table with BLOB (Column 'd') |
| 250 |
set @b1 = 'b1'; |
| 251 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 252 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 253 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 254 |
|
| 255 |
INSERT INTO rbwr_tests (table_name, query, execute_count) VALUES |
| 256 |
("t5", "update t5 set d = @b1 where a = 1", 3), #PK |
| 257 |
("t5", "update t5 set d = NULL where a = 1", 3), #PK |
| 258 |
|
| 259 |
("t5", "update t5 set d = @b1 where b = 1", 3), #UQ index |
| 260 |
("t5", "update t5 set d = NULL where b = 1", 3), #UQ index |
| 261 |
|
| 262 |
("t5", "update t5 set d = @b1 where c = 3", 3), #Scan 1 row |
| 263 |
("t5", "update t5 set d = NULL where c = 3", 3), #Scan, update 1 row |
| 264 |
#("t5", "update t5 set d = @b1 where c > 1", 7), #Scan, update 5 rows |
| 265 |
#("t5", "update t5 set d = @b1", 12), #Scan, update 10 rows |
| 266 |
|
| 267 |
# Update BLOB in non existing rows |
| 268 |
("t5", "update t5 set d = @b1 where a = 99", 2), #PK |
| 269 |
("t5", "update t5 set d = NULL where a = 99", 2), #PK |
| 270 |
|
| 271 |
("t5", "update t5 set d = @b1 where b = 99", 2), #UQ index |
| 272 |
("t5", "update t5 set d = NULL where b = 99", 2), #UQ index |
| 273 |
|
| 274 |
("t5", "update t5 set d = @b1 where c = 99", 2), #Scan |
| 275 |
("t5", "update t5 set d = NULL where c = 99", 2), #Scan |
| 276 |
|
| 277 |
# Update BLOB also req. to read the BLOB column |
| 278 |
("t5", "update t5 set d = concat(d,@b1) where a = 1", 3), #PK |
| 279 |
("t5", "update t5 set d = concat(d,@b1) where b = 1", 3), #UQ index |
| 280 |
("t5", "update t5 set d = concat(d,@b1) where c = 3", 3), #Scan 1 row |
| 281 |
#("t5", "update t5 set d = concat(d,@b1) where c > 1", 7), #Scan 5 rows |
| 282 |
#("t5", "update t5 set d = concat(d,@b1)", 11), #Scan 10 rows |
| 283 |
|
| 284 |
# Delete rows with BLOB |
| 285 |
("t5", "delete from t5 where a = 1", 2), #PK |
| 286 |
("t5", "delete from t5 where b = 1", 2), #UQ index |
| 287 |
("t5", "delete from t5 where c = 3", 3), #Scan, single match |
| 288 |
#("t5", "delete from t5 where c > 1", 4), #Scan, match 5 rows |
| 289 |
#("t5", "delete from t5 where c > 0", 4), #Scan, match 10 rows |
| 290 |
("t5", "delete from t5 where c > 9", 2) #Scan, no match |
| 291 |
#("t5", "delete from t5", 4) |
| 292 |
; |
| 293 |
|
| 294 |
|
| 295 |
### Run testcases while there are tests left in the param table |
| 296 |
while (`select count(*) from rbwr_tests`) |
| 297 |
{ |
| 298 |
# Find one testcase |
| 299 |
select @test_id := test_id, |
| 300 |
@test_table := table_name, |
| 301 |
@test_query := query, |
| 302 |
@test_execute_count := execute_count, |
| 303 |
@test_affected_rows := affected_rows |
| 304 |
from rbwr_tests order by test_id limit 1; |
| 305 |
let $test_query = `select @test_query`; |
| 306 |
let $test_table = `select @test_table`; |
| 307 |
let $test_execute_count = `select @test_execute_count`; |
| 308 |
|
| 309 |
--source rbwr.inc |
| 310 |
|
| 311 |
# Delete testcase |
| 312 |
delete from rbwr_tests where test_id = @test_id; |
| 313 |
} |
| 314 |
|
| 315 |
## Table with triggers |
| 316 |
CREATE TABLE main(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, val int)engine=ndb; |
| 317 |
CREATE TABLE log(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, bug_text varchar(500) NOT NULL)engine=ndb; |
| 318 |
|
| 319 |
# Check execute count to verify that read removal is not done for |
| 320 |
# a delete on a table which has a delete trigger |
| 321 |
CREATE TRIGGER log_deletes AFTER DELETE ON main FOR EACH ROW |
| 322 |
INSERT INTO log (bug_text) VALUES ("one row deleted"); |
| 323 |
|
| 324 |
let $before= `select VARIABLE_VALUE from performance_schema.session_status |
| 325 |
where variable_name like 'NDB_EXECUTE_COUNT'`; |
| 326 |
DELETE FROM main WHERE id = 1; |
| 327 |
let $after= `select VARIABLE_VALUE from performance_schema.session_status |
| 328 |
where variable_name like 'NDB_EXECUTE_COUNT'`; |
| 329 |
if (!`select $after-$before >= 4`) |
| 330 |
{ |
| 331 |
enable_result_log; |
| 332 |
echo before: $before; |
| 333 |
echo after: $after; |
| 334 |
die Read removal used even when table has delete triggers; |
| 335 |
} |
| 336 |
|
| 337 |
# Check execute count to verify that read removal is not done for |
| 338 |
# an update on a table which has an update trigger |
| 339 |
CREATE TRIGGER log_updates AFTER UPDATE ON main FOR EACH ROW |
| 340 |
INSERT INTO log (bug_text) VALUES ("one row updated"); |
| 341 |
|
| 342 |
let $before= `select VARIABLE_VALUE from performance_schema.session_status |
| 343 |
where variable_name like 'NDB_EXECUTE_COUNT'`; |
| 344 |
UPDATE main SET val = 111 WHERE id = 1; |
| 345 |
let $after= `select VARIABLE_VALUE from performance_schema.session_status |
| 346 |
where variable_name like 'NDB_EXECUTE_COUNT'`; |
| 347 |
if (!`select $after-$before >= 3`) |
| 348 |
{ |
| 349 |
enable_result_log; |
| 350 |
echo before: $before; |
| 351 |
echo after: $after; |
| 352 |
die Read removal used even when table has update triggers; |
| 353 |
} |
| 354 |
|
| 355 |
### Cleanup |
| 356 |
# Drop test tables |
| 357 |
drop table t1, data_t1; |
| 358 |
drop table t2, data_t2; |
| 359 |
drop table t3, data_t3; |
| 360 |
drop table t4, data_t4; |
| 361 |
drop table t5, data_t5; |
| 362 |
|
| 363 |
drop trigger log_deletes; |
| 364 |
drop trigger log_updates; |
| 365 |
drop table main; |
| 366 |
drop table log; |
| 367 |
|
| 368 |
# Drop parameter tables |
| 369 |
drop table rbwr_tests; |
| 370 |
|
| 371 |
--enable_query_log |
| 372 |
--enable_result_log |
| 373 |
|
| 374 |
if ($ndb_rbwr_fail) |
| 375 |
{ |
| 376 |
die Test failure detected!; |
| 377 |
} |
| 378 |
|
| 379 |
|