ComuneLucca
asked on
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?
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?
HuyBD
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.
5) RESTORE the INDEXES.
6) RELOAD the TABLE.
1) Drop the INDEXES of that table.
2) Load the TABLE from your script.
3) Set all values to ZERO.
4) DUMP the TABLE.
5) RESTORE the INDEXES.
6) RELOAD the TABLE.
ASKER
>HuyBD:
>change PK column to normal before backup then change back
>Nellios:
>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?)
thanks
>change PK column to normal before backup then change back
>Nellios:
>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?)
thanks
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).
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).
ASKER
>Nellios:
>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.
>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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.