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.

asked 09 Feb, 03:38

softecspprt's gravatar image

softecspprt
26223
accept rate: 0%

edited 10 Feb, 06:45

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676

2

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?

(09 Feb, 05:19) John Smirnios
Replies hidden

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

(10 Feb, 06:36) softecspprt

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)?

(10 Feb, 06:42) Volker Barth

<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)

(10 Feb, 06:48) softecspprt

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?

(10 Feb, 08:11) Volker Barth

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)

(10 Feb, 09:01) softecspprt
2

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.

(10 Feb, 09:30) Breck Carter

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

(13 Feb, 10:33) softecspprt
Replies hidden

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.

(14 Feb, 05:40) Volker Barth
showing 2 of 9 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×209
×86
×27
×27
×8

question asked: 09 Feb, 03:38

question was seen: 301 times

last updated: 14 Feb, 05:40