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.
Best Answer
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
checkindexutility. 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.