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 <spame...@gmail.com>:
> 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 <spame...@gmail.com>:
>> 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
