Can NOUPDATE prevent a DBF corruption

[ajenat]

Hi
I am wondering, can we prevent a dbf table from corruption (headers) by using it with NOUPDATE (readonly) if I don't need write access.

 

[Chriss Miller]

Well, corruptions only happen when changing a dbf, so when writing. By just reading you'll never corrupt a dbf. So you don't need the NOUPDATE clause. If you use it, you will just ensure there are no changes possible and would identify any code or control (and user interaction with it) that tries to change a record.

 

[Paul Gibson]

I agree with Chriss in that it seems fairly redundant for the specific purpose you are describing but also be aware that if you aren't 100% certain that the table you are opening is only ever read from and you start to open it with NOUPDATE then you are also introducing an extra potential for a crash or crashes and that means you may have to regression test your application or at least do some strenuous searching of the source code.

Any writes to a table that has been opened with NOUPDATE will cause an Error 111 Cannot Update the cursors "x", since it is read-only.

In short, NOUPDATE won't give you the specific protection you are looking for plus if you do write to the table at some point and you just didn't spot it you've now sown the seeds for an Error 111.

 

[igorsandler]

Hi
I am wondering, can we prevent a dbf table from corruption (headers) by using it with NOUPDATE (readonly) if I don't need write access.

Keep in mind the size of the table. Reaching 2GB will corrupt the table permanently

 

[Chriss Miller]

plus if you do write to the table at some point and you just didn't spot it you've now sown the seeds for an Error 111.

But that's also a chance to find places where there is unintentional changes to the DBF that were the cause of earlier corruptions. Also with just reading a table you cannot prevent all errors, anyway. If a table already is corrupt using it won't do a complete health check of the DBF, in short VFP checks whether the record count that's stored in the table matches the expected size oif the dbf file that is HEADER()+RECCOUNT()*RECSIZE(), essentially. Maybe +1 for an EOF byte or plus some more, you can easily find out on healthy tables how that calculation turns out exactly the file size. But you never have a check which verifies every byte for validity in the corresponding context.

What's never checked is if all memo pointers to the FPT file are pointing to correct places, that all index trees are intact and no node points to non existant records, there could be illegal values in the file bytes that store a fields value, and several more problems. You only step upon corruptions when they affect what you want to read, so corruptions can actually exist for a week, month, even longer, as long as you never try read some specific memo, the corruption doesn't surface.

It's not that corruptions due to problems of writing to a file are actually immediately detected at that point, otherwise you would always have the actual creattion of a defect reporting itself. Corruptions occur, because VFP assumes what it didi did effect the file as intended, but the file system failed, the network failed and still reported success, for example.

If you want to avoid file corruptions of dbfs and their related files, the only way to do that is to use another database.

What you can do as precautions is regularly removing deleted records with PACK - needs exclusive access, though. Regularly reindexing the table - with caution when there could be CDX header corruptions that changed the index expression. If there are, you'd have broekn index nodes anyway and need a reindex based on the actually planned index expression, no matter if this is just a basic expression of a field only or an expression combining several fields into one index tag. The recommended recipe for reindexng therefore is storing the meta data about what index tag names and index expressions and index types (primary, regular, candidate, binary) should exist somewhere separate - metadata, and then DELETE TAG ALL and run the necessary indexing commands. Since you can do that, once you take care of storing the index meta data, index corruptions are all fixable, data (in dbf and fpt) that's lost os lost, unless you have frequent enough backups. Index corruptions happening often can still be a PITA, as the recoverability of the indexes is not something you do on the fly on large tables, so all these measures of keeping the data as shrinked as it can be with bloat removed from dbf, fpt and cdx files, you get less problems, but you don't have any chance to get them to a guaranteed 0%, that's never possible with any database, but you can get to better results with a database server in that aspect.

 

[Chriss Miller]

I assume you ask for a reason, and that may be that you have a case where the data exists to drive something, like a dictionary for a grammar checker, so you don't need updates for this data, at least not as regularly as it would be in case of a shop database, as a very common example. There's a similar, yet not idential differentiation with the terms of OLAP vs OLTP.

If you're after a way of using DBFs read only the simplest thing is making the files readonly by the readonly attribute, though it doesn't need high priviledges on a file to change the attributes and be able to write, too, the best way to make DBFs (and FPT/CDX, perhaps IDX, secondary CDX) available read only is setting file permissions to read only, that can't be changed that easily, especially when the users are not the file owners, i.e. don't create the DBFs on the fly but you setup a database administratevly where all files and the data directory exist under an owner the users can't be and their premissions are read only, of course. Besides the other obvious possibility: Storing them on read only storage, like CDs, which makes access much slower, though. If you do that, it's possible to ensure all data files to be verified in all their bits, quite literally, and besides ensuring they are unchanged by file access limitations, you can store and verify checksums.

If you're not mainly after readonly data but after avoiding defects, then the best way is to make use of data maintenance routines, starting with backups and then essentially all the precautions I mentioned in my previous post.

 

[Paul Gibson]

If you want to avoid file corruptions of dbfs and their related files, the only way to do that is to use another database.

100% agree, it is my biggest regret in the main VFP application I manage that the previous developers didn't leave any hope for ever migrating to a different database, short of completely rewriting the application from scratch and that's not really possible because the application is gargantuan. For this app, its age means it is no surprise to find database code is written at every level/layer. Calling it a spider's web would be giving the current level of separation a fake description of organisation, a rat's nest is definitely more accurate. I longed to migrate to another database, not just to reduce the chances of corruption, thankfully that's rare enough, but for things like easier updates when not needing exclusive access to DBFs, but it's just not possible for us, in this app.

As such, I've had many years experience dealing with different types of corruption that can happen in a networked VFP app that uses DBFs by opening them over the network.

There is corruption, then there is corruption. Corruption could mean anything but happily it's rare and even when it does happen we only see a few variations. Thankfully the easiest one to deal with is by far the most common, certainly for us. The old record count in the file header, not matching the actual total number of records. In our application we have a formal utility for users to go through to fix that kind, it's quite nice, even if I do say so myself.

The utility checks the header to see if that is a problem and if so zips up the DBF, CDX, FPT (where applicable) as a backup, then fixes the header to match the correct total before checking the table again. If the table can now be opened it deletes the backup zip and if not tells the user to get us involved and we'll download the zip and examine things and sort from there.

I'm grateful that probably more than 95% of the corruption our users face is easily fixed with that one utility. If it is not that, if it's one or more indexes that have gone bad we have a reindex facility which goes through the tables, tearing the indexes down and rebuilding them from scratch. That does require user downtime but if it is needed then that's better than working with bad tables. There isn't really a 100|% reliable way to check for bad indexes so we have to use other ways of detecting if unexpected behaviour of the application could be due to an index not working as expected. It's never easy for that one.

The killer though is that memo corruption that you mentioned, mainly because of the reason you stated, it can be a sleeper problem that only raises it's head long after the problem happened in the first place. This can mean that backups are not viable. The corruption might not even make the application crash, depending on the form of the corruption that application may continue to operate as if there is no problem, just outputting garbage instead of the correct memo text. That's definitely a more difficult form of corruption to rectify.

We have a tool, FoxFix, which we sometimes use when our own efforts are to no avail and thankfully it is sometimes years between occasions where we need to do that. As long as our users have robust networks and robust power supplies the frequency of corruption events that we're made aware of is rare and I continually cross my fingers that it stays that way.

 

[Chriss Miller]

This can mean that backups are not viable.

Truly a problem, you can reduce that problem by using a rotation scheme like grandfather-father-son backups. Ideally VFP would be transactional, so every state, especially every healthy state of single records is kept so you could always at least get back to the last healthy state.

 

[ajenat]

Thanks everyone for your reply..

Chriss, I store index expressions in a reindex program which, when executed, removes cdx files and creates new files.
Index files can be checked on starting and recreated if required.
I am searching of preventing my data from damage where index file corruption isn't important because it can be recreated by a program on a single click or on next start.

 

[ajenat]

... In our application we have a formal utility for users to go through to fix that kind, it's quite nice, even if I do say so myself.

Can you please share it

 

[Chriss Miller]

If your concern for data loss means you're looking for the kind of seamless recording of the data history as transactional databases log them, the feature to use is index/update/delete triggers. It will require to have a DBC. When using free or legacy DBFs in a legacy software your best bet is on more frequent backups and in that case more in the nature of live backups of any change. Windows offers monitoring file changes with WMI (in more detail WQL notification queries on file creation, modification and deletion events. Well, in case of DBFs mainly the modification events. When doing it that way you don't have the insider knowledge about what actually changed in the DBF, so the triggers are clearly the simpler way of establishing that, as any trigger code starts with the workarea affected and the record affected being the current record pointer in the current workarea, i.e. you can't get better to the point of change, the downside of a trigger is that it happens for every single record, i.e. you have a lot of trigger calls also for batch operations like an APPEND, whereas file modification event notifications can be configured to be monitored within an interval of time, which means you get at most one call every interval. The interval, of course, also becomes the precision of backup, if you call it that, i.e. not every single change is cared for.

All this, no matter what feature you use, obviously can itself cause concurrency problems, even though these triggers and notification monitoring will not need read write access (sorry, read, not write I already was at the second half of the thought), but would write their log information or actual record backups into other files, ideally not even part of the original database. They will still cause any data modifications to slow down write performance.

One major disadvantage of doing it with mechanisms from outside the client that does the changes is that locks and transactions hinder you to see changes you want to ensure are getting into a transaction log, so triggers are surely the best thing you can use as they happen from inside of VFP. And not only that, they happen on the client triggering the change, so you have access to buffered yet not committed changed data and the state of data within a transaction, if there is one.

 

[tomk3]

I recommend to switch to CS database, too. We did that with remote views which can be USEed and INDEXed like tables on startup, then we optimized it with where clauses,...
Another approach might be to switch to a remote solution (MSTSC, TSPLUS) and run the app on the server with the dbfs