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
