VFP CDX Corruption (vfp6)

[boanrb]

Dear friends,

I have a bad situation at hand and it’s as follows:

I help support a Vfp6 app in a hospital and the .dbfs/cdx/fpts are stored on a 64bit Windows Server 2008. They do not use DBC. It’s a mix of OSes; winxp and win7 mostly.

From 2012 October things have been fairly ok. (There has been one problem though. Sometimes vfp fails to save some records posted at client computers without an error)

In March 2014 the app all over sudden started having SEVERE CDX CORRUPTION. The experience is now a nightmare to all.

Recreating the index tags stops the problem for some few hours then cdxes get corrupted again.

Fatal Error exception C000000FD
(The error is at insert level, particulary when a key field is changed)

I googled some info and got a list of things to check for.

1.) Bad characters in fields {I removed especially from key fields but the problem persist}
2.) Disable Oplocks {IT manager refused siting –ve implications on some applications}
3.) One bad NIC could result in bad behaviour – {not able to test this]
4.) Bad network environment – {not sure what to look for}
5.) Disable SMB2 – {not sure of any -ve implications on the network}

The system is actually unusable for now.

Kindly advise on what to do.

Benson

 

[Olaf Doschke]

Regedit does not allow to add a key "TEST", if there already is a key "test", so the registry is case insensitive as is the file system. If this is your last straw opf hope, why not try to see if that makes a difference.

Bye, Olaf.

 

[boanrb]

Olaf,

If that is the case then no need to try let me explore other options.

Thanks alot.

Benson

 

[boanrb]

Olaf,

Sorry to bother you again.

If the files are only read but never written to, is there a chance, from your experience, that they could get corrupted? I have particular tables and they are big, their CDXes easily get corrupted and need reindexing.

I have other means to update them if user post transactions from their workstations. So I just wanted to confirm that if I open them for reading only their CDXes wont corrupt.

At this point I am just trying to put the system back to a useable state since I a not sure why all over a sudden severe cdx corruption started yet the system has been stable since 2012. It could be the network but I am not in a position to tell.

Thanks a lot for your patience.

Benson

 

[Olaf Doschke]

Reading them is not changing them, corruptions only happen when writing.

The only bad thing about oplocks is as it's described in detail by dataaccess.com: Files get a quasi excluisve lock (also when reading) and are prefetched and cached at the first user. The second wanting access to the same dbf (also just read) breaks the oplock (as it's not a real exclusive lock, that's how it's designed) and that invalidates the cached data at user 1. If there are no changes nothing. But if the OS thinks there are changes, they are written back and that corrupt the data anyway.

One way to have changes without really changing something is to even just display data in a control via controlsource. Not very uncommon, is it? The user just tabs through such a control, and the unchanged data is written back.

One easy way to ensure data would only be read is turn on the readonly flag on the table files, then you'll see, if your app und usage of data really only reads. Even if you don't have any REPLACE, UPDATE, TABLUPDAET in your code, just using the controlsource means writing. Be aware of that.

Bye, Olaf.

 

[boanrb]

Olaf,

I ain't displaying the data in any way. I don't use any control for this tables. I USE the table in code then only look for a record. That's all. Can the OS in this case 'think' that my table has changed when I close it?

Thanks alot for your patience.

Benson

 

[Olaf Doschke]

In that case just seeking some value or using it won't change anything. If you want to make sure, then use SQL SELECT queries INTO CURSOR and work on the cursor instead of the table(s). Makes me wonder, though: If that's all you do, why are there corruptions at all?

Bye, Olaf.

 

[boanrb]

Sorry I didn't phrase my self properly. My intention is to update those tables using other means as I figure out what to do. Currently they are updated directly at the client machine so the Os has to write the changes back to the server. I think it is this scenario that is causing the corruption.

So I asked because I wanted to be sure that I am in a situation where the OS wont 'think' that the table has changed and write back causing corruption. I intend to just USE the table, check for something then close preferably using USE ... NOUPDATE.

I am actually trying to avoid the Fatal error c0000fd that has just refused to go away.

Much appreciated Olaf.

 

[Olaf Doschke]

Using table with NOUPDATE clause is a good idea. Still, it rather seems now it's anything else than oplocks. If even the idea of using the files twice shared at the server itself doesn't help, then it's surely not oplocks causing the trouble.

Bye, Olaf.

 

[boanrb]

Would you by any chance be knowing another possibility of what the problem could be?

 

[Olaf Doschke]

So, look, all that questions about the registry, etc. is obsolete if you just open the tables twice, then once and for all suspicions about oplocks are done with:

ON SHUTDOWN QUIT
SET EXCLUSIVE OFF && it's the default inside an EXE, but anyway
USE yourtable1 IN 0 
USE yourtable1 IN 0 AGAIN ALIAS _yourtable1
USE yourtable2 IN 0 
USE yourtable2 IN 0 AGAIN ALIAS _yourtable2
...
READ EVENTS

If it's many more tables use ADIR or ADBOBJECTS and loop the result array.

That's it.

Bye, Olaf.

 

[Olaf Doschke]

Very many reasons: Bad hard drive, bad network, in general any involved hardware. DBF corruptions also don't need to raise errors, so if you recover to a state already having a silent bug inside the file you regularly get back the same corruption. Griff also already mentioned other reasons about index lengths, unbalanced index tree causing trouble. I also said VFP6 is not the most stable version.

Bye, Olaf.

 

[boanrb]

With all the information given, let me try to reach the bottom of the problem.

Thanks a lot. You have helped much in the area and I appreciate.

 

[dbMark]

I've sometimes heard that these stubborn issues can be caused by other software added or updated, for example, an aggressive antivirus program that is scanning every single file. Did the antivirus get upgraded or changed to a new brand - likely location is the server - about the time your problems started last month? Why not edit the AV software options to ignore files with .DBF and .CDX extensions and then wait to see if your issues are reduced?

 

[dbMark]

Another idea that may reduce the likelihood of corruption: INSERT INTO is said to be faster (and simpler in the underlying VFP process) than APPEND & REPLACE. "Faster" = expose you to less risk of corruption? But implementing that would mean extensive changes throughout your code unless you can just focus on the routines and tables that are the ones most impacted.

 

[Olaf Doschke]

Corruptions mostly will occur due to UPDATEs, both SQL-UDATE, but also TABLEUPDATE, which causes SQL-DELETE, SQL-UPDATE and SQL-INSERTs behind the scenes depending on GETFLDSTATE anyway.

I think the aspect of antivirus is worth checking, as we didn't have that in focus up to now, I think. It can also occur suddenly because of a new virus recognition pattern, or a minor change of how a virus scanner scans files.

Bye, Olaf.

 

[boanrb]

Sorry guys for delay. I was on holiday.

Thanks for your suggestions about INSERT INTO.. and AV. I check all this. I modified the AV settings to avoid the folders with the data. Yes, the INSERT INTO is ok. The problem persists all the same.

I have mapped out the cleanest and fasted way to go SQL and that's what I am working on right now. I had suggested this to the hospital eons ago but they just didn't buy the idea. Now they are forced to by circumstances.

They are able to work, albeit with lots of re-indexing.

To be honest I just don't know what the problem is.

Thanks nonetheless for your concern and help so far.

Benson

 

[dbMark]

While this does not precisely apply to CDX corruption, it prompted me to upload my code that fixes table corruption due to the record count in the header not getting updated after an append or insert.
faq184-7723