1create temporary table t1 2(keyc int, c1 char(100), c2 char(100), 3primary key(keyc), index sec_index(c1) 4) engine = innodb; 5create temporary table t2 6(keyc int, c1 char(100), c2 char(100), 7primary key(keyc), index sec_index(c1) 8) engine = innodb; 9create procedure populate_t1() 10begin 11declare i int default 1; 12while (i <= 200) do 13insert into t1 values (i, 'a', 'b'); 14set i = i + 1; 15end while; 16end| 17create procedure populate_t1_small() 18begin 19declare i int default 1; 20while (i <= 20) do 21insert into t1 values (i, 'c', 'd'); 22set i = i + 1; 23end while; 24end| 25create procedure populate_t1_small2() 26begin 27declare i int default 30; 28while (i <= 50) do 29insert into t1 values (i, 'e', 'f'); 30set i = i + 1; 31end while; 32end| 33begin; 34select count(*) from t1; 35count(*) 360 37call populate_t1(); 38select count(*) from t1; 39count(*) 40200 41select * from t1 limit 10; 42keyc c1 c2 431 a b 442 a b 453 a b 464 a b 475 a b 486 a b 497 a b 508 a b 519 a b 5210 a b 53rollback; 54select count(*) from t1; 55count(*) 560 57begin; 58call populate_t1(); 59select count(*) from t1; 60count(*) 61200 62commit; 63select count(*) from t1; 64count(*) 65200 66truncate table t1; 67select count(*) from t1; 68count(*) 690 70call populate_t1_small(); 71select count(*) from t1; 72count(*) 7320 74rollback; 75select count(*) from t1; 76count(*) 7720 78truncate table t1; 79call populate_t1(); 80select count(*) from t1; 81count(*) 82200 83delete from t1 where keyc <= 60; 84select count(*) from t1; 85count(*) 86140 87call populate_t1_small(); 88select count(*) from t1; 89count(*) 90160 91select * from t1 limit 10; 92keyc c1 c2 931 c d 942 c d 953 c d 964 c d 975 c d 986 c d 997 c d 1008 c d 1019 c d 10210 c d 103begin; 104call populate_t1_small2(); 105select count(*) from t1; 106count(*) 107181 108select * from t1 where keyc > 30 limit 10; 109keyc c1 c2 11031 e f 11132 e f 11233 e f 11334 e f 11435 e f 11536 e f 11637 e f 11738 e f 11839 e f 11940 e f 120rollback; 121select count(*) from t1; 122count(*) 123160 124select * from t1 where keyc > 30 limit 10; 125keyc c1 c2 12661 a b 12762 a b 12863 a b 12964 a b 13065 a b 13166 a b 13267 a b 13368 a b 13469 a b 13570 a b 136update t1 set keyc = keyc + 2000; 137select * from t1 limit 10; 138keyc c1 c2 1392001 c d 1402002 c d 1412003 c d 1422004 c d 1432005 c d 1442006 c d 1452007 c d 1462008 c d 1472009 c d 1482010 c d 149rollback; 150begin; 151update t1 set keyc = keyc + 2000; 152select * from t1 limit 10; 153keyc c1 c2 1544001 c d 1554002 c d 1564003 c d 1574004 c d 1584005 c d 1594006 c d 1604007 c d 1614008 c d 1624009 c d 1634010 c d 164rollback; 165select * from t1 limit 10; 166keyc c1 c2 1672001 c d 1682002 c d 1692003 c d 1702004 c d 1712005 c d 1722006 c d 1732007 c d 1742008 c d 1752009 c d 1762010 c d 177commit; 178select * from t1 limit 10; 179keyc c1 c2 1802001 c d 1812002 c d 1822003 c d 1832004 c d 1842005 c d 1852006 c d 1862007 c d 1872008 c d 1882009 c d 1892010 c d 190insert into t2 select * from t1 where keyc < 2101; 191select count(*) from t2; 192count(*) 19360 194drop procedure populate_t1; 195drop procedure populate_t1_small; 196drop procedure populate_t1_small2; 197drop table t1; 198drop table t2; 199create temporary table t1 200(keyc int, c1 char(100), c2 char(100), 201primary key(keyc), index sec_index(c1) 202) engine = innodb key_block_size = 4; 203set innodb_strict_mode=off; 204create temporary table t2 205(keyc int, c1 char(100), c2 char(100), 206primary key(keyc), index sec_index(c1) 207) engine = innodb key_block_size = 8; 208set innodb_strict_mode=default; 209create procedure populate_t1() 210begin 211declare i int default 1; 212while (i <= 200) do 213insert into t1 values (i, 'a', 'b'); 214set i = i + 1; 215end while; 216end| 217create procedure populate_t1_small() 218begin 219declare i int default 1; 220while (i <= 20) do 221insert into t1 values (i, 'c', 'd'); 222set i = i + 1; 223end while; 224end| 225create procedure populate_t1_small2() 226begin 227declare i int default 30; 228while (i <= 50) do 229insert into t1 values (i, 'e', 'f'); 230set i = i + 1; 231end while; 232end| 233begin; 234select count(*) from t1; 235count(*) 2360 237call populate_t1(); 238select count(*) from t1; 239count(*) 240200 241select * from t1 limit 10; 242keyc c1 c2 2431 a b 2442 a b 2453 a b 2464 a b 2475 a b 2486 a b 2497 a b 2508 a b 2519 a b 25210 a b 253rollback; 254select count(*) from t1; 255count(*) 2560 257begin; 258call populate_t1(); 259select count(*) from t1; 260count(*) 261200 262commit; 263select count(*) from t1; 264count(*) 265200 266truncate table t1; 267select count(*) from t1; 268count(*) 2690 270call populate_t1_small(); 271select count(*) from t1; 272count(*) 27320 274rollback; 275select count(*) from t1; 276count(*) 27720 278truncate table t1; 279call populate_t1(); 280select count(*) from t1; 281count(*) 282200 283delete from t1 where keyc <= 60; 284select count(*) from t1; 285count(*) 286140 287call populate_t1_small(); 288select count(*) from t1; 289count(*) 290160 291select * from t1 limit 10; 292keyc c1 c2 2931 c d 2942 c d 2953 c d 2964 c d 2975 c d 2986 c d 2997 c d 3008 c d 3019 c d 30210 c d 303begin; 304call populate_t1_small2(); 305select count(*) from t1; 306count(*) 307181 308select * from t1 where keyc > 30 limit 10; 309keyc c1 c2 31031 e f 31132 e f 31233 e f 31334 e f 31435 e f 31536 e f 31637 e f 31738 e f 31839 e f 31940 e f 320rollback; 321select count(*) from t1; 322count(*) 323160 324select * from t1 where keyc > 30 limit 10; 325keyc c1 c2 32661 a b 32762 a b 32863 a b 32964 a b 33065 a b 33166 a b 33267 a b 33368 a b 33469 a b 33570 a b 336update t1 set keyc = keyc + 2000; 337select * from t1 limit 10; 338keyc c1 c2 3392001 c d 3402002 c d 3412003 c d 3422004 c d 3432005 c d 3442006 c d 3452007 c d 3462008 c d 3472009 c d 3482010 c d 349rollback; 350begin; 351update t1 set keyc = keyc + 2000; 352select * from t1 limit 10; 353keyc c1 c2 3544001 c d 3554002 c d 3564003 c d 3574004 c d 3584005 c d 3594006 c d 3604007 c d 3614008 c d 3624009 c d 3634010 c d 364rollback; 365select * from t1 limit 10; 366keyc c1 c2 3672001 c d 3682002 c d 3692003 c d 3702004 c d 3712005 c d 3722006 c d 3732007 c d 3742008 c d 3752009 c d 3762010 c d 377commit; 378select * from t1 limit 10; 379keyc c1 c2 3802001 c d 3812002 c d 3822003 c d 3832004 c d 3842005 c d 3852006 c d 3862007 c d 3872008 c d 3882009 c d 3892010 c d 390insert into t2 select * from t1 where keyc < 2101; 391select count(*) from t2; 392count(*) 39360 394drop procedure populate_t1; 395drop procedure populate_t1_small; 396drop procedure populate_t1_small2; 397drop table t1; 398drop table t2; 399set global innodb_file_per_table = 0; 400create temporary table t1 401(keyc int, c1 char(100), c2 char(100), 402primary key(keyc), index sec_index(c1) 403) engine = innodb; 404create temporary table t2 405(keyc int, c1 char(100), c2 char(100), 406primary key(keyc), index sec_index(c1) 407) engine = innodb; 408create procedure populate_t1() 409begin 410declare i int default 1; 411while (i <= 200) do 412insert into t1 values (i, 'a', 'b'); 413set i = i + 1; 414end while; 415end| 416create procedure populate_t1_small() 417begin 418declare i int default 1; 419while (i <= 20) do 420insert into t1 values (i, 'c', 'd'); 421set i = i + 1; 422end while; 423end| 424create procedure populate_t1_small2() 425begin 426declare i int default 30; 427while (i <= 50) do 428insert into t1 values (i, 'e', 'f'); 429set i = i + 1; 430end while; 431end| 432begin; 433select count(*) from t1; 434count(*) 4350 436call populate_t1(); 437select count(*) from t1; 438count(*) 439200 440select * from t1 limit 10; 441keyc c1 c2 4421 a b 4432 a b 4443 a b 4454 a b 4465 a b 4476 a b 4487 a b 4498 a b 4509 a b 45110 a b 452rollback; 453select count(*) from t1; 454count(*) 4550 456begin; 457call populate_t1(); 458select count(*) from t1; 459count(*) 460200 461commit; 462select count(*) from t1; 463count(*) 464200 465truncate table t1; 466select count(*) from t1; 467count(*) 4680 469call populate_t1_small(); 470select count(*) from t1; 471count(*) 47220 473rollback; 474select count(*) from t1; 475count(*) 47620 477truncate table t1; 478call populate_t1(); 479select count(*) from t1; 480count(*) 481200 482delete from t1 where keyc <= 60; 483select count(*) from t1; 484count(*) 485140 486call populate_t1_small(); 487select count(*) from t1; 488count(*) 489160 490select * from t1 limit 10; 491keyc c1 c2 4921 c d 4932 c d 4943 c d 4954 c d 4965 c d 4976 c d 4987 c d 4998 c d 5009 c d 50110 c d 502begin; 503call populate_t1_small2(); 504select count(*) from t1; 505count(*) 506181 507select * from t1 where keyc > 30 limit 10; 508keyc c1 c2 50931 e f 51032 e f 51133 e f 51234 e f 51335 e f 51436 e f 51537 e f 51638 e f 51739 e f 51840 e f 519rollback; 520select count(*) from t1; 521count(*) 522160 523select * from t1 where keyc > 30 limit 10; 524keyc c1 c2 52561 a b 52662 a b 52763 a b 52864 a b 52965 a b 53066 a b 53167 a b 53268 a b 53369 a b 53470 a b 535update t1 set keyc = keyc + 2000; 536select * from t1 limit 10; 537keyc c1 c2 5382001 c d 5392002 c d 5402003 c d 5412004 c d 5422005 c d 5432006 c d 5442007 c d 5452008 c d 5462009 c d 5472010 c d 548rollback; 549begin; 550update t1 set keyc = keyc + 2000; 551select * from t1 limit 10; 552keyc c1 c2 5534001 c d 5544002 c d 5554003 c d 5564004 c d 5574005 c d 5584006 c d 5594007 c d 5604008 c d 5614009 c d 5624010 c d 563rollback; 564select * from t1 limit 10; 565keyc c1 c2 5662001 c d 5672002 c d 5682003 c d 5692004 c d 5702005 c d 5712006 c d 5722007 c d 5732008 c d 5742009 c d 5752010 c d 576commit; 577select * from t1 limit 10; 578keyc c1 c2 5792001 c d 5802002 c d 5812003 c d 5822004 c d 5832005 c d 5842006 c d 5852007 c d 5862008 c d 5872009 c d 5882010 c d 589insert into t2 select * from t1 where keyc < 2101; 590select count(*) from t2; 591count(*) 59260 593drop procedure populate_t1; 594drop procedure populate_t1_small; 595drop procedure populate_t1_small2; 596drop table t1; 597drop table t2; 598set global innodb_file_per_table = 1; 599