Problem restoring table in MySQL whith autoincrement fields set to 0.

I'm restoring a databse from a mysqldump.
A simple table has an autoincrement field (id). The values includes a record with id=0, and this cause an error "duplicate entry".
It seems than a value 0 for an autoincrement filed means "get the next values", which is 1 at the beginning, so I have a duplication when going to insert the second record (which has id=1).
Put AUTO_INCREMENT=0 in the table definition dosn't helps.
Any suggestion?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

change PK column to normal before backup then change back
A short workaround could be:

1) Drop the INDEXES of that table.
2) Load the TABLE from your script.
3) Set all values to ZERO.
4) DUMP the TABLE.
ComuneLuccaAuthor Commented:
>change PK column to normal before backup then change back

>A short workaround could be: [...]

What I've already tryed is to remove the "auto_increment", then restore the db, then put auto_increment back (which is what you suggested, rigth?)
BUT it doesn't work.
When I try to modify the table setting auto_increment on field id, I get back the same error (duplicate key). (Maybe mysql checks all records and try to apply a new value to the id=0 record?)

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Nope I didn't propose that.

The basic idea is that the script would work if all ID were either unique or all set to 0.

My proposal shows a fast way to make all 0:

My idea is to allow your table to accept duplicates by removing primary key or unique indices.
Once you do that you will be able to load the script.
Having the table full will allow you to set all values of the field id to 0 ( an update statement).
This way if you dump and restore again with auto_increment activated mysql will handle all records (all records have id set to 0).
ComuneLuccaAuthor Commented:
>This way if you dump and restore again with auto_increment activated mysql will handle all records (all >records have id set to 0).

But this way the records will have new id, rigth?. And this may broke relations with other tables (I don't know the structure of the db).
In other words: in the original database the table had a record with id = 0 (at least this is what I see in the dump...) and I'm not able to reproduce the same in the restored one.
If you have other tables refering to that id means that you already have a problem there, which is also unsolvable. :-(

So I guess what we want is to save the non zero record ids and find a way to give valid values to the rest. How about this ....

IDEA: Load all the non zero records to the table and then load the 0 ones. If we manage to load first the valid ones and then the 0 ones we will not have overlapping ids. So:

load data with droped keys & unique indices and then:

create table pivot select * from original_table;
delete * from original_table where id=0;
delete * from pivot where id<>0;

-- now we have the original table filled with non zero ids and pivot table full of 0s.
-- restore your indices and then:

insert into original_table ( select * from pivot );

There is nothing more u can do I guess.

Hope that helps.
ComuneLuccaAuthor Commented:
Solved !!!

MySQL modes let me use set id to 0!

"NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. (Added in MySQL 4.1.1)

This mode can be useful if 0 has been stored in a table's AUTO_INCREMENT column. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with mysqldump and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. As of MySQL 4.1.1, mysqldump automatically includes a statement in the dump output that enables NO_AUTO_VALUE_ON_ZERO to avoid this problem. "

Which is exactly my situation !


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.