Mysterious VFP event

[dmusicant]

I created a new field in one of my Foxpro tables, a numeric (6,2), browsed the table and hand entered about 45 values in a browse window. The browse runs inside a complex PRG. When I went to close the table, VFP hung and I had to close it by killing it. When I went back to look at the data, it was all missing. The new field was there, but the values I entered (some 45 of them), were not there. In my experience, when changing data in Foxpro and you move to a different field in the table, all the time being in a browse, the table is updated automatically. What could have happened? Yes, I can reenter the data (it required looking up the values on the internet), so I have my cure for the problem, but I'm upset that I have to do so. What could be the matter. BTW, VFP 9, at least concerning this app has been acting up lately, is very extremely slow (taking over a minute, if it gets there at all) to access the tables on my NAS. Is there something I can check to fix that? It's just the last few days.

 

[Olaf Doschke]

If you have table buffering, no record will change right away, that must have been the case.
As your tables are on a NAS and you have problems lately, that can be a network problem, too. A NAS is always a somewhat limited file server and mostly linux based with samba or something like that. If you use this and already know it shows problems for a few days, then you should not put anything original in there, until you solve that problem, so part of your anger is self made, isn't it?

If any data entry is valuable for me, I'd always first store local, then on a network attached drive. Of course also to backup the work.

Bye, Olaf.

 

[dmusicant]

Thanks, Olaf. My first Foxpro class (2.6 for DOS), our instructor had us save to temporary files before updating the permanent data. I suppose I can do that, have the temporary files local, that would help prevent problems. That would introduce a big new wrinkle in my application, but I think I should seriously consider it. Thanks again for that excellent suggestion!

I've never seen this before, so thought I was safe. I don't think I have table buffering going on, but maybe I'm wrong there. How else to explain this but table buffering? Where would I check that in VFP 9? The app actually will run in FPW 2.6, I long ago made whatever tweaks were necessary to have it work in VFP.

In VFP, under Options / Data / Locking and Buffering, Buffering is set to Off. I don't know what to think. I searched my local data HD, and my server and found no alternative table where I could have been working that's been modified recently.

I just reentered all the data.

I figure I can try some temporary band-aid fixes: Reboot the local machine, the wireless router, the NAS and the DSL modem.

Don't know how to troubleshoot a network issue with the NAS. The other day, my 2 month old NAS was inaccessible from any of my machines, wireless and ethernet connected. I had to power down the NAS by pressing the on/off key, holding it, then restart the NAS. Then access seemed normal. I called Synology support and was told I should put in a request at Synology's website on the problem. I did so and got an auto-response email but no follow up AFAIK. Don't know if that problem is part of my current sometimes-slow access problems. It seems to me that there's a complex interaction of components. Local machine, NAS, maybe even the router.

 

[Olaf Doschke]

I can confirm network troubleshooting in general is very cumbersome and often even has no result.

I wasn't thinking of really a two staged saving in general, just as the developer I load both source code and data to a local repository (at least if the backend is not a server anyway) and work on that. If you have new master data you'd rather write an update for that than enter it into tables.

If you would want the safety of double saving to a central database and a local backup I'd do that in parallel, on top of saving to the DBF take the locally queried data and COPY TO a local DBF. You may even write a local log that way. That of course means you have to have a client/server approach anyway, with loading data into a view pr any type of cursor instead of the DBF itself.

And about the option settings and what was eventually set at the time you lost your entered data: Any setting in the options can be overriden at runtime, any default is just a default, it's not set in stone. USE some.dbf SHARED means using it shared, no matter what option is set in regard to exclusive, you hopefully know that, so you know options are not telling you anything about what has been the case, it's just telling what would most probably have been the case.

Buffering is a setting you can change per workarea and the form.buffermode can override the options default. If you work with third party code/framework, you may not even know you work buffered. Indeed several settings have to come togehter for table buffering, SET MULTILOCKS must be ON. But eg Cursorsetprop("Buffering",5,0) turns on optimistic table buffering for workarea 0, meaning for all future dbfs opened even without any cursorsetprop specifically done for them. This is per data session, but as the developer you typically work on one datasession only, the default datasession. So this can sneak upon you.

Bye, Olaf.

 

[Mike Lewis]

As Olaf has said, if the data was buffered, that would explain the behaviour you saw. But so would transaction processing. If your PRG had issued a[tt] BEGIN TRANSACTION[/tt], and didn't do a corresponding [tt]END TRANSACTION[/tt] before the crash, then you would lose the data in the way you described.

I admit it's not very likely that that would have happened without you knowing about it, but you did say it was a complex PRG, so it might be worth checking.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Overall,

neither buffering nor transaction would be mysterious events and I don't know of a bug in saving to a DBF from a BROWSE, there are bugs, but more about varchar than in this direction of failing to update the underlying DBF. So if you don't find anything in your complex PRG doing buffering or transaction the "mysterious" event most probably was not in VFP, but in the NAS or network.

Especially the same buffering or transactions would make the problem repeatable, if it was caused by that.

Bye, Olaf.

 

[dmusicant]

All these ideas notwithstanding, I still have uppermost in my mind the idea that I was somehow working on another table. It seems very unlikely because the app finds the data from metadata, specifying the exact path. However, the behavior I describe in my mind, with my code, corresponds only with working on a different table. However, just prior to doing the data entry for this field, I created the field. It didn't exist until a few minutes before I did the data entry to it. So, any other versions of this table (it's saved in many locations, as backup and for offline usage on occasion) did not have this field in the first place. When I went to look at the table after having populated fifty records with this data I only found about a dozen records with non-zero data. Those dozen records I had entered a bit earlier yesterday morning. I had some squirrely things happen with the network and then the last ~48 values I'd entered right in the browse window were gone, zip, as though I'd never entered them.

This app was written entirely in FPW 2.6a, including an FPW screen. There are no forms, no properties, events. I run it in VFP 9.0, however it will run fine in FPW 2.6a, as long as the tables are compatible, and in general they are. VFP is pretty much default in its configuration. The only tweaks I've made (one or two, perhaps) were to make VFP run OK on my machines.

 

[Olaf Doschke]

Changing table structure is not buffered, it's impossible to buffer, this is directly done to the DBF file, it onvolves creating the new DBF, copying in the old data and deleting the old version. Entering data could only have been done afterwards, or else you wouldn't have seen the new field. What you report is that you see the new field, just empty, and the only regular explanation besides any network bug would be your data entry was buffered. And that must have been seperate from the table altering, you can't have alter and data entry in a buffer. Altering a table is never buffered, as it's done on the file level, never in memory.

Bye, Olaf.

 

[Dan Freeman]

I had some squirrely things happen with the network and then

and the you rebooted?

Windows does not *necessarily* automatically correct when squirrely things happen on the network, nor does VFP. The single most important thing to a VFP app is an absolutely uninterrupted connection to the data.

Remember that anything you "write to the table" is actually written to a cache maintained by the network. The network will take its own dang time to actually update the physical table, goaded along generally by someone else asking to see the same data. If the network goes wonky and you're still writing to the same cache I can imagine the cache getting orphaned once the wonky phase passes.

There are a lot of things you can fix from within a VFP application. A wonky network isn't one of them.

 

[dmusicant]

Seems, Dan Freeman, that you might have the idea about what happened. Sleeping on it last night I had another idea. The NAS is RAID1, so there's DISK1 and DISK2, identical WD 3TB Red HDs. I don't know how that works. Is it possible that the table alteration and first input of data (~12 records), was mirrored to DISK2 on the NAS, but the input of the other ~38 values to the newly created Num(6,2) field wasn't mirrored yet before network issues developed to prevent the mirroring and I wound up with a reversion to what had been saved to DISK2?

 

[dmusicant]

I suppose my idea in the last post of DISK1 info not reaching DISK2 is not inconsistent with Dan Freeman's idea about network wonkiness. Indeed, I see no other explanation. The info must have been cached on the network, failed to update either of the RAID1 HDs, reversion to saved data occurred, and I was left scratching my head and fuming, having to worry about what could have happened and having to reenter all the data.

And the answer to your first question, Dan Freeman, I am pretty sure I did not reboot the local machine, at least not before the data disappearance happened.

Clearly, I should introduce another layer in my code so I don't experience something like this again, or worse.

 

[dmusicant]

I have another idea, maybe someone can speak to this...

The delays I get in using VFP against data on the NAS HD's are bad enough, but losing cached data that appears uncached is unacceptable. Synology DSM 5 (the latest OS for their NAS's) has a local network cloud feature I've heard about, haven't tried it yet. It is supposed to (if I understand it correctly) let you work locally in an application, i.e. on local data, and the NAS OS ensures that when you work on other local machines you will be able to work on the updated data. This, if true, would seem to be a dream solution. Hopefully, no glitches, great speed in working with local data, and presumably the NAS handles the update of local data on other machines in an unobtrusive manner. Could be a pipe dream, but I'm going to look into it. Of course, VFP is pretty quick working on local data.

 

[Olaf Doschke]

Well a feature like that sounds like a cloud syncing in the same way as Dropbox, GDrive or OneDrive. This will only sync complete files and be a nightmare more than a dream, if your DBFs grow larger.
If you really need DBF/CDX changes to spread into the cloud a client/server solution is needed, and a database server or cloud database, not a cloud file space.

No matter how advanced NAS OSes are, a NAS is no file server, syncing to cloud space is not a connection to a remote database and this always has disadvantages in concurrent access. It would even not be as predictable as "last change wins" optimistic locking, but somewhat like "any change wins", if two concurrent users each edit their local copy of a dbf and the cloud service part is syncing each local change with the central cloud space.

Bye, Olaf.

 

[Dan Freeman]

I'll agree with Olaf here. Cloud storage is no answer. If you want data stored in the cloud, use a database designed for that. VFP isn't one of those.

VFP relies on rock-solid reliable persistent shared connecions to files. The part that manages connection sharing is the LAN. Move the data to a place not controlled by the LAN and you've lost VFP's ability to share files.

 

[cdm99]

What Olaf said.
You might try adjusting the registry keys for OpLocks and CachedOpenLimit, and depending on your version of Windows try disabling SMB3. In a commercial environment (Sage Pro Series accounting) we need to do that to make network operations reliable. Good info on that whole science here:

http://www.dataaccess.com/whitepapers/opportunlockingreadcaching.html

It's written for DataPro but this issue affects all databases that aren't self-contained.
Also, for the speed issue, try disabling real-time scanning in your AV program.
HTH

 

[Olaf Doschke]

If your NAS drive is synced to cloud space that even could be your "mysterious" event cutting you off while adding the values in the browse window. As you say VFP saves avery record change, the NAS will not start syncing after each file access, it'll take rounds and see which files changed since last syncing in less frequent intervals. But if the NAS started syncing at the time you had input the first values into the DBF, VFP was cut off the file, which doesn't shut down the browse window but leads to the "squirelly" behaviour. You could only prevent that with exclusive access and in regard to a NAS not running a windows and only emulating/translating SMB protocol and NTFS file system I don't know if that would respect your exclusive access anyway in it's own native file system. You can have all kind of side effects.

As DAN said VFP needs LAN access, best a real Windows file server, and the sharing then goes as far as the LAN goes, any additional non VFP process with read access to the file can sync it in any state - final or not - to the cloud space destination. And if that even cuts off shared access to clients currently having that file open, as file handle management also isn't done as Windows does it, and the NAS locks the file to prevent changes to the file it currently uploads, then it causes trouble for VFP to sync it's state with the NAS file and that would be it.

Synology an promise you to be 100% NTFS/SMB compatible as often as they like. Alone for locking VFP makes use of undocumented inofficial NTFS behavior that surely isn't software emulated.

Bye, Olaf.

 

[mjcmkrsr]

Hi,

Well a feature like that sounds like a cloud syncing in the same way as Dropbox, GDrive or OneDrive. This will only sync complete files and be a nightmare more than a dream, if your DBFs grow larger.
If you really need DBF/CDX changes to spread into the cloud a client/server solution is needed, and a database server or cloud database, not a cloud file space.

Olaf and Dan are right - the cloud service offered by Synology is a FILE syncing service. Hands OFF if more users are working concurrently on living data.
hth
MK

 

[dmusicant]

OK, looks like the Synology cloud idea for my VFP data on a variety of machines on my LAN isn't a good idea. However, I'm the only user in this environment. No one else is involved. I suppose I could get into trouble if I have a table open (in whatever state, including a browse) on more than one machine at a time, it could happen.

Maybe I should work locally and at convenient times write local data to the NAS. There's a point in my app where it goes to the FPW screen, and at the point the table that was in use is closed. I could write code that copies the table (local now), to the network, if it was just changed. I could write code to see if local data is older than the network data and if so, download the updated table. This app uses hundreds of tables, they aren't changed unless I work with them directly. I could manage the updating process with metadata. Thus, have flags to indicate if each machine has downloaded the updated table... check that instead of Windows sometimes peculiar data. The satisfaction level of this approach would be involved with how long some of these downloads would take. Most of the tables aren't large, but the bulk of it is in FPT files, the largest of which is 31KB. I just copied that to this, my slowest machine on the network and it took 15 seconds. I think that tradeoff might work OK. The networked versions of the files would never be accessed by VFP directly.

 

[dmusicant]

No one has commented on my last post. Maybe it needs clarification. The idea is to have data on each of my local machines. The app right now has over 300 DBFs. There's no database, they are free tables. I can and do create new tables at will and there's a system so that where ever the app is run, it will know about the new tables.

I could have copies of the tables not only on the NAS, but on each local machine. When running the app, if a table is changed it's new version would be uploaded to the NAS and flags set for it, one for each of the other local machines. The flag would be .T. for a local machine if it needs the new version of the table. If and when one of those local machines tries to open that table, it first checks the flag and if .T. it downloads the latest version of it from the NAS and the flag is set to .F. for that local machine. If the table is then changed, the new version of the table is uploaded to the NAS and the flags for the other local machines are set to .T. . The largest file is right now 31KB, not ungainly I think.

Do you like this idea?

 

[Dan Freeman]

Seems like a lot of work and you'll end up with a non-trivial application architecture that's STILL running on an unstable platform.

Note that there's nothing inherently unusable about NAS. The acronym stands for Network Attached Storage. It's storage. It's still managed by the network. Stabilize the network and you'll be fine.

Seems like you're trying to force a square peg into a round hole. <shrug>