CORRUPTED VPF TABLE MEMO FIELD DATA - REPAIR SOLUTION NEEDED

[awesomeheero17]

Can you help me to find a solution in my problem with vfp table corrupted memo data?
what i need is to recover the memo data of each records of the table.
As my further analysis i found out that memo data of some records are all mixed up in one field that is supposed to be different and some are having invalid characters and its too many in one record's field that's resulted to vfp to prompt a fatal error msg and close when i access that data.
i have tried to delete the CDX file and recreate it.
a had also tried to delete every single records having erroneous memo field and it went fine. but "data mixed up" is still my problem. can you help me guys on this? i have tried also the "DBF Recovery - DBF Repair Tool" but it doesn't help. thanks in advance

 

[JRB-Bldr]

Have you tried going to the Backup copies of your data files?

Based on what you have already tried, that may be your only resolution.

Good Luck,
JRB-Bldr

 

[awesomeheero17]

that's my only last resort but the problem is, my back up is almost 2 month ago.
this is terrible this always happens in vfp table specially if there is a inconsistency in connection in our network.
but still can you or anyone help me think of any solution other than that?, well thanks to that anyway
maybe my nest question is how these thing can be prevented?

 

[JRB-Bldr]

Prevented?

There is not much that can PREVENT data table problems from occurring - no matter if VFP, SQL Server, MySQL, Oracle, or whatever.

That is why applications like Acronis, etc. have 'real time' backup solutions.

Having supported and developed for a good number of clients on a number of network configurations over the years, the number of times they have experienced 'network connection' issues resulting in data corruption has been very few.

Yes, it has happened, but seldom.

I'd recommend looking into why you have network connection 'inconsistencies'/problems and attempt to resolve that as best you can First.

Also what size are your data table files - the DBF, CDX & FTP files?
If they are unusually large your chance for problems increases.
Are any of them nearing the 2GB limitation?

There might be some things you can do with how your application uses data that might enhance reliability, but without knowing your application and how it reads/writes data I would have no definitive suggestions.

Good Luck,
JRB-Bldr

 

[awesomeheero17]

it only sizes like this:
dbf : 35331kb
fpt: 34867kb
cdx: 2056kb
53404 records

 

[Olaf Doschke]

>i have tried also the "DBF Recovery - DBF Repair Tool" but it doesn't help.
There are several tools, foxfix is another.

But from what you write there is no real hope to get it fixed, also because you began to fix records manually. Perhaps you have a backup of the DBF/CDX/FPT from before you began?

The memo content is in the FPT file, CDX is just containing index tags, and as memo fields are not indexable, to redo that file doesn't help at all.

More often backups are needed of course.

Depending on how large the table is, opening the FPT in notepad you could try to get the memo texts out manually. But that only really helps for small tables, where a manual reconstruction of data is possible, eg 10-100 records.

How to prevent: There is no 100% prevention of file corruption, do more backups.

Bye, Olaf.

 

[Dan Freeman]

In the Word online communities, one of the most frequently asked questions is "I closed my document without saving. How can I recover it?"

The answer: there is nothing to recover. It's a little brutal but it's the only answer that is both honest and accurate. With your most recent backup two weeks old, you're in pretty much the same boat.

You can mitigate damage going forward with more frequent backups.

You can minimize the likelihood of data corruption with backup power supplies (don't forget the servers and the routers!), and making sure your users know better than to turn off the computer without exiting properly.

But you can't recover what you haven't saved.

 

[awesomeheero17]

nice tip on prevention i knew that is why i made a autobackup program but this time the luck is not really mine.
yes i backed up before i do my own remedy. reserving for any solution on it.

"The memo content is in the FPT file, CDX is just containing index tags, and as memo fields are not indexable, to redo that file doesn't help at all."

yes i know this,

"opening the FPT in notepad you could try to get the memo texts out manually"
i think this is not necessary since after i repair the table and made a re-indexing it shows the data but still the problems is the crossed linked data to a fpt file which effects almost 5000 records.

haaay i think i will not get any help on this one. i should have ready myself on end users complains tsk. >_<

thanks olaf

 

[GriffMG]

VFP 9 is a little less tolerant of memos than earlier versions.

What I usually do is open the table in VFP6, then copy the table to a temp one, and then
zap the original. After that I import the temp one into the original

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[awesomeheero17]

you will not able to copy a table with erroneous data on its memo file it will cause you a fatal error if it hit that particular record.
that is why i did a loop program locating each of them and delete. I have repeated that procedure many times till i got the last one bad record. after that i was able to copy the table with clause !DELETED() for me to isolate the accessible data.
but still my problem is those good memo records has been damage. Data are crosslinked, memo1 data is having a data of memo2 and/or memo3 of the same record OR of other records of the table.

 

[Olaf Doschke]

Well, about backup strategy: Yes, the truth often isn't very polite, but that's how it is, that's not us.

It#s not that we don't want to help, there just is no possibility to fix any corruption, it's never just fixing links, if some content overwrote meta data about adresses or length of the memo, that structural data, the "cross links" as you say it, are gone and not recoverable. Even if just the 4 byte adresses in the dbf were corrupted, what would be a way to know the right pointer address into the fpt file, if you just have a wrong address? There is no recovering right from wrong adresses. How would that be possible?

Dan is totally right in that you can't recover what hasn't been saved. Or what has been overwritten. There had to be a major rewrite of the fpt like in a PACK, which didn't have exclusive access. Then that of course does not only apply to new data, but data corruption of course only occurs when writing to the files. A write error effecting a single place or block of the dbf and/or cdx and/or fpt might cause a ripple effect, if you assume a memo block was reused, but content was written in the bytes which are pointing to next memo content. In the normal case, VFP does not reuse memo blocks, it just invalidates a block and writes new or changed content at the fpt end. So actually you must have done something like PACK or PACK MEMO. Or the corruption was creeping into your files memo by memo, until you detected it.

What you overlooked / Didn't respond to is to try other repair tools, eg foxfix. As you have the original corrupted table it might be able to fix the "cross links".

> since after i repair the table and made a re-indexing
Did you do both or did you first repair, which didn't resolve, then reindexed? Was the reindexing just needed to be able to open the table again, perhaps?
You could delete tag all first and see if that already re-enables opening the table. Did your repair tool really fixed at least some memos? Or did it perhaps introduce further cross links, because of working on an deprecated definition of the fpt file structure?

You gave the file size after I posted, with that amount of data, of course manually retrieving texts is no option.

Bye, Olaf.

 

[awesomeheero17]

with foxfix and the backed up dbf fpt, it posted a msgbox saying..

"An inordinate number of errors have been found. this may be due to actual file corruption,however it is more likely to be due to a record length, header lenght or memo block size having invalid values....."

ok its "CORRUPTED" no repair solution to it.

thanks to all who replied.
and keep posting if you found alternative solutions thnks

 

[Olaf Doschke]

>"however it is more likely to be due to a record length, header lenght or memo block size having invalid values"

Did you continue after that message? Did it try to fix the memo block size?

Bye, Olaf.

 

[JRB-Bldr]

This suggestion is most likely Not applicable, but if the circumstances were right (a slim chance) it might work - not to Repair the Memo field contents, but instead to ReBuild them.

IF the FPT file itself was not Corrupted and IF (and only if) the Memo field contents somehow contained info in them that associated them with the correct records, you could maybe do the following...
1. Make a copy of the Original table (maybe 2 copies just-in-case).
2. Blank ALL of the Memo fields in the Original table.
3. Write a program to analyze the contents of the Memo Fields in the Copy table
4. When the Memo field content identified the correct record association, write it to the Original table record's Memo field.

There is a Very Slim Chance that this would be applicable to resolve your issue, but you asked us to 'think outside of the box' to help you.

Regardless - get a Better & More Frequent Backup solution in place.

Good Luck,
JRB-Bldr

 

[awesomeheero17]

"however it is more likely to be due to a record length, header lenght or memo block size having invalid values"
Did you continue after that message? Did it try to fix the memo block size?

I did Both. If i Continue it forcefully closed. if i stop it will lead me to my previous remedy. finding the corrupted records and remove them . i cant fix its contetnt coz it will close forcefully when that memo field gotten focus.
I have tried using it with my other copies in which corrupted records has been removed. it resulted as the same as what i have done before without using it, it shows the memo values but its cross-linked.

and... "record length, header lenght or memo block size having invalid values" exactly all of it is my problem i think.

 

[awesomeheero17]

Thank you JRB-Bldr for that 'think outside of the box' Idea.

IF the FPT file itself was not Corrupted and IF (and only if) the Memo field contents somehow contained info in them that associated them with the correct records, you could maybe do the following...
1. Make a copy of the Original table (maybe 2 copies just-in-case).
2. Blank ALL of the Memo fields in the Original table.
3. Write a program to analyze the contents of the Memo Fields in the Copy table
4. When the Memo field content identified the correct record association, write it to the Original table record's Memo field.


*---
I did this one too before the two table thing, but with original table and my latest backup. and im ok with it. The things is that the records that was affected not covered by my backup is my problem.

"analyze the contents of the Memo Fields in the Copy table"
what particular analysis are you mentioning on this one?
"When the Memo field content identified the correct record association"
how will it be identified? is there something a unique tags associated between record's field on the table and a data on a memo(fpt) file that I can get either in low level way? or you are pertaining to just a glance checking?
if it just a glance checking its much better to re-encode it using the hard copies since it involves patient diagnosis =| (in which the last resort i had suggested and currently on going)

Regardless - get a Better & More Frequent Backup solution in place.
..yes i do understand it,that's why i made a program doing a scheduled autobackup. as i said before its just not my lucky day =|.

well guys I think from here we have gave our best tips on this particular problem. and hoping that some of it helped other people out there experiencing similar probs. Keep replying if you found more tips for future helps. And above all im hoping this prob wont happen to anyone else

KUDOS!
Thanks - Heero

 

[JRB-Bldr]

One thing I forgot to mention was that, after you create the COPY of the Original data table you might want to MODIFY STRUCTURE of the Orig data table and Delete the Memo Field so as to get rid of the current (bad) FPT file and any corruption/bad cross-references it might have.

Then add a new Memo Field (same original field name) to create a New Blank FPT file into which the corrected Memo field data would be saved.

As for an 'analysis' application....
Remember that I said that this approach might have a VERY, VERY SLIM chance of being applicable at all.

You would have to write a small 'analysis' program to scan through the Copy file and examine the Memo field contents

* --- Add New Memo Field To Receive Corrected Contents ---
USE OrigFile IN 0 ALIAS Orig EXCL
SELECT Orig
ALTER TABLE Orig ADD NewMemoFld M
USE

* --- Now Begin Analysis of Memo Field Contents in Copy Table ---
USE CopyFile IN 0
SELECT CopyFile
SCAN
   cThisCustomer = CopyFile.CustName
   cChkMemoContent = CopyFile.MemoFld
   
   * --- Some code here to examine the string contents of the Memo field ---
   < do whatever >
   * --- Example Code Here ---
   IF "Customer: " $ cChkMemoContent
      cCustomerName = SUBSTR(cChkMemoContent,AT("Customer: ",cChkMemoContent) + 10,20)
      IF UPPER(ALLTRIM(cCustomerName)) = UPPER(LEFT(cThisCustomer,20))
         * --- This Memo Contents OK ---
         * --- Leave it alone ---
      ELSE
         * --- This Memo Contents 'Belongs' To A Different Customer ---
         REPLACE CopyFile.MemoFld WITH ''
         =PutIntoCorrectRec()
      ENDIF
   ENDIF
   
   SELECT CopyFile
ENDSCAN
USE

* --- Once Complete Move All NewMemoFld Data Into MemoFld ---
USE OrigFile IN 0 ALIAS Orig EXCL
SELECT Orig
REPLACE ALL MemoFld WITH NewMemoFld
* --- Get Rid Of Temporary NewMemoFld Now ---
ALTER TABLE Orig DROP NewMemoFld
USE


* ---------------
FUNCTION PutIntoCorrectRec
USE OrigFile IN 0 ALIAS Orig EXCL
SELECT Orig
* --- Put Correct Data into 'Correct' Customer NewMemo Field ---
LOCATE FOR UPPER(ALLTRIM(Orig.CustName),20) == cCustomerName
IF FOUND()
   REPLACE Orig.NewMemoFld WITH cChkMemoContent
ENDIF
USE
RETURN .T.

Obviously this example code is very simplistic and you would need to modify it to meet your own needs (if it were even applicable at all).

And, as I said earlier, this approach is totally Dependent on there being something within the Memo field content (or value) which could identify it as being associated with some specific record (such as Customer Name or CustomerID or FirstName + LastName, etc.).

Good Luck,
JRB-Bldr

 

[awesomeheero17]

thanks for the effort JRB-Bldr!
unfortunately if we depend on part of memo content as a KEY to linking to its records its not possible for this case. coz the data that we correcting is diagnosis which is mostly from a template value or mix value of templates.
Nice one JRB-Bldr thanks again.

NOW...
i wonder how DBFs find its memo value in a FPT file. there must be something in it like a prefix or a header attached to its memo value.
if there is, it might be those things i am seeing in some memo file like special characters. the next possible step would be pulling out that values and placed it somewhere and used it as correcting reference.
what do you think guys?

 

[Olaf Doschke]

The DBF and FPT file structures are no secret.

Memo values are store in FPT, but each record in the DBF has 4 bytes for a memo field, whoch contain a pointer to the memo block in the fpt. How else would VFP knwo where in the FPT the content of that memo field is? The rest is in the FPT.

http://msdn.microsoft.com/en-us/library/8599s21w(v=vs.80).aspx

As you can see from here, the memo block to which the DBF memo field points, contains a so called signature (what kind of memo field) and the memo length, then the content. And if the value stored as the memo length is too large, you fetch this memo text and more from the next memo block(s) including their header data. If the length is too low you fetch just part of the memo content.

Now if the memo fiel header is wrong, especiall the four bytes containinge the next free block point to a used block or anywhere in the file, writing new data OR updating data overwrites content and that will not be recoverable. Why also with updates? Because at least when a new value is longer, the original memo block isn't reused, a new memo block is written. There is a so called bloat effect of fpt, which simply results from such abandoned blocks. And since they are in the middle of nowhere and there is no section of the fpt file maintaining a list of free unused block which could be refilled with content up to their length, this is just wasted file space and only a PACK get's rid of it by rewriting every currently used memo block into a new fpt file.

So, if there is the case of writing into used block because there was a faulty value in the first four bytes of the fpt file, even if that value NOW is correct, then you have some memo block overwriting older blocks and most probably also their headers, which lets their record point to somewhere within these new blocks, so 4 bytes of the content of such a record are interpreted as a length, which results in fetching all kind of wrong content.

And of course also the DBF can be corrupted in the 4 byte memo pointer values. If they point to a wrong offset in the fpt file, all goes higgeldy-piggeldy.

JRB-Bldrs proposed repairing was considering exactly that, an intact FPT file, with some wrong pointers in the DBF. You could at least read the correct memos into a new DBF with a new Memo field. But it also won't recover records pointing to wrong positions in the fpt.

Bye, Olaf.

 

[IlyaRabyy]

Colleagues,

After that much's been said and suggested, may this humble one put in his measly 2 kopeeks... ehrr... oboluses... mmm... centavos... bloody Alzheimer! Whachemicallit? ... Aah-hem! ... Oh! - 2 cents? ;-)

How about using the TRANSACTIONs? (Even if you're dealing with free tables, you can make them transactable.) Then you can verify/validate the transaction before committing the actual Disk I/O "write" operation, and roll it all back if something went astray.

HTH.

I tried it in the past, and it did work (I think).

Of course, nothing would prevent data corruption during transfer over a network if the network hardware's malfunctioning (say, someone hacks the cable with an axe to steal the copper... I kid you not, colleagues! It happened to me approx. 7 years ago: some dude cut the ground cable - and the entire block stayed without electricity for over 24 hours! We laearned later (from police) that the thieves do that to sell the copper for scrap metal.)



Regards,

Ilya