[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 903
  • Last Modified:

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?
  • 3
  • 3
1 Solution
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?)

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 !


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now