What settings? (innodb_autoinc_lock_mode comes to mind, but there may be others.)
It is acceptable, by the definition of AUTO_INCREMENT, for it to burn the missing 15K ids. > -----Original Message----- > From: spameden [mailto:spame...@gmail.com] > Sent: Tuesday, March 12, 2013 2:34 PM > To: mysql@lists.mysql.com > Subject: auto_increment field behavior > > Hi, I'm running MySQL-5.5 on Ubuntu > > ~ $ mysqld -V > mysqld Ver 5.5.29-0ubuntu0.12.04.2 for debian-linux-gnu on x86_64 > ((Ubuntu)) > > Would like to know if it's normal behavior with auto_increment field > (tried both signed and unsigned now): > > mysql> show create table phone_codes; > +-------------+-------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > --+ > | Table | Create Table > > > > > > > > > | > +-------------+-------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > --+ > | phone_codes | CREATE TABLE `phone_codes` ( > `pc_id` int(11) NOT NULL AUTO_INCREMENT, > `pc_type` enum('ABC','DEF') DEFAULT NULL, > `pc_code` decimal(3,0) NOT NULL, > `pc_from` decimal(7,0) NOT NULL, > `pc_to` decimal(7,0) NOT NULL, > `pc_capacity` decimal(8,0) NOT NULL, > `pc_operator` varchar(255) DEFAULT NULL, > `pc_city` varchar(255) DEFAULT NULL, > `pc_region` varchar(255) DEFAULT NULL, > PRIMARY KEY (`pc_id`), > KEY `pc_code` (`pc_code`), > KEY `pc_code_from_to` (`pc_code`,`pc_from`,`pc_to`), > KEY `pc_operator` (`pc_operator`), > KEY `pc_city` (`pc_city`), > KEY `pc_region` (`pc_region`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | > +-------------+-------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > --+ > 1 row in set (0.00 sec) > > mysql> load data infile '/tmp/ABC3x' into table phone_codes fields > terminated by ';' enclosed by '#' lines terminated by '\r\n' > (pc_code,pc_from,pc_to,pc_capacity,pc_operator,pc_city,pc_region) SET > pc_type='ABC'; Query OK, 17922 rows affected (4.44 sec) > Records: 17922 Deleted: 0 Skipped: 0 Warnings: 0 > > mysql> show create table phone_codes; > +-------------+-------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > -----------------------+ > | Table | Create Table > > > > > > > > > | > +-------------+-------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > -----------------------+ > | phone_codes | CREATE TABLE `phone_codes` ( > `pc_id` int(11) NOT NULL AUTO_INCREMENT, > `pc_type` enum('ABC','DEF') DEFAULT NULL, > `pc_code` decimal(3,0) NOT NULL, > `pc_from` decimal(7,0) NOT NULL, > `pc_to` decimal(7,0) NOT NULL, > `pc_capacity` decimal(8,0) NOT NULL, > `pc_operator` varchar(255) DEFAULT NULL, > `pc_city` varchar(255) DEFAULT NULL, > `pc_region` varchar(255) DEFAULT NULL, > PRIMARY KEY (`pc_id`), > KEY `pc_code` (`pc_code`), > KEY `pc_code_from_to` (`pc_code`,`pc_from`,`pc_to`), > KEY `pc_operator` (`pc_operator`), > KEY `pc_city` (`pc_city`), > KEY `pc_region` (`pc_region`) > ) ENGINE=InnoDB AUTO_INCREMENT=32768 DEFAULT CHARSET=utf8 | > +-------------+-------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > ----------------------------------------------------------------------- > -----------------------+ > 1 row in set (0.00 sec) > > NOTE: AUTO_INCREMENT is 32768 instead of 17923 ! So next inserted row > would have pc_id=32768. > > Please suggest if it's normal behavior or not. > > Many thanks. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
