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".

Page generated in 0.052 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.