Topic: MS access database unable to operate with more than 1 or 2 users

[JBrennan]

Thanks Boris for your quick response the other day and Mary for your patience with me as I had some basic flaws in my understanding of linux commands.  It looks like we have finally nailed it.

db accounts show nameofibay
now includes:  VetoOplockFiles=/*.mdb/*.MDB/ 

I did have to do a reboot before the different computers could edit the database at the same time without hanging or running slow.

The relevant commands then were:

db accounts setprop nameofibay VetoOplockFiles /*.mdb/*.MDB/
signal-event ibay-modify nameofibay
signal-event post-upgrade
signal-event reboot

John

[janet]

JBrennan

I did have to do a reboot before the different computers could edit the database at the same time without hanging or running slow.

As you are effectively making changes to samba configuration, it is always the case that workstation users need to log off and then log back in to pick up those changes.

I do not see it necessary to reboot SME after having done
signal-event ibay-modify nameofibay

[purvis]

I would of tried turning off opportunistic file locking on the windows client os first then compared for trouble and performance on the client computers first.
I always suggest turning off opportunistic file locking on client computers where performance for a client to server is not affected much.

[JBrennan]

As you are effectively making changes to samba configuration, it is always the case that workstation users need to log off and then log back in to pick up those changes.

That is helpful to know, but I’m struggling to understand why it seems to work one day, and not the next. Yesterday (after the reboot), I tested with 4 computers editing the same table in the same access file and it worked wonderfully. Today, with client computers logging in afresh, a second computer trying to open the program and connect to the Access file hangs on start up!!

db accounts show ibayname  returns:

regulated=ibay
CgiBin=disabled
Gid=5012
Group=shared
name= Database and Backup
PasswordSet=no
PublicAccess=none
Vid=5012
UserAccess=wr-group-rd-group
VetoOplockFiles=/*.mdb/*.MDB/

I would of tried turning off opportunistic file locking on the windows client os first

From what I read, this increases the chances of corruption. And as I mentioned in my first post, the old linux server (whatever it was) was handling things fine.

John

[janet]

JBrennan

Perhaps you should also disable opportunistic locking (oplocks) on your windows workstations
http://www.samba.org/samba/docs/man/Samba-HOWTO-Collection/locking.html

[purvis]

here is an registry entry that needs to be created or edited to force/stop a windows os 2000/XP  client computer from using oplocks.
setting the entry to 0 will allow oplocks to be used from a client computer, the default of windows when not registry entry exist and the default of windows too.
reboot of the client computer is needed after changing the registry in order for the setting to take place.
a trick i use after making a change on one computer is to use the windows regedit to export the registry setting to a file ending with ".reg".
then i edit the reg file to remove any unwanted registry settings exported into the file.
then by just coping the reg file to other computers and while logged in as administrator account, just type the file name you saved with the .reg file extension, then reboot.
i have two files i use for this purpose, oplocksdisabled.reg and oplocksenabled.reg
this only changes the client oplocks, there is a server oplock too, search google.

Code: [Select]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MRxSmb\Parameters]
"OplocksDisabled"=dword:00000001


[JBrennan]

Thanks Purvis for your latest post including help with updating registry entries which I will save for future reference as I approach such a task with much care.
I’ve just had a visit from a guy with more computer nous than I, and he not only updated a couple of computers with the registry key you posted, but also ruled out interference from the Anti virus program, and checked the MS Access settings.

But testing with these two computers still produced the same behaviour!??!
John

[Stefano]

But testing with these two computers still produced the same behaviour!??!

then it's a Access issue..
another good reason to trash access

[MSmith]

@Stefano:  that may be, but OP did say that "previous Linux server, whatever it was" handled the requirement just fine.

So JBrennan, it might be time to give up on SME for now.  What about just using XP Pro as a peer "server" for your half-dozen workstations?  Give it plenty of RAM (up to 3 gigabytes, but more than 2 won't help that much) and do this:

Right-click My Computer, choose Properties, click Advanced tab.  Click "Settings" in Performance box.  Click this new Advanced tab.  Tick "Background Services" and "System Cache" radio buttons.  "OK" until you're out, then reboot. 

Gigabit Ethernet network-wide.  Don't use the peer server as a workstation.  Don't bother with antivirus on the peer server.  Use hardware RAID-1 on the peer server if its chipset supports it, as XP Pro won't do software RAID without a hack.  How big is the database?  Maybe copy often to a Dropbox folder for offsite backup and versioning.

[MSmith]

And oh yeah, you can run the peer server headless with Remote Desktop, or VNC, or even LogMeIn.

[purvis]

JBrennan

Trust me here, you are going to have to get a grip on what oplocks(opportunistic file locking) does.
Microsoft created as far as i know and included it in windows NT. Basically to speed up sharing of files in a terrible way that does not work with general programming of sharing and locking files or locking records within files.
Do a google on either "oplock" or "opportunistic file locking".
I believe linux has had to deal oplocks because of windows nt os.
Here is microsoft suggested way of controlling it with the registry.
http://support.microsoft.com/kb/296264

I have read that microsoft did not want make any corrections to oplocks in order to sell MS SQL, that is their answer some i had read, but that does not solve my issue either.

You have to turn oplocks off all the workstations not just one if you are going to control oplocks from client computers.
Windows client workstations have a choice of either oplocks being off or on, the default on a windows workstation is it that is turned off(the windows registry entry for oplocks is not created.)
In Sme Linux, you can turn if off(on the server side) i believe, but you can leave it turned on, the default state, or set certain files names to not use oplocks. This is something that cannot be done in windows as far as i know.
By turning it off on all client workstations, this gives you a chance to test your software for trouble as well as performance.

As mentioned also, you can test or run access on another windows nt peer computer acting as a sole server, I did this for years before using Sme Linux.
There is also a setting on a windows nt os computer where you can set the oplocks to not be allowed in a server role, set the above microsoft website link.
If you set the windows nt os computer server to not use oplocks in a server role, you then do not have to make changes to any client computers registry as far as oplocks go.
and if you like that setup, as was suggested, run the computer without monitor and use ultravnc remote control software.

One thing to consider, was your access application written correctly to begin with for a multi-user system, if not, and it was written for a single user, then you will always have issues when it comes to multi-users.

One a sme linux server, i suggest doing what i did, have a way to see what files are in a open state, this will help you to possibly figure out what is going on.
See what files are open when a client computer has oplocks turned on compared to turned off.



Here is a problem i solved.  We have databases in use with a certain program. Everything runs fine when oplocks are turn off, but there are some reports at the end of the day and end of the month where it would take 20 minutes to run these routines. To solve that problem, i only let a single certain workstation run these routines with oplocks turned on and all other workstations oplocks turn off.
Also on the Sme Linux server, i set vetoplocks for all files that had nothing to do with high performance in doing the daily and monthy routines, i tweaked my oplocks so to say, but like i said,  on other client workstations the oplocks where all set to off using the registry entry.

And personally, i disliked Access from the first time i used it from years ago.
It will get you by for a few years, but then there is a time when you probably are going to have to bite the bullet and use something more robust and dependable.
If somebody tells me they written something with Access and are using that a database, i just think how low skilled they are with database programming.
I personally would suggest you move away from Access ASAP, even if it takes a few years to do it. And Dbase is not much different.

 




 

   

[purvis]

here are two php webpage scripts to view your open files with.
one will run in a loop every 2 seconds, refresh webpage every 2 seconds
you can see how your files stay open on a sme server with oplock locks turned on a windows nt client computer.
create these two files in your  ibays/Primary/html folder and end the file with a ".php" extension.
notice that when oplocks are turned on, that your files are still open for about 10 seconds on the server after you have gotten out of your windows based program.
notice that when oplocks are turned off, the file close immediately when you get out of your windows based program.

i use these programs all the time to detect if somebody is either logged into the server, or logged in and has files opened. this is a must know when you are maintaining the sme server as to not damage the files the workstations are using.


no loop code
samabafilesopen.php

Code: [Select]

<!--DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"-->
<html>
<head>
<title>Server Status</title>
</head>
<body>
<tt>
<b>
<div>
<?php
print "<pre>";
echo  '<font size="4">';
print "Server Status Report\n";
print "\n";
print "Current date : ";
system("date +'%m-%d-%Y  %T %Z' | col -b",$return);
print "    URL name : ";
print  ($_SERVER['SERVER_NAME']);
print "\n";
print " Server name : ";
system("uname -n | col -b",$return);
print "************** UPTIME ***************\n";
system("uptime | col -b",$return);
print "\n";
print "\n";
print "************** SMB STATUS ****************\n";
system("smbstatus | col -b",$return);
print "\n";
print "\n---end of report---\n";
print "</pre>";
?>
</div>
</body>
</html>

loop code added
samabafilesopenloop.php

Code: [Select]

!--DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"-->
<html>
<head>
<meta http-equiv="refresh" content="2">
<title>Server Status</title>
</head>
<body>
<tt>
<b>
<div>
<?php
print "<pre>";
echo  '<font size="4">';
print "Server Status Report\n";
print "\n";
print "Current date : ";
system("date +'%m-%d-%Y  %T %Z' | col -b",$return);
print "    URL name : ";
print  ($_SERVER['SERVER_NAME']);
print "\n";
print " Server name : ";
system("uname -n | col -b",$return);
print "************** UPTIME ***************\n";
system("uptime | col -b",$return);
print "\n";
print "\n";
print "************** SMB STATUS ****************\n";
system("smbstatus | col -b",$return);
print "\n";
print "\n---end of report---\n";
print "</pre>";
?>
</div>
</body>
</html>


[JBrennan]

Hey Purvis, you are recording a lot of interesting stuff here. 
In in regard to your first post giving a background to Oplocks, I am waiting on someone to come and help me work through that stuff as it is a bit beyond me.

I have tried out the php webpage scripts. I have successfully copied them to primary/html  folder. But how do I get them to work? I found a way of opening them up but the browser just treated them as text files.

In regard to the earlier posts by others, I don't believe it is an Access issue.
I have just run a test linking a couple of windows work stations with the data file on another windows workstation and they ran like a charm. The computers edited adjacent records in the same table at the same time without complaint or noticeable slowness. And I had not applied any oplock registry adjustments to the computers involved.

[janet]

JBrennan

Open a web browser and type in the URL of
http://www.yourdomain.com/samabafilesopen.php
Do similar for the other filename

[JBrennan]

Thanks Mary.
Its asking for a Username and Password.
The only ones I have set up are for admin and root. So I have tried usernames admin, root and Primary without success.