[firebird-support] Cannot restore a database because of failed unique key index
Hi, We use Firebird 2.5.1 on Linux 64 bit Firebird fails to restore because of a unique constraint( see below ). However when interrogating the data, no duplicate is found. ( see below SQL, unique key is on the grouped by fields ) How can this be? Kind Regards, Robert. SELECT ID_VN, CD_ITM_SPR FROM AS_ITM_SPR GROUP BY ID_VN, CD_ITM_SPR HAVING COUNT(*) > 1 gbak:activating and creating deferred index AK_AS_ITM_SPR gbak:cannot commit index AK_AS_ITM_SPR gbak: ERROR:attempt to store duplicate value (visible to active transactions) in unique index "AK_AS_ITM_SPR" gbak: ERROR:action cancelled by trigger (3) to preserve data integrity gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint gbak:Exiting before completion due to errors
Re: [firebird-support] Cannot restore a database because of failed unique key index
Hello, Your query uses index, which is correct (it contains unique values). Try to run this query on partially restored database. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) > Hi, > > We use Firebird 2.5.1 on Linux 64 bit > > Firebird fails to restore because of a unique constraint( see below ). > However when interrogating the data, no duplicate is found. ( see below SQL, > unique key is on the grouped by fields ) > > How can this be? > > Kind Regards, > > Robert. > > > > SELECT ID_VN, CD_ITM_SPR > FROM AS_ITM_SPR > GROUP BY ID_VN, CD_ITM_SPR > HAVING COUNT(*) > 1 > > > > > gbak:activating and creating deferred index AK_AS_ITM_SPR > gbak:cannot commit index AK_AS_ITM_SPR > gbak: ERROR:attempt to store duplicate value (visible to active transactions) > in unique index "AK_AS_ITM_SPR" > gbak: ERROR:action cancelled by trigger (3) to preserve data integrity > gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint > gbak:Exiting before completion due to errors > > >
Re: [firebird-support] Cannot restore a database because of failed unique key index
> We use Firebird 2.5.1 on Linux 64 bit > > Firebird fails to restore because of a unique constraint( see below ). > However when interrogating the data, no duplicate is found. ( see below SQL, > unique key is on the grouped by fields ) > > How can this be? > > Kind Regards, > > Robert. > > > > SELECT ID_VN, CD_ITM_SPR > FROM AS_ITM_SPR > GROUP BY ID_VN, CD_ITM_SPR > HAVING COUNT(*) > 1 > > > > > gbak:activating and creating deferred index AK_AS_ITM_SPR > gbak:cannot commit index AK_AS_ITM_SPR > gbak: ERROR:attempt to store duplicate value (visible to active transactions) > in unique index "AK_AS_ITM_SPR" > gbak: ERROR:action cancelled by trigger (3) to preserve data integrity > gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint > gbak:Exiting before completion due to errors Sounds like a corrupt index. Your query above probably uses that index, thus you won't see any result set. Executing the query without that index, probably show duplicates then. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re: [firebird-support] Cannot restore a database because of failed unique key index
Your query needs an aggregate field. Count. Alan On Oct 24, 2012 1:49 PM, "sir_wally_lewis" wrote: > Hi, > > We use Firebird 2.5.1 on Linux 64 bit > > Firebird fails to restore because of a unique constraint( see below ). > However when interrogating the data, no duplicate is found. ( see below > SQL, unique key is on the grouped by fields ) > > How can this be? > > Kind Regards, > > Robert. > > > > SELECT ID_VN, CD_ITM_SPR > FROM AS_ITM_SPR > GROUP BY ID_VN, CD_ITM_SPR > HAVING COUNT(*) > 1 > > > > > gbak:activating and creating deferred index AK_AS_ITM_SPR > gbak:cannot commit index AK_AS_ITM_SPR > gbak: ERROR:attempt to store duplicate value (visible to active > transactions) in unique index "AK_AS_ITM_SPR" > gbak: ERROR:action cancelled by trigger (3) to preserve data integrity > gbak: ERROR:Cannot deactivate index used by a PRIMARY/UNIQUE constraint > gbak:Exiting before completion due to errors > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++ > Yahoo! Groups Links > > > > [Non-text portions of this message have been removed]