1SET DEFAULT_STORAGE_ENGINE='tokudb'; 2drop table if exists t1; 3drop table if exists t2; 4SET SQL_WARNINGS=1; 5set tokudb_load_save_space=1; 6create table t1 (a int not null auto_increment,b int, primary key (a)) engine=tokudb auto_increment=3; 7insert into t1 values (1,1),(NULL,3),(NULL,4); 8delete from t1 where a=4; 9insert into t1 values (NULL,5),(NULL,6); 10select * from t1; 11a b 121 1 133 3 146 5 157 6 16delete from t1 where a=6; 17replace t1 values (3,1); 18ALTER TABLE t1 add c int; 19replace t1 values (3,3,3); 20insert into t1 values (NULL,7,7); 21update t1 set a=9,b=b+1,c=c+1 where a=7; 22insert into t1 values (NULL,10,10); 23select * from t1; 24a b c 251 1 NULL 263 3 3 278 7 7 289 7 NULL 2910 10 10 30drop table t1; 31create table t1 ( 32skey tinyint unsigned NOT NULL auto_increment PRIMARY KEY, 33sval char(20) 34); 35insert into t1 values (NULL, "hello"); 36insert into t1 values (NULL, "hey"); 37select * from t1; 38skey sval 391 hello 402 hey 41select _rowid,t1._rowid,skey,sval from t1; 42_rowid _rowid skey sval 431 1 1 hello 442 2 2 hey 45drop table t1; 46create table t1 (a char(10) not null, b int not null auto_increment, primary key(a,b)); 47insert into t1 values ("a",1),("b",2),("a",2),("c",1); 48insert into t1 values ("a",NULL),("b",NULL),("c",NULL),("e",NULL); 49insert into t1 (a) values ("a"),("b"),("c"),("d"); 50insert into t1 (a) values ('k'),('d'); 51insert into t1 (a) values ("a"); 52insert into t1 values ("d",last_insert_id()); 53select * from t1; 54a b 55a 1 56a 2 57a 3 58a 4 59a 5 60b 2 61b 3 62b 4 63c 1 64c 2 65c 3 66d 1 67d 2 68d 5 69e 1 70k 1 71drop table t1; 72create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ordid), index(ord,ordid)); 73insert into t1 (ordid,ord) values (NULL,'sdj'),(NULL,'sdj'); 74select * from t1; 75ordid ord 761 sdj 772 sdj 78drop table t1; 79create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)); 80insert into t1 values (NULL,'sdj'),(NULL,'sdj'),(NULL,"abc"),(NULL,'abc'),(NULL,'zzz'),(NULL,'sdj'),(NULL,'abc'); 81select * from t1; 82ordid ord 831 abc 842 abc 853 abc 861 sdj 872 sdj 883 sdj 891 zzz 90drop table t1; 91create table t1 (sid char(5), id int(2) NOT NULL auto_increment, key(sid, id)); 92create table t2 (sid char(20), id int(2)); 93insert into t2 values ('skr',NULL),('skr',NULL),('test',NULL); 94insert into t1 select * from t2; 95select * from t1; 96sid id 97skr 1 98skr 2 99test 1 100drop table t1,t2; 101create table t1 (a int not null primary key auto_increment); 102insert into t1 values (0); 103update t1 set a=0; 104select * from t1; 105a 1060 107check table t1; 108Table Op Msg_type Msg_text 109test.t1 check status OK 110drop table t1; 111create table t1 (a int not null auto_increment primary key); 112insert into t1 values (NULL); 113insert into t1 values (-1); 114select last_insert_id(); 115last_insert_id() 1161 117insert into t1 values (NULL); 118select * from t1; 119a 120-1 1211 1222 123drop table t1; 124create table t1 (a int not null auto_increment primary key); 125insert into t1 values (NULL); 126insert into t1 values (-1); 127select last_insert_id(); 128last_insert_id() 1291 130insert into t1 values (NULL); 131select * from t1; 132a 133-1 1341 1352 136drop table t1; 137create table t1 (i tinyint unsigned not null auto_increment primary key); 138insert into t1 set i = 254; 139insert into t1 set i = null; 140select last_insert_id(); 141last_insert_id() 142255 143explain extended select last_insert_id(); 144id select_type table type possible_keys key key_len ref rows filtered Extra 1451 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used 146Warnings: 147Note 1003 select last_insert_id() AS `last_insert_id()` 148insert into t1 set i = 254; 149ERROR 23000: Duplicate entry '254' for key 'PRIMARY' 150select last_insert_id(); 151last_insert_id() 152255 153insert into t1 set i = null; 154Got one of the listed errors 155select last_insert_id(); 156last_insert_id() 157255 158drop table t1; 159create table t1 (i tinyint unsigned not null auto_increment, key (i)); 160insert into t1 set i = 254; 161insert into t1 set i = null; 162select last_insert_id(); 163last_insert_id() 164255 165insert into t1 set i = null; 166ERROR 22003: Out of range value for column 'i' at row 1 167select last_insert_id(); 168last_insert_id() 169255 170drop table t1; 171create table t1 (i tinyint unsigned not null auto_increment primary key, b int, unique (b)); 172insert into t1 values (NULL, 10); 173select last_insert_id(); 174last_insert_id() 1751 176insert into t1 values (NULL, 15); 177select last_insert_id(); 178last_insert_id() 1792 180insert into t1 values (NULL, 10); 181ERROR 23000: Duplicate entry '10' for key 'b' 182select last_insert_id(); 183last_insert_id() 1842 185drop table t1; 186create table t1(a int auto_increment,b int null,primary key(a)); 187SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO; 188insert into t1(a,b)values(NULL,1); 189insert into t1(a,b)values(200,2); 190insert into t1(a,b)values(0,3); 191insert into t1(b)values(4); 192insert into t1(b)values(5); 193insert into t1(b)values(6); 194insert into t1(b)values(7); 195select * from t1 order by b; 196a b 1971 1 198200 2 1990 3 200201 4 201202 5 202203 6 203204 7 204alter table t1 modify b mediumint; 205select * from t1 order by b; 206a b 2071 1 208200 2 2090 3 210201 4 211202 5 212203 6 213204 7 214create table t2 (a int); 215insert t2 values (1),(2); 216alter table t2 add b int auto_increment primary key; 217select * from t2; 218a b 2191 1 2202 2 221drop table t2; 222delete from t1 where a=0; 223update t1 set a=0 where b=5; 224select * from t1 order by b; 225a b 2261 1 227200 2 228201 4 2290 5 230203 6 231204 7 232delete from t1 where a=0; 233update t1 set a=NULL where b=6; 234Warnings: 235Warning 1048 Column 'a' cannot be null 236update t1 set a=300 where b=7; 237SET SQL_MODE=''; 238insert into t1(a,b)values(NULL,8); 239insert into t1(a,b)values(400,9); 240insert into t1(a,b)values(0,10); 241insert into t1(b)values(11); 242insert into t1(b)values(12); 243insert into t1(b)values(13); 244insert into t1(b)values(14); 245select * from t1 order by b; 246a b 2471 1 248200 2 249201 4 2500 6 251300 7 252301 8 253400 9 254401 10 255402 11 256403 12 257404 13 258405 14 259delete from t1 where a=0; 260update t1 set a=0 where b=12; 261select * from t1 order by b; 262a b 2631 1 264200 2 265201 4 266300 7 267301 8 268400 9 269401 10 270402 11 2710 12 272404 13 273405 14 274delete from t1 where a=0; 275update t1 set a=NULL where b=13; 276Warnings: 277Warning 1048 Column 'a' cannot be null 278update t1 set a=500 where b=14; 279select * from t1 order by b; 280a b 2811 1 282200 2 283201 4 284300 7 285301 8 286400 9 287401 10 288402 11 2890 13 290500 14 291drop table t1; 292create table t1 (a bigint); 293insert into t1 values (1), (2), (3), (NULL), (NULL); 294alter table t1 modify a bigint not null auto_increment primary key; 295select * from t1; 296a 2971 2982 2993 3004 3015 302drop table t1; 303create table t1 (a bigint); 304insert into t1 values (1), (2), (3), (0), (0); 305alter table t1 modify a bigint not null auto_increment primary key; 306select * from t1; 307a 3081 3092 3103 3114 3125 313drop table t1; 314create table t1 (a bigint); 315insert into t1 values (0), (1), (2), (3); 316set sql_mode=NO_AUTO_VALUE_ON_ZERO; 317alter table t1 modify a bigint not null auto_increment primary key; 318set sql_mode= ''; 319select * from t1; 320a 3210 3221 3232 3243 325drop table t1; 326create table t1 (a int auto_increment primary key , b int null); 327set sql_mode=NO_AUTO_VALUE_ON_ZERO; 328insert into t1 values (0,1),(1,2),(2,3); 329select * from t1; 330a b 3310 1 3321 2 3332 3 334set sql_mode= ''; 335alter table t1 modify b varchar(255); 336insert into t1 values (0,4); 337select * from t1; 338a b 3390 1 3401 2 3412 3 3423 4 343drop table t1; 344CREATE TABLE t1 ( a INT AUTO_INCREMENT, b BLOB, PRIMARY KEY (a,b(10))); 345INSERT INTO t1 (b) VALUES ('aaaa'); 346CHECK TABLE t1; 347Table Op Msg_type Msg_text 348test.t1 check status OK 349INSERT INTO t1 (b) VALUES (''); 350CHECK TABLE t1; 351Table Op Msg_type Msg_text 352test.t1 check status OK 353INSERT INTO t1 (b) VALUES ('bbbb'); 354CHECK TABLE t1; 355Table Op Msg_type Msg_text 356test.t1 check status OK 357DROP TABLE IF EXISTS t1; 358CREATE TABLE `t1` ( 359t1_name VARCHAR(255) DEFAULT NULL, 360t1_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 361KEY (t1_name), 362PRIMARY KEY (t1_id) 363) AUTO_INCREMENT = 1000; 364INSERT INTO t1 (t1_name) VALUES('MySQL'); 365INSERT INTO t1 (t1_name) VALUES('MySQL'); 366INSERT INTO t1 (t1_name) VALUES('MySQL'); 367SELECT * from t1; 368t1_name t1_id 369MySQL 1000 370MySQL 1001 371MySQL 1002 372SHOW CREATE TABLE `t1`; 373Table Create Table 374t1 CREATE TABLE `t1` ( 375 `t1_name` varchar(255) DEFAULT NULL, 376 `t1_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 377 PRIMARY KEY (`t1_id`), 378 KEY `t1_name` (`t1_name`) 379) ENGINE=TokuDB AUTO_INCREMENT=1003 DEFAULT CHARSET=latin1 380DROP TABLE `t1`; 381create table t1(a int not null auto_increment primary key); 382create table t2(a int not null auto_increment primary key, t1a int); 383insert into t1 values(NULL); 384insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); 385insert into t1 values (NULL); 386insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), 387(NULL, LAST_INSERT_ID()); 388insert into t1 values (NULL); 389insert into t2 values (NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()), 390(NULL, LAST_INSERT_ID()), (NULL, LAST_INSERT_ID()); 391select * from t2; 392a t1a 3931 1 3942 1 3953 2 3964 2 3975 2 3986 3 3997 3 4008 3 4019 3 402drop table t1, t2; 403End of 4.1 tests 404CREATE TABLE t1 ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL,PRIMARY KEY (`a`),UNIQUE KEY `b` (`b`)); 405insert into t1 (b) values (1); 406replace into t1 (b) values (2), (1), (3); 407select * from t1; 408a b 4093 1 4102 2 4114 3 412truncate table t1; 413insert into t1 (b) values (1); 414replace into t1 (b) values (2); 415replace into t1 (b) values (1); 416replace into t1 (b) values (3); 417select * from t1; 418a b 4193 1 4202 2 4214 3 422drop table t1; 423create table t1 (rowid int not null auto_increment, val int not null,primary 424key (rowid), unique(val)); 425replace into t1 (val) values ('1'),('2'); 426replace into t1 (val) values ('1'),('2'); 427insert into t1 (val) values ('1'),('2'); 428ERROR 23000: Duplicate entry '1' for key 'val' 429select * from t1; 430rowid val 4313 1 4324 2 433drop table t1; 434create table t1 (a int not null auto_increment primary key, val int); 435insert into t1 (val) values (1); 436update t1 set a=2 where a=1; 437insert into t1 (val) values (1); 438select * from t1; 439a val 4402 1 4413 1 442drop table t1; 443CREATE TABLE t1 (t1 INT(10) PRIMARY KEY, t2 INT(10)); 444INSERT INTO t1 VALUES(0, 0); 445INSERT INTO t1 VALUES(1, 1); 446ALTER TABLE t1 CHANGE t1 t1 INT(10) auto_increment; 447ERROR 23000: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY' 448DROP TABLE t1; 449create table t1 (a int primary key auto_increment, b int, c int, d timestamp default current_timestamp, unique(b),unique(c)); 450insert into t1 values(null,1,1,now()); 451insert into t1 values(null,0,0,null); 452replace into t1 values(null,1,0,null); 453select last_insert_id(); 454last_insert_id() 4553 456drop table t1; 457