Primary key Index error

[Former Member]

Some of our customers faced this problem: After some time of heavy usage of one table (many inserts and deletes, but max about 200 rows in average table size) sometimes the primary key index of this table doesnt work any more. Selecting by id gives incorrect results.

Validating table and index doesnt show an error. Rebuilding the primary key index fixes this problem temporarily.

The problem appears in different intervals (sometimes hours, sometimes days, sometimes weeks). As a dirty workaround we used a database event rebuilding this index hourly.

Anybody facing this problem too? Any hints what can cause this problem?

Server version is SQL Anywhere 16.0.0.2322. Client software uses sajdbc4 driver.

[johnsmirnios]

Can you please clarify what you mean by 'selecting by id gives incorrect results'? Do you mean that if you "select ... order by id" they do not come out in order? Or do you mean that sometimes it does a sequential scan+sort rather than using the index? Or something else?

[Former Member]

incorrect results means: selecting by id (WHERE ID = <id>) does not give a result while selecting all rows gives results containing the id <id>

[VolkerBarth]

And you say that validating the according primary key index does result in a successful validation? Weird.

FWIW, I'm relating to

 VALIDATE INDEX PRIMARY KEY ON MyTable;

or the according DBVALID options...

---

Is <id> a number, or is it a string (and therefore could contain unexpected trailing/leading blanks, unusual chars or the like)?

[Former Member]

<id> is a bigint. explicit rebuilding the index helps restarting dbsrv16 seems to help (downloading the db and starting a dbsrv16 server on it does not show this behaviour)

[VolkerBarth]

Could you please confirm that at those times the query on <id> behaves unexpected, the PK index still is successfully validated?

(Have you considered RAM errors on the machine hosting the database server?

[Former Member]

thanks for suggestions. a memory check on the server machine will be done. To test PK index again i have to wait for the problem to happen again, before it gets fixed by the workaround. (we cannot reproduce it outside the production environment)

[Breck_Carter]

If you even suspect a RAM error, stop running the database on that computer until you can check the RAM.

The reason is, every single disk operation goes through RAM, so a RAM error can eventually lead to permanent data corruption on the hard drive.

[Former Member]

RAM check (mdsched.exe, its a windows server) does not show any error. i am clueless.

[VolkerBarth]

How long did your RAM tests take? (AFAIK, mdsched.exe should run "over night" for a full check...). The MEMTEST86(+) tool might also be worthwhile.