List:General Discussion« Previous MessageNext Message »
From:spameden Date:March 12 2013 10:22pm
Subject:Re: auto_increment field behavior
View as plain text  
Also, forget to quote from the docs
(http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html)

"With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1
(“consecutive”), the auto-increment values generated by any given
statement will be consecutive, without gaps, because the table-level
AUTO-INC lock is held until the end of the statement, and only one
such statement can execute at a time. "

So I believe this is a bug in MySQL because there were no parallel
INSERTs at all.

Sorry for the spam :)

2013/3/13 spameden <spameden@stripped>:
> After setting innodb_autoinc_lock_mode=0 it seems to start working as
> expected for me:
>
> mysql> show variables like 'innodb_autoinc_lock_mode';
> +--------------------------+-------+
> | Variable_name            | Value |
> +--------------------------+-------+
> | innodb_autoinc_lock_mode | 0     |
> +--------------------------+-------+
> 1 row in set (0.00 sec)
>
> mysql> truncate test;
> Query OK, 0 rows affected (0.01 sec)
>
> mysql> load data infile '/tmp/ABC3x' into table test fields terminated
> by ';' enclosed by '#' lines terminated by '\r\n'
> (@var1,@var2,@var3,@var4,@var5,@var6,@var7) SET pc_type='ABC';
> Query OK, 17922 rows affected (0.21 sec)
> Records: 17922  Deleted: 0  Skipped: 0  Warnings: 0
>
> mysql> show create table test;
>
> +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | Table | Create Table
>
>                                                                     |
>
> +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> | test  | CREATE TABLE `test` (
>   `pc_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
>   `pc_type` enum('ABC','DEF') DEFAULT NULL,
>   PRIMARY KEY (`pc_id`)
> ) ENGINE=InnoDB AUTO_INCREMENT=17923 DEFAULT CHARSET=utf8 |
>
> +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> 1 row in set (0.00 sec)
>
> Shame it's a read-only variable and need to restart whole MySQL server.
>
>
> 2013/3/13 spameden <spameden@stripped>:
>> 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 <spameden@stripped>:
>>> 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