1use test; 2CREATE TABLE test.part_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, 3dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, 4fkid MEDIUMINT, filler VARCHAR(255), 5PRIMARY KEY(id)) ENGINE='innodb' 6PARTITION BY RANGE(id) 7SUBPARTITION BY hash(id) subpartitions 2 8(PARTITION pa3 values less than (42), 9PARTITION pa6 values less than (60), 10PARTITION pa7 values less than (70), 11PARTITION pa8 values less than (80), 12PARTITION pa9 values less than (90), 13PARTITION pa10 values less than (100), 14PARTITION pa11 values less than MAXVALUE); 15CREATE PROCEDURE test.proc_part() 16BEGIN 17DECLARE ins_count INT DEFAULT 1000; 18DECLARE del_count INT; 19DECLARE cur_user VARCHAR(255); 20DECLARE local_uuid VARCHAR(255); 21DECLARE local_time TIMESTAMP; 22SET local_time= NOW(); 23SET cur_user= CURRENT_USER(); 24SET local_uuid= UUID(); 25WHILE ins_count > 0 DO 26INSERT INTO test.part_tbl VALUES (NULL, NOW(), USER() , UUID(), 27ins_count,'Going to test MBR for MySQL'); 28SET ins_count = ins_count - 1; 29END WHILE; 30SELECT MAX(id) FROM test.part_tbl INTO del_count; 31WHILE del_count > 0 DO 32DELETE FROM test.part_tbl WHERE id = del_count; 33select count(*) as internal_count, del_count -- these two lines are for 34FROM test.part_tbl; -- debug to show the problem 35SET del_count = del_count - 2; 36END WHILE; 37END| 38CALL test.proc_part(); 39internal_count del_count 40999 1000 41internal_count del_count 42998 998 43internal_count del_count 44997 996 45internal_count del_count 46996 994 47internal_count del_count 48995 992 49internal_count del_count 50994 990 51internal_count del_count 52993 988 53internal_count del_count 54992 986 55internal_count del_count 56991 984 57internal_count del_count 58990 982 59internal_count del_count 60989 980 61internal_count del_count 62988 978 63internal_count del_count 64987 976 65internal_count del_count 66986 974 67internal_count del_count 68985 972 69internal_count del_count 70984 970 71internal_count del_count 72983 968 73internal_count del_count 74982 966 75internal_count del_count 76981 964 77internal_count del_count 78980 962 79internal_count del_count 80979 960 81internal_count del_count 82978 958 83internal_count del_count 84977 956 85internal_count del_count 86976 954 87internal_count del_count 88975 952 89internal_count del_count 90974 950 91internal_count del_count 92973 948 93internal_count del_count 94972 946 95internal_count del_count 96971 944 97internal_count del_count 98970 942 99internal_count del_count 100969 940 101internal_count del_count 102968 938 103internal_count del_count 104967 936 105internal_count del_count 106966 934 107internal_count del_count 108965 932 109internal_count del_count 110964 930 111internal_count del_count 112963 928 113internal_count del_count 114962 926 115internal_count del_count 116961 924 117internal_count del_count 118960 922 119internal_count del_count 120959 920 121internal_count del_count 122958 918 123internal_count del_count 124957 916 125internal_count del_count 126956 914 127internal_count del_count 128955 912 129internal_count del_count 130954 910 131internal_count del_count 132953 908 133internal_count del_count 134952 906 135internal_count del_count 136951 904 137internal_count del_count 138950 902 139internal_count del_count 140949 900 141internal_count del_count 142948 898 143internal_count del_count 144947 896 145internal_count del_count 146946 894 147internal_count del_count 148945 892 149internal_count del_count 150944 890 151internal_count del_count 152943 888 153internal_count del_count 154942 886 155internal_count del_count 156941 884 157internal_count del_count 158940 882 159internal_count del_count 160939 880 161internal_count del_count 162938 878 163internal_count del_count 164937 876 165internal_count del_count 166936 874 167internal_count del_count 168935 872 169internal_count del_count 170934 870 171internal_count del_count 172933 868 173internal_count del_count 174932 866 175internal_count del_count 176931 864 177internal_count del_count 178930 862 179internal_count del_count 180929 860 181internal_count del_count 182928 858 183internal_count del_count 184927 856 185internal_count del_count 186926 854 187internal_count del_count 188925 852 189internal_count del_count 190924 850 191internal_count del_count 192923 848 193internal_count del_count 194922 846 195internal_count del_count 196921 844 197internal_count del_count 198920 842 199internal_count del_count 200919 840 201internal_count del_count 202918 838 203internal_count del_count 204917 836 205internal_count del_count 206916 834 207internal_count del_count 208915 832 209internal_count del_count 210914 830 211internal_count del_count 212913 828 213internal_count del_count 214912 826 215internal_count del_count 216911 824 217internal_count del_count 218910 822 219internal_count del_count 220909 820 221internal_count del_count 222908 818 223internal_count del_count 224907 816 225internal_count del_count 226906 814 227internal_count del_count 228905 812 229internal_count del_count 230904 810 231internal_count del_count 232903 808 233internal_count del_count 234902 806 235internal_count del_count 236901 804 237internal_count del_count 238900 802 239internal_count del_count 240899 800 241internal_count del_count 242898 798 243internal_count del_count 244897 796 245internal_count del_count 246896 794 247internal_count del_count 248895 792 249internal_count del_count 250894 790 251internal_count del_count 252893 788 253internal_count del_count 254892 786 255internal_count del_count 256891 784 257internal_count del_count 258890 782 259internal_count del_count 260889 780 261internal_count del_count 262888 778 263internal_count del_count 264887 776 265internal_count del_count 266886 774 267internal_count del_count 268885 772 269internal_count del_count 270884 770 271internal_count del_count 272883 768 273internal_count del_count 274882 766 275internal_count del_count 276881 764 277internal_count del_count 278880 762 279internal_count del_count 280879 760 281internal_count del_count 282878 758 283internal_count del_count 284877 756 285internal_count del_count 286876 754 287internal_count del_count 288875 752 289internal_count del_count 290874 750 291internal_count del_count 292873 748 293internal_count del_count 294872 746 295internal_count del_count 296871 744 297internal_count del_count 298870 742 299internal_count del_count 300869 740 301internal_count del_count 302868 738 303internal_count del_count 304867 736 305internal_count del_count 306866 734 307internal_count del_count 308865 732 309internal_count del_count 310864 730 311internal_count del_count 312863 728 313internal_count del_count 314862 726 315internal_count del_count 316861 724 317internal_count del_count 318860 722 319internal_count del_count 320859 720 321internal_count del_count 322858 718 323internal_count del_count 324857 716 325internal_count del_count 326856 714 327internal_count del_count 328855 712 329internal_count del_count 330854 710 331internal_count del_count 332853 708 333internal_count del_count 334852 706 335internal_count del_count 336851 704 337internal_count del_count 338850 702 339internal_count del_count 340849 700 341internal_count del_count 342848 698 343internal_count del_count 344847 696 345internal_count del_count 346846 694 347internal_count del_count 348845 692 349internal_count del_count 350844 690 351internal_count del_count 352843 688 353internal_count del_count 354842 686 355internal_count del_count 356841 684 357internal_count del_count 358840 682 359internal_count del_count 360839 680 361internal_count del_count 362838 678 363internal_count del_count 364837 676 365internal_count del_count 366836 674 367internal_count del_count 368835 672 369internal_count del_count 370834 670 371internal_count del_count 372833 668 373internal_count del_count 374832 666 375internal_count del_count 376831 664 377internal_count del_count 378830 662 379internal_count del_count 380829 660 381internal_count del_count 382828 658 383internal_count del_count 384827 656 385internal_count del_count 386826 654 387internal_count del_count 388825 652 389internal_count del_count 390824 650 391internal_count del_count 392823 648 393internal_count del_count 394822 646 395internal_count del_count 396821 644 397internal_count del_count 398820 642 399internal_count del_count 400819 640 401internal_count del_count 402818 638 403internal_count del_count 404817 636 405internal_count del_count 406816 634 407internal_count del_count 408815 632 409internal_count del_count 410814 630 411internal_count del_count 412813 628 413internal_count del_count 414812 626 415internal_count del_count 416811 624 417internal_count del_count 418810 622 419internal_count del_count 420809 620 421internal_count del_count 422808 618 423internal_count del_count 424807 616 425internal_count del_count 426806 614 427internal_count del_count 428805 612 429internal_count del_count 430804 610 431internal_count del_count 432803 608 433internal_count del_count 434802 606 435internal_count del_count 436801 604 437internal_count del_count 438800 602 439internal_count del_count 440799 600 441internal_count del_count 442798 598 443internal_count del_count 444797 596 445internal_count del_count 446796 594 447internal_count del_count 448795 592 449internal_count del_count 450794 590 451internal_count del_count 452793 588 453internal_count del_count 454792 586 455internal_count del_count 456791 584 457internal_count del_count 458790 582 459internal_count del_count 460789 580 461internal_count del_count 462788 578 463internal_count del_count 464787 576 465internal_count del_count 466786 574 467internal_count del_count 468785 572 469internal_count del_count 470784 570 471internal_count del_count 472783 568 473internal_count del_count 474782 566 475internal_count del_count 476781 564 477internal_count del_count 478780 562 479internal_count del_count 480779 560 481internal_count del_count 482778 558 483internal_count del_count 484777 556 485internal_count del_count 486776 554 487internal_count del_count 488775 552 489internal_count del_count 490774 550 491internal_count del_count 492773 548 493internal_count del_count 494772 546 495internal_count del_count 496771 544 497internal_count del_count 498770 542 499internal_count del_count 500769 540 501internal_count del_count 502768 538 503internal_count del_count 504767 536 505internal_count del_count 506766 534 507internal_count del_count 508765 532 509internal_count del_count 510764 530 511internal_count del_count 512763 528 513internal_count del_count 514762 526 515internal_count del_count 516761 524 517internal_count del_count 518760 522 519internal_count del_count 520759 520 521internal_count del_count 522758 518 523internal_count del_count 524757 516 525internal_count del_count 526756 514 527internal_count del_count 528755 512 529internal_count del_count 530754 510 531internal_count del_count 532753 508 533internal_count del_count 534752 506 535internal_count del_count 536751 504 537internal_count del_count 538750 502 539internal_count del_count 540749 500 541internal_count del_count 542748 498 543internal_count del_count 544747 496 545internal_count del_count 546746 494 547internal_count del_count 548745 492 549internal_count del_count 550744 490 551internal_count del_count 552743 488 553internal_count del_count 554742 486 555internal_count del_count 556741 484 557internal_count del_count 558740 482 559internal_count del_count 560739 480 561internal_count del_count 562738 478 563internal_count del_count 564737 476 565internal_count del_count 566736 474 567internal_count del_count 568735 472 569internal_count del_count 570734 470 571internal_count del_count 572733 468 573internal_count del_count 574732 466 575internal_count del_count 576731 464 577internal_count del_count 578730 462 579internal_count del_count 580729 460 581internal_count del_count 582728 458 583internal_count del_count 584727 456 585internal_count del_count 586726 454 587internal_count del_count 588725 452 589internal_count del_count 590724 450 591internal_count del_count 592723 448 593internal_count del_count 594722 446 595internal_count del_count 596721 444 597internal_count del_count 598720 442 599internal_count del_count 600719 440 601internal_count del_count 602718 438 603internal_count del_count 604717 436 605internal_count del_count 606716 434 607internal_count del_count 608715 432 609internal_count del_count 610714 430 611internal_count del_count 612713 428 613internal_count del_count 614712 426 615internal_count del_count 616711 424 617internal_count del_count 618710 422 619internal_count del_count 620709 420 621internal_count del_count 622708 418 623internal_count del_count 624707 416 625internal_count del_count 626706 414 627internal_count del_count 628705 412 629internal_count del_count 630704 410 631internal_count del_count 632703 408 633internal_count del_count 634702 406 635internal_count del_count 636701 404 637internal_count del_count 638700 402 639internal_count del_count 640699 400 641internal_count del_count 642698 398 643internal_count del_count 644697 396 645internal_count del_count 646696 394 647internal_count del_count 648695 392 649internal_count del_count 650694 390 651internal_count del_count 652693 388 653internal_count del_count 654692 386 655internal_count del_count 656691 384 657internal_count del_count 658690 382 659internal_count del_count 660689 380 661internal_count del_count 662688 378 663internal_count del_count 664687 376 665internal_count del_count 666686 374 667internal_count del_count 668685 372 669internal_count del_count 670684 370 671internal_count del_count 672683 368 673internal_count del_count 674682 366 675internal_count del_count 676681 364 677internal_count del_count 678680 362 679internal_count del_count 680679 360 681internal_count del_count 682678 358 683internal_count del_count 684677 356 685internal_count del_count 686676 354 687internal_count del_count 688675 352 689internal_count del_count 690674 350 691internal_count del_count 692673 348 693internal_count del_count 694672 346 695internal_count del_count 696671 344 697internal_count del_count 698670 342 699internal_count del_count 700669 340 701internal_count del_count 702668 338 703internal_count del_count 704667 336 705internal_count del_count 706666 334 707internal_count del_count 708665 332 709internal_count del_count 710664 330 711internal_count del_count 712663 328 713internal_count del_count 714662 326 715internal_count del_count 716661 324 717internal_count del_count 718660 322 719internal_count del_count 720659 320 721internal_count del_count 722658 318 723internal_count del_count 724657 316 725internal_count del_count 726656 314 727internal_count del_count 728655 312 729internal_count del_count 730654 310 731internal_count del_count 732653 308 733internal_count del_count 734652 306 735internal_count del_count 736651 304 737internal_count del_count 738650 302 739internal_count del_count 740649 300 741internal_count del_count 742648 298 743internal_count del_count 744647 296 745internal_count del_count 746646 294 747internal_count del_count 748645 292 749internal_count del_count 750644 290 751internal_count del_count 752643 288 753internal_count del_count 754642 286 755internal_count del_count 756641 284 757internal_count del_count 758640 282 759internal_count del_count 760639 280 761internal_count del_count 762638 278 763internal_count del_count 764637 276 765internal_count del_count 766636 274 767internal_count del_count 768635 272 769internal_count del_count 770634 270 771internal_count del_count 772633 268 773internal_count del_count 774632 266 775internal_count del_count 776631 264 777internal_count del_count 778630 262 779internal_count del_count 780629 260 781internal_count del_count 782628 258 783internal_count del_count 784627 256 785internal_count del_count 786626 254 787internal_count del_count 788625 252 789internal_count del_count 790624 250 791internal_count del_count 792623 248 793internal_count del_count 794622 246 795internal_count del_count 796621 244 797internal_count del_count 798620 242 799internal_count del_count 800619 240 801internal_count del_count 802618 238 803internal_count del_count 804617 236 805internal_count del_count 806616 234 807internal_count del_count 808615 232 809internal_count del_count 810614 230 811internal_count del_count 812613 228 813internal_count del_count 814612 226 815internal_count del_count 816611 224 817internal_count del_count 818610 222 819internal_count del_count 820609 220 821internal_count del_count 822608 218 823internal_count del_count 824607 216 825internal_count del_count 826606 214 827internal_count del_count 828605 212 829internal_count del_count 830604 210 831internal_count del_count 832603 208 833internal_count del_count 834602 206 835internal_count del_count 836601 204 837internal_count del_count 838600 202 839internal_count del_count 840599 200 841internal_count del_count 842598 198 843internal_count del_count 844597 196 845internal_count del_count 846596 194 847internal_count del_count 848595 192 849internal_count del_count 850594 190 851internal_count del_count 852593 188 853internal_count del_count 854592 186 855internal_count del_count 856591 184 857internal_count del_count 858590 182 859internal_count del_count 860589 180 861internal_count del_count 862588 178 863internal_count del_count 864587 176 865internal_count del_count 866586 174 867internal_count del_count 868585 172 869internal_count del_count 870584 170 871internal_count del_count 872583 168 873internal_count del_count 874582 166 875internal_count del_count 876581 164 877internal_count del_count 878580 162 879internal_count del_count 880579 160 881internal_count del_count 882578 158 883internal_count del_count 884577 156 885internal_count del_count 886576 154 887internal_count del_count 888575 152 889internal_count del_count 890574 150 891internal_count del_count 892573 148 893internal_count del_count 894572 146 895internal_count del_count 896571 144 897internal_count del_count 898570 142 899internal_count del_count 900569 140 901internal_count del_count 902568 138 903internal_count del_count 904567 136 905internal_count del_count 906566 134 907internal_count del_count 908565 132 909internal_count del_count 910564 130 911internal_count del_count 912563 128 913internal_count del_count 914562 126 915internal_count del_count 916561 124 917internal_count del_count 918560 122 919internal_count del_count 920559 120 921internal_count del_count 922558 118 923internal_count del_count 924557 116 925internal_count del_count 926556 114 927internal_count del_count 928555 112 929internal_count del_count 930554 110 931internal_count del_count 932553 108 933internal_count del_count 934552 106 935internal_count del_count 936551 104 937internal_count del_count 938550 102 939internal_count del_count 940549 100 941internal_count del_count 942548 98 943internal_count del_count 944547 96 945internal_count del_count 946546 94 947internal_count del_count 948545 92 949internal_count del_count 950544 90 951internal_count del_count 952543 88 953internal_count del_count 954542 86 955internal_count del_count 956541 84 957internal_count del_count 958540 82 959internal_count del_count 960539 80 961internal_count del_count 962538 78 963internal_count del_count 964537 76 965internal_count del_count 966536 74 967internal_count del_count 968535 72 969internal_count del_count 970534 70 971internal_count del_count 972533 68 973internal_count del_count 974532 66 975internal_count del_count 976531 64 977internal_count del_count 978530 62 979internal_count del_count 980529 60 981internal_count del_count 982528 58 983internal_count del_count 984527 56 985internal_count del_count 986526 54 987internal_count del_count 988525 52 989internal_count del_count 990524 50 991internal_count del_count 992523 48 993internal_count del_count 994522 46 995internal_count del_count 996521 44 997internal_count del_count 998520 42 999internal_count del_count 1000519 40 1001internal_count del_count 1002518 38 1003internal_count del_count 1004517 36 1005internal_count del_count 1006516 34 1007internal_count del_count 1008515 32 1009internal_count del_count 1010514 30 1011internal_count del_count 1012513 28 1013internal_count del_count 1014512 26 1015internal_count del_count 1016511 24 1017internal_count del_count 1018510 22 1019internal_count del_count 1020509 20 1021internal_count del_count 1022508 18 1023internal_count del_count 1024507 16 1025internal_count del_count 1026506 14 1027internal_count del_count 1028505 12 1029internal_count del_count 1030504 10 1031internal_count del_count 1032503 8 1033internal_count del_count 1034502 6 1035internal_count del_count 1036501 4 1037internal_count del_count 1038500 2 1039select count(*) as Part from test.part_tbl; 1040Part 1041500 1042DROP PROCEDURE test.proc_part; 1043DROP TABLE test.part_tbl; 1044