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
