New records not being visible to other data sessions.

[AndrewMozley]

An application has been running fairly satisfactorily on several workstations for some years, but after a change in the file server, it seems that new records in a table are not always visible to other forms in the same application unless the user closes the application and starts it again.

For example, there is a customer table, XCUST.dbf, and a form in the application which allows new customer records to be created.

A new customer is created in that form (which the user chooses to leave open on his screen). Then another form ‘Sales Order Entry’ is activated – with its own data session. The customer is identified, using the primary key (it would report an error if an invalid customer were asked for). However the details from the customer record (name and address &c) are not shown correctly: instead, the fields are being taken from the first record on file. It is as if the record pointer in the table XCUST has not moved.

The XCUST table also remains open in another data session : there is an object which provides common services (to several forms in the application) which keeps that table open.

The problem does not happen if the application is being run from the server - which is the only way that I can log in remotely at present. And it goes away (for the user who has noticed the problem) if he closes the accounting application and starts again. I also think it goes away if the user closes the ‘Customer maintenance’ form, but have not yet been able to test that myself.

Have any other people come across a problem like this? I would be grateful for helpful guidance.

Thanks. Andrew Mozley

 

[Olaf Doschke]

If this worked and the only change was the file server OS version, it smells like an oplocks problem (SMB version).

At the same time, this part of your description suggests you're not using and binding data in some of the several standard ways VFP suggests:

However the details from the customer record (name and address &c) are not shown correctly: instead, the fields are being taken from the first record on file. It is as if the record pointer in the table XCUST has not moved.

The case new data is not seen from other workstations has haunted me way back when the oplocks phenomenon wasn't known. But then, well, I would query with a left join and have detail records missing, they would only show up in the workstation, which entered it (ie from its cache).

This just sounds like a SET RELATION is unset and you don't move in the details table to the customer records.
Maybe you're doing a LOCATE and then do something IF FOUND(), which up to now was always working and running. And since this problem means you now find nothing (even though you should) you branch into the ELSE branch and there GO TOP. Anything that only runs now, for the first time, as this is a corner case you usually never had.

So what are you doing in code here? Working with a relation? With SET FILTER? SET KEY? Views? Queries?

It's likely you have the problem caused by the newer file server Windows version AND on top of that your code reacts wrong to the case of not finding data, for example with GO TOP, where you should stay at EOF to not display something unrelated.

Or you neer LOCATE or INDEXSEEK() or SET RELATION or SET FILTER/KEY and just are at the top record because that's where any USE of a table or any table of a DE starts with its record pointer.

Nye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

One thing I also always forget to check: It's important that all caching of server, switches or routers on the way from server to workstations only cache in write-through mode.

In short, as I'm also no hardware guy in all these details: The default hardware settings for drives and network cards usually are good for performance, but not good for the databases atomicity concept and consistency of the transactional processing of data changes. If you want transaction isolation, then only that of the database itself, the VFP runtime, nothing on top of the OS and the file system.

Also, VFP always used tricks about the file storage not in the toolbelt of usual software, doing some things about NTFS in non-official ways. I have the suspicion some of these VFP mechanisms are not compatible with hardware and software performance optimization strategies and such trickery was also a reason to retire VFP.

Because the solution to the oplocks problem was to turn the oplocks mechanism off, it's indeed also a caching mechanism, not as the name suggests locking. And it became increasingly difficult to do so. In the end only by going back to an older version of SMB.

And that's not advisable as it invites Ransomware.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[AndrewMozley]

Thank you for your thoughts, Olaf. I will pass your note about caching through to our network support team.

The code is being executed in the Valid() method of the text box into which the user has entered an account code; the ORDER is set to the primary key of the Customer table, the 'account number' (alphanumeric). The code is effectively :

  lcAcount = RTRIM(<what the user entered>)
  SELECT <The customer table>  
  SEEK lcAccount

The value of FOUND() is tested; this is .T.

This has worked happily for some time. But the problem is that (recently, for one user on the network, and only occasionally) the fields of the record are those of the first record on file; it is as if the pointer in the file has not moved.

There is no active SET FILTER, no SET RELATION, no SET KEY. And there is no binding of the control to any field in the Customer table. And at this stage there is no ‘Detail’ table involved.

I would be grateful to learn if any users other than Olaf have experienced a problem like this.

Thanks again.

 

[Olaf Doschke]

If that's the case, have you thought of rebuilding the index?

There's a simple general index check whether it finds all the values it should find:

Select distinct field from yourtable into cursor testvalues
Select testvalues
scan
   If SEEK(testvalues.field,"yourtable","indextagname")
      If Not (testvalues.field==yourtable.field)
         ? "index points to wrong record for value ", testvalues.field
      Endif
   Else
      ? testvalues.field "exists in the table but is not found in the index." 
   Endif
endscan

You say you check the SEEK result with FOUND, well as you see from my code there is the SEEK() function, that has this built-in and returns .T. when the seek succeeded.
Besides that, when an index is corrupted that can just mean you're set to the wrong record, maybe also recno=1, though that's not likely.

Anyway, to thoroughly check an index you therefore also check whether the record number found by the SEEK is actually having the sought value in its field.

AS you end up in recno 1 I still suspect something else is going wrong, have you actually single stepped through your code and see it really get where yoiu expect it to go?

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Besides this index test. What you show is locating the customer.

You say your problem is in a detail table about customers, not the customer itself. So how do you navigate there, ie into orders or other details?
That would be where you display the whole table instead of only related data.

If you don't SET RELATION from customer to detail tables and don't SET FILTER or SET KEY, do you SQL query from detail tables or what do you do in that aspect?
If you only LOCATE to the first detail record matching the customer, then somehow GO TOP, you still get to record 1. And even in case you don't go to record one, users can scroll up to records not related to the customer, LOCATE doesn't filter data, it just moves the record pointer and a user can simply scroll back to top.

You can do a quasi query with a LOCATE and DO WHILE FOUND() read data and CONTINUE/ENDDO, but when a table is bound to a grid the grid scrolling is not bound to just the records fitting the LOCATE FOR condition.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[AndrewMozley]

Thank you for your continued interest and support, Olaf.

You say your problem is in a detail table about customers

This is not quite the case. There is no detail table involved - just one table, the Customer table. The user just wants to identify the customer before he starts entering a new sales order.

To that end, he keys in a customer account code, and this code is executed :

lcAcount = RTRIM(<what the user entered>)
SELECT <The customer table>  
SEEK lcAccount

The value of FOUND() is tested; this is .T. I appreciate that I could have combined the SEEK and FOUND() statements into a single IF SEEK()

There is no SQL query at this stage.

What happen then is that code which has worked reliably in the past sometimes does not work at one workstation on the network: the fields of the current customer record are not those of the customer whose account code has just been entered. It appears to be a network problem, and the only way the user can overcome the problem and enter the sales order is to exit the application and start it again. In this case the code works and the user can enter the order.

 

[Mike Gagnon]

Private datasession?



If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Olaf Doschke]

So you do find the customer, but the fields in its record are not the most recent data?
You talked of instead going to recno 1?
Are there more records than one about a customer in your customers.dbf?

If you SEEK you only find the first one of them, that's normal.

SEEK() is indeed not only cmbining, but ensuring you seek in a specific index tag. The SEEK command relies on the right index order being set.

You have my index heck routine, have you found an index error with it?
You could or should perhaps start logging things that need to be set right, like SET('ORDER'), is it the index in which you really want to seek at that moment?

I know the "worked before" argument, but there can be many changes having a side effect, especially programming for the current workarea and not specific to certain workarea alias names, some code, even of someone else could SET ORDER TO in your customer table workarea, while it wanted to set order somewhere else and that has a side effect on you.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

And last not least, a few more things to investigate:

1. One very obv0ous reason you don't find the latest values in some row which were just updated at another workstation is that this other workstation buffers changes and its tableupdate() failed.

2. If that's working (returning .t.), not seeing that written change can have two reasons:
a) the already discussed write caching effect when you(VFP) or the network routers or file serer caches writes instead write-through caching - which just means no waiting with writes.
b) the updated dbf does not cascade the information about invalidating read caches in other clients. So the other side of the cache fails. You rad back the same old value. See, that's also part of caching and is quite an effort done mainly by the file system knwoing which clients have open the changed file. And that's also were oplocks fail to work, the SMB3 protocol thing.

3. When you have the customer table open at the workstation not seeing the changes, then SET REFRESH could have an effect, though that's mainly about BROWSE windows.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Andrew,
are you checking buffering in your SQL..Select ( Buffering = .T. ) ?
Koen

 

[AndrewMozley]

Thank you Koen.

I take your point. However there was no SQL statement involved in the addition of the record to the customer table, and there is no SQL statement involved in locating and reading the customer record (in a different data session).

- Andrew

 

[Koen Piller]

Andrew,
I see but what is the buffering state?
Koen

 

[AndrewMozley]

The buffering is 4 (Pessimistic Table). When the user asks to SAVE the newly created customer, a TABLEUPDATE() command is issued.

At present I have not been examining the return status from that command. I can and will do that. Feel it is unlikely that this has been causing the problem, but any checks that can be done will help.

Thanks. Andrew

 

[Olaf Doschke]

We know the code didn't change, but the situation can change and put you into the else branch - which doesn't exist in your case.

It can be interesting to see the return value because if it's .T. and the new record still is not seen on other clients or another data session you have a good reason to think in the direction of the oplocks problem. If you get .F. you have a strong sign your code fails in the changed circumstances of the new server, which could mean many things but then likely not the oplocks/SMB3 problem.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Koen Piller]

Amdrew,

A check on the return is obligatory.
Try also with Buffering = 5, just as a try.
Koen

 

[GriffMG]

I can't be sure this would work, but, if you made sure all the tables were closed, then wrote a little app to hold then all open in shared mode on the server - using 'real' paths to the tables (not mapped drives if that is possible) then that process would get a kind of 'ownership' of the tables and you MIGHT find as your workstations open the data they get it shared 'properly' too.

This is a trick we used to employ back in the day when OpsLocks were more aggressive - the process holding all the tables open could be used to do other things too - run packs and redindexes (if the tables could be opened exclusively), scan data for anomalies, generate regular reports - stuff like that.

Regards

Griff
Keep [Smile]ing

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

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​