Microsoft Access Database Corruption on SMB Share

[Diode]

I run a pair of TrueNAS systems, one replicating to another read-only unit. Has been great for many years. Using RAID 5 SAS drives on HP DL380. We use a shared Access database for our MRP system, on a SAMBA share. After upgrading to Version 12, we started getting database corruption occurring regularly and apparently randomly. Typically once every 2-3 hours, and certainly at least once a day. Sometimes this was recoverable by built in Access database repair tools, sometimes by laborious manual means, or just cloning a replicated file to recover it. We have about 10 simultaneous users. After this database was relocated to a Windows 2016 server, the trouble completely stopped. (Yes, we are aware that Access and the Jet database engine have some serious inherent issues, but for various reasons, cannot migrate to SQL or such)

Has anybody else had similar trouble with Access databases on TrueNAS or FreeNAS shares, and if so, were you able to fix this, and how?
Any hints on what to investigate or try?

 

[anodos]

Hi, can you PM me a debug from the server? System->Advanced->Save debug. Have you observed this issue on 12.0-U2 (released on Feb 9)?

 

[Diode]

Have not tried this on 12.0-U2. Migrating back from the Windows server for a test would be very disruptive. Would saving a debug now be of use to you if the errors occurred a month ago?

 

[anodos]

Have not tried this on 12.0-U2. Migrating back from the Windows server for a test would be very disruptive. Would saving a debug now be of use to you if the errors occurred a month ago?

Possibly not. There were some issues with SMB service in earlier releases that are fixed in U2. If you have a reliable way of reproducing in a VM, that would be quite helpful in fixing the issue.

 

[ChrisRJ]

Have not tried this on 12.0-U2. Migrating back from the Windows server for a test would be very disruptive.

What about doing this on a test system?

 

[sretalla]

You might want to think about the locking aspects of the Access Database format.

Microsoft code relies heavily on their own implementation of file locking in SMB, so I would first consider it on an official Microsoft SMB stack (meaning a Windows server, perhaps a VM if you don't have another box available) before losing too much time chasing dragons with the nuances of SAMBA and file locks (which is already stated as very complicated and not perfect... http://wiki.samba.org/images/9/91/Get_share_mode_lock.png and chapter 17.2.1 here: https://www.samba.org/samba/docs/Samba-HOWTO-Collection.pdf... it's a tough read, good luck).

 

[Diode]

You might want to think about the locking aspects of the Access Database format.

Microsoft code relies heavily on their own implementation of file locking in SMB, so I would first consider it on an official Microsoft SMB stack (meaning a Windows server, perhaps a VM if you don't have another box available) before losing too much time chasing dragons with the nuances of SAMBA and file locks (which is already stated as very complicated and not perfect... http://wiki.samba.org/images/9/91/Get_share_mode_lock.png and chapter 17.2.1 here: https://www.samba.org/samba/docs/Samba-HOWTO-Collection.pdf... it's a tough read, good luck).

Access works fine with many users when hosted from a Microsoft server.

 

[Diode]

What about doing this on a test system?

We will try that at some point. Challenge is simulating multiple users randomly doing different things. Trying to run live MRP with risk of database corruption is scary.

 

[elPicione]

Hello

Sorry I don't have any answers but I'm interested too in serverless databases
Do you have any reccomendation? Do you keep sync enabled to make sure write queries go through? Does compression influence stability/speed?
Don't know if Access enables concurrent connections by default or you have to enabled it in some way

Thank you

 

[anodos]

We will try that at some point. Challenge is simulating multiple users randomly doing different things. Trying to run live MRP with risk of database corruption is scary.

It's hard to fix issues without a reliable reproducer. There were some SMB issues in earlier releases that may have been to blame. Also default configuration changed to using durable handles on pure SMB shares. This means support for byte-range locks is being handled internally in samba as opposed to setting fcntl locks via OS. If you can reproduce on 12.0-U2, try without durable handles on the share.

 

[Diode]

Hello

Sorry I don't have any answers but I'm interested too in serverless databases
Do you have any reccomendation? Do you keep sync enabled to make sure write queries go through? Does compression influence stability/speed?
Don't know if Access enables concurrent connections by default or you have to enabled it in some way

Thank you

No idea. I am not a database or filesystem expert. I used mostly default FreeNAS SAMBA share settings and am using Active Directory. Worked for years without trouble but suddenly started failing after 12.0 upgrade.

 

[Diode]

It's hard to fix issues without a reliable reproducer. There were some SMB issues in earlier releases that may have been to blame. Also default configuration changed to using durable handles on pure SMB shares. This means support for byte-range locks is being handled internally in samba as opposed to setting fcntl locks via OS. If you can reproduce on 12.0-U2, try without durable handles on the share.

How do I make handles not "durable"?

 

[Diode]

No idea. I am not a database or filesystem expert. I used mostly default FreeNAS SAMBA share settings and am using Active Directory. Worked for years without trouble but suddenly started failing after 12.0 upgrade.

Here is what the vendor of our mini MRP system says about using Access.

 

[anodos]

How do I make handles not "durable"?

There's a checkbox for it in your share configuration.

 

[Sasquatch]

All will be in vein.
I can't find the microsoft support ticket now, but in 2014/15 there was similiar problem with synology NAS.
Microsoft statement was in short, "No data integrity can be guaranteed if database file is shared on filesystem other than NTFS by OS other than windows server 2008 or later"
We had same problem getting worse after upgrading from 11.u*, with 5 users it became unusable:2-3 data corruptions a day!!
Durable handles on or off no noticeable difference.
Interestingly upgrading office from 20013 to 2019 and database format from 2003 to 2019 gave us 3 week of problem free usage, followed by 2-3 corruptions every day.
Spun up mysql server in jail, migrated all tables, linked them to forms, and (touching wood) so far so good, 4 hours of abuse (6 users editing data at once) and not a single corruption detected.

I will report in a week.

 

[anodos]

All will be in vein.
I can't find the microsoft support ticket now, but in 2014/15 there was similiar problem with synology NAS.
Microsoft statement was in short, "No data integrity can be guaranteed if database file is shared on filesystem other than NTFS by OS other than windows server 2008 or later"
We had same problem getting worse after upgrading from 11.u*, with 5 users it became unusable:2-3 data corruptions a day!!
Durable handles on or off no noticeable difference.
Interestingly upgrading office from 20013 to 2019 and database format from 2003 to 2019 gave us 3 week of problem free usage, followed by 2-3 corruptions every day.
Spun up mysql server in jail, migrated all tables, linked them to forms, and (touching wood) so far so good, 4 hours of abuse (6 users editing data at once) and not a single corruption detected.

I will report in a week.

Is there any explanation of this? Feature missing? Unsupported IOCTL over SMB2? What's special about Server 2008 and later?

 

[Sasquatch]

Is there any explanation of this? Feature missing? Unsupported IOCTL over SMB2? What's special about Server 2008 and later?

my explanation - Microsoft is $#ite
official explanation - no explanation was given other than that it wasn't tested in any other configuration.

Problem escalates with record count, at ~3000 it was once in a 2 months one field got NULLed
at ~8000(still only 3.5 MB) every crash was more serious, like unique auto increment fields being duplicated or records completely dropped
upgrade only made it more frequent, seriousness of corruptions didn't change.

 

[Diode]

This old issue appears to have gotten worse. I use Mentor PADS circuit board CAD, and for the last couple of TrueNAS versions, we are unable to reliably store files on a TrueNAS SAMBA share. The CAD software seems to use some sort of proprietary file locks and after an initial save of a file, I cannot save again. It transitions to "save as" when I try. If I try to save to the same filename, it says "file locked". It appears that file handles persist too long, or update too slowly for this program. I can keep saving under incrementing filenames. This is maddening for several users, and I need to migrate my CAD workers off TrueNAS servers because I can't figure this out. Works fine on Windows servers and I do not have durable handles enabled. I also notice that when looking at directories on the NAS with File Explorer, changes seem slow to register, and I have to manually refresh a lot, when creating new directories, for example. The network is plenty fast. I doubt that network speed is the problem.

 

[Evertb1]

To me the discussion above is a bit beyond TrueNAS but let me put in my view on things.

Beyond a lot of web related software I have 24 years experience in building Client Server software. In those years I have encountered many applications build in MS Acces. It's fair to say that I have experience with MS Acces from MS Access 2 to MS Acces 365. And I am used to the quirks of the product.

One of the first things I always advise my customers (if there are no plans for a complete rebuild on another platform) is to move the MS Backend to SQL Server or even to Oracle (a bit harder to do) if that is remotely possible. Why? Because with an MS Access back-end file you sooner or later get corruption. And if it is a shared file over the network with a fair amount of records and many users it's most of the time sooner. The compact and repair function in MS Access is a well used function.

You can write a book about the reasons for that but one of the problems is that the jet engine is running in proces on the client machine and that is were all the data processing is done. If multiple users are busy with the same backend and the network connections are so so and/or your Samba share is not rock steady its an accident waiting to happen.

 

[Diode]

I have given up trying to run Access on FreeNAS. Now my trouble is with file locks with software unrelated to Access. I should start a new thread