1DROP TABLE IF EXISTS t1; 2#--------------------------- 3# auto_increment_offset 4#--------------------------- 5SET auto_increment_offset = 200; 6CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=rocksdb; 7INSERT INTO t1 (a,b) VALUES (NULL,'a'),(NULL,'b'),(NULL,'c'); 8SELECT LAST_INSERT_ID(); 9LAST_INSERT_ID() 101 11SELECT a,b FROM t1 ORDER BY a; 12a b 131 a 142 b 153 c 16#--------------------------- 17# auto_increment_increment 18#--------------------------- 19SET auto_increment_increment = 300; 20INSERT INTO t1 (a,b) VALUES (NULL,'d'),(NULL,'e'),(NULL,'f'); 21SELECT LAST_INSERT_ID(); 22LAST_INSERT_ID() 23200 24SELECT a,b FROM t1 ORDER BY a; 25a b 261 a 272 b 283 c 29200 d 30500 e 31800 f 32SET auto_increment_increment = 50; 33INSERT INTO t1 (a,b) VALUES (NULL,'g'),(NULL,'h'),(NULL,'i'); 34SELECT LAST_INSERT_ID(); 35LAST_INSERT_ID() 36850 37SELECT a,b FROM t1 ORDER BY a; 38a b 391 a 402 b 413 c 42200 d 43500 e 44800 f 45850 g 46900 h 47950 i 48DROP TABLE t1; 49#--------------------------- 50# offset is greater than the max value 51#--------------------------- 52SET auto_increment_increment = 500; 53SET auto_increment_offset = 300; 54CREATE TABLE t1 (a TINYINT AUTO_INCREMENT PRIMARY KEY) ENGINE=rocksdb; 55# In MariaDB, this is an error: 56INSERT INTO t1 (a) VALUES (NULL); 57ERROR 22003: Out of range value for column 'a' at row 1 58SELECT LAST_INSERT_ID(); 59LAST_INSERT_ID() 60850 61SELECT a FROM t1 ORDER BY a; 62a 63DROP TABLE t1; 64#--------------------------- 65# test large autoincrement values 66#--------------------------- 67SET auto_increment_increment = 1; 68SET auto_increment_offset = 1; 69CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=rocksdb; 70INSERT INTO t1 VALUES (18446744073709551613, 'a'); 71SHOW CREATE TABLE t1; 72Table Create Table 73t1 CREATE TABLE `t1` ( 74 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 75 `b` char(8) DEFAULT NULL, 76 PRIMARY KEY (`a`) 77) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=latin1 78INSERT INTO t1 VALUES (NULL, 'b'); 79SHOW CREATE TABLE t1; 80Table Create Table 81t1 CREATE TABLE `t1` ( 82 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 83 `b` char(8) DEFAULT NULL, 84 PRIMARY KEY (`a`) 85) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 86INSERT INTO t1 VALUES (NULL, 'c'); 87ERROR HY000: Failed to read auto-increment value from storage engine 88SELECT * FROM t1; 89a b 9018446744073709551613 a 9118446744073709551614 b 92DROP TABLE t1; 93SET auto_increment_increment = 300; 94CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=rocksdb; 95INSERT INTO t1 VALUES (18446744073709551613, 'a'); 96SHOW CREATE TABLE t1; 97Table Create Table 98t1 CREATE TABLE `t1` ( 99 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 100 `b` char(8) DEFAULT NULL, 101 PRIMARY KEY (`a`) 102) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=latin1 103INSERT INTO t1 VALUES (NULL, 'b'); 104ERROR HY000: Failed to read auto-increment value from storage engine 105SHOW CREATE TABLE t1; 106Table Create Table 107t1 CREATE TABLE `t1` ( 108 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 109 `b` char(8) DEFAULT NULL, 110 PRIMARY KEY (`a`) 111) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 112INSERT INTO t1 VALUES (NULL, 'c'); 113ERROR HY000: Failed to read auto-increment value from storage engine 114SELECT * FROM t1; 115a b 11618446744073709551613 a 117DROP TABLE t1; 118SET auto_increment_offset = 200; 119CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=rocksdb; 120INSERT INTO t1 VALUES (18446744073709551613, 'a'); 121SHOW CREATE TABLE t1; 122Table Create Table 123t1 CREATE TABLE `t1` ( 124 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 125 `b` char(8) DEFAULT NULL, 126 PRIMARY KEY (`a`) 127) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=latin1 128INSERT INTO t1 VALUES (NULL, 'b'); 129ERROR HY000: Failed to read auto-increment value from storage engine 130SHOW CREATE TABLE t1; 131Table Create Table 132t1 CREATE TABLE `t1` ( 133 `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 134 `b` char(8) DEFAULT NULL, 135 PRIMARY KEY (`a`) 136) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1 137INSERT INTO t1 VALUES (NULL, 'c'); 138ERROR HY000: Failed to read auto-increment value from storage engine 139SELECT * FROM t1; 140a b 14118446744073709551613 a 142DROP TABLE t1; 143#---------------------------------- 144# Issue #792 Crash in autoincrement 145#---------------------------------- 146CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY,C2 CHAR) ENGINE=ROCKSDB; 147INSERT INTO t1 VALUES(2177,0); 148DROP TABLE t1; 149CREATE TABLE t0(c0 BLOB) ENGINE=ROCKSDB; 150INSERT INTO t0 VALUES(0); 151ALTER TABLE t0 AUTO_INCREMENT=0; 152DROP TABLE t0; 153#--------------------------------------------------------------- 154# MDEV-16703 Assertion failed in load_auto_incr_value_from_index 155#--------------------------------------------------------------- 156CREATE TABLE t1 (pk INT AUTO_INCREMENT, a INT, PRIMARY KEY(pk)) ENGINE=RocksDB; 157INSERT INTO t1 (a) VALUES (1); 158UPDATE t1 SET pk = 3; 159ALTER TABLE t1 AUTO_INCREMENT 2; 160DROP TABLE t1; 161#---------------------------------- 162# Issue #792 Crash in autoincrement 163#---------------------------------- 164CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY,C2 CHAR) ENGINE=ROCKSDB; 165INSERT INTO t1 VALUES(2177,0); 166DROP TABLE t1; 167CREATE TABLE t0(c0 BLOB) ENGINE=ROCKSDB; 168INSERT INTO t0 VALUES(0); 169ALTER TABLE t0 AUTO_INCREMENT=0; 170DROP TABLE t0; 171#---------------------------------- 172# Issue #869 Crash in autoincrement 173#---------------------------------- 174CREATE TABLE t1 (pk INT AUTO_INCREMENT, a INT, PRIMARY KEY(pk)) ENGINE=RocksDB; 175INSERT INTO t1 (a) VALUES (1); 176UPDATE t1 SET pk = 3; 177ALTER TABLE t1 AUTO_INCREMENT 2; 178DROP TABLE t1; 179#---------------------------------- 180# Issue #902 Debug assert in autoincrement with small field type 181#---------------------------------- 182SET auto_increment_increment=100, auto_increment_offset=10; 183CREATE TABLE t1(i INT AUTO_INCREMENT PRIMARY KEY) ENGINE=ROCKSDB AUTO_INCREMENT=18446744073709551615; 184INSERT INTO t1 VALUES (NULL); 185ERROR HY000: Failed to read auto-increment value from storage engine 186SELECT * FROM t1; 187i 188ALTER TABLE t1 AUTO_INCREMENT=1; 189INSERT INTO t1 VALUES (NULL); 190SELECT * FROM t1; 191i 19210 193ALTER TABLE t1 AUTO_INCREMENT=18446744073709551615; 194INSERT INTO t1 VALUES (NULL); 195ERROR HY000: Failed to read auto-increment value from storage engine 196SELECT * FROM t1; 197i 19810 199DROP TABLE t1; 200