1DROP TABLE IF EXISTS t1; 2# test without partitioning for reference 3CREATE TABLE t1 ( 4c1 INT NOT NULL AUTO_INCREMENT, 5PRIMARY KEY (c1)) 6ENGINE='Blackhole'; 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=BLACKHOLE 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 231 24SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 25AND TABLE_NAME='t1'; 26AUTO_INCREMENT 271 28INSERT INTO t1 VALUES (0); 29INSERT INTO t1 VALUES (5), (16); 30# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 31# mysql_errno: 0 32INSERT INTO t1 VALUES (17); 33INSERT INTO t1 VALUES (19), (NULL); 34INSERT INTO t1 VALUES (NULL), (10), (NULL); 35INSERT INTO t1 VALUES (NULL); 36SET INSERT_ID = 30; 37INSERT INTO t1 VALUES (NULL); 38SET INSERT_ID = 29; 39INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 40# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 41# mysql_errno: 0 42INSERT INTO t1 VALUES (NULL); 43UPDATE t1 SET c1 = 50 WHERE c1 = 17; 44UPDATE t1 SET c1 = 51 WHERE c1 = 19; 45FLUSH TABLES; 46UPDATE t1 SET c1 = 40 WHERE c1 = 50; 47UPDATE t1 SET c1 = -1 WHERE c1 = 40; 48SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 49 AND TABLE_NAME='t1'; 50AUTO_INCREMENT 511 52UPDATE IGNORE t1 SET c1 = NULL WHERE c1 = 4; 53INSERT INTO t1 VALUES (NULL); 54INSERT INTO t1 VALUES (NULL); 55SELECT * FROM t1 ORDER BY c1; 56c1 57DROP TABLE t1; 58CREATE TABLE t1 ( 59c1 INT NOT NULL AUTO_INCREMENT, 60PRIMARY KEY (c1)) 61ENGINE='Blackhole'; 62SHOW CREATE TABLE t1; 63Table Create Table 64t1 CREATE TABLE `t1` ( 65 `c1` int(11) NOT NULL AUTO_INCREMENT, 66 PRIMARY KEY (`c1`) 67) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 68FLUSH TABLE; 69SHOW CREATE TABLE t1; 70Table Create Table 71t1 CREATE TABLE `t1` ( 72 `c1` int(11) NOT NULL AUTO_INCREMENT, 73 PRIMARY KEY (`c1`) 74) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 75INSERT INTO t1 VALUES (4); 76FLUSH TABLE; 77SHOW CREATE TABLE t1; 78Table Create Table 79t1 CREATE TABLE `t1` ( 80 `c1` int(11) NOT NULL AUTO_INCREMENT, 81 PRIMARY KEY (`c1`) 82) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 83INSERT INTO t1 VALUES (NULL); 84FLUSH TABLE; 85SHOW CREATE TABLE t1; 86Table Create Table 87t1 CREATE TABLE `t1` ( 88 `c1` int(11) NOT NULL AUTO_INCREMENT, 89 PRIMARY KEY (`c1`) 90) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 91DELETE FROM t1; 92INSERT INTO t1 VALUES (NULL); 93INSERT INTO t1 VALUES (-1); 94INSERT INTO t1 VALUES (NULL); 95INSERT INTO t1 VALUES (NULL); 96SHOW CREATE TABLE t1; 97Table Create Table 98t1 CREATE TABLE `t1` ( 99 `c1` int(11) NOT NULL AUTO_INCREMENT, 100 PRIMARY KEY (`c1`) 101) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 102SELECT * FROM t1 ORDER BY c1; 103c1 104TRUNCATE TABLE t1; 105INSERT INTO t1 VALUES (NULL); 106SHOW CREATE TABLE t1; 107Table Create Table 108t1 CREATE TABLE `t1` ( 109 `c1` int(11) NOT NULL AUTO_INCREMENT, 110 PRIMARY KEY (`c1`) 111) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 112SELECT * FROM t1 ORDER BY c1; 113c1 114INSERT INTO t1 VALUES (100); 115INSERT INTO t1 VALUES (NULL); 116DELETE FROM t1 WHERE c1 >= 100; 117OPTIMIZE TABLE t1; 118Table Op Msg_type Msg_text 119test.t1 optimize note The storage engine for the table doesn't support optimize 120SHOW CREATE TABLE t1; 121Table Create Table 122t1 CREATE TABLE `t1` ( 123 `c1` int(11) NOT NULL AUTO_INCREMENT, 124 PRIMARY KEY (`c1`) 125) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 126DROP TABLE t1; 127CREATE TABLE t1 128(a INT NULL AUTO_INCREMENT, 129UNIQUE KEY (a)) 130ENGINE='Blackhole'; 131SET LAST_INSERT_ID = 999; 132SET INSERT_ID = 0; 133INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 134SELECT LAST_INSERT_ID(); 135LAST_INSERT_ID() 136999 137SELECT * FROM t1; 138a 139INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 140SELECT LAST_INSERT_ID(); 141LAST_INSERT_ID() 142999 143SELECT * FROM t1; 144a 145UPDATE t1 SET a = 1 WHERE a IS NULL; 146SELECT LAST_INSERT_ID(); 147LAST_INSERT_ID() 148999 149SELECT * FROM t1; 150a 151UPDATE t1 SET a = NULL WHERE a = 1; 152SELECT LAST_INSERT_ID(); 153LAST_INSERT_ID() 154999 155SELECT * FROM t1; 156a 157DROP TABLE t1; 158SET INSERT_ID = 1; 159# Simple test with NULL 160CREATE TABLE t1 ( 161c1 INT NOT NULL AUTO_INCREMENT, 162PRIMARY KEY (c1)) 163ENGINE='Blackhole' 164PARTITION BY HASH(c1) 165PARTITIONS 2; 166Warnings: 167Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 168INSERT INTO t1 VALUES (NULL); 169Warnings: 170Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 171SHOW CREATE TABLE t1; 172Table Create Table 173t1 CREATE TABLE `t1` ( 174 `c1` int(11) NOT NULL AUTO_INCREMENT, 175 PRIMARY KEY (`c1`) 176) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 177/*!50100 PARTITION BY HASH (c1) 178PARTITIONS 2 */ 179Warnings: 180Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 181SELECT * FROM t1; 182c1 183DROP TABLE t1; 184# Test with sql_mode and first insert as 0 185CREATE TABLE t1 ( 186c1 INT, 187c2 INT NOT NULL AUTO_INCREMENT, 188PRIMARY KEY (c2)) 189ENGINE='Blackhole' 190PARTITION BY HASH(c2) 191PARTITIONS 2; 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. 194INSERT INTO t1 VALUES (1, NULL); 195Warnings: 196Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 197INSERT INTO t1 VALUES (1, 1), (99, 99); 198# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 199# mysql_errno: 0 200INSERT INTO t1 VALUES (1, NULL); 201SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; 202Warnings: 203Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 204INSERT INTO t1 VALUES (1, 0); 205SELECT * FROM t1 ORDER BY c1, c2; 206c1 c2 207DROP TABLE t1; 208CREATE TABLE t1 ( 209c1 INT, 210c2 INT NOT NULL AUTO_INCREMENT, 211PRIMARY KEY (c2)) 212ENGINE='Blackhole' 213PARTITION BY HASH(c2) 214PARTITIONS 2; 215Warnings: 216Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 217INSERT INTO t1 VALUES (1, 0); 218Warnings: 219Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 220INSERT INTO t1 VALUES (1, 1), (1, NULL); 221INSERT INTO t1 VALUES (2, NULL), (4, 7); 222INSERT INTO t1 VALUES (1, NULL); 223SELECT * FROM t1 ORDER BY c1, c2; 224c1 c2 225SET @@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'; 226Warnings: 227Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 228DROP TABLE t1; 229# Simple test with NULL, 0 and explicit values both incr. and desc. 230CREATE TABLE t1 ( 231c1 INT NOT NULL AUTO_INCREMENT, 232PRIMARY KEY (c1)) 233ENGINE='Blackhole' 234PARTITION BY HASH(c1) 235PARTITIONS 2; 236Warnings: 237Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 238INSERT INTO t1 VALUES (2), (4), (NULL); 239Warnings: 240Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 241INSERT INTO t1 VALUES (0); 242INSERT INTO t1 VALUES (5), (16); 243# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 244# mysql_errno: 0 245INSERT INTO t1 VALUES (17), (19), (NULL); 246INSERT INTO t1 VALUES (NULL), (10), (NULL); 247INSERT INTO t1 VALUES (NULL), (9); 248INSERT INTO t1 VALUES (59), (55); 249INSERT INTO t1 VALUES (NULL), (90); 250INSERT INTO t1 VALUES (NULL); 251UPDATE t1 SET c1 = 150 WHERE c1 = 17; 252UPDATE t1 SET c1 = 151 WHERE c1 = 19; 253FLUSH TABLES; 254UPDATE t1 SET c1 = 140 WHERE c1 = 150; 255Warnings: 256Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 257SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 258 AND TABLE_NAME='t1'; 259AUTO_INCREMENT 2601 261Warnings: 262Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 263UPDATE IGNORE t1 SET c1 = NULL WHERE c1 = 4; 264INSERT INTO t1 VALUES (NULL); 265INSERT INTO t1 VALUES (NULL); 266SELECT * FROM t1 ORDER BY c1; 267c1 268DROP TABLE t1; 269# Test with auto_increment_increment and auto_increment_offset. 270CREATE TABLE t1 ( 271c1 INT NOT NULL AUTO_INCREMENT, 272PRIMARY KEY (c1)) 273ENGINE='Blackhole' 274PARTITION BY HASH(c1) 275PARTITIONS 2; 276Warnings: 277Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 278SET @@session.auto_increment_increment = 10; 279SET @@session.auto_increment_offset = 5; 280INSERT INTO t1 VALUES (1); 281Warnings: 282Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 283INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 284SET @@session.auto_increment_increment = 5; 285SET @@session.auto_increment_offset = 3; 286INSERT INTO t1 VALUES (NULL); 287INSERT INTO t1 VALUES (33 + 1); 288INSERT INTO t1 VALUES (NULL); 289INSERT INTO t1 VALUES (38 + 2); 290INSERT INTO t1 VALUES (NULL); 291INSERT INTO t1 VALUES (43 + 3); 292INSERT INTO t1 VALUES (NULL); 293INSERT INTO t1 VALUES (48 + 4); 294INSERT INTO t1 VALUES (NULL); 295INSERT INTO t1 VALUES (53 + 5); 296INSERT INTO t1 VALUES (NULL); 297INSERT INTO t1 VALUES (63 + 6); 298INSERT INTO t1 VALUES (NULL); 299SET @@session.auto_increment_increment = 1; 300SET @@session.auto_increment_offset = 1; 301SELECT * FROM t1 ORDER BY c1; 302c1 303DROP TABLE t1; 304# Test reported auto_increment value 305CREATE TABLE t1 ( 306c1 INT NOT NULL AUTO_INCREMENT, 307PRIMARY KEY (c1)) 308ENGINE='Blackhole' 309PARTITION BY HASH (c1) 310PARTITIONS 2; 311Warnings: 312Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 313SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 314AND TABLE_NAME='t1'; 315AUTO_INCREMENT 3161 317Warnings: 318Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 319INSERT INTO t1 VALUES (2); 320SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 321AND TABLE_NAME='t1'; 322AUTO_INCREMENT 3233 324Warnings: 325Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 326INSERT INTO t1 VALUES (4); 327INSERT INTO t1 VALUES (NULL); 328SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 329AND TABLE_NAME='t1'; 330AUTO_INCREMENT 3316 332Warnings: 333Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 334INSERT INTO t1 VALUES (NULL); 335INSERT INTO t1 VALUES (17); 336INSERT INTO t1 VALUES (19); 337INSERT INTO t1 VALUES (NULL); 338INSERT INTO t1 VALUES (NULL); 339SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 340AND TABLE_NAME='t1'; 341AUTO_INCREMENT 34222 343Warnings: 344Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 345SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 346AND TABLE_NAME='t1'; 347AUTO_INCREMENT 34822 349Warnings: 350Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 351INSERT INTO t1 VALUES (10); 352SELECT * FROM t1 ORDER BY c1; 353c1 354INSERT INTO t1 VALUES (NULL); 355SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' 356AND TABLE_NAME='t1'; 357AUTO_INCREMENT 35823 359Warnings: 360Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 361INSERT INTO t1 VALUES (NULL); 362INSERT INTO t1 VALUES (15); 363INSERT INTO t1 VALUES (NULL); 364SELECT * FROM t1 ORDER BY c1; 365c1 366INSERT INTO t1 VALUES (NULL); 367DELETE FROM t1; 368INSERT INTO t1 VALUES (NULL); 369SHOW CREATE TABLE t1; 370Table Create Table 371t1 CREATE TABLE `t1` ( 372 `c1` int(11) NOT NULL AUTO_INCREMENT, 373 PRIMARY KEY (`c1`) 374) ENGINE=BLACKHOLE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 375/*!50100 PARTITION BY HASH (c1) 376PARTITIONS 2 */ 377Warnings: 378Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 379SELECT * FROM t1 ORDER BY c1; 380c1 381TRUNCATE TABLE t1; 382Warnings: 383Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 384INSERT INTO t1 VALUES (NULL); 385SHOW CREATE TABLE t1; 386Table Create Table 387t1 CREATE TABLE `t1` ( 388 `c1` int(11) NOT NULL AUTO_INCREMENT, 389 PRIMARY KEY (`c1`) 390) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 391/*!50100 PARTITION BY HASH (c1) 392PARTITIONS 2 */ 393Warnings: 394Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 395SELECT * FROM t1 ORDER BY c1; 396c1 397INSERT INTO t1 VALUES (100); 398INSERT INTO t1 VALUES (NULL); 399DELETE FROM t1 WHERE c1 >= 100; 400OPTIMIZE TABLE t1; 401Table Op Msg_type Msg_text 402test.t1 optimize note The storage engine for the table doesn't support optimize 403test.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. 404SHOW CREATE TABLE t1; 405Table Create Table 406t1 CREATE TABLE `t1` ( 407 `c1` int(11) NOT NULL AUTO_INCREMENT, 408 PRIMARY KEY (`c1`) 409) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 410/*!50100 PARTITION BY HASH (c1) 411PARTITIONS 2 */ 412Warnings: 413Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 414DROP TABLE t1; 415# Test with two threads 416# con default 417CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 418ENGINE = 'Blackhole' 419PARTITION BY HASH(c1) 420PARTITIONS 2; 421Warnings: 422Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 423INSERT INTO t1 (c1) VALUES (2); 424Warnings: 425Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 426INSERT INTO t1 (c1) VALUES (4); 427# con1 428INSERT INTO t1 (c1) VALUES (NULL); 429INSERT INTO t1 (c1) VALUES (10); 430# con default 431INSERT INTO t1 (c1) VALUES (NULL); 432INSERT INTO t1 (c1) VALUES (NULL); 433INSERT INTO t1 (c1) VALUES (19); 434INSERT INTO t1 (c1) VALUES (21); 435# con1 436INSERT INTO t1 (c1) VALUES (NULL); 437# con default 438INSERT INTO t1 (c1) VALUES (16); 439# con1 440INSERT INTO t1 (c1) VALUES (NULL); 441# con default 442INSERT INTO t1 (c1) VALUES (NULL); 443SELECT * FROM t1 ORDER BY c1; 444c1 445DROP TABLE t1; 446# Test with two threads + start transaction NO PARTITIONING 447# con default 448CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 449ENGINE = 'Blackhole'; 450START TRANSACTION; 451INSERT INTO t1 (c1) VALUES (2); 452INSERT INTO t1 (c1) VALUES (4); 453# con1 454START TRANSACTION; 455INSERT INTO t1 (c1) VALUES (NULL); 456INSERT INTO t1 (c1) VALUES (10); 457# con default 458INSERT INTO t1 (c1) VALUES (NULL); 459INSERT INTO t1 (c1) VALUES (NULL); 460INSERT INTO t1 (c1) VALUES (19); 461INSERT INTO t1 (c1) VALUES (21); 462# con1 463INSERT INTO t1 (c1) VALUES (NULL); 464# con default 465INSERT INTO t1 (c1) VALUES (16); 466# con1 467INSERT INTO t1 (c1) VALUES (NULL); 468SELECT * FROM t1 ORDER BY c1; 469c1 470COMMIT; 471SELECT * FROM t1 ORDER BY c1; 472c1 473# con default 474INSERT INTO t1 (c1) VALUES (NULL); 475SELECT * FROM t1 ORDER BY c1; 476c1 477COMMIT; 478SELECT * FROM t1 ORDER BY c1; 479c1 480DROP TABLE t1; 481# Test with two threads + start transaction 482# con default 483CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 484ENGINE = 'Blackhole' 485PARTITION BY HASH(c1) 486PARTITIONS 2; 487Warnings: 488Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 489START TRANSACTION; 490INSERT INTO t1 (c1) VALUES (2); 491Warnings: 492Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 493INSERT INTO t1 (c1) VALUES (4); 494# con1 495START TRANSACTION; 496INSERT INTO t1 (c1) VALUES (NULL), (10); 497# con default 498INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19); 499INSERT INTO t1 (c1) VALUES (21); 500# con1 501INSERT INTO t1 (c1) VALUES (NULL); 502# con default 503INSERT INTO t1 (c1) VALUES (16); 504# con1 505INSERT INTO t1 (c1) VALUES (NULL); 506SELECT * FROM t1 ORDER BY c1; 507c1 508COMMIT; 509SELECT * FROM t1 ORDER BY c1; 510c1 511# con default 512INSERT INTO t1 (c1) VALUES (NULL); 513SELECT * FROM t1 ORDER BY c1; 514c1 515COMMIT; 516SELECT * FROM t1 ORDER BY c1; 517c1 518DROP TABLE t1; 519# Test with another column after 520CREATE TABLE t1 ( 521c1 INT NOT NULL AUTO_INCREMENT, 522c2 INT, 523PRIMARY KEY (c1,c2)) 524ENGINE = 'Blackhole' 525PARTITION BY HASH(c2) 526PARTITIONS 2; 527Warnings: 528Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 529INSERT INTO t1 VALUES (1, 0); 530Warnings: 531Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 532INSERT INTO t1 VALUES (1, 1); 533INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3); 534INSERT INTO t1 VALUES (NULL, 3); 535INSERT INTO t1 VALUES (2, 0), (NULL, 2); 536INSERT INTO t1 VALUES (2, 2); 537INSERT INTO t1 VALUES (2, 22); 538INSERT INTO t1 VALUES (NULL, 2); 539SELECT * FROM t1 ORDER BY c1,c2; 540c1 c2 541DROP TABLE t1; 542# Test with another column before 543CREATE TABLE t1 ( 544c1 INT, 545c2 INT NOT NULL AUTO_INCREMENT, 546PRIMARY KEY (c2)) 547ENGINE = 'Blackhole' 548PARTITION BY HASH(c2) 549PARTITIONS 2; 550Warnings: 551Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 552INSERT INTO t1 VALUES (1, 0); 553Warnings: 554Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 555INSERT INTO t1 VALUES (1, 1); 556# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 557# mysql_errno: 0 558INSERT INTO t1 VALUES (1, NULL); 559INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0); 560INSERT INTO t1 VALUES (2, NULL); 561INSERT INTO t1 VALUES (2, 2); 562# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 563# mysql_errno: 0 564INSERT INTO t1 VALUES (2, 22); 565INSERT INTO t1 VALUES (2, NULL); 566SELECT * FROM t1 ORDER BY c1,c2; 567c1 c2 568DROP TABLE t1; 569# Test with auto_increment on secondary column in multi-column-index 570CREATE TABLE t1 ( 571c1 INT, 572c2 INT NOT NULL AUTO_INCREMENT, 573PRIMARY KEY (c1,c2)) 574ENGINE = 'Blackhole' 575PARTITION BY HASH(c2) 576PARTITIONS 2; 577Warnings: 578Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 579INSERT INTO t1 VALUES (1, 0); 580Warnings: 581Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 582INSERT INTO t1 VALUES (1, 1); 583# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 584# mysql_errno: 0 585INSERT INTO t1 VALUES (1, NULL); 586INSERT INTO t1 VALUES (2, NULL); 587INSERT INTO t1 VALUES (3, NULL); 588INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL); 589INSERT INTO t1 VALUES (2, 2); 590# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY 591# mysql_errno: 0 592INSERT INTO t1 VALUES (2, 22), (2, NULL); 593SELECT * FROM t1 ORDER BY c1,c2; 594c1 c2 595DROP TABLE t1; 596# Test AUTO_INCREMENT in CREATE 597CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 598ENGINE = 'Blackhole' 599AUTO_INCREMENT = 15 600PARTITION BY HASH(c1) 601PARTITIONS 2; 602Warnings: 603Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 604SHOW CREATE TABLE t1; 605Table Create Table 606t1 CREATE TABLE `t1` ( 607 `c1` int(11) NOT NULL AUTO_INCREMENT, 608 PRIMARY KEY (`c1`) 609) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 610/*!50100 PARTITION BY HASH (c1) 611PARTITIONS 2 */ 612Warnings: 613Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 614INSERT INTO t1 (c1) VALUES (4); 615SHOW CREATE TABLE t1; 616Table Create Table 617t1 CREATE TABLE `t1` ( 618 `c1` int(11) NOT NULL AUTO_INCREMENT, 619 PRIMARY KEY (`c1`) 620) ENGINE=BLACKHOLE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 621/*!50100 PARTITION BY HASH (c1) 622PARTITIONS 2 */ 623Warnings: 624Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 625INSERT INTO t1 (c1) VALUES (0); 626SHOW CREATE TABLE t1; 627Table Create Table 628t1 CREATE TABLE `t1` ( 629 `c1` int(11) NOT NULL AUTO_INCREMENT, 630 PRIMARY KEY (`c1`) 631) ENGINE=BLACKHOLE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 632/*!50100 PARTITION BY HASH (c1) 633PARTITIONS 2 */ 634Warnings: 635Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 636INSERT INTO t1 (c1) VALUES (NULL); 637SHOW CREATE TABLE t1; 638Table Create Table 639t1 CREATE TABLE `t1` ( 640 `c1` int(11) NOT NULL AUTO_INCREMENT, 641 PRIMARY KEY (`c1`) 642) ENGINE=BLACKHOLE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 643/*!50100 PARTITION BY HASH (c1) 644PARTITIONS 2 */ 645Warnings: 646Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 647SELECT * FROM t1 ORDER BY c1; 648c1 649# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO' 650SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; 651Warnings: 652Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 653INSERT INTO t1 (c1) VALUES (300); 654SHOW CREATE TABLE t1; 655Table Create Table 656t1 CREATE TABLE `t1` ( 657 `c1` int(11) NOT NULL AUTO_INCREMENT, 658 PRIMARY KEY (`c1`) 659) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 660/*!50100 PARTITION BY HASH (c1) 661PARTITIONS 2 */ 662Warnings: 663Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 664INSERT INTO t1 (c1) VALUES (0); 665SHOW CREATE TABLE t1; 666Table Create Table 667t1 CREATE TABLE `t1` ( 668 `c1` int(11) NOT NULL AUTO_INCREMENT, 669 PRIMARY KEY (`c1`) 670) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 671/*!50100 PARTITION BY HASH (c1) 672PARTITIONS 2 */ 673Warnings: 674Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 675INSERT INTO t1 (c1) VALUES (NULL); 676SHOW CREATE TABLE t1; 677Table Create Table 678t1 CREATE TABLE `t1` ( 679 `c1` int(11) NOT NULL AUTO_INCREMENT, 680 PRIMARY KEY (`c1`) 681) ENGINE=BLACKHOLE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 682/*!50100 PARTITION BY HASH (c1) 683PARTITIONS 2 */ 684Warnings: 685Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 686SELECT * FROM t1 ORDER BY c1; 687c1 688SET @@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'; 689Warnings: 690Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 691DROP TABLE t1; 692# Test SET INSERT_ID 693CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) 694ENGINE = 'Blackhole' 695PARTITION BY HASH(c1) 696PARTITIONS 2; 697Warnings: 698Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 699SHOW CREATE TABLE t1; 700Table Create Table 701t1 CREATE TABLE `t1` ( 702 `c1` int(11) NOT NULL AUTO_INCREMENT, 703 PRIMARY KEY (`c1`) 704) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 705/*!50100 PARTITION BY HASH (c1) 706PARTITIONS 2 */ 707Warnings: 708Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 709INSERT INTO t1 (c1) VALUES (NULL); 710SHOW CREATE TABLE t1; 711Table Create Table 712t1 CREATE TABLE `t1` ( 713 `c1` int(11) NOT NULL AUTO_INCREMENT, 714 PRIMARY KEY (`c1`) 715) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 716/*!50100 PARTITION BY HASH (c1) 717PARTITIONS 2 */ 718Warnings: 719Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 720SELECT * FROM t1; 721c1 722SET INSERT_ID = 23; 723SHOW CREATE TABLE t1; 724Table Create Table 725t1 CREATE TABLE `t1` ( 726 `c1` int(11) NOT NULL AUTO_INCREMENT, 727 PRIMARY KEY (`c1`) 728) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 729/*!50100 PARTITION BY HASH (c1) 730PARTITIONS 2 */ 731Warnings: 732Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 733INSERT INTO t1 (c1) VALUES (NULL); 734SHOW CREATE TABLE t1; 735Table Create Table 736t1 CREATE TABLE `t1` ( 737 `c1` int(11) NOT NULL AUTO_INCREMENT, 738 PRIMARY KEY (`c1`) 739) ENGINE=BLACKHOLE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 740/*!50100 PARTITION BY HASH (c1) 741PARTITIONS 2 */ 742Warnings: 743Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 744SET INSERT_ID = 22; 745INSERT INTO t1 VALUES (NULL), (NULL), (NULL); 746# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY 747# mysql_errno: 0 748INSERT INTO t1 VALUES (NULL); 749SELECT * FROM t1 ORDER BY c1; 750c1 751DROP TABLE t1; 752# Testing with FLUSH TABLE 753CREATE TABLE t1 ( 754c1 INT NOT NULL AUTO_INCREMENT, 755PRIMARY KEY (c1)) 756ENGINE='Blackhole' 757PARTITION BY HASH(c1) 758PARTITIONS 2; 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. 761SHOW CREATE TABLE t1; 762Table Create Table 763t1 CREATE TABLE `t1` ( 764 `c1` int(11) NOT NULL AUTO_INCREMENT, 765 PRIMARY KEY (`c1`) 766) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 767/*!50100 PARTITION BY HASH (c1) 768PARTITIONS 2 */ 769Warnings: 770Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 771FLUSH TABLE; 772SHOW CREATE TABLE t1; 773Table Create Table 774t1 CREATE TABLE `t1` ( 775 `c1` int(11) NOT NULL AUTO_INCREMENT, 776 PRIMARY KEY (`c1`) 777) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 778/*!50100 PARTITION BY HASH (c1) 779PARTITIONS 2 */ 780Warnings: 781Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 782INSERT INTO t1 VALUES (4); 783FLUSH TABLE; 784SHOW CREATE TABLE t1; 785Table Create Table 786t1 CREATE TABLE `t1` ( 787 `c1` int(11) NOT NULL AUTO_INCREMENT, 788 PRIMARY KEY (`c1`) 789) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 790/*!50100 PARTITION BY HASH (c1) 791PARTITIONS 2 */ 792Warnings: 793Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 794INSERT INTO t1 VALUES (NULL); 795FLUSH TABLE; 796SHOW CREATE TABLE t1; 797Table Create Table 798t1 CREATE TABLE `t1` ( 799 `c1` int(11) NOT NULL AUTO_INCREMENT, 800 PRIMARY KEY (`c1`) 801) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 802/*!50100 PARTITION BY HASH (c1) 803PARTITIONS 2 */ 804Warnings: 805Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 806SELECT * FROM t1 ORDER BY c1; 807c1 808DROP TABLE t1; 809############################################################################# 810# Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 811# Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 812############################################################################## 813# Inserting negative autoincrement values into a partition table (partitions >= 4) 814CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 815c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 816Warnings: 817Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 818INSERT INTO t(c2) VALUES (10); 819Warnings: 820Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 821INSERT INTO t(c2) VALUES (20); 822INSERT INTO t VALUES (-1,-10); 823INSERT INTO t(c2) VALUES (30); 824INSERT INTO t(c2) VALUES (40); 825SELECT * FROM t ORDER BY c1 ASC; 826c1 c2 827DROP TABLE t; 828# Reading from a partition table (partitions >= 2 ) after inserting a negative 829# value into the auto increment column 830CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 831c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 2; 832Warnings: 833Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 834INSERT INTO t VALUES (-2,-20); 835Warnings: 836Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 837INSERT INTO t(c2) VALUES (30); 838SELECT * FROM t ORDER BY c1 ASC; 839c1 c2 840DROP TABLE t; 841# Inserting negative auto increment value into a partition table (partitions >= 2) 842# auto increment value > 2. 843CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 844c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 2; 845Warnings: 846Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 847INSERT INTO t VALUES (-4,-20); 848Warnings: 849Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 850INSERT INTO t(c2) VALUES (30); 851INSERT INTO t(c2) VALUES (40); 852SELECT * FROM t ORDER BY c1 ASC; 853c1 c2 854DROP TABLE t; 855# Inserting -1 into autoincrement column of a partition table (partition >= 4) 856CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 857c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 858Warnings: 859Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 860INSERT INTO t(c2) VALUES (10); 861Warnings: 862Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 863INSERT INTO t(c2) VALUES (20); 864INSERT INTO t VALUES (-1,-10); 865SELECT * FROM t ORDER BY c1 ASC; 866c1 c2 867INSERT INTO t(c2) VALUES (30); 868SELECT * FROM t ORDER BY c1 ASC; 869c1 c2 870DROP TABLE t; 871# Deleting from an auto increment table after inserting negative values 872CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 873c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 874Warnings: 875Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 876INSERT INTO t(c2) VALUES (10); 877Warnings: 878Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 879INSERT INTO t(c2) VALUES (20); 880INSERT INTO t VALUES (-1,-10); 881INSERT INTO t(c2) VALUES (30); 882INSERT INTO t VALUES (-3,-20); 883INSERT INTO t(c2) VALUES (40); 884SELECT * FROM t ORDER BY c1 ASC; 885c1 c2 886DELETE FROM t WHERE c1 > 1; 887SELECT * FROM t ORDER BY c1 ASC; 888c1 c2 889DROP TABLE t; 890# Inserting a positive value that exceeds maximum allowed value for an 891# Auto Increment column (positive maximum) 892CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 893c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 894Warnings: 895Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 896INSERT INTO t(c2) VALUES (10); 897Warnings: 898Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 899INSERT INTO t(c2) VALUES (20); 900INSERT INTO t VALUES (126,30); 901INSERT INTO t VALUES (127,40); 902SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 903Warnings: 904Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 905INSERT INTO t VALUES (128,50); 906Warnings: 907Warning 1264 Out of range value for column 'c1' at row 1 908# ERROR (Only OK if Blackhole) 909# expecting ER_DUP_ENTRY or ER_DUP_KEY, mysql_errno: 0 910INSERT INTO t VALUES (129,60); 911Warnings: 912Warning 1264 Out of range value for column 'c1' at row 1 913# ERROR (only OK if Blackhole) 914# expecting ER_DUP_ENTRY or ER_DUP_KEY, mysql_errno: 0 915SELECT * FROM t ORDER BY c1 ASC; 916c1 c2 917DROP TABLE t; 918# Inserting a negative value that goes below minimum allowed value for an 919# Auto Increment column (negative minimum) 920CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 921c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 922Warnings: 923Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 924INSERT INTO t(c2) VALUES (10); 925Warnings: 926Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 927INSERT INTO t(c2) VALUES (20); 928INSERT INTO t VALUES (-127,30); 929INSERT INTO t VALUES (-128,40); 930INSERT INTO t VALUES (-129,50); 931Warnings: 932Warning 1264 Out of range value for column 'c1' at row 1 933# ERROR (only OK if Blackhole) 934echo # should give ER_DUP_KEY or ER_DUP_ENTRY mysql_errno: 0 935INSERT INTO t VALUES (-130,60); 936Warnings: 937Warning 1264 Out of range value for column 'c1' at row 1 938# ERROR (only OK if Blackhole) 939echo # should give ER_DUP_KEY or ER_DUP_ENTRY mysql_errno: 0 940SELECT * FROM t ORDER BY c1 ASC; 941c1 c2 942DROP TABLE t; 943# Updating the partition table with a negative Auto Increment value 944CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 945c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 946Warnings: 947Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 948INSERT INTO t(c2) VALUES (10); 949Warnings: 950Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 951INSERT INTO t(c2) VALUES (20); 952INSERT INTO t VALUES (-1,-10); 953INSERT INTO t(c2) VALUES (30); 954SELECT * FROM t ORDER BY c1 ASC; 955c1 c2 956UPDATE t SET c1 = -6 WHERE c1 = 2; 957SELECT * FROM t ORDER BY c1 ASC; 958c1 c2 959INSERT INTO t(c2) VALUES (40); 960INSERT INTO t(c2) VALUES (50); 961UPDATE t SET c1 = -6 WHERE c1 = 2; 962SELECT * FROM t ORDER BY c1 ASC; 963c1 c2 964DROP TABLE t; 965SET sql_mode = default; 966# Updating the partition table with a value that crosses the upper limits 967# on both the positive and the negative side. 968CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), 969c2 INT) ENGINE='Blackhole' PARTITION BY HASH(c1) PARTITIONS 4; 970Warnings: 971Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 972INSERT INTO t(c2) VALUES (10); 973Warnings: 974Warning 1287 The partition engine, used by table 'test.t', is deprecated and will be removed in a future release. Please use native partitioning instead. 975INSERT INTO t(c2) VALUES (20); 976INSERT INTO t VALUES (126,30); 977INSERT INTO t VALUES (127,40); 978SELECT * FROM t ORDER BY c1 ASC; 979c1 c2 980UPDATE IGNORE t SET c1 = 130 where c1 = 127; 981SELECT * FROM t ORDER BY c1 ASC; 982c1 c2 983UPDATE IGNORE t SET c1 = -140 where c1 = 126; 984SELECT * FROM t ORDER BY c1 ASC; 985c1 c2 986DROP TABLE t; 987CREATE TABLE t1 988(a INT NULL AUTO_INCREMENT, 989UNIQUE KEY (a)) 990ENGINE='Blackhole' 991PARTITION BY KEY(a) PARTITIONS 2; 992Warnings: 993Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 994SET LAST_INSERT_ID = 999; 995INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 996Warnings: 997Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 998SELECT LAST_INSERT_ID(); 999LAST_INSERT_ID() 1000999 1001SELECT * FROM t1; 1002a 1003INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; 1004SELECT LAST_INSERT_ID(); 1005LAST_INSERT_ID() 1006999 1007SELECT * FROM t1; 1008a 1009UPDATE t1 SET a = 1 WHERE a IS NULL; 1010SELECT LAST_INSERT_ID(); 1011LAST_INSERT_ID() 1012999 1013SELECT * FROM t1; 1014a 1015UPDATE t1 SET a = NULL WHERE a = 1; 1016SELECT LAST_INSERT_ID(); 1017LAST_INSERT_ID() 1018999 1019SELECT * FROM t1; 1020a 1021DROP TABLE t1; 1022############################################################################## 1023# 1024# Test overflow 1025# 1026# Single row insert signed int 1027CREATE TABLE t1 1028(a int not null auto_increment primary key, b varchar(64)) 1029ENGINE = 'Blackhole' 1030PARTITION BY HASH (a) PARTITIONS 3; 1031Warnings: 1032Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1033INSERT INTO t1 VALUES (0x7FFFFFFD, "2"); 1034Warnings: 1035Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1036INSERT INTO t1 VALUES (NULL, "1"); 1037INSERT INTO t1 VALUES (NULL, "0"); 1038INSERT INTO t1 VALUES (NULL, "Overflow"); 1039SHOW WARNINGS; 1040Level Code Message 1041Error 1264 Out of range value for column 'a' at row 1 1042Error 1264 Out of range value for column 't1' at row 167 1043INSERT INTO t1 VALUES (5, "non-generated number"); 1044FLUSH TABLES; 1045SHOW CREATE TABLE t1; 1046Table Create Table 1047t1 CREATE TABLE `t1` ( 1048 `a` int(11) NOT NULL AUTO_INCREMENT, 1049 `b` varchar(64) DEFAULT NULL, 1050 PRIMARY KEY (`a`) 1051) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1052/*!50100 PARTITION BY HASH (a) 1053PARTITIONS 3 */ 1054Warnings: 1055Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1056SELECT * FROM t1; 1057a b 1058DROP TABLE t1; 1059CREATE TABLE `t1` ( 1060`a` int(11) NOT NULL AUTO_INCREMENT, 1061`b` varchar(64) DEFAULT NULL, 1062PRIMARY KEY (`a`) 1063) ENGINE='Blackhole' 1064AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1 1065PARTITION BY HASH (a) PARTITIONS 3; 1066Warnings: 1067Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1068INSERT INTO t1 VALUES (NULL, "Overflow"); 1069Warnings: 1070Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1071# mysql_errno: 0 1072INSERT INTO t1 VALUES (NULL, "Overflow"); 1073# mysql_errno: 0 1074DROP TABLE t1; 1075# Multi row insert signed int 1076CREATE TABLE t1 1077(a int not null auto_increment primary key, b varchar(64)) 1078ENGINE = 'Blackhole' 1079PARTITION BY HASH (a) PARTITIONS 3; 1080Warnings: 1081Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1082INSERT INTO t1 VALUES (0x7FFFFFFD, "2"); 1083Warnings: 1084Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1085INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1086ERROR HY000: Failed to read auto-increment value from storage engine 1087INSERT INTO t1 VALUES (NULL, "1"); 1088# mysql_errno: 1264 1089INSERT INTO t1 VALUES (NULL, "0"); 1090# mysql_errno: 1264 1091INSERT INTO t1 VALUES (NULL, "Overflow"); 1092ERROR 22003: Out of range value for column 'a' at row 1 1093INSERT INTO t1 VALUES (5, "non-generated number"); 1094FLUSH TABLES; 1095SHOW CREATE TABLE t1; 1096Table Create Table 1097t1 CREATE TABLE `t1` ( 1098 `a` int(11) NOT NULL AUTO_INCREMENT, 1099 `b` varchar(64) DEFAULT NULL, 1100 PRIMARY KEY (`a`) 1101) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1102/*!50100 PARTITION BY HASH (a) 1103PARTITIONS 3 */ 1104Warnings: 1105Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1106SELECT * FROM t1; 1107a b 1108DROP TABLE t1; 1109# Single row insert unsigned int 1110CREATE TABLE t1 1111(a int unsigned not null auto_increment primary key, b varchar(64)) 1112ENGINE = 'Blackhole' 1113PARTITION BY HASH (a) PARTITIONS 3; 1114Warnings: 1115Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1116INSERT INTO t1 VALUES (0xFFFFFFFD, "2"); 1117Warnings: 1118Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1119INSERT INTO t1 VALUES (NULL, "1"); 1120INSERT INTO t1 VALUES (NULL, "0"); 1121INSERT INTO t1 VALUES (NULL, "Overflow"); 1122ERROR 22003: Out of range value for column 'a' at row 1 1123INSERT INTO t1 VALUES (5, "non-generated number"); 1124FLUSH TABLES; 1125SHOW CREATE TABLE t1; 1126Table Create Table 1127t1 CREATE TABLE `t1` ( 1128 `a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1129 `b` varchar(64) DEFAULT NULL, 1130 PRIMARY KEY (`a`) 1131) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1132/*!50100 PARTITION BY HASH (a) 1133PARTITIONS 3 */ 1134Warnings: 1135Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1136SELECT * FROM t1; 1137a b 1138DROP TABLE t1; 1139CREATE TABLE `t1` ( 1140`a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1141`b` varchar(64) DEFAULT NULL, 1142PRIMARY KEY (`a`) 1143) ENGINE='Blackhole' AUTO_INCREMENT=4294967296 DEFAULT CHARSET=latin1 1144PARTITION BY HASH (a) PARTITIONS 3; 1145Warnings: 1146Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1147INSERT INTO t1 VALUES (NULL, "Overflow"); 1148Warnings: 1149Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1150# mysql_errno: 0 1151DROP TABLE t1; 1152# Multi row insert unsigned int 1153CREATE TABLE t1 1154(a int unsigned not null auto_increment primary key, b varchar(64)) 1155ENGINE = 'Blackhole' 1156PARTITION BY HASH (a) PARTITIONS 3; 1157Warnings: 1158Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1159INSERT INTO t1 VALUES (0xFFFFFFFD, "2"); 1160Warnings: 1161Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1162INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1163ERROR HY000: Failed to read auto-increment value from storage engine 1164INSERT INTO t1 VALUES (NULL, "1"); 1165# mysql_errno: 1264 1166INSERT INTO t1 VALUES (NULL, "0"); 1167# mysql_errno: 1264 1168INSERT INTO t1 VALUES (NULL, "Overflow"); 1169ERROR 22003: Out of range value for column 'a' at row 1 1170INSERT INTO t1 VALUES (5, "non-generated number"); 1171FLUSH TABLES; 1172SHOW CREATE TABLE t1; 1173Table Create Table 1174t1 CREATE TABLE `t1` ( 1175 `a` int(10) unsigned NOT NULL AUTO_INCREMENT, 1176 `b` varchar(64) DEFAULT NULL, 1177 PRIMARY KEY (`a`) 1178) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1179/*!50100 PARTITION BY HASH (a) 1180PARTITIONS 3 */ 1181Warnings: 1182Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1183SELECT * FROM t1; 1184a b 1185DROP TABLE t1; 1186# Single row insert signed bigint 1187CREATE TABLE t1 1188(a bigint not null auto_increment primary key, b varchar(64)) 1189ENGINE = 'Blackhole' 1190PARTITION BY HASH (a) PARTITIONS 3; 1191Warnings: 1192Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1193INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, "2"); 1194Warnings: 1195Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1196INSERT INTO t1 VALUES (NULL, "1"); 1197INSERT INTO t1 VALUES (NULL, "0"); 1198INSERT INTO t1 VALUES (NULL, "Overflow"); 1199ERROR 22003: Out of range value for column 'a' at row 1 1200INSERT INTO t1 VALUES (5, "non-generated number"); 1201FLUSH TABLES; 1202SHOW CREATE TABLE t1; 1203Table Create Table 1204t1 CREATE TABLE `t1` ( 1205 `a` bigint(20) NOT NULL AUTO_INCREMENT, 1206 `b` varchar(64) DEFAULT NULL, 1207 PRIMARY KEY (`a`) 1208) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1209/*!50100 PARTITION BY HASH (a) 1210PARTITIONS 3 */ 1211Warnings: 1212Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1213SELECT * FROM t1; 1214a b 1215DROP TABLE t1; 1216CREATE TABLE `t1` ( 1217`a` bigint(20) NOT NULL AUTO_INCREMENT, 1218`b` varchar(64) DEFAULT NULL, 1219PRIMARY KEY (`a`) 1220) ENGINE='Blackhole' AUTO_INCREMENT=9223372036854775808 DEFAULT CHARSET=latin1 1221PARTITION BY HASH (a) 1222PARTITIONS 3; 1223Warnings: 1224Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1225INSERT INTO t1 VALUES (NULL, "Overflow"); 1226Warnings: 1227Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1228# mysql_errno: 0 1229DROP TABLE t1; 1230# Multi row insert signed bigint 1231CREATE TABLE t1 1232(a bigint not null auto_increment primary key, b varchar(64)) 1233ENGINE = 'Blackhole' 1234PARTITION BY HASH (a) PARTITIONS 3; 1235Warnings: 1236Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1237INSERT INTO t1 VALUES (0x7FFFFFFFFFFFFFFD, "2"); 1238Warnings: 1239Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1240INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1241ERROR HY000: Failed to read auto-increment value from storage engine 1242INSERT INTO t1 VALUES (NULL, "1"); 1243# mysql_errno: 1264 1244INSERT INTO t1 VALUES (NULL, "0"); 1245# mysql_errno: 1264 1246INSERT INTO t1 VALUES (NULL, "Overflow"); 1247ERROR 22003: Out of range value for column 'a' at row 1 1248INSERT INTO t1 VALUES (5, "non-generated number"); 1249FLUSH TABLES; 1250SHOW CREATE TABLE t1; 1251Table Create Table 1252t1 CREATE TABLE `t1` ( 1253 `a` bigint(20) NOT NULL AUTO_INCREMENT, 1254 `b` varchar(64) DEFAULT NULL, 1255 PRIMARY KEY (`a`) 1256) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1257/*!50100 PARTITION BY HASH (a) 1258PARTITIONS 3 */ 1259Warnings: 1260Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1261SELECT * FROM t1; 1262a b 1263DROP TABLE t1; 1264# Single row insert unsigned bigint 1265CREATE TABLE t1 1266(a bigint unsigned not null auto_increment primary key, b varchar(64)) 1267ENGINE = 'Blackhole' 1268PARTITION BY HASH (a) PARTITIONS 3; 1269Warnings: 1270Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1271INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFD, "2"); 1272Warnings: 1273Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1274INSERT INTO t1 VALUES (NULL, "1"); 1275INSERT INTO t1 VALUES (NULL, "0"); 1276ERROR HY000: Failed to read auto-increment value from storage engine 1277INSERT INTO t1 VALUES (NULL, "Overflow"); 1278ERROR HY000: Failed to read auto-increment value from storage engine 1279INSERT INTO t1 VALUES (5, "non-generated number"); 1280FLUSH TABLES; 1281SHOW CREATE TABLE t1; 1282Table Create Table 1283t1 CREATE TABLE `t1` ( 1284 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 1285 `b` varchar(64) DEFAULT NULL, 1286 PRIMARY KEY (`a`) 1287) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1288/*!50100 PARTITION BY HASH (a) 1289PARTITIONS 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. 1292SELECT * FROM t1; 1293a b 1294DROP TABLE t1; 1295CREATE TABLE `t1` ( 1296`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 1297`b` varchar(64) DEFAULT NULL, 1298PRIMARY KEY (`a`) 1299) ENGINE='Blackhole' AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 1300PARTITION BY HASH (a) 1301PARTITIONS 3; 1302Warnings: 1303Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1304INSERT INTO t1 VALUES (NULL, "Overflow"); 1305Warnings: 1306Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1307# mysql_errno: 0 1308DROP TABLE t1; 1309# Multi row insert unsigned bigint 1310CREATE TABLE t1 1311(a bigint unsigned not null auto_increment primary key, b varchar(64)) 1312ENGINE = 'Blackhole' 1313PARTITION BY HASH (a) PARTITIONS 3; 1314Warnings: 1315Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1316INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFD, "2"); 1317Warnings: 1318Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1319INSERT INTO t1 VALUES (NULL, "1"), (NULL, "0"), (NULL, "Overflow"); 1320ERROR HY000: Failed to read auto-increment value from storage engine 1321INSERT INTO t1 VALUES (NULL, "1"); 1322# mysql_errno: 1467 1323INSERT INTO t1 VALUES (NULL, "0"); 1324# mysql_errno: 1467 1325INSERT INTO t1 VALUES (NULL, "Overflow"); 1326Got one of the listed errors 1327# mysql_errno: 1467 1328INSERT INTO t1 VALUES (5, "non-generated number"); 1329FLUSH TABLES; 1330SHOW CREATE TABLE t1; 1331Table Create Table 1332t1 CREATE TABLE `t1` ( 1333 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 1334 `b` varchar(64) DEFAULT NULL, 1335 PRIMARY KEY (`a`) 1336) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 1337/*!50100 PARTITION BY HASH (a) 1338PARTITIONS 3 */ 1339Warnings: 1340Warning 1287 The partition engine, used by table 'test.t1', is deprecated and will be removed in a future release. Please use native partitioning instead. 1341SELECT * FROM t1; 1342a b 1343DROP TABLE t1; 1344