Imported data is corrupt also?

[LaRetta]

I had a served file which crashed yesterday. It was so trashed that Recover (after 4 hours of working correctly) would freeze up. So I exported new and modified records from 50 tables as Mer. Then I drug one mer to my Developer shortcut to open it and check it for empty or trashed lines. I visually checked them carefully. And then I ran a Set Field[ field; field ] - setting a field to itself, hoping that if there was a problem with it, it would show then. I repeated with every MER file. None showed damage.

I then imported these files back into a clean prior backup (that I know has never crashed). But the new clone crashed when a script required setting one of those records. I deleted that specific group (which happened to be the Invoices we were in the middle of shipping at the moment of crash). New clone ran the process fine on the next day's shipments. I re-imported the raw invoices and clicked 'ship'. File crashed. This makes no sense to me. Exported data shouldn't still contain corruption!! Has anyone else experienced this and what can I do to cleanse the export data?

I have since read a techinfo article to export as tab or csv. Is this what I did wrong? I would like to know as quickly as possible because we crashed again today. And I have to repeat this process tonight.

[gdurniak]

yes, exported data may contain "illegal" characters, see Tech Info Article 949

http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=949&p_created=1115172483&p_sid=wzxBcdFh&p_lva=1580&p_sp=cF9zcmNoPTEmcF9zb3J0X2J5PSZwX2dyaWRzb3J0PSZwX3Jvd19jbnQ9MjI0JnBfcHJvZHM9MCZwX2NhdHM9MCZwX3B2PSZwX2N2PSZwX3BhZ2U9MSZwX3NlYXJjaF90ZXh0PUFTQ0lJIGNoYXJhY3RlcnM*&p_li=&p_topview=1

also, keep in mind that your clone might be corrupted

[LaRetta]

i considered that my clone might be corrupted although it has never left my sight and thus has never crashed. And each time I step us back to a different prior clone. But I pull in the current data that has crashed - that's why I began to suspect corruption in the data itself. I don't believe the issue is the design this time. Thanks so much for the link!

[LaRetta]

I am looking for a program which can scan through text and quickly identify invalid or corrupt characters. David McKee had a program named Character Sieve (thanks Fenton), which could take a text export and look for unexpected characters but it's for Mac.

I find it hard to believe, in this world of data magicians and technology and software (and men on the moon), that such tools aren't readily available for Windows. I thought an export produced raw good data and that is simply not so! And yes, I believed in the Easter Bunny also.

I want this ability (Clorox a data-set) in my Developer toolkit. Ideas would be most appreciated and, if there is no such handy tool, we need to make one.

LaRetta :wink2:

[Fenton]

Here is the link to David McKee's stuff. Most of it is quite old. It's too bad he didn't have time to continue updating his plug-ins, which he was doing in his spare time, after his regular job as a FileMaker engineer.

http://www.geocities.com/SiliconValley/Network/9327/index.html

Specifically, there is a Windows-compatible plug-in to show corruption, Field Inspector. But it is old; I have no idea whether it will run with FileMaker 7. It is clunky to check a whole file, as it works, as most plug-ins do, on a field-by-field record-by-record basis. You would need at the very least a loop.

http://www.geocities.com/SiliconValley/Network/9327/winfiles/fiTxWin.zip

To give you an idea how old it is, there is no version for Mac OS X; hence I use Character Sieve; which is very fast, and can do an entire file of exported text in one shot.

http://www.geocities.com/SiliconValley/Network/9327/macfiles/CharSieve2.sit

[LaRetta]

I have narrowed down the corruption. It was not found with Character Sieve. It was not found by visually scanning the text (which took HOURS). But it most certainly came back into my file through IMPORT. I found it when it again crashed our file. I have exported data as MER from a trashed design and re-imported into clean clone. I tried exporting/importing in every format to clean the data, each time bringing the corruption back into clean clone - stepping back in time to be sure I wasn't using a corrupted clone also. Nada. My file kept crashing only when script ran shipping process (in Invoices) - and ONLY when the imported Invoices were added back in the file. It would only crash where script was setting those Invoices with the ShipDate.

Invoice table layout with those import invoices present: ONLY when I clicked into ShipDate and ONLY on some Invoices, typing a ShipDate will crash me. Changing data in any other fields seems fine. And changing ShipDate on Invoices not during the crash is fine. One by one I identified the Invoice numbers that crashed me (by crashing me) then stepping back to prior copy and trying the next invoice number. I repeated my tests to be sure it always crashed on the SAME Invoice. Repeated tests were consistent - always crashing only on those certain invoices and only when entering a ShipDate on them.

Once I identified all the bad INVOICES, I deleted them. File seemed fine. I ran Save A Copy As (compacted). I shipped THROUGH those three bad days again. It worked! I thought I had it resolved. I then ran File Maintenance and received Error 824 (FM's attempt to recover). Stepping back and repeating (with clean copy of file) and without those three days of crashed Invoices, file is fine. I guess at this point, we'll re-enter the invoices because I can't isolate what further damage they may contain.

Now - firsthand - I understand why Steven and Wim are so fanatical about proper deployment. I've experienced 3 crashes (in a week) of the worst kind - the DATA. I always thought I had a crash covered ... if we crash, just grab backup and export/import into clean design and we'll be fine. NOPE. The corruption can be brought right back in through the import data!! I have NEVER trusted keeping a design which has crashed but corrupted data is worse because in 3 days there were over 4,500 changes and additions to 50 tables. Where could the damage be? I thought I could trust export/import to clean the data. NOT TRUE. Our business continues to work on another copy while I attempt cleanup, so I'll be doing another migration tonight to make us one file again (sans crashed Invoices for 3 days).

BTW, I might have been bringing the corruption back in because attempts to run Save A Copy As (compacted) and attempts to run Recover both locked up on the crashed file (both applied to independent copies of the file). So I just exported the data before FM could address the damage. Maybe this was my mistake. If anyone knows a better way of saving data if the file locks up on Save A Copy As (compacted) or Recover, I'm open to suggestions.

I will script a process which will loop through every field of every table, maybe setting the field to itself, to see if I can find further damage (after a crash). You couldn't pay me enough to go through this again. And somehow, I'll find ways to Clorox that import data so I can trust bringing it back in. I no longer believe in the Easter Bunny either ...