Bug #17923 LOAD DATA INFILE do not correctly affect AUTO_INCREMENT field
Submitted: 5 Mar 2006 4:41 Modified: 6 Mar 2006 1:30
Reporter: Liu Yan Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.18 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[5 Mar 2006 4:41] Liu Yan
Description:
I have a text file which contains only 1 fields per line, here is an example of the text file:
203.81.58.122:27015
203.81.54.228:27015
203.81.54.226:27022
203.81.54.226:27018
203.81.53.254:27016
203.95.229.118:27015
203.81.53.254:27015
..............
..............

I want to import it into database, I use MySQL5.0.18_without_installer on Windows XP, and I create a table which contains 2 primary key, one is BIGINT AUTO_INCREMENT, another is VARCHAR, I use LOAD DATA INFILE statement to do this (see the below), but when it done, the AUTO_INCREMENT field (see the below) does not increased correctly.

/*
-- import...
*/
mysql> load data infile 'server all.txt' into table servers lines terminated by '\r\n' (serverAddress);
Query OK, 52672 rows affected (2.31 sec)
Records: 52672  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select distinct(serverid) from servers;
+----------+
| serverid |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
+----------+
4 rows in set (0.13 sec)

mysql> describe servers;
+-----------------+-----------------------+------+-----+---------+----------------+
| Field           | Type                  | Null | Key | Default | Extra          |
+-----------------+-----------------------+------+-----+---------+----------------+
| ServerId        | bigint(20) unsigned   | NO   | PRI | NULL    | auto_increment |
| ServerName      | varchar(100)          | NO   |     |         |                |
| ServerAddress   | varchar(45)           | NO   | PRI |         |                |
| ServerIPString  | varchar(45)           | NO   |     |         |                |
| ServerIP        | int(10) unsigned      | NO   |     | 0       |                |
| ServerPort      | int(10) unsigned      | NO   |     | 0       |                |
| GameDirectory   | varchar(45)           | NO   |     |         |                |
| GameDescription | varchar(45)           | NO   |     |         |                |
| MapName         | varchar(45)           | NO   |     |         |                |
| ModWebsite      | varchar(45)           | NO   |     |         |                |
| IsSecureServer  | tinyint(3) unsigned   | NO   |     | 0       |                |
| ServerType      | tinyint(3) unsigned   | NO   |     | 0       |                |
| ServerOS        | tinyint(3) unsigned   | NO   |     | 0       |                |
| SteamAppId      | int(10) unsigned      | NO   |     | 0       |                |
| IsNeedPassword  | tinyint(3) unsigned   | NO   |     | 0       |                |
| PlayerCount     | mediumint(8) unsigned | NO   |     | 0       |                |
| MaxPlayers      | mediumint(8) unsigned | NO   |     | 0       |                |
| BotCount        | mediumint(8) unsigned | NO   |     | 0       |                |
| ProtocolVersion | mediumint(8) unsigned | NO   |     | 0       |                |
| UpdateTimes     | int(10) unsigned      | NO   |     | 0       |                |
| LastUpdateTime  | datetime              | YES  |     | NULL    |                |
+-----------------+-----------------------+------+-----+---------+----------------+
21 rows in set (0.00 sec)

mysql> select LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

How to repeat:
Step 1. just create a text file named 'server all.txt', fill it with the following text

203.81.58.122:27015
203.81.54.228:27015
203.81.54.226:27022
203.81.54.226:27018
203.81.53.254:27016
203.95.229.118:27015
203.81.53.254:27015

Step 2. Create a table named 'Servers'
CREATE TABLE  `servers` (
  `ServerId` bigint(20) unsigned NOT NULL auto_increment,
  `ServerName` varchar(100) NOT NULL default '',
  `ServerAddress` varchar(45) NOT NULL default '' COMMENT '服务器地址,形势为x.x.x.x:Port',
  `ServerIPString` varchar(45) NOT NULL default '' COMMENT '服务器IP地址(字符串)',
  `ServerIP` int(10) unsigned NOT NULL default '0' COMMENT '服务器IP地址',
  `ServerPort` int(10) unsigned NOT NULL default '0' COMMENT '服务器端口',
  `GameDirectory` varchar(45) NOT NULL default '',
  `GameDescription` varchar(45) NOT NULL default '',
  `MapName` varchar(45) NOT NULL default '',
  `ModWebsite` varchar(45) NOT NULL default '',
  `IsSecureServer` tinyint(3) unsigned NOT NULL default '0',
  `ServerType` tinyint(3) unsigned NOT NULL default '0',
  `ServerOS` tinyint(3) unsigned NOT NULL default '0',
  `SteamAppId` int(10) unsigned NOT NULL default '0',
  `IsNeedPassword` tinyint(3) unsigned NOT NULL default '0',
  `PlayerCount` mediumint(8) unsigned NOT NULL default '0',
  `MaxPlayers` mediumint(8) unsigned NOT NULL default '0',
  `BotCount` mediumint(8) unsigned NOT NULL default '0',
  `ProtocolVersion` mediumint(8) unsigned NOT NULL default '0',
  `UpdateTimes` int(10) unsigned NOT NULL default '0',
  `LastUpdateTime` datetime default NULL,
  PRIMARY KEY  (`ServerAddress`,`ServerId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='服务器';

Step 3. Use LOAD DATA INFILE to import
load data infile 'server all.txt' into table servers lines terminated by '\r\n' (serverAddress);

Step 4. select the ServerID field to see if it it correct or not.
[5 Mar 2006 11:54] Valeriy Kravchuk
Thank you for a problem report. Sorry, but it is not a bug. It is intended and documented behaviour. Read the manual (http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html):

"For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
);

INSERT INTO animals (grp,name) VALUES 
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Note that in this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group."
[6 Mar 2006 1:30] Liu Yan
Very sorry to report a NOT A BUG post, I should check the manual first.
Thank your quickly response, and, sorry again.