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.
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.
InnoDB and MyISAM act differently, especially after recovering from a crash.
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.
There may be more. Most of those are covered here:
http://mysql.rjweb.org/doc.php/ricksrots
> -----Original Message-----
> From: spameden [mailto:[email protected]]
> Sent: Tuesday, March 12, 2013 2:46 PM
> To: Rick James
> Cc: [email protected]
> Subject: Re: auto_increment field behavior
>
> 2013/3/13 Rick James <[email protected]>:
> > 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:[email protected]]
> >> Sent: Tuesday, March 12, 2013 2:34 PM
> >> To: [email protected]
> >> 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
