Also, forget to quote from the docs (http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html)
"With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time. " So I believe this is a bug in MySQL because there were no parallel INSERTs at all. Sorry for the spam :) 2013/3/13 spameden <spame...@gmail.com>: > After setting innodb_autoinc_lock_mode=0 it seems to start working as > expected for me: > > mysql> show variables like 'innodb_autoinc_lock_mode'; > +--------------------------+-------+ > | Variable_name | Value | > +--------------------------+-------+ > | innodb_autoinc_lock_mode | 0 | > +--------------------------+-------+ > 1 row in set (0.00 sec) > > mysql> truncate test; > Query OK, 0 rows affected (0.01 sec) > > mysql> load data infile '/tmp/ABC3x' into table test fields terminated > by ';' enclosed by '#' lines terminated by '\r\n' > (@var1,@var2,@var3,@var4,@var5,@var6,@var7) SET pc_type='ABC'; > Query OK, 17922 rows affected (0.21 sec) > Records: 17922 Deleted: 0 Skipped: 0 Warnings: 0 > > mysql> show create table test; > +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | Table | Create Table > > | > +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > | test | CREATE TABLE `test` ( > `pc_id` int(11) unsigned NOT NULL AUTO_INCREMENT, > `pc_type` enum('ABC','DEF') DEFAULT NULL, > PRIMARY KEY (`pc_id`) > ) ENGINE=InnoDB AUTO_INCREMENT=17923 DEFAULT CHARSET=utf8 | > +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ > 1 row in set (0.00 sec) > > Shame it's a read-only variable and need to restart whole MySQL server. > > > 2013/3/13 spameden <spame...@gmail.com>: >> Nevermind, I've found the bug: >> >> http://bugs.mysql.com/bug.php?id=57643 >> >> I'm gonna subscribe for it and see if it's gonna be resolved. >> >> Many thanks guys for all your assistance! >> >> 2013/3/13 spameden <spame...@gmail.com>: >>> 2013/3/13 Rick James <rja...@yahoo-inc.com>: >>>> AUTO_INCREMENT guarantees that it will not assign the same number twice. >>>> That's about all it is willing to guarantee. >>>> >>>> With InnoDB, if a transaction starts, uses an auto_inc value, then rolls >>>> back, that id is lost. >>> >>> True, but if you do not specify START TRANSACTION I believe it's done >>> automatically? Nothing rolled back for me for that table and noone has >>> been using it except me. >>> >>>> >>>> When you have multiple threads loading data into the same table, diff >>>> values of innodb_autoinc_lock_mode give you diff tradeoff between speed >>>> and predictability. If replication is involved, you want predictability. >>> >>> No, I do not have multiple threads, only 1. >>> >>>> >>>> InnoDB and MyISAM act differently, especially after recovering from a >>>> crash. >>> >>> I understand the difference between InnoDB and MyISAM. InnoDB is a >>> transactional DB engine with single row-level locking. >>> >>>> >>>> If you DELETE the _highest_ id, then restart the server, that id will be >>>> reused. (This is irritating to some people.) Otherwise, a deleted id >>>> will not be reused. >>> >>> I didn't DELETE anything! The only actions I did: >>> >>> 1. Created the TABLE >>> 2. used LOAD FILE only via command line (1 thread) >>> >>> So is it normal or should I fill a bug? >>>> >>>> There may be more. Most of those are covered here: >>>> http://mysql.rjweb.org/doc.php/ricksrots >>>> >>>> >>>> >>>> >>>>> -----Original Message----- >>>>> From: spameden [mailto:spame...@gmail.com] >>>>> Sent: Tuesday, March 12, 2013 2:46 PM >>>>> To: Rick James >>>>> Cc: mysql@lists.mysql.com >>>>> Subject: Re: auto_increment field behavior >>>>> >>>>> 2013/3/13 Rick James <rja...@yahoo-inc.com>: >>>>> > What settings? (innodb_autoinc_lock_mode comes to mind, but there >>>>> may >>>>> > be others.) >>>>> Hi, Rick. >>>>> >>>>> Many thanks for the quick answer here is my settings: >>>>> >>>>> mysql> show variables like '%inc%'; >>>>> +-----------------------------+-------+ >>>>> | Variable_name | Value | >>>>> +-----------------------------+-------+ >>>>> | auto_increment_increment | 1 | >>>>> | auto_increment_offset | 1 | >>>>> | div_precision_increment | 4 | >>>>> | innodb_autoextend_increment | 8 | >>>>> | innodb_autoinc_lock_mode | 1 | >>>>> +-----------------------------+-------+ >>>>> 5 rows in set (0.00 sec) >>>>> >>>>> >>>>> > >>>>> > It is acceptable, by the definition of AUTO_INCREMENT, for it to burn >>>>> the missing 15K ids. >>>>> >>>>> I don't get this explanation, could you please explain bit more? So >>>>> it's completely normal for AUTO_INCREMENT field to act like this? >>>>> >>>>> >>>>> > >>>>> >> -----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