1# WL#6745 InnoDB R-tree support 2# This test case will test R-tree split. 3 4--source include/have_innodb.inc 5--source include/have_debug.inc 6--source include/have_debug_sync.inc 7--source include/big_test.inc 8 9# Create table with R-tree index. 10create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb; 11 12# Insert enough values to let R-tree split. 13insert into t1 values(1, Point(1,1)); 14insert into t1 values(2, Point(2,2)); 15insert into t1 values(3, Point(3,3)); 16insert into t1 values(4, Point(4,4)); 17insert into t1 values(5, Point(5,5)); 18insert into t1 values(6, Point(6,6)); 19insert into t1 values(7, Point(7,7)); 20insert into t1 values(8, Point(8,8)); 21insert into t1 values(9, Point(9,9)); 22 23insert into t1 select * from t1; 24insert into t1 select * from t1; 25insert into t1 select * from t1; 26insert into t1 select * from t1; 27 28insert into t1 select * from t1; 29insert into t1 select * from t1; 30 31connect (a,localhost,root,,); 32SET debug_dbug='+d,rtr_pcur_move_to_next_return'; 33 34set session transaction isolation level serializable; 35set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); 36SET DEBUG_SYNC = 'RESET'; 37SET DEBUG_SYNC = 'row_search_for_mysql_before_return SIGNAL started WAIT_FOR go_ahead'; 38--send select count(*) from t1 where MBRWithin(t1.c2, @g1); 39 40connect (con1,localhost,root,,); 41set session transaction isolation level serializable; 42 43SET DEBUG_SYNC = 'now WAIT_FOR started'; 44insert into t1 select * from t1; 45SET DEBUG_SYNC = 'now SIGNAL go_ahead'; 46 47connection a; 48reap; 49select count(*) from t1 where MBRWithin(t1.c2, @g1); 50 51connection default; 52insert into t1 select * from t1; 53insert into t1 select * from t1; 54 55set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); 56select count(*) from t1 where MBRWithin(t1.c2, @g1); 57 58set @g1 = ST_GeomFromText('Polygon((10 10,10 800,800 800,800 10,10 10))'); 59select count(*) from t1 where MBRWithin(t1.c2, @g1); 60 61set session transaction isolation level serializable; 62 63truncate t1; 64 65# Test on predicate locking 66INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)')); 67INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)')); 68INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)')); 69INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)')); 70INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)')); 71INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)')); 72INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)')); 73 74insert into t1 select * from t1; 75insert into t1 select * from t1; 76insert into t1 select * from t1; 77insert into t1 select * from t1; 78 79# Connection 'a' will place predicate lock on almost all pages 80connection a; 81set session transaction isolation level serializable; 82select @@tx_isolation; 83start transaction; 84set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); 85select count(*) from t1 where MBRwithin(t1.c2, @g1); 86 87# The split will replicate locks across pages 88connect (b,localhost,root,,); 89set session transaction isolation level serializable; 90set session innodb_lock_wait_timeout = 1; 91 92select @@tx_isolation; 93 94insert into t1 select * from t1; 95insert into t1 select * from t1; 96insert into t1 select * from t1; 97 98# FIXME: Put this back once we sort out the shrink business 99#insert into t1 select * from t1; 100 101connection a; 102commit; 103 104connection default; 105select count(*) from t1; 106 107# Insert a record that would be in the search range 108insert into t1 values (105, Point(105, 105)); 109 110# Connection 'a' will place predicate lock on almost all pages 111connection a; 112start transaction; 113set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); 114select count(*) from t1 where MBRwithin(t1.c2, @g1); 115 116connection b; 117select @@innodb_lock_wait_timeout; 118select @@tx_isolation; 119 120--error ER_LOCK_WAIT_TIMEOUT 121insert into t1 select * from t1; 122select count(*) from t1; 123 124connection a; 125select sleep(2); 126commit; 127 128#==================Test predicates on "MBRIntersects"========================== 129connection default; 130truncate t1; 131 132# Test on predicate locking 133INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)')); 134INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)')); 135INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(4 4, 170 170)')); 136INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)')); 137INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)')); 138INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)')); 139INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)')); 140 141insert into t1 select * from t1; 142insert into t1 select * from t1; 143insert into t1 select * from t1; 144insert into t1 select * from t1; 145 146# Connection 'a' will place predicate lock on almost all pages 147connection a; 148set session transaction isolation level serializable; 149select @@tx_isolation; 150start transaction; 151set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); 152select count(*) from t1 where MBRwithin(t1.c2, @g1); 153 154# The split will replicate locks across pages 155connection b; 156set session transaction isolation level serializable; 157set session innodb_lock_wait_timeout = 1; 158 159select @@tx_isolation; 160 161insert into t1 select * from t1; 162insert into t1 select * from t1; 163insert into t1 select * from t1; 164insert into t1 select * from t1; 165 166connection a; 167commit; 168 169connection default; 170select count(*) from t1; 171 172# Insert a record that would be in the search range 173insert into t1 values (105, Point(105, 105)); 174 175# Connection 'a' will place predicate lock on almost all pages 176connection a; 177start transaction; 178set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); 179select count(*) from t1 where MBRwithin(t1.c2, @g1); 180select count(*) from t1 where MBRIntersects(t1.c2, @g1); 181 182connection b; 183select @@innodb_lock_wait_timeout; 184select @@tx_isolation; 185 186# this should conflict with the "MBRIntersects" predicate lock in session "a" 187--error ER_LOCK_WAIT_TIMEOUT 188INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 105, 200 105)')); 189select count(*) from t1; 190 191connection a; 192select sleep(2); 193commit; 194 195#==================Test predicate lock on "delete"========================== 196connection default; 197truncate t1; 198 199# Test on predicate locking 200INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)')); 201INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)')); 202INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)')); 203INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)')); 204INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)')); 205INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)')); 206 207# Connection default will place predicate lock on follow range 208set @g1 = ST_GeomFromText('Polygon((3 3, 3 5, 5 5, 5 3, 3 3))'); 209start transaction; 210delete from t1 where MBRWithin(t1.c2, @g1); 211 212connection a; 213set session innodb_lock_wait_timeout = 1; 214select @@innodb_lock_wait_timeout; 215--error ER_LOCK_WAIT_TIMEOUT 216insert into t1 values(4, Point(4,4)); 217 218connection default; 219rollback; 220 221#==================Test predicate lock on "select for update"================== 222connection default; 223truncate t1; 224 225# Test on predicate locking 226INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)')); 227INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(3 3, 160 160)')); 228INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)')); 229INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)')); 230INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)')); 231INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)')); 232 233# Connection default will place predicate lock on follow range 234set @g1 = ST_GeomFromText('Polygon((3 3, 3 5, 5 5, 5 3, 3 3))'); 235start transaction; 236select count(*) from t1 where MBRWithin(t1.c2, @g1) for update; 237 238connection a; 239set session innodb_lock_wait_timeout = 1; 240select @@innodb_lock_wait_timeout; 241--error ER_LOCK_WAIT_TIMEOUT 242insert into t1 values(4, Point(4,4)); 243 244connection default; 245rollback; 246#==================Test predicates replicate through split ================= 247connection default; 248truncate t1; 249 250delimiter |; 251create procedure insert_t1(IN start int, IN total int) 252begin 253 declare i int default 1; 254 set i = start; 255 while (i <= total) DO 256 insert into t1 values (i, Point(i, i)); 257 set i = i + 1; 258 end while; 259end| 260delimiter ;| 261 262CALL insert_t1(0, 1000); 263 264# Connection 'a' will place predicate lock on root and last leaf page 265connection a; 266set session transaction isolation level serializable; 267select @@tx_isolation; 268start transaction; 269set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))'); 270select count(*) from t1 where MBRwithin(t1.c2, @g1); 271 272# Connection 'b' will split the last leaf page, so the predicate 273# lock should replicate 274connection b; 275 276CALL insert_t1(1001, 2000); 277 278# This insert goes to the new page after split, it should be blocked 279set session transaction isolation level serializable; 280set session innodb_lock_wait_timeout = 1; 281# Insert a record that would be in the search range 282--error ER_LOCK_WAIT_TIMEOUT 283insert into t1 values (1200, Point(950, 950)); 284 285connection a; 286select sleep(2); 287commit; 288disconnect a; 289disconnect b; 290 291# Clean up. 292connection default; 293drop table t1; 294 295drop procedure insert_t1; 296 297#============ Test row locks ======================= 298create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb; 299 300 301INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(2 2, 150 150)')); 302INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(3 3, 160 160)')); 303INSERT INTO t1 VALUES (2, ST_GeomFromText('LineString(4 4, 170 170)')); 304INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(5 5, 180 180)')); 305INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(6 6, 190 190)')); 306INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(7 7, 200 200)')); 307INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 8, 210 210)')); 308 309connect (a,localhost,root,,); 310SET SESSION debug_dbug='+d,rtr_pcur_move_to_next_return'; 311 312set transaction isolation level serializable; 313start transaction; 314set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); 315select count(*) from t1 where MBRwithin(t1.c2, @g1); 316 317connect (b,localhost,root,,); 318 319# This should be successful 320delete from t1 where c1 = 1; 321 322connection a; 323commit; 324set transaction isolation level serializable; 325start transaction; 326set @g1 = ST_GeomFromText('Polygon((0 0, 0 300, 300 300, 300 0, 0 0))'); 327select count(*) from t1 where MBRwithin(t1.c2, @g1); 328 329connection b; 330 331set session innodb_lock_wait_timeout = 1; 332--error ER_LOCK_WAIT_TIMEOUT 333delete from t1 where c1 = 2; 334 335# Clean up. 336connection a; 337commit; 338 339connection default; 340drop table t1; 341SET DEBUG_SYNC= 'RESET'; 342 343# Test btr_discard_page adjust concurrent search path 344create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb; 345 346start transaction; 347insert into t1 values(1, Point(1,1)); 348insert into t1 values(2, Point(2,2)); 349insert into t1 values(3, Point(3,3)); 350insert into t1 values(4, Point(4,4)); 351insert into t1 values(5, Point(5,5)); 352insert into t1 values(6, Point(6,6)); 353insert into t1 values(7, Point(7,7)); 354insert into t1 values(8, Point(8,8)); 355insert into t1 values(9, Point(9,9)); 356 357insert into t1 select * from t1; 358insert into t1 select * from t1; 359insert into t1 select * from t1; 360insert into t1 select * from t1; 361 362insert into t1 select * from t1; 363insert into t1 select * from t1; 364insert into t1 select * from t1; 365insert into t1 select * from t1; 366 367insert into t1 select * from t1; 368 369select count(*) from t1; 370 371connection b; 372set @g1 = ST_GeomFromText('Polygon((0 0,0 100,100 100,100 0,0 0))'); 373set transaction isolation level read uncommitted; 374SET DEBUG_SYNC= 'row_search_for_mysql_before_return SIGNAL siga WAIT_FOR sigb'; 375send select count(*) from t1 where MBRWithin(t1.c2, @g1); 376 377connection default; 378SET DEBUG_SYNC= 'now WAIT_FOR siga'; 379rollback; 380SET DEBUG_SYNC= 'now SIGNAL sigb'; 381 382connection b; 383--reap 384select count(*) from t1 where MBRWithin(t1.c2, @g1); 385 386connection default; 387DROP TABLE t1; 388SET DEBUG_SYNC = 'RESET'; 389 390create table t1 (c1 int, c2 geometry not null, spatial index (c2))engine=innodb; 391 392delimiter |; 393create procedure insert_t1(IN total int) 394begin 395 declare i int default 1; 396 while (i <= total) DO 397 insert into t1 values (i, Point(i, i)); 398 set i = i + 1; 399 end while; 400end| 401delimiter ;| 402 403start transaction; 404 405CALL insert_t1(100); 406 407connection a; 408set @g1 = ST_GeomFromText('Polygon((0 0,0 1000,1000 1000,1000 0,0 0))'); 409SET DEBUG_SYNC= 'rtr_pcur_move_to_next_return SIGNAL siga WAIT_FOR sigb'; 410--send select count(*) from t1 where MBRWithin(t1.c2, @g1); 411 412connection default; 413SET DEBUG_SYNC= 'now WAIT_FOR siga'; 414rollback; 415 416SET DEBUG_SYNC= 'now SIGNAL sigb'; 417 418connection a; 419reap; 420 421connection default; 422drop procedure insert_t1; 423DROP TABLE t1; 424 425disconnect a; 426disconnect b; 427 428connection default; 429SET DEBUG_SYNC = 'RESET'; 430