1# 2# Test of auto_increment at end of range 3# 4--disable_warnings 5drop table if exists t1; 6--enable_warnings 7 8--echo # 9--echo # Testing ranges with smallint 10--echo # 11let $type=smallint; 12let $range_max=32767; 13 14eval create table t1 (a $type primary key auto_increment); 15eval insert into t1 values($range_max); 16--error HA_ERR_AUTOINC_ERANGE 17insert into t1 values(NULL); 18truncate table t1; 19eval insert into t1 values($range_max-1); 20insert into t1 values(NULL); 21--error HA_ERR_AUTOINC_ERANGE 22insert into t1 values(NULL); 23select * from t1; 24truncate table t1; 25--error HA_ERR_AUTOINC_ERANGE 26eval insert into t1 values($range_max),(NULL); 27select * from t1; 28truncate table t1; 29--error HA_ERR_AUTOINC_ERANGE 30eval insert into t1 values($range_max-1),(NULL),(NULL); 31truncate table t1; 32eval insert ignore into t1 values($range_max+1); 33select * from t1; 34--error HA_ERR_AUTOINC_ERANGE 35eval insert into t1 values(NULL); 36drop table t1; 37 38--echo # 39--echo # Testing ranges with unsigned smallint 40--echo # 41 42let $type=smallint unsigned; 43let $range_max=65535; 44 45eval create table t1 (a $type primary key auto_increment); 46eval insert into t1 values($range_max); 47--error HA_ERR_AUTOINC_ERANGE 48insert into t1 values(NULL); 49truncate table t1; 50eval insert into t1 values($range_max-1); 51insert into t1 values(NULL); 52--error HA_ERR_AUTOINC_ERANGE 53insert into t1 values(NULL); 54select * from t1; 55truncate table t1; 56--error HA_ERR_AUTOINC_ERANGE 57eval insert into t1 values($range_max),(NULL); 58select * from t1; 59truncate table t1; 60--error HA_ERR_AUTOINC_ERANGE 61eval insert into t1 values($range_max-1),(NULL),(NULL); 62truncate table t1; 63eval insert ignore into t1 values($range_max+1); 64select * from t1; 65--error HA_ERR_AUTOINC_ERANGE 66eval insert into t1 values(NULL); 67drop table t1; 68 69--echo # 70--echo # Testing ranges with integer 71--echo # 72 73let $type=int; 74let $range_max=2147483647; 75 76eval create table t1 (a $type primary key auto_increment); 77eval insert into t1 values($range_max); 78--error HA_ERR_AUTOINC_ERANGE 79insert into t1 values(NULL); 80truncate table t1; 81eval insert into t1 values($range_max-1); 82insert into t1 values(NULL); 83--error HA_ERR_AUTOINC_ERANGE 84insert into t1 values(NULL); 85select * from t1; 86truncate table t1; 87--error HA_ERR_AUTOINC_ERANGE 88eval insert into t1 values($range_max),(NULL); 89select * from t1; 90truncate table t1; 91--error HA_ERR_AUTOINC_ERANGE 92eval insert into t1 values($range_max-1),(NULL),(NULL); 93truncate table t1; 94eval insert ignore into t1 values($range_max+1); 95select * from t1; 96--error HA_ERR_AUTOINC_ERANGE 97eval insert into t1 values(NULL); 98drop table t1; 99 100--echo # 101--echo # Testing ranges with unsigned integer 102--echo # 103 104let $type=int unsigned; 105let $range_max=4294967295; 106 107eval create table t1 (a $type primary key auto_increment); 108eval insert into t1 values($range_max); 109--error HA_ERR_AUTOINC_ERANGE 110insert into t1 values(NULL); 111truncate table t1; 112eval insert into t1 values($range_max-1); 113insert into t1 values(NULL); 114--error HA_ERR_AUTOINC_ERANGE 115insert into t1 values(NULL); 116select * from t1; 117truncate table t1; 118--error HA_ERR_AUTOINC_ERANGE 119eval insert into t1 values($range_max),(NULL); 120select * from t1; 121truncate table t1; 122--error HA_ERR_AUTOINC_ERANGE 123eval insert into t1 values($range_max-1),(NULL),(NULL); 124truncate table t1; 125eval insert ignore into t1 values($range_max+1); 126select * from t1; 127--error HA_ERR_AUTOINC_ERANGE 128eval insert into t1 values(NULL); 129drop table t1; 130 131--echo # 132--echo # Testing ranges with bigint 133--echo # 134 135let $type=bigint; 136let $range_max=cast(9223372036854775807 as unsigned); 137 138eval create table t1 (a $type primary key auto_increment); 139eval insert into t1 values($range_max); 140--error HA_ERR_AUTOINC_ERANGE 141insert into t1 values(NULL); 142truncate table t1; 143eval insert into t1 values($range_max-1); 144insert into t1 values(NULL); 145--error HA_ERR_AUTOINC_ERANGE 146insert into t1 values(NULL); 147select * from t1; 148truncate table t1; 149--error HA_ERR_AUTOINC_ERANGE 150eval insert into t1 values($range_max),(NULL); 151select * from t1; 152truncate table t1; 153--error HA_ERR_AUTOINC_ERANGE 154eval insert into t1 values($range_max-1),(NULL),(NULL); 155truncate table t1; 156eval insert ignore into t1 values($range_max+1); 157select * from t1; 158--error HA_ERR_AUTOINC_ERANGE 159eval insert into t1 values(NULL); 160drop table t1; 161 162--echo # 163--echo # Testing ranges with unsigned bigint 164--echo # 165 166let $type=bigint unsigned; 167let $range_max=18446744073709551615; 168 169eval create table t1 (a $type primary key auto_increment); 170eval insert into t1 values($range_max-1); 171--error ER_AUTOINC_READ_FAILED 172insert into t1 values(NULL); 173--error ER_AUTOINC_READ_FAILED 174insert into t1 values(NULL); 175truncate table t1; 176eval insert into t1 values($range_max-1); 177--error ER_AUTOINC_READ_FAILED 178insert into t1 values(NULL); 179--error ER_AUTOINC_READ_FAILED 180insert into t1 values(NULL); 181select * from t1; 182truncate table t1; 183--error ER_AUTOINC_READ_FAILED 184eval insert into t1 values($range_max),(NULL); 185select * from t1; 186truncate table t1; 187--error ER_AUTOINC_READ_FAILED 188eval insert into t1 values($range_max-1),(NULL),(NULL); 189drop table t1; 190 191--echo # 192--echo # Test IGNORE and strict mode 193--echo # 194create table t1 (a smallint primary key auto_increment); 195insert ignore into t1 values(32766),(NULL),(NULL),(1); 196select * from t1; 197truncate table t1; 198 199set @org_mode=@@sql_mode; 200set @@sql_mode='ansi,traditional'; 201insert ignore into t1 values(32766),(NULL),(NULL); 202truncate table t1; 203--error HA_ERR_AUTOINC_ERANGE 204insert into t1 values(32766),(NULL),(NULL); 205set @@sql_mode=@org_mode; 206drop table t1; 207 208--echo # 209--echo # Test auto increment with negative numbers 210--echo # 211CREATE TABLE t1 (a INTEGER AUTO_INCREMENT, PRIMARY KEY (a)); 212INSERT INTO t1 VALUES (NULL), (2), (-5), (NULL); 213INSERT INTO t1 VALUES (NULL); 214SELECT * FROM t1; 215TRUNCATE TABLE t1; 216INSERT INTO t1 VALUES (-5), (NULL); 217SELECT * FROM t1; 218DROP TABLE t1; 219 220--echo # 221--echo # Test inserting a value out-of-range into an auto increment column 222--echo # 223CREATE TABLE t1 (a smallint AUTO_INCREMENT, PRIMARY KEY (a)); 224INSERT INTO t1 VALUES (2); 225INSERT IGNORE INTO t1 VALUES (32768); 226--error HA_ERR_AUTOINC_ERANGE 227INSERT INTO t1 VALUES (NULL); 228SELECT * FROM t1; 229DROP TABLE t1; 230 231 232--echo # 233--echo # Test old behaviour 234--echo # 235create table t1 (a smallint primary key auto_increment); 236insert into t1 values(32766),(NULL); 237delete from t1 where a=32767; 238--error HA_ERR_AUTOINC_ERANGE 239insert into t1 values(NULL); 240drop table t1; 241 242--echo # 243--echo # MDEV-17333 Assertion in update_auto_increment() upon exotic LOAD 244--echo # 245--source include/have_partition.inc 246let $mysqld_datadir= `select @@datadir`; 247--write_file $mysqld_datadir/test/load.data 2481 1 2490 2 2503 3 2514 1 2520 1 2536 6 254EOF 255create or replace table t1 (pk int auto_increment, x int, primary key(pk), unique key(x)) 256with system versioning partition by system_time interval 2 day 257(partition p1 history, partition pn current); 258load data infile 'load.data' ignore into table t1; 259--remove_file $mysqld_datadir/test/load.data 260drop table t1; 261