1DROP TABLE IF EXISTS t1; 2# test without partitioning for reference 3CREATE TABLE t1 ( 4c1 INT NOT NULL AUTO_INCREMENT, 5PRIMARY KEY (c1)) 6ENGINE='Memory'; 7SHOW CREATE TABLE t1; 8Table Create Table 9t1 CREATE TABLE `t1` ( 10 `c1` int(11) NOT NULL AUTO_INCREMENT, 11 PRIMARY KEY (`c1`) 12) ENGINE=MEMORY DEFAULT CHARSET=latin1 13SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 14AND TABLE_NAME='t1'; 15AUTO_INCREMENT 161 17INSERT INTO t1 VALUES (2); 18INSERT INTO t1 VALUES (4); 19INSERT INTO t1 VALUES (NULL); 20SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 21AND TABLE_NAME='t1'; 22AUTO_INCREMENT 236 24SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 25AND TABLE_NAME='t1'; 26AUTO_INCREMENT 276 28INSERT INTO t1 VALUES (0); 29INSERT INTO t1 VALUES (5), (16); 30INSERT INTO t1 VALUES (17); 31INSERT INTO t1 VALUES (19), (NULL); 32INSERT INTO t1 VALUES (NULL), (10), (NULL); 33INSERT INTO t1 VALUES (NULL); 34SET INSERT_ID = 30; 35INSERT INTO t1 VALUES (NULL); 36SET INSERT_ID = 29; 37INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 38INSERT INTO t1 VALUES (NULL); 39UPDATE t1 SET c1 = 50 WHERE c1 = 17; 40UPDATE t1 SET c1 = 51 WHERE c1 = 19; 41FLUSH TABLES; 42UPDATE t1 SET c1 = 40 WHERE c1 = 50; 43UPDATE t1 SET c1 = -1 WHERE c1 = 40; 44SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 45 AND TABLE_NAME='t1'; 46AUTO_INCREMENT 4752 48UPDATE IGNORE t1 SET c1 = NULL WHERE c1 = 4; 49Warnings: 50Warning 1048 Column 'c1' cannot be null 51INSERT INTO t1 VALUES (NULL); 52INSERT INTO t1 VALUES (NULL); 53SELECT * FROM t1 ORDER BY c1; 54c1 55-1 560 572 585 596 6010 6120 6221 6322 6423 6529 6630 6731 6851 6952 7053 71DROP TABLE t1; 72CREATE TABLE t1 ( 73c1 INT NOT NULL AUTO_INCREMENT, 74PRIMARY KEY (c1)) 75ENGINE='Memory'; 76SHOW CREATE TABLE t1; 77Table Create Table 78t1 CREATE TABLE `t1` ( 79 `c1` int(11) NOT NULL AUTO_INCREMENT, 80 PRIMARY KEY (`c1`) 81) ENGINE=MEMORY DEFAULT CHARSET=latin1 82FLUSH TABLE; 83SHOW CREATE TABLE t1; 84Table Create Table 85t1 CREATE TABLE `t1` ( 86 `c1` int(11) NOT NULL AUTO_INCREMENT, 87 PRIMARY KEY (`c1`) 88) ENGINE=MEMORY DEFAULT CHARSET=latin1 89INSERT INTO t1 VALUES (4); 90FLUSH TABLE; 91SHOW CREATE TABLE t1; 92Table Create Table 93t1 CREATE TABLE `t1` ( 94 `c1` int(11) NOT NULL AUTO_INCREMENT, 95 PRIMARY KEY (`c1`) 96) ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 97INSERT INTO t1 VALUES (NULL); 98FLUSH TABLE; 99SHOW CREATE TABLE t1; 100Table Create Table 101t1 CREATE TABLE `t1` ( 102 `c1` int(11) NOT NULL AUTO_INCREMENT, 103 PRIMARY KEY (`c1`) 104) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 105DELETE FROM t1; 106INSERT INTO t1 VALUES (NULL); 107INSERT INTO t1 VALUES (-1); 108INSERT INTO t1 VALUES (NULL); 109INSERT INTO t1 VALUES (NULL); 110SHOW CREATE TABLE t1; 111Table Create Table 112t1 CREATE TABLE `t1` ( 113 `c1` int(11) NOT NULL AUTO_INCREMENT, 114 PRIMARY KEY (`c1`) 115) ENGINE=MEMORY AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 116SELECT * FROM t1 ORDER BY c1; 117c1 118-1 1196 1207 1218 122TRUNCATE TABLE t1; 123INSERT INTO t1 VALUES (NULL); 124SHOW CREATE TABLE t1; 125Table Create Table 126t1 CREATE TABLE `t1` ( 127 `c1` int(11) NOT NULL AUTO_INCREMENT, 128 PRIMARY KEY (`c1`) 129) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 130SELECT * FROM t1 ORDER BY c1; 131c1 1321 133INSERT INTO t1 VALUES (100); 134INSERT INTO t1 VALUES (NULL); 135DELETE FROM t1 WHERE c1 >= 100; 136OPTIMIZE TABLE t1; 137Table Op Msg_type Msg_text 138test.t1 optimize note The storage engine for the table doesn't support optimize 139SHOW CREATE TABLE t1; 140Table Create Table 141t1 CREATE TABLE `t1` ( 142 `c1` int(11) NOT NULL AUTO_INCREMENT, 143 PRIMARY KEY (`c1`) 144) ENGINE=MEMORY AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 145DROP TABLE t1; 146CREATE TABLE t1 147(a INT NULL AUTO_INCREMENT, 148UNIQUE KEY (a)) 149ENGINE='Memory'; 150SET LAST_INSERT_ID = 999; 151SET INSERT_ID = 0; 152INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 153SELECT LAST_INSERT_ID(); 154LAST_INSERT_ID() 155999 156SELECT * FROM t1; 157a 1581 159INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 160SELECT LAST_INSERT_ID(); 161LAST_INSERT_ID() 162999 163SELECT * FROM t1; 164a 1650 166UPDATE t1 SET a = 1 WHERE a IS NULL; 167SELECT LAST_INSERT_ID(); 168LAST_INSERT_ID() 169999 170SELECT * FROM t1; 171a 1720 173UPDATE t1 SET a = NULL WHERE a = 1; 174SELECT LAST_INSERT_ID(); 175LAST_INSERT_ID() 176999 177SELECT * FROM t1; 178a 1790 180DROP TABLE t1; 181SET INSERT_ID = 1; 182# Simple test with NULL 183CREATE TABLE t1 ( 184c1 INT NOT NULL AUTO_INCREMENT, 185PRIMARY KEY (c1)) 186ENGINE='Memory' 187PARTITION BY HASH(c1) 188PARTITIONS 2; 189Warnings: 190Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 191INSERT INTO t1 VALUES (NULL); 192Warnings: 193Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 194SHOW CREATE TABLE t1; 195Table Create Table 196t1 CREATE TABLE `t1` ( 197 `c1` int(11) NOT NULL AUTO_INCREMENT, 198 PRIMARY KEY (`c1`) 199) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 200/*!50100 PARTITION BY HASH (c1) 201PARTITIONS 2 */ 202Warnings: 203Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 204SELECT * FROM t1; 205c1 2061 207DROP TABLE t1; 208# Test with sql_mode and first insert as 0 209CREATE TABLE t1 ( 210c1 INT, 211c2 INT NOT NULL AUTO_INCREMENT, 212PRIMARY KEY (c2)) 213ENGINE='Memory' 214PARTITION BY HASH(c2) 215PARTITIONS 2; 216Warnings: 217Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 218INSERT INTO t1 VALUES (1, NULL); 219Warnings: 220Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 221INSERT INTO t1 VALUES (1, 1), (99, 99); 222INSERT INTO t1 VALUES (1, NULL); 223SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; 224Warnings: 225Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 226INSERT INTO t1 VALUES (1, 0); 227SELECT * FROM t1 ORDER BY c1, c2; 228c1 c2 2291 0 2301 1 2311 2 232DROP TABLE t1; 233CREATE TABLE t1 ( 234c1 INT, 235c2 INT NOT NULL AUTO_INCREMENT, 236PRIMARY KEY (c2)) 237ENGINE='Memory' 238PARTITION BY HASH(c2) 239PARTITIONS 2; 240Warnings: 241Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 242INSERT INTO t1 VALUES (1, 0); 243Warnings: 244Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 245INSERT INTO t1 VALUES (1, 1), (1, NULL); 246INSERT INTO t1 VALUES (2, NULL), (4, 7); 247INSERT INTO t1 VALUES (1, NULL); 248SELECT * FROM t1 ORDER BY c1, c2; 249c1 c2 2501 0 2511 1 2521 2 2531 8 2542 3 2554 7 256SET @@session.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 257Warnings: 258Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 259DROP TABLE t1; 260# Simple test with NULL, 0 and explicit values both incr. and desc. 261CREATE TABLE t1 ( 262c1 INT NOT NULL AUTO_INCREMENT, 263PRIMARY KEY (c1)) 264ENGINE='Memory' 265PARTITION BY HASH(c1) 266PARTITIONS 2; 267Warnings: 268Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 269INSERT INTO t1 VALUES (2), (4), (NULL); 270Warnings: 271Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 272INSERT INTO t1 VALUES (0); 273INSERT INTO t1 VALUES (5), (16); 274INSERT INTO t1 VALUES (17), (19), (NULL); 275INSERT INTO t1 VALUES (NULL), (10), (NULL); 276INSERT INTO t1 VALUES (NULL), (9); 277INSERT INTO t1 VALUES (59), (55); 278INSERT INTO t1 VALUES (NULL), (90); 279INSERT INTO t1 VALUES (NULL); 280UPDATE t1 SET c1 = 150 WHERE c1 = 17; 281UPDATE t1 SET c1 = 151 WHERE c1 = 19; 282FLUSH TABLES; 283UPDATE t1 SET c1 = 140 WHERE c1 = 150; 284Warnings: 285Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 286SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 287 AND TABLE_NAME='t1'; 288AUTO_INCREMENT 289152 290Warnings: 291Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 292UPDATE IGNORE t1 SET c1 = NULL WHERE c1 = 4; 293Warnings: 294Warning 1048 Column 'c1' cannot be null 295INSERT INTO t1 VALUES (NULL); 296INSERT INTO t1 VALUES (NULL); 297SELECT * FROM t1 ORDER BY c1; 298c1 2990 3002 3015 3026 3039 30410 30520 30621 30722 30823 30955 31059 31160 31290 31391 314140 315151 316152 317153 318DROP TABLE t1; 319# Test with auto_increment_increment and auto_increment_offset. 320CREATE TABLE t1 ( 321c1 INT NOT NULL AUTO_INCREMENT, 322PRIMARY KEY (c1)) 323ENGINE='Memory' 324PARTITION BY HASH(c1) 325PARTITIONS 2; 326Warnings: 327Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 328SET @@session.auto_increment_increment = 10; 329SET @@session.auto_increment_offset = 5; 330INSERT INTO t1 VALUES (1); 331Warnings: 332Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 333INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 334SET @@session.auto_increment_increment = 5; 335SET @@session.auto_increment_offset = 3; 336INSERT INTO t1 VALUES (NULL); 337INSERT INTO t1 VALUES (33 + 1); 338INSERT INTO t1 VALUES (NULL); 339INSERT INTO t1 VALUES (38 + 2); 340INSERT INTO t1 VALUES (NULL); 341INSERT INTO t1 VALUES (43 + 3); 342INSERT INTO t1 VALUES (NULL); 343INSERT INTO t1 VALUES (48 + 4); 344INSERT INTO t1 VALUES (NULL); 345INSERT INTO t1 VALUES (53 + 5); 346INSERT INTO t1 VALUES (NULL); 347INSERT INTO t1 VALUES (63 + 6); 348INSERT INTO t1 VALUES (NULL); 349SET @@session.auto_increment_increment = 1; 350SET @@session.auto_increment_offset = 1; 351SELECT * FROM t1 ORDER BY c1; 352c1 3531 3545 35515 35625 35733 35834 35938 36040 36143 36246 36348 36452 36553 36658 36763 36869 36973 370DROP TABLE t1; 371# Test reported auto_increment value 372CREATE TABLE t1 ( 373c1 INT NOT NULL AUTO_INCREMENT, 374PRIMARY KEY (c1)) 375ENGINE='Memory' 376PARTITION BY HASH (c1) 377PARTITIONS 2; 378Warnings: 379Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 380SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 381AND TABLE_NAME='t1'; 382AUTO_INCREMENT 3831 384Warnings: 385Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 386INSERT INTO t1 VALUES (2); 387SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 388AND TABLE_NAME='t1'; 389AUTO_INCREMENT 3903 391Warnings: 392Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 393INSERT INTO t1 VALUES (4); 394INSERT INTO t1 VALUES (NULL); 395SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 396AND TABLE_NAME='t1'; 397AUTO_INCREMENT 3986 399Warnings: 400Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 401INSERT INTO t1 VALUES (NULL); 402INSERT INTO t1 VALUES (17); 403INSERT INTO t1 VALUES (19); 404INSERT INTO t1 VALUES (NULL); 405INSERT INTO t1 VALUES (NULL); 406SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 407AND TABLE_NAME='t1'; 408AUTO_INCREMENT 40922 410Warnings: 411Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 412SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 413AND TABLE_NAME='t1'; 414AUTO_INCREMENT 41522 416Warnings: 417Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 418INSERT INTO t1 VALUES (10); 419SELECT * FROM t1 ORDER BY c1; 420c1 4212 4224 4235 4246 42510 42617 42719 42820 42921 430INSERT INTO t1 VALUES (NULL); 431SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 432AND TABLE_NAME='t1'; 433AUTO_INCREMENT 43423 435Warnings: 436Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 437INSERT INTO t1 VALUES (NULL); 438INSERT INTO t1 VALUES (15); 439INSERT INTO t1 VALUES (NULL); 440SELECT * FROM t1 ORDER BY c1; 441c1 4422 4434 4445 4456 44610 44715 44817 44919 45020 45121 45222 45323 45424 455INSERT INTO t1 VALUES (NULL); 456DELETE FROM t1; 457INSERT INTO t1 VALUES (NULL); 458SHOW CREATE TABLE t1; 459Table Create Table 460t1 CREATE TABLE `t1` ( 461 `c1` int(11) NOT NULL AUTO_INCREMENT, 462 PRIMARY KEY (`c1`) 463) ENGINE=MEMORY AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 464/*!50100 PARTITION BY HASH (c1) 465PARTITIONS 2 */ 466Warnings: 467Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 468SELECT * FROM t1 ORDER BY c1; 469c1 47026 471TRUNCATE TABLE t1; 472Warnings: 473Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 474INSERT INTO t1 VALUES (NULL); 475SHOW CREATE TABLE t1; 476Table Create Table 477t1 CREATE TABLE `t1` ( 478 `c1` int(11) NOT NULL AUTO_INCREMENT, 479 PRIMARY KEY (`c1`) 480) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 481/*!50100 PARTITION BY HASH (c1) 482PARTITIONS 2 */ 483Warnings: 484Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 485SELECT * FROM t1 ORDER BY c1; 486c1 4871 488INSERT INTO t1 VALUES (100); 489INSERT INTO t1 VALUES (NULL); 490DELETE FROM t1 WHERE c1 >= 100; 491OPTIMIZE TABLE t1; 492Table Op Msg_type Msg_text 493test.t1 optimize note The storage engine for the table doesn't support optimize 494test.t1 optimize warning The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 495SHOW CREATE TABLE t1; 496Table Create Table 497t1 CREATE TABLE `t1` ( 498 `c1` int(11) NOT NULL AUTO_INCREMENT, 499 PRIMARY KEY (`c1`) 500) ENGINE=MEMORY AUTO_INCREMENT=102 DEFAULT CHARSET=latin1 501/*!50100 PARTITION BY HASH (c1) 502PARTITIONS 2 */ 503Warnings: 504Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 505DROP TABLE t1; 506# Test with two threads 507# con default 508CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 509ENGINE = 'Memory' 510PARTITION BY HASH(c1) 511PARTITIONS 2; 512Warnings: 513Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 514INSERT INTO t1 (c1) VALUES (2); 515Warnings: 516Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 517INSERT INTO t1 (c1) VALUES (4); 518# con1 519INSERT INTO t1 (c1) VALUES (NULL); 520INSERT INTO t1 (c1) VALUES (10); 521# con default 522INSERT INTO t1 (c1) VALUES (NULL); 523INSERT INTO t1 (c1) VALUES (NULL); 524INSERT INTO t1 (c1) VALUES (19); 525INSERT INTO t1 (c1) VALUES (21); 526# con1 527INSERT INTO t1 (c1) VALUES (NULL); 528# con default 529INSERT INTO t1 (c1) VALUES (16); 530# con1 531INSERT INTO t1 (c1) VALUES (NULL); 532# con default 533INSERT INTO t1 (c1) VALUES (NULL); 534SELECT * FROM t1 ORDER BY c1; 535c1 5362 5374 5385 53910 54011 54112 54216 54319 54421 54522 54623 54724 548DROP TABLE t1; 549# Test with two threads + start transaction NO PARTITIONING 550# con default 551CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 552ENGINE = 'Memory'; 553START TRANSACTION; 554INSERT INTO t1 (c1) VALUES (2); 555INSERT INTO t1 (c1) VALUES (4); 556# con1 557START TRANSACTION; 558INSERT INTO t1 (c1) VALUES (NULL); 559INSERT INTO t1 (c1) VALUES (10); 560# con default 561INSERT INTO t1 (c1) VALUES (NULL); 562INSERT INTO t1 (c1) VALUES (NULL); 563INSERT INTO t1 (c1) VALUES (19); 564INSERT INTO t1 (c1) VALUES (21); 565# con1 566INSERT INTO t1 (c1) VALUES (NULL); 567# con default 568INSERT INTO t1 (c1) VALUES (16); 569# con1 570INSERT INTO t1 (c1) VALUES (NULL); 571SELECT * FROM t1 ORDER BY c1; 572c1 5732 5744 5755 57610 57711 57812 57916 58019 58121 58222 58323 584COMMIT; 585SELECT * FROM t1 ORDER BY c1; 586c1 5872 5884 5895 59010 59111 59212 59316 59419 59521 59622 59723 598# con default 599INSERT INTO t1 (c1) VALUES (NULL); 600SELECT * FROM t1 ORDER BY c1; 601c1 6022 6034 6045 60510 60611 60712 60816 60919 61021 61122 61223 61324 614COMMIT; 615SELECT * FROM t1 ORDER BY c1; 616c1 6172 6184 6195 62010 62111 62212 62316 62419 62521 62622 62723 62824 629DROP TABLE t1; 630# Test with two threads + start transaction 631# con default 632CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 633ENGINE = 'Memory' 634PARTITION BY HASH(c1) 635PARTITIONS 2; 636Warnings: 637Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 638START TRANSACTION; 639INSERT INTO t1 (c1) VALUES (2); 640Warnings: 641Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 642INSERT INTO t1 (c1) VALUES (4); 643# con1 644START TRANSACTION; 645INSERT INTO t1 (c1) VALUES (NULL), (10); 646# con default 647INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19); 648INSERT INTO t1 (c1) VALUES (21); 649# con1 650INSERT INTO t1 (c1) VALUES (NULL); 651# con default 652INSERT INTO t1 (c1) VALUES (16); 653# con1 654INSERT INTO t1 (c1) VALUES (NULL); 655SELECT * FROM t1 ORDER BY c1; 656c1 6572 6584 6595 66010 66111 66212 66316 66419 66521 66622 66723 668COMMIT; 669SELECT * FROM t1 ORDER BY c1; 670c1 6712 6724 6735 67410 67511 67612 67716 67819 67921 68022 68123 682# con default 683INSERT INTO t1 (c1) VALUES (NULL); 684SELECT * FROM t1 ORDER BY c1; 685c1 6862 6874 6885 68910 69011 69112 69216 69319 69421 69522 69623 69724 698COMMIT; 699SELECT * FROM t1 ORDER BY c1; 700c1 7012 7024 7035 70410 70511 70612 70716 70819 70921 71022 71123 71224 713DROP TABLE t1; 714# Test with another column after 715CREATE TABLE t1 ( 716c1 INT NOT NULL AUTO_INCREMENT, 717c2 INT, 718PRIMARY KEY (c1,c2)) 719ENGINE = 'Memory' 720PARTITION BY HASH(c2) 721PARTITIONS 2; 722Warnings: 723Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 724INSERT INTO t1 VALUES (1, 0); 725Warnings: 726Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 727INSERT INTO t1 VALUES (1, 1); 728INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3); 729INSERT INTO t1 VALUES (NULL, 3); 730INSERT INTO t1 VALUES (2, 0), (NULL, 2); 731INSERT INTO t1 VALUES (2, 2); 732INSERT INTO t1 VALUES (2, 22); 733INSERT INTO t1 VALUES (NULL, 2); 734SELECT * FROM t1 ORDER BY c1,c2; 735c1 c2 7361 0 7371 1 7382 0 7392 1 7402 2 7412 22 7423 2 7434 3 7445 3 7456 2 7467 2 747DROP TABLE t1; 748# Test with another column before 749CREATE TABLE t1 ( 750c1 INT, 751c2 INT NOT NULL AUTO_INCREMENT, 752PRIMARY KEY (c2)) 753ENGINE = 'Memory' 754PARTITION BY HASH(c2) 755PARTITIONS 2; 756Warnings: 757Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 758INSERT INTO t1 VALUES (1, 0); 759Warnings: 760Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 761INSERT INTO t1 VALUES (1, 1); 762INSERT INTO t1 VALUES (1, NULL); 763INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0); 764INSERT INTO t1 VALUES (2, NULL); 765INSERT INTO t1 VALUES (2, 2); 766INSERT INTO t1 VALUES (2, 22); 767INSERT INTO t1 VALUES (2, NULL); 768SELECT * FROM t1 ORDER BY c1,c2; 769c1 c2 7701 1 7711 2 7722 3 7732 13 7742 14 7752 22 7762 23 7773 11 7783 12 779DROP TABLE t1; 780# Test with auto_increment on secondary column in multi-column-index 781CREATE TABLE t1 ( 782c1 INT, 783c2 INT NOT NULL AUTO_INCREMENT, 784PRIMARY KEY (c1,c2)) 785ENGINE = 'Memory' 786PARTITION BY HASH(c2) 787PARTITIONS 2; 788ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key 789# Test AUTO_INCREMENT in CREATE 790CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 791ENGINE = 'Memory' 792AUTO_INCREMENT = 15 793PARTITION BY HASH(c1) 794PARTITIONS 2; 795Warnings: 796Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 797SHOW CREATE TABLE t1; 798Table Create Table 799t1 CREATE TABLE `t1` ( 800 `c1` int(11) NOT NULL AUTO_INCREMENT, 801 PRIMARY KEY (`c1`) 802) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 803/*!50100 PARTITION BY HASH (c1) 804PARTITIONS 2 */ 805Warnings: 806Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 807INSERT INTO t1 (c1) VALUES (4); 808SHOW CREATE TABLE t1; 809Table Create Table 810t1 CREATE TABLE `t1` ( 811 `c1` int(11) NOT NULL AUTO_INCREMENT, 812 PRIMARY KEY (`c1`) 813) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 814/*!50100 PARTITION BY HASH (c1) 815PARTITIONS 2 */ 816Warnings: 817Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 818INSERT INTO t1 (c1) VALUES (0); 819SHOW CREATE TABLE t1; 820Table Create Table 821t1 CREATE TABLE `t1` ( 822 `c1` int(11) NOT NULL AUTO_INCREMENT, 823 PRIMARY KEY (`c1`) 824) ENGINE=MEMORY AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 825/*!50100 PARTITION BY HASH (c1) 826PARTITIONS 2 */ 827Warnings: 828Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 829INSERT INTO t1 (c1) VALUES (NULL); 830SHOW CREATE TABLE t1; 831Table Create Table 832t1 CREATE TABLE `t1` ( 833 `c1` int(11) NOT NULL AUTO_INCREMENT, 834 PRIMARY KEY (`c1`) 835) ENGINE=MEMORY AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 836/*!50100 PARTITION BY HASH (c1) 837PARTITIONS 2 */ 838Warnings: 839Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 840SELECT * FROM t1 ORDER BY c1; 841c1 8424 84315 84416 845# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO' 846SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; 847Warnings: 848Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 849INSERT INTO t1 (c1) VALUES (300); 850SHOW CREATE TABLE t1; 851Table Create Table 852t1 CREATE TABLE `t1` ( 853 `c1` int(11) NOT NULL AUTO_INCREMENT, 854 PRIMARY KEY (`c1`) 855) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 856/*!50100 PARTITION BY HASH (c1) 857PARTITIONS 2 */ 858Warnings: 859Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 860INSERT INTO t1 (c1) VALUES (0); 861SHOW CREATE TABLE t1; 862Table Create Table 863t1 CREATE TABLE `t1` ( 864 `c1` int(11) NOT NULL AUTO_INCREMENT, 865 PRIMARY KEY (`c1`) 866) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 867/*!50100 PARTITION BY HASH (c1) 868PARTITIONS 2 */ 869Warnings: 870Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 871INSERT INTO t1 (c1) VALUES (NULL); 872SHOW CREATE TABLE t1; 873Table Create Table 874t1 CREATE TABLE `t1` ( 875 `c1` int(11) NOT NULL AUTO_INCREMENT, 876 PRIMARY KEY (`c1`) 877) ENGINE=MEMORY AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 878/*!50100 PARTITION BY HASH (c1) 879PARTITIONS 2 */ 880Warnings: 881Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 882SELECT * FROM t1 ORDER BY c1; 883c1 8840 8854 88615 88716 888300 889301 890SET @@session.sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 891Warnings: 892Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 893DROP TABLE t1; 894# Test SET INSERT_ID 895CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 896ENGINE = 'Memory' 897PARTITION BY HASH(c1) 898PARTITIONS 2; 899Warnings: 900Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 901SHOW CREATE TABLE t1; 902Table Create Table 903t1 CREATE TABLE `t1` ( 904 `c1` int(11) NOT NULL AUTO_INCREMENT, 905 PRIMARY KEY (`c1`) 906) ENGINE=MEMORY DEFAULT CHARSET=latin1 907/*!50100 PARTITION BY HASH (c1) 908PARTITIONS 2 */ 909Warnings: 910Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 911INSERT INTO t1 (c1) VALUES (NULL); 912SHOW CREATE TABLE t1; 913Table Create Table 914t1 CREATE TABLE `t1` ( 915 `c1` int(11) NOT NULL AUTO_INCREMENT, 916 PRIMARY KEY (`c1`) 917) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 918/*!50100 PARTITION BY HASH (c1) 919PARTITIONS 2 */ 920Warnings: 921Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 922SELECT * FROM t1; 923c1 9241 925SET INSERT_ID = 23; 926SHOW CREATE TABLE t1; 927Table Create Table 928t1 CREATE TABLE `t1` ( 929 `c1` int(11) NOT NULL AUTO_INCREMENT, 930 PRIMARY KEY (`c1`) 931) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 932/*!50100 PARTITION BY HASH (c1) 933PARTITIONS 2 */ 934Warnings: 935Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 936INSERT INTO t1 (c1) VALUES (NULL); 937SHOW CREATE TABLE t1; 938Table Create Table 939t1 CREATE TABLE `t1` ( 940 `c1` int(11) NOT NULL AUTO_INCREMENT, 941 PRIMARY KEY (`c1`) 942) ENGINE=MEMORY AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 943/*!50100 PARTITION BY HASH (c1) 944PARTITIONS 2 */ 945Warnings: 946Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 947SET INSERT_ID = 22; 948INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 949INSERT INTO t1 VALUES (NULL); 950SELECT * FROM t1 ORDER BY c1; 951c1 9521 95322 95423 95524 956DROP TABLE t1; 957# Testing with FLUSH TABLE 958CREATE TABLE t1 ( 959c1 INT NOT NULL AUTO_INCREMENT, 960PRIMARY KEY (c1)) 961ENGINE='Memory' 962PARTITION BY HASH(c1) 963PARTITIONS 2; 964Warnings: 965Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 966SHOW CREATE TABLE t1; 967Table Create Table 968t1 CREATE TABLE `t1` ( 969 `c1` int(11) NOT NULL AUTO_INCREMENT, 970 PRIMARY KEY (`c1`) 971) ENGINE=MEMORY DEFAULT CHARSET=latin1 972/*!50100 PARTITION BY HASH (c1) 973PARTITIONS 2 */ 974Warnings: 975Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 976FLUSH TABLE; 977SHOW CREATE TABLE t1; 978Table Create Table 979t1 CREATE TABLE `t1` ( 980 `c1` int(11) NOT NULL AUTO_INCREMENT, 981 PRIMARY KEY (`c1`) 982) ENGINE=MEMORY DEFAULT CHARSET=latin1 983/*!50100 PARTITION BY HASH (c1) 984PARTITIONS 2 */ 985Warnings: 986Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 987INSERT INTO t1 VALUES (4); 988FLUSH TABLE; 989SHOW CREATE TABLE t1; 990Table Create Table 991t1 CREATE TABLE `t1` ( 992 `c1` int(11) NOT NULL AUTO_INCREMENT, 993 PRIMARY KEY (`c1`) 994) ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 995/*!50100 PARTITION BY HASH (c1) 996PARTITIONS 2 */ 997Warnings: 998Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 999INSERT INTO t1 VALUES (NULL); 1000FLUSH TABLE; 1001SHOW CREATE TABLE t1; 1002Table Create Table 1003t1 CREATE TABLE `t1` ( 1004 `c1` int(11) NOT NULL AUTO_INCREMENT, 1005 PRIMARY KEY (`c1`) 1006) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 1007/*!50100 PARTITION BY HASH (c1) 1008PARTITIONS 2 */ 1009Warnings: 1010Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1011SELECT * FROM t1 ORDER BY c1; 1012c1 10134 10145 1015DROP TABLE t1; 1016############################################################################# 1017# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 1018# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 1019############################################################################## 1020# Inserting negative autoincrement values into a partition table (partitions >= 4) 1021CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1022c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1023Warnings: 1024Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1025INSERT INTO t(c2) VALUES (10); 1026Warnings: 1027Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1028INSERT INTO t(c2) VALUES (20); 1029INSERT INTO t VALUES (-1,-10); 1030INSERT INTO t(c2) VALUES (30); 1031INSERT INTO t(c2) VALUES (40); 1032SELECT * FROM t ORDER BY c1 ASC; 1033c1 c2 1034-1 -10 10351 10 10362 20 10373 30 10384 40 1039DROP TABLE t; 1040# Reading from a partition table (partitions >= 2 ) after inserting a negative 1041# value into the auto increment column 1042CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1043c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 2; 1044Warnings: 1045Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1046INSERT INTO t VALUES (-2,-20); 1047Warnings: 1048Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1049INSERT INTO t(c2) VALUES (30); 1050SELECT * FROM t ORDER BY c1 ASC; 1051c1 c2 1052-2 -20 10531 30 1054DROP TABLE t; 1055# Inserting negative auto increment value into a partition table (partitions >= 2) 1056# auto increment value > 2. 1057CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1058c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 2; 1059Warnings: 1060Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1061INSERT INTO t VALUES (-4,-20); 1062Warnings: 1063Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1064INSERT INTO t(c2) VALUES (30); 1065INSERT INTO t(c2) VALUES (40); 1066SELECT * FROM t ORDER BY c1 ASC; 1067c1 c2 1068-4 -20 10691 30 10702 40 1071DROP TABLE t; 1072# Inserting -1 into autoincrement column of a partition table (partition >= 4) 1073CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1074c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1075Warnings: 1076Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1077INSERT INTO t(c2) VALUES (10); 1078Warnings: 1079Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1080INSERT INTO t(c2) VALUES (20); 1081INSERT INTO t VALUES (-1,-10); 1082SELECT * FROM t ORDER BY c1 ASC; 1083c1 c2 1084-1 -10 10851 10 10862 20 1087INSERT INTO t(c2) VALUES (30); 1088SELECT * FROM t ORDER BY c1 ASC; 1089c1 c2 1090-1 -10 10911 10 10922 20 10933 30 1094DROP TABLE t; 1095# Deleting from an auto increment table after inserting negative values 1096CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1097c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1098Warnings: 1099Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1100INSERT INTO t(c2) VALUES (10); 1101Warnings: 1102Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1103INSERT INTO t(c2) VALUES (20); 1104INSERT INTO t VALUES (-1,-10); 1105INSERT INTO t(c2) VALUES (30); 1106INSERT INTO t VALUES (-3,-20); 1107INSERT INTO t(c2) VALUES (40); 1108SELECT * FROM t ORDER BY c1 ASC; 1109c1 c2 1110-3 -20 1111-1 -10 11121 10 11132 20 11143 30 11154 40 1116DELETE FROM t WHERE c1 > 1; 1117SELECT * FROM t ORDER BY c1 ASC; 1118c1 c2 1119-3 -20 1120-1 -10 11211 10 1122DROP TABLE t; 1123# Inserting a positive value that exceeds maximum allowed value for an 1124# Auto Increment column (positive maximum) 1125CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1126c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1127Warnings: 1128Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1129INSERT INTO t(c2) VALUES (10); 1130Warnings: 1131Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1132INSERT INTO t(c2) VALUES (20); 1133INSERT INTO t VALUES (126,30); 1134INSERT INTO t VALUES (127,40); 1135SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 1136Warnings: 1137Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 1138INSERT INTO t VALUES (128,50); 1139INSERT INTO t VALUES (129,60); 1140SELECT * FROM t ORDER BY c1 ASC; 1141c1 c2 11421 10 11432 20 1144126 30 1145127 40 1146DROP TABLE t; 1147# Inserting a negative value that goes below minimum allowed value for an 1148# Auto Increment column (negative minimum) 1149CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1150c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1151Warnings: 1152Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1153INSERT INTO t(c2) VALUES (10); 1154Warnings: 1155Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1156INSERT INTO t(c2) VALUES (20); 1157INSERT INTO t VALUES (-127,30); 1158INSERT INTO t VALUES (-128,40); 1159INSERT INTO t VALUES (-129,50); 1160INSERT INTO t VALUES (-130,60); 1161SELECT * FROM t ORDER BY c1 ASC; 1162c1 c2 1163-128 40 1164-127 30 11651 10 11662 20 1167DROP TABLE t; 1168# Updating the partition table with a negative Auto Increment value 1169CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1170c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1171Warnings: 1172Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1173INSERT INTO t(c2) VALUES (10); 1174Warnings: 1175Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1176INSERT INTO t(c2) VALUES (20); 1177INSERT INTO t VALUES (-1,-10); 1178INSERT INTO t(c2) VALUES (30); 1179SELECT * FROM t ORDER BY c1 ASC; 1180c1 c2 1181-1 -10 11821 10 11832 20 11843 30 1185UPDATE t SET c1 = -6 WHERE c1 = 2; 1186SELECT * FROM t ORDER BY c1 ASC; 1187c1 c2 1188-6 20 1189-1 -10 11901 10 11913 30 1192INSERT INTO t(c2) VALUES (40); 1193INSERT INTO t(c2) VALUES (50); 1194UPDATE t SET c1 = -6 WHERE c1 = 2; 1195SELECT * FROM t ORDER BY c1 ASC; 1196c1 c2 1197-6 20 1198-1 -10 11991 10 12003 30 12014 40 12025 50 1203DROP TABLE t; 1204SET sql_mode = default; 1205# Updating the partition table with a value that crosses the upper limits 1206# on both the positive and the negative side. 1207CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 1208c2 INT) ENGINE='Memory' PARTITION BY HASH(c1) PARTITIONS 4; 1209Warnings: 1210Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1211INSERT INTO t(c2) VALUES (10); 1212Warnings: 1213Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 1214INSERT INTO t(c2) VALUES (20); 1215INSERT INTO t VALUES (126,30); 1216INSERT INTO t VALUES (127,40); 1217SELECT * FROM t ORDER BY c1 ASC; 1218c1 c2 12191 10 12202 20 1221126 30 1222127 40 1223UPDATE IGNORE t SET c1 = 130 where c1 = 127; 1224Warnings: 1225Warning 1264 Out of range value for column 'c1' at row 1 1226SELECT * FROM t ORDER BY c1 ASC; 1227c1 c2 12281 10 12292 20 1230126 30 1231127 40 1232UPDATE IGNORE t SET c1 = -140 where c1 = 126; 1233Warnings: 1234Warning 1264 Out of range value for column 'c1' at row 1 1235SELECT * FROM t ORDER BY c1 ASC; 1236c1 c2 1237-128 30 12381 10 12392 20 1240127 40 1241DROP TABLE t; 1242CREATE TABLE t1 1243(a INT NULL AUTO_INCREMENT, 1244UNIQUE KEY (a)) 1245ENGINE='Memory' 1246PARTITION BY KEY(a) PARTITIONS 2; 1247Warnings: 1248Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1249SET LAST_INSERT_ID = 999; 1250INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 1251Warnings: 1252Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1253SELECT LAST_INSERT_ID(); 1254LAST_INSERT_ID() 1255999 1256SELECT * FROM t1; 1257a 12581 1259INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 1260SELECT LAST_INSERT_ID(); 1261LAST_INSERT_ID() 1262999 1263SELECT * FROM t1; 1264a 12650 1266UPDATE t1 SET a = 1 WHERE a IS NULL; 1267SELECT LAST_INSERT_ID(); 1268LAST_INSERT_ID() 1269999 1270SELECT * FROM t1; 1271a 12720 1273UPDATE t1 SET a = NULL WHERE a = 1; 1274SELECT LAST_INSERT_ID(); 1275LAST_INSERT_ID() 1276999 1277SELECT * FROM t1; 1278a 12790 1280DROP TABLE t1; 1281############################################################################## 1282# 1283# Test overflow 1284# 1285# Single row insert signed int 1286CREATE TABLE t1 1287(a int not null auto_increment primary key, b varchar(64)) 1288ENGINE = 'Memory' 1289PARTITION BY HASH (a) PARTITIONS 3; 1290Warnings: 1291Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1292INSERT INTO t1 VALUES (0x7FFFFFFD, "2"); 1293Warnings: 1294Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1295INSERT INTO t1 VALUES (NULL, "1"); 1296INSERT INTO t1 VALUES (NULL, "0"); 1297INSERT INTO t1 VALUES (NULL, "Overflow"); 1298SHOW WARNINGS; 1299Level Code Message 1300Error 1264 Out of range value for column 'a' at row 1 1301Error 1264 Out of range value for column 't1' at row 167 1302INSERT INTO t1 VALUES (5, "non-generated number"); 1303FLUSH TABLES; 1304SHOW CREATE TABLE t1; 1305Table Create Table 1306t1 CREATE TABLE `t1` ( 1307 `a` int(11) NOT NULL AUTO_INCREMENT, 1308 `b` varchar(64) DEFAULT NULL, 1309 PRIMARY KEY (`a`) 1310) ENGINE=MEMORY AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 1311/*!50100 PARTITION BY HASH (a) 1312PARTITIONS 3 */ 1313Warnings: 1314Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1315SELECT * FROM t1; 1316a b 13172147483645 2 13182147483646 1 13192147483647 0 13205 non-generated number 1321DROP TABLE t1; 1322CREATE TABLE `t1` ( 1323`a` int(11) NOT NULL AUTO_INCREMENT, 1324`b` varchar(64) DEFAULT NULL, 1325PRIMARY KEY (`a`) 1326) ENGINE='Memory' 1327AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 1328PARTITION BY HASH (a) PARTITIONS 3; 1329Warnings: 1330Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1331INSERT INTO t1 VALUES (NULL, "Overflow"); 1332# mysql_errno: 1264 1333INSERT INTO t1 VALUES (NULL, "Overflow"); 1334# mysql_errno: 1264 1335DROP TABLE t1; 1336# Multi row insert signed int 1337CREATE TABLE t1 1338(a int not null auto_increment primary key, b varchar(64)) 1339ENGINE = 'Memory' 1340PARTITION BY HASH (a) PARTITIONS 3; 1341Warnings: 1342Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1343INSERT INTO t1 VALUES (0x7FFFFFFD, "2"); 1344Warnings: 1345Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1346INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1347ERROR HY000: Failed to read auto-increment value from storage engine 1348INSERT INTO t1 VALUES (NULL, "1"); 1349# mysql_errno: 1264 1350INSERT INTO t1 VALUES (NULL, "0"); 1351# mysql_errno: 1264 1352INSERT INTO t1 VALUES (NULL, "Overflow"); 1353ERROR 22003: Out of range value for column 'a' at row 1 1354INSERT INTO t1 VALUES (5, "non-generated number"); 1355FLUSH TABLES; 1356SHOW CREATE TABLE t1; 1357Table Create Table 1358t1 CREATE TABLE `t1` ( 1359 `a` int(11) NOT NULL AUTO_INCREMENT, 1360 `b` varchar(64) DEFAULT NULL, 1361 PRIMARY KEY (`a`) 1362) ENGINE=MEMORY AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 1363/*!50100 PARTITION BY HASH (a) 1364PARTITIONS 3 */ 1365Warnings: 1366Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1367SELECT * FROM t1; 1368a b 13692147483645 2 13702147483646 1 13712147483647 0 13725 non-generated number 1373DROP TABLE t1; 1374# Single row insert unsigned int 1375CREATE TABLE t1 1376(a int unsigned not null auto_increment primary key, b varchar(64)) 1377ENGINE = 'Memory' 1378PARTITION BY HASH (a) PARTITIONS 3; 1379Warnings: 1380Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1381INSERT INTO t1 VALUES (0xFFFFFFFD, "2"); 1382Warnings: 1383Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1384INSERT INTO t1 VALUES (NULL, "1"); 1385INSERT INTO t1 VALUES (NULL, "0"); 1386INSERT INTO t1 VALUES (NULL, "Overflow"); 1387ERROR 22003: Out of range value for column 'a' at row 1 1388INSERT INTO t1 VALUES (5, "non-generated number"); 1389FLUSH TABLES; 1390SHOW CREATE TABLE t1; 1391Table Create Table 1392t1 CREATE TABLE `t1` ( 1393 `a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1394 `b` varchar(64) DEFAULT NULL, 1395 PRIMARY KEY (`a`) 1396) ENGINE=MEMORY AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1 1397/*!50100 PARTITION BY HASH (a) 1398PARTITIONS 3 */ 1399Warnings: 1400Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1401SELECT * FROM t1; 1402a b 14034294967293 2 14044294967294 1 14054294967295 0 14065 non-generated number 1407DROP TABLE t1; 1408CREATE TABLE `t1` ( 1409`a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1410`b` varchar(64) DEFAULT NULL, 1411PRIMARY KEY (`a`) 1412) ENGINE='Memory' AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1 1413PARTITION BY HASH (a) PARTITIONS 3; 1414Warnings: 1415Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1416INSERT INTO t1 VALUES (NULL, "Overflow"); 1417# mysql_errno: 1264 1418DROP TABLE t1; 1419# Multi row insert unsigned int 1420CREATE TABLE t1 1421(a int unsigned not null auto_increment primary key, b varchar(64)) 1422ENGINE = 'Memory' 1423PARTITION BY HASH (a) PARTITIONS 3; 1424Warnings: 1425Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1426INSERT INTO t1 VALUES (0xFFFFFFFD, "2"); 1427Warnings: 1428Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1429INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1430ERROR HY000: Failed to read auto-increment value from storage engine 1431INSERT INTO t1 VALUES (NULL, "1"); 1432# mysql_errno: 1264 1433INSERT INTO t1 VALUES (NULL, "0"); 1434# mysql_errno: 1264 1435INSERT INTO t1 VALUES (NULL, "Overflow"); 1436ERROR 22003: Out of range value for column 'a' at row 1 1437INSERT INTO t1 VALUES (5, "non-generated number"); 1438FLUSH TABLES; 1439SHOW CREATE TABLE t1; 1440Table Create Table 1441t1 CREATE TABLE `t1` ( 1442 `a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1443 `b` varchar(64) DEFAULT NULL, 1444 PRIMARY KEY (`a`) 1445) ENGINE=MEMORY AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1 1446/*!50100 PARTITION BY HASH (a) 1447PARTITIONS 3 */ 1448Warnings: 1449Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1450SELECT * FROM t1; 1451a b 14524294967293 2 14534294967294 1 14544294967295 0 14555 non-generated number 1456DROP TABLE t1; 1457# Single row insert signed bigint 1458CREATE TABLE t1 1459(a bigint not null auto_increment primary key, b varchar(64)) 1460ENGINE = 'Memory' 1461PARTITION BY HASH (a) PARTITIONS 3; 1462Warnings: 1463Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1464INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, "2"); 1465Warnings: 1466Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1467INSERT INTO t1 VALUES (NULL, "1"); 1468INSERT INTO t1 VALUES (NULL, "0"); 1469INSERT INTO t1 VALUES (NULL, "Overflow"); 1470ERROR 22003: Out of range value for column 'a' at row 1 1471INSERT INTO t1 VALUES (5, "non-generated number"); 1472FLUSH TABLES; 1473SHOW CREATE TABLE t1; 1474Table Create Table 1475t1 CREATE TABLE `t1` ( 1476 `a` bigint(20) NOT NULL AUTO_INCREMENT, 1477 `b` varchar(64) DEFAULT NULL, 1478 PRIMARY KEY (`a`) 1479) ENGINE=MEMORY AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=latin1 1480/*!50100 PARTITION BY HASH (a) 1481PARTITIONS 3 */ 1482Warnings: 1483Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1484SELECT * FROM t1; 1485a b 14865 non-generated number 14879223372036854775805 2 14889223372036854775806 1 14899223372036854775807 0 1490DROP TABLE t1; 1491CREATE TABLE `t1` ( 1492`a` bigint(20) NOT NULL AUTO_INCREMENT, 1493`b` varchar(64) DEFAULT NULL, 1494PRIMARY KEY (`a`) 1495) ENGINE='Memory' AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=latin1 1496PARTITION BY HASH (a) 1497PARTITIONS 3; 1498Warnings: 1499Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1500INSERT INTO t1 VALUES (NULL, "Overflow"); 1501# mysql_errno: 1264 1502DROP TABLE t1; 1503# Multi row insert signed bigint 1504CREATE TABLE t1 1505(a bigint not null auto_increment primary key, b varchar(64)) 1506ENGINE = 'Memory' 1507PARTITION BY HASH (a) PARTITIONS 3; 1508Warnings: 1509Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1510INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, "2"); 1511Warnings: 1512Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1513INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1514ERROR HY000: Failed to read auto-increment value from storage engine 1515INSERT INTO t1 VALUES (NULL, "1"); 1516# mysql_errno: 1264 1517INSERT INTO t1 VALUES (NULL, "0"); 1518# mysql_errno: 1264 1519INSERT INTO t1 VALUES (NULL, "Overflow"); 1520ERROR 22003: Out of range value for column 'a' at row 1 1521INSERT INTO t1 VALUES (5, "non-generated number"); 1522FLUSH TABLES; 1523SHOW CREATE TABLE t1; 1524Table Create Table 1525t1 CREATE TABLE `t1` ( 1526 `a` bigint(20) NOT NULL AUTO_INCREMENT, 1527 `b` varchar(64) DEFAULT NULL, 1528 PRIMARY KEY (`a`) 1529) ENGINE=MEMORY AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=latin1 1530/*!50100 PARTITION BY HASH (a) 1531PARTITIONS 3 */ 1532Warnings: 1533Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1534SELECT * FROM t1; 1535a b 15365 non-generated number 15379223372036854775805 2 15389223372036854775806 1 15399223372036854775807 0 1540DROP TABLE t1; 1541# Single row insert unsigned bigint 1542CREATE TABLE t1 1543(a bigint unsigned not null auto_increment primary key, b varchar(64)) 1544ENGINE = 'Memory' 1545PARTITION BY HASH (a) PARTITIONS 3; 1546Warnings: 1547Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1548INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFD, "2"); 1549Warnings: 1550Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1551INSERT INTO t1 VALUES (NULL, "1"); 1552INSERT INTO t1 VALUES (NULL, "0"); 1553ERROR HY000: Failed to read auto-increment value from storage engine 1554INSERT INTO t1 VALUES (NULL, "Overflow"); 1555ERROR HY000: Failed to read auto-increment value from storage engine 1556INSERT INTO t1 VALUES (5, "non-generated number"); 1557FLUSH TABLES; 1558SHOW CREATE TABLE t1; 1559Table Create Table 1560t1 CREATE TABLE `t1` ( 1561 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 1562 `b` varchar(64) DEFAULT NULL, 1563 PRIMARY KEY (`a`) 1564) ENGINE=MEMORY AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 1565/*!50100 PARTITION BY HASH (a) 1566PARTITIONS 3 */ 1567Warnings: 1568Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1569SELECT * FROM t1; 1570a b 157118446744073709551613 2 157218446744073709551614 1 15735 non-generated number 1574DROP TABLE t1; 1575CREATE TABLE `t1` ( 1576`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 1577`b` varchar(64) DEFAULT NULL, 1578PRIMARY KEY (`a`) 1579) ENGINE='Memory' AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 1580PARTITION BY HASH (a) 1581PARTITIONS 3; 1582Warnings: 1583Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1584INSERT INTO t1 VALUES (NULL, "Overflow"); 1585# mysql_errno: 1467 1586DROP TABLE t1; 1587# Multi row insert unsigned bigint 1588CREATE TABLE t1 1589(a bigint unsigned not null auto_increment primary key, b varchar(64)) 1590ENGINE = 'Memory' 1591PARTITION BY HASH (a) PARTITIONS 3; 1592Warnings: 1593Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1594INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFD, "2"); 1595Warnings: 1596Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1597INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1598ERROR HY000: Failed to read auto-increment value from storage engine 1599INSERT INTO t1 VALUES (NULL, "1"); 1600# mysql_errno: 1467 1601INSERT INTO t1 VALUES (NULL, "0"); 1602# mysql_errno: 1467 1603INSERT INTO t1 VALUES (NULL, "Overflow"); 1604Got one of the listed errors 1605# mysql_errno: 1467 1606INSERT INTO t1 VALUES (5, "non-generated number"); 1607FLUSH TABLES; 1608SHOW CREATE TABLE t1; 1609Table Create Table 1610t1 CREATE TABLE `t1` ( 1611 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 1612 `b` varchar(64) DEFAULT NULL, 1613 PRIMARY KEY (`a`) 1614) ENGINE=MEMORY AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 1615/*!50100 PARTITION BY HASH (a) 1616PARTITIONS 3 */ 1617Warnings: 1618Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1619SELECT * FROM t1; 1620a b 162118446744073709551613 2 162218446744073709551614 1 16235 non-generated number 1624DROP TABLE t1; 1625