.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.

 

[Vince Wen]

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.

Chriss, thank you for your comprehensive suggestion. We do NOT backup data. Which is a mistake in my view as well. (if our program is on a network server, the network server will typically have daily restore points; if our program is on local C:, then no backup)
When you say backup, do you mean simplistically making a duplicate copy of the relevant .dbf files (and .cdx, .fpt, .ndx files)? Or is there a more built-in feature/function native to VFP9 I should be aware of?

 

[Vince Wen]

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.

I have not been able to get back in my client's system to investigate further. They've basically moved on.

However, for unrelated reasons, I happen to have the full datafolder of a few of my clients. To be clear, these are not the disappearing .dbf cases I mentioned above. But a quick examination of their data folder shows multiple .bak files, but they all have corresponding .dbf and .cdx files. (note: I don't know how these .bak were created and I don't know why they were not 'cleaned up'. These clients have no issues with their data.)

I am not sure what you mean by "only documented case for a bak file is using the table designer", because I was going down the rabbit hole Talbess was suggesting that it was due to an incomplete/interrupted PACK, then you suggested that any 'strange name' would have a .bak file extension.

 

[Chriss Miller]

you suggested that any 'strange name' would have a .bak file extension.

No, you were asking "Does the 'strange name' have any pattern?" and all I was saying is that VFP only chnages the extension of files it works on or creates temporary files with other extensions than normal to finally rename them.

If you search the help for bak or .bak you don't find PACK, so it's not documented what temp file extension it uses. How it operates with new files and final renames and deletions is known, though. When you modify a table with the table designer the bak file of the dbf remains as does the tbk backup file of the fpt file. They are simply menat as a sticking backup, not just temp files.

There aer other uses of the extension bak, if you have the option to make backups in Tools->Options, IDE tab->Save options section. Those .bak files are overwritten with the next .bak file when you edit a prg.

Anyway, you shouldn't concentrate on bak files of DBFs, in case they are from a striuctural modification that worked long ago, you'd also only get back a ver old state that has a previous table structure.

If you look in the folder containing the data files you will probably find a file with a strange name created recently - this is the temporary file used by VFP when manipulating a database.

I disagree with that, you don't get names that are not at all corresponding to the original file names. just other extensions. If you call that strange names, I'd think of SYS(2015), I'd not call that strange.

The reason why a dbf file was removed and not replaced by a bak file in such a table modification case I think has more to do with that the dbf you edit in the table designer was used by another user, meanwhile, not by network errors. It's a bad idea to offer the table designer to end users in a shared database environment with the database (be it DBC or only a set of DBFs doesn't matter) in a network share. Not only because of the network but because of the shared scenario.

 

[Chriss Miller]

Okay, I experimented a bit and in case someone modifies a table with the table designer, one of two cases apply:
a) the table designer uses the dbf exclusive and no other user can open it
b) the table designer does not get exclusive access and only shows the dbf structure in read only mode (caption of the window has [Read Only]

You can have cases, when the usage status of a dbf in a network is considered different from what it actually is, though.

Before you dive deeper into that the question is whether your code actually offers MODIFY STRUCTURE. If not, it's users with their own VFP that use the IDE to do so, you can't prevent that ttechnically. If you offer them execution of VFP scripts they could also have MODIFY STRUCTURE in their own code, it's a command the runtime supports, they don't need to own VFP to use that. Besides other tools surely availabel to modify dbf structures, which could cause this because they are based on VFPs MODIFY STRUCTURE.

The help on MODIFY STRUCTURE tells:

MODIFY STRUCTURE Command
Remember that Visual FoxPro creates a .bak file for the original table file and, if the table has a memo field, a .tbk copy of the original memo file. If you have any problems with MODIFY STRUCTURE, you can delete the new file or files and rename the .bak file and .tbk file, if any, to the original file extensions (.dbf and .fpt).

So that suggests it's a safe way to recover, but it's only meant directly after you modify structure and realize you removed something you want to keep and then revert to the old files immediately. It's not something you could use as a repair mechanism in an application, I agree with Gregs warning to oversimplify using bak files.

If you have no .dbf and just a .bak file , that's obviously better, but keep in mind the .bak only is the dbf, a tbk is about the fpt backup, you have nothing for the cdx though. And using a dbf with a cdx that's not about its state can cause very strange effects of finding records that don't contain seeked data, having records in the index that were different or having index nodes about records not in the bak, so the minimum to do then is to delete all tags and redo the indexing to have a valid dbf.

 

[Chriss Miller]

To be very clear about PACK: It's neither documented that it creates new files nor what file extensions they get, it's only clearly known from experience. Likewise that once it completes you have the same filenames and no kept bak or other files. What file extensiosn PACK uses is undocumented, .bak is documented for MODIFY STRUCTURE (and that's calling the table designer). It's very probable that Calvin Hsia, Yair Alan Griver or whoever wrote the C++ code that is the PACK command used the same mechanism as the table designer to cater for the temporary file extensions. But I don't even know whether PACK begins by making the bak copy, I think it first generates a new dbf with other file extension or name and only renamed the .dbf to .bak (or whatever else) short before the new dbf file is renamed to the original dbf file name (besides renaming the newly generated fpt and cdx correspondingly). I'm pretty sure the dbf file is not copied as bak but renamed to bak, anything else would be ineffective programming. I think you could perhaps find out what exactly happens in a normal run of PACK using the FileSystemWatcher (FSW) IlyaRabyy mentioned.

Leftover files from PACK must be from an incomplete PACK. Whereas in case of table modifiation either manually starting the table designer or by MODIFY STRUCTURE command will leave the bak and tbk even when completed. You can easily distinguish the cases where you still have a .dbf on top of a .bak vs only having a .bak file.