Subject Re: [firebird-support] ALTER INDEX causes "attempt to store duplicate value" @L
Author Alec Swan
Thanks for the feedback, Ann. Your cosmic and other scientific analogies
really shed the light on the issue :)

My last question is logistical. When I posted the original message I
received a strange email (in Spanish) containing the following link:

Does this mean that this is a bug and your team is working on it?


P.S. Here is a copy of that email:
-- do not edit --

Foi criado um ticket de suporte: (#777682) e um funcion�rio ir� lhe
responder em breve.

Voc� pode ver o progresso deste ticket online aqui:

OBS: Se quiser enviar informa��o adicional referente a este ticket, por
favor n�o mande outro e-mail. Ao invez disto, responda a este e-mail
mantendo o assunto com o n�mero do ticket.

On Tue, Feb 9, 2010 at 3:38 PM, Ann W. Harrison <aharrison@...>wrote:

> Alec Swan wrote:
> > I posted the following message about a week ago and have not received
> > any response. Does anybody know what's happening here? Should I avoid
> > using "ALTER INDEX" and instead just use "SET STATISTICS" to avoid
> > this problem?
> Well, SET STATISTICS is much more efficient than ALTER INDEX. The
> former just reads the existing index and computes the selectivity.
> The latter deletes and recreates the index, reading all the records,
> sorting the key values, and rebuilding the index from the bottom
> up. Rebuilding the index can make it more dense, thus more efficient
> for reads and (possibly) less efficient for writes. So, from that
> point of view, you should choose the path that gets you where you
> want to go - just good statistics, or condensing the index.
> But, in your case, the ALTER INDEX has turned up a problem, or
> at least the appearance of a problem that could cause unexpected
> (unwanted and wrong) results of queries.
> > FBSQLException: GDS Exception. 335544349.
> > attempt to store duplicate value (visible to active transactions)
> > in unique index "IDX_MK2ruDPOuXCeZtnMo6Hg2Q=="
> > Reason: attempt to store duplicate value (visible to active transactions)
> > in unique index "IDX_MK2ruDPOuXCeZtnMo6Hg2Q=="
> > org.firebirdsql.jdbc.FBSQLException:
> > GDS Exception. 335544349. attempt to store duplicate value
> > (visible to active transactions)
> > in unique index "IDX_MK2ruDPOuXCeZtnMo6Hg2Q=="
> and so on. I assume you've actually got an index withe the
> intuitive and lovely name "IDX_MK2ruDPOuXCeZtnMo6Hg2Q==".
> It's got a duplicate value in it, possibly the result of
> cosmic radiation, the phase of the moon, or a bad memory chip.
> What you should do is figure out which table that index belongs
> to - probably encoded in the name - and which fields are the keys.
> then a query like this
> select <key fields> from <table>
> group by <key fields>
> having count (*) > 1
> will tell you where the duplicates are, and you can decided how
> to handle the situation.
> Among the bad things that happen when you've got duplicates where
> you don't want them is that queries that require a singleton result
> fail. And you double count some things. And possible sell the
> same cyclotron twice. Bad things...
> Good luck,
> Ann

[Non-text portions of this message have been removed]