Auto Increment Problem

[JoSno]

Hi guys,

I've come across a problem which I believe is to do with the auto increment feature and wondered if anyone else had come across it.

Basically to keep it simple I've got one table with an integer key set to auto increment. I need to have this enabled to allow new records to be added once the system is in it's initial state.

Now to get to that initial state the table has to have a load of records read in from a text file. The problem is regarding where to start the keys from.

(the keys are in the file - I'll come back to that) The first record has key zero and gets entered into the table as it should, the second record in the file has key one but it doesn't get entered into the table. Everything after that (2+) gets entered fine but it leaves me with a table with keys that go 0,2,3,4,5,...

If I change the keys to start at 1 then everything goes fine and I get a table with keys that go 1,2,3,4,5,....

It makes me think it's a problem with the auto-increment not likin gstarting at 0 and being told what to put in, but like I said it is highly desirable to me that this feature is set enabled so that more entries in the future can be added. the other thing is to start from 1 rather than 0 but that does cause a bit of work further back down the chain.

Like i said aswell, the keys are explicitly set in the text file and I could take them out and just rely on MySQL to keep them in the right order but that does lead to the possibility of files getting corrupted or altered badly without it being obvious to me.

Any ideas or known reports on this matter (bug?) would be greatly appreciated

Cheers
Jo

 

[skiflyer]

idea...

turn it off, load your data, turn it on. You don't want to use it for loading your data anyway it seems.

-Rob

 

[JoSno]

Can you turn the auto incrmenet off and on then? Sorry I didn't know i could do that else you're right it would be perfect!

While I'm here could you tell me how to do that pleeease!?

Cheers
Jo - Everyone's favourite snowman

 

[sleipnir214]

You could also try adjusting the starting number of the auto_increment before you perform your data load:

ALTER TABLE tablename AUTO_INCREMENT = 0

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[skiflyer]

Sure...

ALTER TABLE <tablename> CHANGE <columnname> <columnname> <columntype> <other arguments i.e. UNSIGNED NOT NULL> 

then insert the data then
ALTER TABLE <tablename> CHANGE <columnname> <columnname> <columntype> <other arguments i.e. UNSIGNED NOT NULL> AUTO_INCREMENT

And I believe that's it, though you may need to use sleipnir's method of altering the auto_increment to the appropriate level after the inserts... or of course if his method works all by itself this would all be unnecessary.

Of course, you can also just click around phpmyadmin to do it all (that's where I cut'n paste that above code from).

-Rob