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