Subject | Re: "database [database name] shutdown" - solution |
---|---|
Author | spongebob7487 |
Post date | 2005-09-01T15:53:14Z |
Ok. We've discovered that the issue is due to the fact that a table
has more than one row with the same primary key value in it. It
sounds impossible but it is happening. The restore then fails when
it tries to reactivate the primary key constraint for the table. The
error message is something like:
gbak: ERROR: attempt to store duplicate value (visible to active
transactions) in unique index "RDB$PRIMARY1259"
gbak: Index "RDB$PRIMARY1259" failed to activate because:
gbak: The unique index has duplicate values or NULLs.
gbak: Delete or Update duplicate values or NULLs, and activate
index with
gbak: ALTER INDEX "RDB$PRIMARY1259" ACTIVE;
action cancelled by trigger (3) to preserve data integrity
-Cannot deactivate primary index
We can fix the issue by dropping the primary key constraint on the
table and finding the duplicated row using SQL like this:
-- Drop the primary key constraint so that the duplicated keys are
shown.
-- You can't find them without doing this for some weird reason.
alter table myTableName drop constraint pk_name;
commit;
-- Find all rows with duplicate keys
select keyColumn1, keyColumn2, count(*) from myTableName
group by keyColumn1, keyColumn2
having count(*) > 1
This SQL shows us what the primary key values are and how many
duplicated rows there are for the given table. We then manually
delete the unwanted rows and re-add the primary key constraint back.
The backup and restore process then completes successfully. We also
could have used the -i switch with GBAK to get the restore to work
but then we would have had to manually activate each index. We
prefer to go this route.
Anyway... I wanted to thank everyone for their time and suggestions.
Hopefully this post will save someone else some time in the future.
Matt
has more than one row with the same primary key value in it. It
sounds impossible but it is happening. The restore then fails when
it tries to reactivate the primary key constraint for the table. The
error message is something like:
gbak: ERROR: attempt to store duplicate value (visible to active
transactions) in unique index "RDB$PRIMARY1259"
gbak: Index "RDB$PRIMARY1259" failed to activate because:
gbak: The unique index has duplicate values or NULLs.
gbak: Delete or Update duplicate values or NULLs, and activate
index with
gbak: ALTER INDEX "RDB$PRIMARY1259" ACTIVE;
action cancelled by trigger (3) to preserve data integrity
-Cannot deactivate primary index
We can fix the issue by dropping the primary key constraint on the
table and finding the duplicated row using SQL like this:
-- Drop the primary key constraint so that the duplicated keys are
shown.
-- You can't find them without doing this for some weird reason.
alter table myTableName drop constraint pk_name;
commit;
-- Find all rows with duplicate keys
select keyColumn1, keyColumn2, count(*) from myTableName
group by keyColumn1, keyColumn2
having count(*) > 1
This SQL shows us what the primary key values are and how many
duplicated rows there are for the given table. We then manually
delete the unwanted rows and re-add the primary key constraint back.
The backup and restore process then completes successfully. We also
could have used the -i switch with GBAK to get the restore to work
but then we would have had to manually activate each index. We
prefer to go this route.
Anyway... I wanted to thank everyone for their time and suggestions.
Hopefully this post will save someone else some time in the future.
Matt