CODEWITHSUNDEEP
mysqlinnodbauto-increment
Nithin S
Nithin S

Reputation: 21

Auto increment value get set to ' 18446744073709551615 ' with multiple load data infile

I am facing one problem with one of my project.I have a inventory upload which import data from csv to a innodb table. What happened here is with a multiple load data command (i suppose) the auto increment value get sets to 18446744073709551615 and not letting other insert to work. It was working fine before. I am not sure if the large amount of data in the table creates this issue or not.

Details of the table is as follows

Software version: 5.5.31-0ubuntu0.12.04.1-log - (Ubuntu)

largest insert id used : 17455787099

number of rows in the table : 23887371

some variables realted to this are as follows

auto increment increment 1 auto increment offset 1 autocommit ON automatic sp privileges ON innodb autoextend increment 8 innodb autoinc lock mode 1 sql auto is null OFF Documentation

i have removed the delete queries from the table. But still the autoincriment is out of sync

any help is much appreciated

thanks

Nithin

Upvotes: 2

Views: 636

Answers (1)

Mihai
Mihai

Reputation: 26784

the auto increment value get sets to 18446744073709551615-largest insert id used : 17455787099

This is a bit confusing.

Try resetting the auto increment value before loading new files:

ALTER TABLE tableName AUTO_INCREMENT = 1

EDIT:

Create a new identical table :

CREATE TABLE tableName LIKE oldtableName;

Copy all rows to the new table, remember to not select the auto_incrementing id.

INSERT INTO tableName (field2, field3, field4)
SELECT field2, field3, field4 FROM oldtableName ORDER BY oldtableName.id;
DROP oldtableName;
RENAME tableName oldtableName;

This will take a while(hours..or more).

EDIT2

If your id column is not referenced by anything

ALTER TABLE tableName DROP id

ALTER TABLE tableName ADD id INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id), AUTO_INCREMENT=1

Upvotes: 2

Related Questions