List:General Discussion« Previous MessageNext Message »
From:Rick James Date:March 12 2013 9:40pm
Subject:RE: auto_increment field behavior
View as plain text  
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:spameden@stripped]
> Sent: Tuesday, March 12, 2013 2:34 PM
> To: mysql@stripped
> 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

Thread
auto_increment field behaviorspameden12 Mar
  • Re: auto_increment field behaviorReindl Harald12 Mar
    • Re: auto_increment field behaviorspameden12 Mar
      • Re: auto_increment field behaviorspameden12 Mar
  • RE: auto_increment field behaviorRick James12 Mar
    • Re: auto_increment field behaviorspameden12 Mar
      • RE: auto_increment field behaviorRick James12 Mar
        • Re: auto_increment field behaviorspameden12 Mar
          • Re: auto_increment field behaviorspameden12 Mar
            • Re: auto_increment field behaviorspameden12 Mar
              • Re: auto_increment field behaviorspameden12 Mar