use test; CREATE TABLE test.part_tbl(id MEDIUMINT NOT NULL AUTO_INCREMENT, dt TIMESTAMP, user CHAR(255), uuidf LONGBLOB, fkid MEDIUMINT, filler VARCHAR(255), PRIMARY KEY(id)) ENGINE='innodb' PARTITION BY RANGE(id) SUBPARTITION BY hash(id) subpartitions 2 (PARTITION pa3 values less than (42), PARTITION pa6 values less than (60), PARTITION pa7 values less than (70), PARTITION pa8 values less than (80), PARTITION pa9 values less than (90), PARTITION pa10 values less than (100), PARTITION pa11 values less than MAXVALUE); CREATE PROCEDURE test.proc_part() BEGIN DECLARE ins_count INT DEFAULT 1000; DECLARE del_count INT; DECLARE cur_user VARCHAR(255); DECLARE local_uuid VARCHAR(255); DECLARE local_time TIMESTAMP; SET local_time= NOW(); SET cur_user= CURRENT_USER(); SET local_uuid= UUID(); WHILE ins_count > 0 DO INSERT INTO test.part_tbl VALUES (NULL, NOW(), USER() , UUID(), ins_count,'Going to test MBR for MySQL'); SET ins_count = ins_count - 1; END WHILE; SELECT MAX(id) FROM test.part_tbl INTO del_count; WHILE del_count > 0 DO DELETE FROM test.part_tbl WHERE id = del_count; select count(*) as internal_count, del_count -- these two lines are for FROM test.part_tbl; -- debug to show the problem SET del_count = del_count - 2; END WHILE; END| CALL test.proc_part(); internal_count del_count 999 1000 internal_count del_count 998 998 internal_count del_count 997 996 internal_count del_count 996 994 internal_count del_count 995 992 internal_count del_count 994 990 internal_count del_count 993 988 internal_count del_count 992 986 internal_count del_count 991 984 internal_count del_count 990 982 internal_count del_count 989 980 internal_count del_count 988 978 internal_count del_count 987 976 internal_count del_count 986 974 internal_count del_count 985 972 internal_count del_count 984 970 internal_count del_count 983 968 internal_count del_count 982 966 internal_count del_count 981 964 internal_count del_count 980 962 internal_count del_count 979 960 internal_count del_count 978 958 internal_count del_count 977 956 internal_count del_count 976 954 internal_count del_count 975 952 internal_count del_count 974 950 internal_count del_count 973 948 internal_count del_count 972 946 internal_count del_count 971 944 internal_count del_count 970 942 internal_count del_count 969 940 internal_count del_count 968 938 internal_count del_count 967 936 internal_count del_count 966 934 internal_count del_count 965 932 internal_count del_count 964 930 internal_count del_count 963 928 internal_count del_count 962 926 internal_count del_count 961 924 internal_count del_count 960 922 internal_count del_count 959 920 internal_count del_count 958 918 internal_count del_count 957 916 internal_count del_count 956 914 internal_count del_count 955 912 internal_count del_count 954 910 internal_count del_count 953 908 internal_count del_count 952 906 internal_count del_count 951 904 internal_count del_count 950 902 internal_count del_count 949 900 internal_count del_count 948 898 internal_count del_count 947 896 internal_count del_count 946 894 internal_count del_count 945 892 internal_count del_count 944 890 internal_count del_count 943 888 internal_count del_count 942 886 internal_count del_count 941 884 internal_count del_count 940 882 internal_count del_count 939 880 internal_count del_count 938 878 internal_count del_count 937 876 internal_count del_count 936 874 internal_count del_count 935 872 internal_count del_count 934 870 internal_count del_count 933 868 internal_count del_count 932 866 internal_count del_count 931 864 internal_count del_count 930 862 internal_count del_count 929 860 internal_count del_count 928 858 internal_count del_count 927 856 internal_count del_count 926 854 internal_count del_count 925 852 internal_count del_count 924 850 internal_count del_count 923 848 internal_count del_count 922 846 internal_count del_count 921 844 internal_count del_count 920 842 internal_count del_count 919 840 internal_count del_count 918 838 internal_count del_count 917 836 internal_count del_count 916 834 internal_count del_count 915 832 internal_count del_count 914 830 internal_count del_count 913 828 internal_count del_count 912 826 internal_count del_count 911 824 internal_count del_count 910 822 internal_count del_count 909 820 internal_count del_count 908 818 internal_count del_count 907 816 internal_count del_count 906 814 internal_count del_count 905 812 internal_count del_count 904 810 internal_count del_count 903 808 internal_count del_count 902 806 internal_count del_count 901 804 internal_count del_count 900 802 internal_count del_count 899 800 internal_count del_count 898 798 internal_count del_count 897 796 internal_count del_count 896 794 internal_count del_count 895 792 internal_count del_count 894 790 internal_count del_count 893 788 internal_count del_count 892 786 internal_count del_count 891 784 internal_count del_count 890 782 internal_count del_count 889 780 internal_count del_count 888 778 internal_count del_count 887 776 internal_count del_count 886 774 internal_count del_count 885 772 internal_count del_count 884 770 internal_count del_count 883 768 internal_count del_count 882 766 internal_count del_count 881 764 internal_count del_count 880 762 internal_count del_count 879 760 internal_count del_count 878 758 internal_count del_count 877 756 internal_count del_count 876 754 internal_count del_count 875 752 internal_count del_count 874 750 internal_count del_count 873 748 internal_count del_count 872 746 internal_count del_count 871 744 internal_count del_count 870 742 internal_count del_count 869 740 internal_count del_count 868 738 internal_count del_count 867 736 internal_count del_count 866 734 internal_count del_count 865 732 internal_count del_count 864 730 internal_count del_count 863 728 internal_count del_count 862 726 internal_count del_count 861 724 internal_count del_count 860 722 internal_count del_count 859 720 internal_count del_count 858 718 internal_count del_count 857 716 internal_count del_count 856 714 internal_count del_count 855 712 internal_count del_count 854 710 internal_count del_count 853 708 internal_count del_count 852 706 internal_count del_count 851 704 internal_count del_count 850 702 internal_count del_count 849 700 internal_count del_count 848 698 internal_count del_count 847 696 internal_count del_count 846 694 internal_count del_count 845 692 internal_count del_count 844 690 internal_count del_count 843 688 internal_count del_count 842 686 internal_count del_count 841 684 internal_count del_count 840 682 internal_count del_count 839 680 internal_count del_count 838 678 internal_count del_count 837 676 internal_count del_count 836 674 internal_count del_count 835 672 internal_count del_count 834 670 internal_count del_count 833 668 internal_count del_count 832 666 internal_count del_count 831 664 internal_count del_count 830 662 internal_count del_count 829 660 internal_count del_count 828 658 internal_count del_count 827 656 internal_count del_count 826 654 internal_count del_count 825 652 internal_count del_count 824 650 internal_count del_count 823 648 internal_count del_count 822 646 internal_count del_count 821 644 internal_count del_count 820 642 internal_count del_count 819 640 internal_count del_count 818 638 internal_count del_count 817 636 internal_count del_count 816 634 internal_count del_count 815 632 internal_count del_count 814 630 internal_count del_count 813 628 internal_count del_count 812 626 internal_count del_count 811 624 internal_count del_count 810 622 internal_count del_count 809 620 internal_count del_count 808 618 internal_count del_count 807 616 internal_count del_count 806 614 internal_count del_count 805 612 internal_count del_count 804 610 internal_count del_count 803 608 internal_count del_count 802 606 internal_count del_count 801 604 internal_count del_count 800 602 internal_count del_count 799 600 internal_count del_count 798 598 internal_count del_count 797 596 internal_count del_count 796 594 internal_count del_count 795 592 internal_count del_count 794 590 internal_count del_count 793 588 internal_count del_count 792 586 internal_count del_count 791 584 internal_count del_count 790 582 internal_count del_count 789 580 internal_count del_count 788 578 internal_count del_count 787 576 internal_count del_count 786 574 internal_count del_count 785 572 internal_count del_count 784 570 internal_count del_count 783 568 internal_count del_count 782 566 internal_count del_count 781 564 internal_count del_count 780 562 internal_count del_count 779 560 internal_count del_count 778 558 internal_count del_count 777 556 internal_count del_count 776 554 internal_count del_count 775 552 internal_count del_count 774 550 internal_count del_count 773 548 internal_count del_count 772 546 internal_count del_count 771 544 internal_count del_count 770 542 internal_count del_count 769 540 internal_count del_count 768 538 internal_count del_count 767 536 internal_count del_count 766 534 internal_count del_count 765 532 internal_count del_count 764 530 internal_count del_count 763 528 internal_count del_count 762 526 internal_count del_count 761 524 internal_count del_count 760 522 internal_count del_count 759 520 internal_count del_count 758 518 internal_count del_count 757 516 internal_count del_count 756 514 internal_count del_count 755 512 internal_count del_count 754 510 internal_count del_count 753 508 internal_count del_count 752 506 internal_count del_count 751 504 internal_count del_count 750 502 internal_count del_count 749 500 internal_count del_count 748 498 internal_count del_count 747 496 internal_count del_count 746 494 internal_count del_count 745 492 internal_count del_count 744 490 internal_count del_count 743 488 internal_count del_count 742 486 internal_count del_count 741 484 internal_count del_count 740 482 internal_count del_count 739 480 internal_count del_count 738 478 internal_count del_count 737 476 internal_count del_count 736 474 internal_count del_count 735 472 internal_count del_count 734 470 internal_count del_count 733 468 internal_count del_count 732 466 internal_count del_count 731 464 internal_count del_count 730 462 internal_count del_count 729 460 internal_count del_count 728 458 internal_count del_count 727 456 internal_count del_count 726 454 internal_count del_count 725 452 internal_count del_count 724 450 internal_count del_count 723 448 internal_count del_count 722 446 internal_count del_count 721 444 internal_count del_count 720 442 internal_count del_count 719 440 internal_count del_count 718 438 internal_count del_count 717 436 internal_count del_count 716 434 internal_count del_count 715 432 internal_count del_count 714 430 internal_count del_count 713 428 internal_count del_count 712 426 internal_count del_count 711 424 internal_count del_count 710 422 internal_count del_count 709 420 internal_count del_count 708 418 internal_count del_count 707 416 internal_count del_count 706 414 internal_count del_count 705 412 internal_count del_count 704 410 internal_count del_count 703 408 internal_count del_count 702 406 internal_count del_count 701 404 internal_count del_count 700 402 internal_count del_count 699 400 internal_count del_count 698 398 internal_count del_count 697 396 internal_count del_count 696 394 internal_count del_count 695 392 internal_count del_count 694 390 internal_count del_count 693 388 internal_count del_count 692 386 internal_count del_count 691 384 internal_count del_count 690 382 internal_count del_count 689 380 internal_count del_count 688 378 internal_count del_count 687 376 internal_count del_count 686 374 internal_count del_count 685 372 internal_count del_count 684 370 internal_count del_count 683 368 internal_count del_count 682 366 internal_count del_count 681 364 internal_count del_count 680 362 internal_count del_count 679 360 internal_count del_count 678 358 internal_count del_count 677 356 internal_count del_count 676 354 internal_count del_count 675 352 internal_count del_count 674 350 internal_count del_count 673 348 internal_count del_count 672 346 internal_count del_count 671 344 internal_count del_count 670 342 internal_count del_count 669 340 internal_count del_count 668 338 internal_count del_count 667 336 internal_count del_count 666 334 internal_count del_count 665 332 internal_count del_count 664 330 internal_count del_count 663 328 internal_count del_count 662 326 internal_count del_count 661 324 internal_count del_count 660 322 internal_count del_count 659 320 internal_count del_count 658 318 internal_count del_count 657 316 internal_count del_count 656 314 internal_count del_count 655 312 internal_count del_count 654 310 internal_count del_count 653 308 internal_count del_count 652 306 internal_count del_count 651 304 internal_count del_count 650 302 internal_count del_count 649 300 internal_count del_count 648 298 internal_count del_count 647 296 internal_count del_count 646 294 internal_count del_count 645 292 internal_count del_count 644 290 internal_count del_count 643 288 internal_count del_count 642 286 internal_count del_count 641 284 internal_count del_count 640 282 internal_count del_count 639 280 internal_count del_count 638 278 internal_count del_count 637 276 internal_count del_count 636 274 internal_count del_count 635 272 internal_count del_count 634 270 internal_count del_count 633 268 internal_count del_count 632 266 internal_count del_count 631 264 internal_count del_count 630 262 internal_count del_count 629 260 internal_count del_count 628 258 internal_count del_count 627 256 internal_count del_count 626 254 internal_count del_count 625 252 internal_count del_count 624 250 internal_count del_count 623 248 internal_count del_count 622 246 internal_count del_count 621 244 internal_count del_count 620 242 internal_count del_count 619 240 internal_count del_count 618 238 internal_count del_count 617 236 internal_count del_count 616 234 internal_count del_count 615 232 internal_count del_count 614 230 internal_count del_count 613 228 internal_count del_count 612 226 internal_count del_count 611 224 internal_count del_count 610 222 internal_count del_count 609 220 internal_count del_count 608 218 internal_count del_count 607 216 internal_count del_count 606 214 internal_count del_count 605 212 internal_count del_count 604 210 internal_count del_count 603 208 internal_count del_count 602 206 internal_count del_count 601 204 internal_count del_count 600 202 internal_count del_count 599 200 internal_count del_count 598 198 internal_count del_count 597 196 internal_count del_count 596 194 internal_count del_count 595 192 internal_count del_count 594 190 internal_count del_count 593 188 internal_count del_count 592 186 internal_count del_count 591 184 internal_count del_count 590 182 internal_count del_count 589 180 internal_count del_count 588 178 internal_count del_count 587 176 internal_count del_count 586 174 internal_count del_count 585 172 internal_count del_count 584 170 internal_count del_count 583 168 internal_count del_count 582 166 internal_count del_count 581 164 internal_count del_count 580 162 internal_count del_count 579 160 internal_count del_count 578 158 internal_count del_count 577 156 internal_count del_count 576 154 internal_count del_count 575 152 internal_count del_count 574 150 internal_count del_count 573 148 internal_count del_count 572 146 internal_count del_count 571 144 internal_count del_count 570 142 internal_count del_count 569 140 internal_count del_count 568 138 internal_count del_count 567 136 internal_count del_count 566 134 internal_count del_count 565 132 internal_count del_count 564 130 internal_count del_count 563 128 internal_count del_count 562 126 internal_count del_count 561 124 internal_count del_count 560 122 internal_count del_count 559 120 internal_count del_count 558 118 internal_count del_count 557 116 internal_count del_count 556 114 internal_count del_count 555 112 internal_count del_count 554 110 internal_count del_count 553 108 internal_count del_count 552 106 internal_count del_count 551 104 internal_count del_count 550 102 internal_count del_count 549 100 internal_count del_count 548 98 internal_count del_count 547 96 internal_count del_count 546 94 internal_count del_count 545 92 internal_count del_count 544 90 internal_count del_count 543 88 internal_count del_count 542 86 internal_count del_count 541 84 internal_count del_count 540 82 internal_count del_count 539 80 internal_count del_count 538 78 internal_count del_count 537 76 internal_count del_count 536 74 internal_count del_count 535 72 internal_count del_count 534 70 internal_count del_count 533 68 internal_count del_count 532 66 internal_count del_count 531 64 internal_count del_count 530 62 internal_count del_count 529 60 internal_count del_count 528 58 internal_count del_count 527 56 internal_count del_count 526 54 internal_count del_count 525 52 internal_count del_count 524 50 internal_count del_count 523 48 internal_count del_count 522 46 internal_count del_count 521 44 internal_count del_count 520 42 internal_count del_count 519 40 internal_count del_count 518 38 internal_count del_count 517 36 internal_count del_count 516 34 internal_count del_count 515 32 internal_count del_count 514 30 internal_count del_count 513 28 internal_count del_count 512 26 internal_count del_count 511 24 internal_count del_count 510 22 internal_count del_count 509 20 internal_count del_count 508 18 internal_count del_count 507 16 internal_count del_count 506 14 internal_count del_count 505 12 internal_count del_count 504 10 internal_count del_count 503 8 internal_count del_count 502 6 internal_count del_count 501 4 internal_count del_count 500 2 select count(*) as Part from test.part_tbl; Part 500 DROP PROCEDURE test.proc_part; DROP TABLE test.part_tbl;