.dbf files mysteriously gone missing (or being deleted)

[Vince Wen]

I have a VFP9 program where users can install on either their local workstation (C or put on a network server for multiple users to use.
There have been several instances (perhaps 3-4 instances this year) where .dbf files have mysteriously gone missing. It is a different .dbf file each time, and there does not seem to be a pattern.
As far as I can tell, the cases have all been happening when the software is on the network server. I have a case where the user was using the program just fine and suddenly it errors with: "File [xxx].dbf file does not exist". This doesn't make sense to me as the user had to be accessing the .dbf file to run the program, and I certainly don't have any code that would delete the .dbf file.
So far, this has only been happening to files on network servers. It has not happened to our C: installations. Of course, this could just be a coincidence.
Does anyone have any hypothesis for me? Or any advice as to where I should begin my investigation?
- Vince

 

[Chriss Miller]

That's not a good idea, you know a dbf most of the time has a cdx and fpt file, too. You said that one has no fpt, but that's not generally the case, is it? If you restore some state of a dbf that was saved as a .bak file that doesn't match the fpt and cdx files, you gain nothing. Find the root cause of the problem and solve it.

 

[tomk3]

Since the software is an a remote share. By definition the files including prg and dbfs are visible to the user.
Either if the whole directory is mapped as a shared drive

Or the shortcut on the users desktop is pointing to the shared path that is not visible to the user as easy.

Its still able to be found. ( if the user has some level of windows knowledge.)

Unfortunately there is no way around this. For foxpro. I haven't found a way to keep these stuff separate from the USER.

You may search for "impersonation" , AFAIK it makes it possible that only your app can access the files
But I guess , too that an AV-Tools and maybe quarantine is the problem.
There are tools to watch a filesystem (filemon from sysinternals and others), but they can produce big logs. There is "notifychanges" code in VFP around to watch changes like delete file , but it slows down program execution and does not show which process deleteted.

 

[Chriss Miller]

That's also an option I thought of, but it's following the whol off topic thread. Since Vince said his software is used by one person and they want to use it, I don't see a reason they sabotage it at all and that train of thought was gone far off and too far in the totally wrong direction.

If it doesn't turn out the case that was not related to PACK, but has any other dead giveaway code that deletes DBFs even directly, perhaps, then that mystery could be solved. And the way to get to know that would be removing deletion permissions on all DBF files. There's even no need for file monitoring. Anyway, I just start feeding that topic once more.

But why don't we (you) start a new thread, mybe of the "Helpful Tip" type that discusses impoersonation. It bears some risk, too, because anything that leads to open file dialogs, directly or indirectly gives users the opportunity to do things in window. For example, when you Select from a nonexisting dbf and don't have SET TABLEPROMPT OFF. Problem is, even if you close that gap you feeler safer than you should.

 

[ggreen61]

There can be problems with renaming a bak file to a dbf file. If the dbf is part of a database (DBC) then the database will contain the structure definition to the dbf. The dbf backup file is usually created when you made a structural change. So, these may not match any longer and you could have problems. You can open the database and then drop the table that is missing before you rename from a bak file. Then add the table back to the database.

 

[Chriss Miller]

I agree with what ggreen said last. But when your dbf is missing after an alter table or using the table designer to change the structure, that means storing the latered table failed and you may have the old structure in a DBC or have some informations about the table updated, some not. In short, you're just in a bad situation you can't easily repair.

The first question to ask before you think about a repair mechnism based on bak files is, whether you even modify a dbf structurally while your application runs. As you say your application is single user, that's viable, but I would suggest even for a single user application structural changes while using an application speak against you as a developer. Databse design is something you usually do once in the development stage of your software. There surely are other opinions. But you see to what problems this way of working with data leads. If you want to offer your user a database development like environment, then the one question is: Why are the tables for a single user on a network share. When developing you usually design your database locally.

 

[Vince Wen]

I agree with what ggreen said last. But when your dbf is missing after an alter table or using the table designer to change the structure, that means storing the latered table failed and you may have the old structure in a DBC or have some informations about the table updated, some not. In short, you're just in a bad situation you can't easily repair.

The first question to ask before you think about a repair mechnism based on bak files is, whether you even modify a dbf structurally while your application runs. As you say your application is single user, that's viable, but I would suggest even for a single user application structural changes while using an application speak against you as a developer. Databse design is something you usually do once in the development stage of your software. There surely are other opinions. But you see to what problems this way of working with data leads. If you want to offer your user a database development like environment, then the one question is: Why are the tables for a single user on a network share. When developing you usually design your database locally.

Hi Chriss, to be clear - what I said was "in one case" it is a single user (so I ruled out self sabotage). As mentioned in my original post - there has been 3-4 deleted .dbf cases (in perhaps a little over 1000 installations). In the other cases, it is a multiple user setting.

In that specific single user case, I now suspect it is an incomplete PACK. We do not use DBC or memos, so I think renaming the .bak to .dbf, and perhaps reindexing the .cdx would fix the issue.

I still do not know what the cause is for the other cases, as I don't think it is related to PACK (or dbf structural alteration).

 

[mjcmkrsr]

Hi Vince,

Please use PACK with extreme caution and best in an environment where no other user can (try to) access the file during the pack process.

From Hacker's Guide to Visual Foxpro

Usage

PACK [ MEMO | DBF ] [ TableName ] [ IN cAlias | nWorkArea ]

Parameter	Value	Meaning
TableName	Name	In VFP 7, specifies the table to pack without opening it first. Just add the path and file name of the table to the command.
cAlias	Character	Pack the table that's open with the specified alias.
Omitted	If nWorkArea and TableName are also omitted, pack the table that's open in the current work area.
nWorkArea	Numeric	Pack the table that's open in the specified work area.
Omitted	If cAlias and TableName are also omitted, pack the table that's open in the current work area.

PACK permanently removes all marked records. What it actually does is copy all the unmarked records into a new file and then rename the new file. (The table is also reindexed.) Because PACK does this behind the scenes, there is a small chance for data loss if your system crashes during a PACK.

Use PACK with caution. Before VFP, the standard advice was to do the same steps yourself, using COPY TO, DELETE FILE and RENAME. That's still your best bet for free tables. Either way, you need exclusive access to the file to perform this maintenance. Also, in either case, you should SET ORDER TO the tag you use most often before starting the process. That way, the records in the resulting file will be in the order in which they are most often accessed, resulting in better performance. Here's an example to get you started:

USE MyTable ORDER BestOrder EXCLUSIVE
COPY TO MyNewTable FOR NOT DELETED()

IF FILE("MyNewTable.DBF") AND FILE("MyNewTable.FPT")
* Only go on if the new version was created
USE
DELETE FILE MyTable.DBF
DELETE FILE MyTable.FPT
DELETE FILE MyTable.CDX
RENAME MyNewTable.DBF TO MyTable.DBF
RENAME MyNewTable.FPT TO MyTable.FPT

* Now re-create the indexes
ELSE
WAIT WINDOW "Trouble in Paradise" NOWAIT
ENDIF

For tables in a database, things are a little more difficult. Using the COPY TO approach, you lose all the database information for the table. In particular, your relations get trashed. Your best bet is to get a good database maintenance tool (like Stonefield Database Toolkit) to handle these chores.

Memo files (FPT) can accumulate a lot of wasted space because replacement of existing data doesn't reuse the same disk space. Over time, this can really add up, especially if those humongous general field OLE objects are used and edited. PACK MEMO cleans up this wasted space without removing the records marked for deletion. There's also a PACK DBF version, but it's another of those items that seems to have been added for completeness, not because it was useful. It removes deleted records without compacting memo space. (If you find a reason to use it, let us know.)

VFP 7 makes it a little easier to use PACK. First, it acquires an IN clause, so you can specify which table you mean and not depend on being in the right work area. More interestingly, the new TableName parameter means that you can pack a table without having to explicitly open it first. To use it, the table must not be open anywhere, since PACK needs exclusive access. Among other things, this means that you can't combine the TableName and IN clauses, because having the table open in a work area prevents it from being opened exclusively for packing. Like a good citizen, VFP cleans up after itself and the table is closed after it's packed. Would that all our code was as careful.

hth

MarK

 

[Chriss Miller]

Have you even found a bak file? The only documented case for a bak file is using the table designer. And that's not a runtime feature. If you just work on data with either APPEND, REPLACE and (xBase) DELETE or SQL INSERT/UPDATE/DELETE you never get a bak file.

 

[Chriss Miller]

In the other cases, it is a multiple user setting.

Sorry, I got this wrong.

You also said: "In one case, the owner is the only one using our software. There isn't any good reason to sabotage himself."
I took that as there being a single user mode, where you might give the user the ability to modify the tables.

There could also be the case where someone actually tries to modify the data owning VFP themselves. And doing so in a production data environment is a bad idea as structural changes to a DBF need exclusive file access, which could be available when starting to go into the table designer, but not when saving the changes. I would assume that would lead to errors on other clients trying to use a dbf that's modified in the table designer.

 

[Chriss Miller]

Just one obvious thing nobody mentioned - maybe because it's obvious:

The best protection against data loss, no matter if by table corruption or the file deletion you encountered, is a backup of data. That should be a feature of your software. You're concentrating very much on just the problem you had, a backup protects against any problem, it won't cover all data since the last backup, of course. But it does not only cover deleted files, also file corruptions.

Other supportive methods of protection are having a table repair tool like foxfix or others. VFP8 and 9 (and their runtimes for EXEs) do validate a table with every use of it, depending on what you configure with SET TABLEVALIDATE, you should get familiar with what this does. I fear you're not using a DBC as you actually also use an older version of VFP. Then this does not apply, but makes a backup even more important.

Well, tablevalidate will not thoroughly check all records integrity, it mainly cheks whether the dbf has a size corresponding to the record count which is stored in the dbf header. It does not validate index or memo integrity, neither whether generally the cdx or fpt headers are okay nor whether any index node points to an existing record number or any pointer from the dbf file into the fpt file is valid. Mainly for performance reasons, you couldn't make such detailed tests without slowing down usage of DBFs drastically. But the corruption of missing records that point out a problem during the last insert or append is the most common dbf corruption problem, so it is good to have. It does not require a dbc, but VFP8 or 9. A DBC can cater for more security as you can have triggers for insert/update/delete, which could be used to write an audit trail of data changes, for example.

This thread concentrates far too much on the problem of a disappearing file. As you don't suspect users manually sabotaging themselves you would only find out whether the reason is in your programming or coming from outside, like Antimalware or using an own VFP to modify data structures, if you remove the permission to delete the files.