Corrupted Table Problem

[TomasDill]

I have a VFP application that used by many users in network to access to the database on the file server (MS Windows NT network used). Occasionally (1-2 times at month) I receive reports that certain tables do not allow to add new record. When I look to them - they corrupted. Either tail of table is cut off or table header contains inappropriate information. Some information about these tables to avoid questions (and only these tables - other are not corrupted)
1. Record added by insert - sql command to view in 3-d buffering mode
2. View contains all fields from table.
3. Tables contain memo fields.
4. Tables contain many fields, but none of them named like any of VFP keywords.
5. Tables contain a lot of information (> 10000 records)
6. Tables are very often to be accessed by many users simultaneously.
To fix this problem, I need to open table in VFP design time mode in browse window, add 1-2 empty records and delete them. In run-time it does not works, because users cannot add new records.
Note, that I really cannot reproduce this situations and users don't know why it occurs, poweroffs was not a case.
Questions:
1. Let suggest that problem occurs because network failed or machine power off or by some other reason. How I can auto-recover corrupted table in run-time?
2. Let suggest that problem is in my application. Why it occurs and how I should insert new records to tables to avoid such problems? Note that change view to table is not acceptable, it will slow down work.
3. Let suggest that problem is many users access to the table simultaneously. What I should add to application to prevent problem?

 

[foxdev]

You might try SET AUTOSAVE ON along with issuing FLUSH at regular, practical points in your code.

Excerpts from Hacker's Guide to Visual FoxPro 6.0 by Granor/Roche:

...releasing record and file locks automatically flushes the data (although not the file headers)... Row- and table-buffering techniques that also commit data to disk as rapidly as possible alleviate most of the need for this command....buffering seems to update the data records, but not always the table and memo headers...consider FLUSHes as a Band-aid to lower the frequency, but not necessarily eliminate, file corruption....Bottom line: If you're losing a lot of data, you can use FLUSH to improve the situation a little. But improving the online reliability of your system will be a far better long-term investment.
Hacker's Guide® to Visual FoxPro 6.0™ is Copyright © 1998 by Tamar E. Granor and Ted Roche. All rights reserved.

 

[TomasDill]

Is any way to fix corrupted table in run-time in automatic mode?

 

[foxdev]

You could trap the couple of error codes involved via ON ERROR, then branch to your fix-it program - but from there, it gets pretty tricky. For certain kinds of errors, you could possibly invoke FixDBF or FoxFix or one of the commercial programs like dSalvage; for other kinds of errors ("memo file is missing/invalid&quot you might be able to fix it by copying it (via VFP's COPY command) to a temp file, then copying back; I've found that this often fixes bad pointers.

 

[TomasDill]

This will simple do not work, because when users try to insert records - all ok. No errors. No messages. Than, after tableupdate() and requery() for view - new record disappears. For all users. Looks strange. It looks like MS guys tried to predict such situations and auto-fix them, but it is not auto-fixed in my case (its auto-fixed from VFP browse window, however).

 

[foxdev]

It looks like MS guys tried to predict such situations and auto-fix them

Coincidentally, Hacker's Guide did say that VFP6 now automatically detects when the table header is off by one, and silently "fixes" the header. I wonder if this is related to your problem.

Also, though I'm sure you've already been down this path, it never hurts to ask: if you are using table buffering, are you passing .T. (or 1) as an argument to TABLEUPDATE()? [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

Of course .F. If it fails, it processed changed fields and asks users to confirm overwrite, and than overwrites by tableupdate(.T.). [sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[foxdev]

Hmmm...force is actually the second (optional) parameter to TableUpdate(); the first parameter is rows. From the on-line hayulp (as we say in the South) on the first parameter for TableUpdate():

Specifies which changes made to the table or cursor are committed. If nRows is 0 (or .F.) and row or table buffering is enabled, only changes made to the current record in the table or cursor are committed. (emphasis mine)

Is this relevant to your situation? [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[TomasDill]

No, I meant '.T.' as second parameter. Really, I always use 'tableupdate(.T.,.T.)' on the simple forms where I do not aware about frequent use of them.
Below I describe the whole process of working in code, that will be more simple and obvious than descrive everything in words Here I also pointed where I suggest to put FLUSH command, and will appreciate when you tell me if I'm thinking right. Note that this is not exact code, it is just a 'draft' compare to real application.

<code>
procedure SaveData && save data in transaction
lparameters plOverWrite && overwrite changes on server

private llResult && should be private to use it properly in subroutines
m.llResult = thisform.OkToSave()
if m.llResult
&& refresh underlying data for correct oldval() and curval() functions result
IF ... check for valid cursor types
refresh()
ENDIF

BEGIN TRANSACTION

....
&& Try to save data
if !tableupdate(.F.,m.plOverWrite) && one record only, when we have 5 buffering mode, we will loop these lines for each record
&& check what the problem
...
llChangesOnServer = ...
...
&& when NOT problem just because another user changed
if !llChangesOnServer
m.llResult = .F.
this.cError = ... && store it to show later to user after transaction
endif
endif
if m.llResult and !llChangesOnServer
&& trap errors here
....
ON ERROR m.llResult = .F.
END TRANSACTION

FLUSH && FLUSH IS HERE!

if !m.llResult
ROLLBACK
endif
&& restore original error handling
...
else
&& just rollback
ROLLBACK
endif
&& Finally
if m.llResult
if llChangesOnServer
&& show messagebox, it should be outside
&& transaction, because transaction should be
&& as quick as possible
if messagebox(&quot;... Overwrite changes on server?&quot;, ...) = IDYES
&& save it again, but with overwrites
this.SaveData(.T.)
endif
else
Wait Window &quot;All data saved.&quot; NOWAIT ...
endif
else
= messagebox(&quot;Error occurred during data saving: &quot; + this.cError)
endif
endif

return m.llResult
</code> [sig]<p>Volodymyr Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]

 

[sdcabq]

I do not reccommend using SQL to insert or modify data in foxpro. It does not have the safeguards the language does. In addition, it is not very good at handling buffered tables. I always use buffering so tablerevert() will work.

I have 300 users accross 3 sites using the applications and have not had any table corruption for over 3 years. Data base size is about 4.5 GB at present. [sig][/sig]

 

[TomasDill]

sdcabq, Please, write more details. I do not use SQL to modify data, but I use SQL INSERT command to create new record with many developer-defined default values. You say, it is bad way?
Also, what you meant it is not very good at handling buffered tables? [sig]<p>Vlad Grynchyshyn<br><a href=mailto:vgryn@softserve.lviv.ua>vgryn@softserve.lviv.ua</a><br>[/sig]