Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are running a pretty popular application in Russia and other CIS countries through a pretty old accounting system called BEST. It's all built on FoxPro (not Visual Foxpro) DBF/CDX tables. It's open but it doesn't have API for interacting with other software. So we have to use direct access to the tables and data.

We use Sybase Advantage Database Server (ADS Internet Connector) to connect our online store to BEST. During developing, testing and running it for the first three months everything was fine. But almost half year ago we started to get index corruption errors. We tried almost everything - replaced network cables and NICs, RAID controller with HDDs, memory, reinstalled windows server. We rebuilt the tables and indexes, looked into the logs - but everything is useless. Once in two days we get index corruption error, so we have to stop the server, reindex tables and start it again and the whole company is waiting for 10 minutes.

We have problems with only 2 tables - orders and their contents. All other 300 tables that are used are never harmed. One more problem that is making the case harder is that the problem is not immediate. When the index file is broken - it's not seen. Users continue to work, until one of them starts a new session BEST or a client makes an order. So it's impossible to catch the moment when it happenes.

Right now we are blaming ADS. Does anyone know about such problems and their solutions in ADS. I've surfed all the internet looking for answers, but found none.

Thanks, if you know something.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

There are two basic types of corruption that can occur with CDX indexes: Logical corruption or physical corruption. Based on the OP description that says it is not detected for a while, it sounds like logical corruption. A common type of logical corruption is when keys either don't exist for records, or they exist but have incorrect key values. Physical corruption in CDX indexes typically manifests itself with index pages that are simply invalid (e.g., mismatch between key count and actual keys) or have pointers to other pages that do not exist.

Physical corruption is usually detected sooner because the application will often produce an error when attempting to process a physically corrupt page. Logical corruption can go undetected for a longer period of time because it is quite possible to use a logically corrupt index without any errors being reported (e.g., not finding a key in a seek operation is a "normal" situation).

Assuming it is logical corruption being described in the OP, one possible reason for this is if Advantage Database Server is using a different collation than the FoxPro application with which it is sharing the data. The older style CDX indexes do not contain any information in them that defines the collation. One quick check you might use to see if this is the case is to download the checkindex utility. It is a simple utility that will scan a table and verify that the keys and records are in agreement with each other. If you reindex a table with FoxPro, you could then run this utility (which will use Advantage) and determine if they are using the same collation. More information about collations here.

share|improve this answer
    
thanks for your comment. It turned out to be an update to the BEST, that was not good. We've downgraded to previous version and now we are not getting index corruption error any more (4 days in a row). –  pablomedok Feb 14 '12 at 18:54
    
no, it was not a cause of the problem. The problem persists –  pablomedok Oct 16 '12 at 21:29
    
thanks for your comment, @mark-wilkins, I checked the index right after reindexing with the chkindex utility and got lots of problems like this: Problem: Record 84516 did not get hit while skipping through the index There was no info about collations. –  pablomedok Oct 16 '12 at 22:30
    
I've noticed that on of the indexes has this condition expression (with single quotes): !DELETE().AND.(Status='1'.AND.(Status1=1.OR.Status1=2).AND.Status2 = 0). While the other index in the same table with double quotes: !DELETE().AND.(Status<>"0".AND.Status1!=0.AND.Status2!=1) Can this be a problem? –  pablomedok Oct 16 '12 at 22:32
    
I don't see any obvious problems with those conditions. They should be okay. What error are you getting? Although I wonder about "DELETE()". That is typically written "DELETED()". I wonder if some piece in the chain of software using the table doesn't like that spelling. –  Mark Wilkins Oct 16 '12 at 23:02
add comment

In my experience with index corruption, it's usually a user's machine that they reset during the day with the FoxPro application open. Ask your users if any of them do this. You might also try writing an automated nightly reindexing program.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.