MySQL Bugs: #62612: LOAD DATA INFILE JUMP Sequencing AUTOINCREMENT

Bug #62612	LOAD DATA INFILE JUMP Sequencing AUTOINCREMENT
Submitted:	3 Oct 2011 20:40	Modified:	3 Nov 2011 23:43
Reporter:	Richard Perez	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.5.15	OS:	Linux (mysql installed with binary source)
Assigned to:		CPU Architecture:	Any
Tags:	LOAD DATA INFILE

Description:
I am reading a information from a file and inserting in a table using LOAD DATA INFILE, the procedure works with no problem, but after I apply the same procedure the sequencing of the AUTOINCREMENT field jump some numbers.

This is a example data I have in a file called lin.txt

1,1,100,2011-09-21,2011-06-15,2011-10-03,2011-10-03
1,2,87,2011-09-12,2011-06-17,2011-10-03,2011-10-03
1,3,105,2011-09-16,2011-06-17,2011-10-03,2011-10-03
1,4,98,2011-09-18,2011-06-17,2011-10-03,2011-10-03
1,5,97,2011-09-23,2011-06-17,2011-10-03,2011-10-03

The table have 8 columns, where the first one is Autoincrement

So I execute this:

LOAD DATA INFILE 'lin.txt'
INTO TABLE tbl1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(c1, c2, c3, c4, c5, c6, c7);

And after make a query get this:

1,1,1,100,2011-09-21,2011-06-15,"2011-10-03 00:00:00",2011-10-03
2,1,2,87,2011-09-12,2011-06-17,"2011-10-03 00:00:00",2011-10-03
3,1,3,105,2011-09-16,2011-06-17,"2011-10-03 00:00:00",2011-10-03
4,1,4,98,2011-09-18,2011-06-17,"2011-10-03 00:00:00",2011-10-03
5,1,5,97,2011-09-23,2011-06-17,"2011-10-03 00:00:00",2011-10-03

If you see the first column have the Id of the table which are okay (1,2,3,4,5)

No problem until now, but If I execute again the sentence:

LOAD DATA INFILE 'lin.txt'
INTO TABLE tbl1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(c1, c2, c3, c4, c5, c6, c7);

and make a query over the table I get this

1,1,1,100,2011-09-21,2011-06-15,"2011-10-03 00:00:00",2011-10-03
2,1,2,87,2011-09-12,2011-06-17,"2011-10-03 00:00:00",2011-10-03
3,1,3,105,2011-09-16,2011-06-17,"2011-10-03 00:00:00",2011-10-03
4,1,4,98,2011-09-18,2011-06-17,"2011-10-03 00:00:00",2011-10-03
5,1,5,97,2011-09-23,2011-06-17,"2011-10-03 00:00:00",2011-10-03
8,1,1,100,2011-09-21,2011-06-15,"2011-10-03 00:00:00",2011-10-03
9,1,2,87,2011-09-12,2011-06-17,"2011-10-03 00:00:00",2011-10-03
10,1,3,105,2011-09-16,2011-06-17,"2011-10-03 00:00:00",2011-10-03
11,1,4,98,2011-09-18,2011-06-17,"2011-10-03 00:00:00",2011-10-03
12,1,5,97,2011-09-23,2011-06-17,"2011-10-03 00:00:00",2011-10-03

if you see in the first column after the 5 jump to 8, why? and if you keep applying the script it will jump and jump....

How to repeat:
This is a example data I have in a file called lin.txt

1,1,100,2011-09-21,2011-06-15,2011-10-03,2011-10-03
1,2,87,2011-09-12,2011-06-17,2011-10-03,2011-10-03
1,3,105,2011-09-16,2011-06-17,2011-10-03,2011-10-03
1,4,98,2011-09-18,2011-06-17,2011-10-03,2011-10-03
1,5,97,2011-09-23,2011-06-17,2011-10-03,2011-10-03

The table have 8 columns, where the first one is Autoincrement

So I execute this:

LOAD DATA INFILE 'lin.txt'
INTO TABLE tbl1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(c1, c2, c3, c4, c5, c6, c7);

And after make a query get this:

1,1,1,100,2011-09-21,2011-06-15,"2011-10-03 00:00:00",2011-10-03
2,1,2,87,2011-09-12,2011-06-17,"2011-10-03 00:00:00",2011-10-03
3,1,3,105,2011-09-16,2011-06-17,"2011-10-03 00:00:00",2011-10-03
4,1,4,98,2011-09-18,2011-06-17,"2011-10-03 00:00:00",2011-10-03
5,1,5,97,2011-09-23,2011-06-17,"2011-10-03 00:00:00",2011-10-03

If you see the first column have the Id of the table which are okay (1,2,3,4,5)

No problem until now, but If I execute again the sentence:

LOAD DATA INFILE 'lin.txt'
INTO TABLE tbl1
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(c1, c2, c3, c4, c5, c6, c7);

and make a query over the table I get this

1,1,1,100,2011-09-21,2011-06-15,"2011-10-03 00:00:00",2011-10-03
2,1,2,87,2011-09-12,2011-06-17,"2011-10-03 00:00:00",2011-10-03
3,1,3,105,2011-09-16,2011-06-17,"2011-10-03 00:00:00",2011-10-03
4,1,4,98,2011-09-18,2011-06-17,"2011-10-03 00:00:00",2011-10-03
5,1,5,97,2011-09-23,2011-06-17,"2011-10-03 00:00:00",2011-10-03
8,1,1,100,2011-09-21,2011-06-15,"2011-10-03 00:00:00",2011-10-03
9,1,2,87,2011-09-12,2011-06-17,"2011-10-03 00:00:00",2011-10-03
10,1,3,105,2011-09-16,2011-06-17,"2011-10-03 00:00:00",2011-10-03
11,1,4,98,2011-09-18,2011-06-17,"2011-10-03 00:00:00",2011-10-03
12,1,5,97,2011-09-23,2011-06-17,"2011-10-03 00:00:00",2011-10-03

if you see in the first column after the 5 jump to 8, why? and if you keep applying the script it will jump and jump....

Please check http://bugs.mysql.com/bug.php?id=57643 for duplicate. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".