Hello Ann,

Thank you for the comprehensive answer to my posts.

We are using Firebird 2.1.2 in Embedded mode with Jaybird JDBC driver 2.1.6
on Windows XP.

Let me clarify the problem with primary key index corruption. We have a
query which returns primary key values from a specific table. This query
returns 2 rows. We have another query which returns all values from the same
table using the first query as a sub-query to filter rows based on primary
key values. This query returns 1 row. Which is incorrect.

If we drop and re-create the primary key on the table and re-run both
queries they consistently return 2 rows each. I have the database and
queries which I can send to you. The database contains production customer
data, so I wonder if I could send it to you in a private email?

Thanks,

Alec

On Mon, Jul 26, 2010 at 3:33 PM, Ann W. Harrison <aharrison@...>wrote:

>
>
> Alec Swan wrote:
>
> >
> > I haven't received any answers to my question below in almost a week.
> > Not sure if my messages don't get posted on the list or just not being
> > answered.
> >
> > What commercial support options are available? Any contact information
> > will be greatly appreciated.
>
> IBPhoenix, among others, provides commercial support.
>
> >
> >>
> >> We just ran into a problem (in production) where a corrupted primary key
> >> caused the query to return incorrect results. We rebuilt statistics on
> all
> >> indexes in the database, but that didn't fix the problem. We had to drop
> and
> >> re-create the primary key to fix the problem.
>
> Part of the problem is that the phrase "corrupted primary key" doesn't
> convey much information. You seem to be using "primary key" as
> shorthand for "primary key index". Which is OK, but you don't say
> how you decided the index was corrupt. I'm guessing that there were
> missing entries, but without more of a hint, I can't go much further.
>
> Do you still have a copy of the database with the misbehaving index?
> If so, could you run gstat and report on the population of the table
> and the index? Losing values in an index is very bad and may be the
> symptom of a bug we should understand. Or it may be something very
> nasty in your environment - like a disk controller that lies about
> writing.
>
>
> >>
> >> Our maintenance plans "set statistics" as Ann suggested in this thread
> >> (http://tech.groups.yahoo.com/group/firebird-support/message/106971).
> >> However, this new problem requires us to drop and recreate primary keys.
>
> >>
> >> My understanding is that the original problem reported in
> >> (http://tech.groups.yahoo.com/group/firebird-support/message/106971)
> was
> >> caused by "bad memory chip" or maybe some other hardware failure. Has
> there
> >> been any work done in the recent Firebird releases to protect or recover
> >> from this kind of problems? If not, how can we reduce the likelihood of
> >> these problems occurring in the future, e.g. flush to disk more often?
>
> In theory, unless you have fsync turned off - or an old version of
> Firebird on Linux (which version did you say you have?) and you've
> had system crashes, Firebird will flush all pages dirtied by a
> transaction on commit.
>
> So, without more information, there's not much to say.
>
> Good luck,
>
> Ann
>
> p.s. Nobody, nobody, nobody at all (except MySQL) designs databases
> so they lose data in indexes or tables. So please help us find this
> problem
>
>
>


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