SQLite and Network Drives

[John Lokanis]

I am using file based SQLite in my LabVIEW application as a temporary data storage file when not using the main database server.  My application is a client-server system so I need the ability to access the SQLite DB File from two machines at a minimum.  The Client app will create the DB if it does not exist and read existing data from the SQLite DB.  When it hands off to the Server application, the Server will write to the DB as it collects information.  Later, I want the user to be able to access the data with their preferred analysis tool.

 

My original plan was to place the SQLite file on a network drive that both the Client machine and the Server machine could access.  But, when I started testing this, I ran into a number of issues:

1. SQLite does not like UNC paths.  I got around this by detecting the UNC path and adding an extra to the start of the path.

2. When accessing a SQLite file using a UNC path, I get lots of random Disk I/O errors and my DB file is missing data.

3. I tried mapping a drive letter to the network path and this seemed to eliminate the Disk I/O errors but the speed was so slow that operations took several minutes or never completed at all.

 

So, after much googling, I found some comments that said SQLite does not work well when the file in on a network drive.  This sucks as it makes my application nearly impossible to do.  I don't want to have a server for the database since these were supposed to be one-off temporary data files for storing data when not using a real DB server.  I require an atomic file based solution.

 

So, has anyone found a way around this?  Is there any clever hacks or settings or other ways to make this work reliably?

 

thanks for any ideas,

 

-John

 

(using LV2013.0.1.f2 and SQLite Interop DLL version 1.0.90.0 based on SQLite3)

 

[ShaunR]

This is what the SQLite peeps have to say.
 

But if you must use a network filesystem to store SQLite database files, consider using a secondary locking mechanism to prevent simultaneous writes to the same database even if the native filesystem locking mechanism malfunctions.

 

They are really talking about a single client, however and you will find it impossible (I think-never tried) to create a shared locking mechanism between two machines. You might as well use a proper client/server DB.

 

There is a compile time option where you can choose the locking mechanism (file based may work better), but you would have to compile it yourself.

[John Lokanis]

In my case, I am never accessing it at the same time from the Client and the Server.  The Client does it's work and then closes the connection.  The Server then connections later and does it's writes.

I could replace this with a flat file of some type (.csv, etc) but I like the ability to query the data since I can reuse some of my SQL code I use with the regular DB server.  This is just sort of an off-line mode I am implementing for experimenting with new ideas where we dont want to pollute the central database but still want to collect data for analysis.

Sucks that SQLite can't do this since it is such a good solution otherwise.  I hate to have to switch to MSAccess but it is the only other file based DB solution I know of.

 

thanks for the feedback.

 

-John

[ShaunR]

In my case, I am never accessing it at the same time from the Client and the Server.  The Client does it's work and then closes the connection.  The Server then connections later and does it's writes.

I could replace this with a flat file of some type (.csv, etc) but I like the ability to query the data since I can reuse some of my SQL code I use with the regular DB server.  This is just sort of an off-line mode I am implementing for experimenting with new ideas where we dont want to pollute the central database but still want to collect data for analysis.

Sucks that SQLite can't do this since it is such a good solution otherwise.  I hate to have to switch to MSAccess but it is the only other file based DB solution I know of.

 

thanks for the feedback.

 

-John

 

Then why not have the server write to the CSV file (or whatever) then just import it to a local database for use? It'll be a one hit performance to retrieve then full SQLite performance whilst in use.

[John Lokanis]

Multiple Clients might be using the Server at the same time.  I want each of them to have their own data file for their work.  If I go with a database server solution then all their data gets intermingled.  And I don't want to spinup a database server for each user on the fly.  Oh, and I don't have a network resource to run the DB server, unless I go to battle with IT.

I wish there was a better way.  My understanding is the issue has to do with making the SQLite file transaction safe in case of power loss.  I wish there was a way to just turn that off.

[drjdpowell]

I wish there was a way to just turn that off.

 

PRAGMA synchronous = OFF

[ShaunR]

There is the possibility that data won't be written to the DB when using PRAGMA SYNCHRONOUS=OFF,on a network share but if that is acceptable then, you should also set the

PRAGMA journal_mode = PERSIST

The default is DELETE and this severely hinders performance on network drives and increases the possibility of collisions and locking errors.

Edited June 5, 2014 by ShaunR

[John Lokanis]

I'll give those a try!

[JackDunaway]

Are you using URI filenames?

 

Try that, with the most conservative flagset of SQLITE_OPEN_URI | SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_PRIVATECACHE and also the most permissive permissions SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE then deprivileging with the "mode" parameter on the URI. You'll also need to call sqlite3_open_v2 rather than sqlite3_open

 

My hunch is that these settings could be sufficient without recompiling SQLite.

[John Lokanis]

Jack: I am using the .net interface for SQLite.  How do I set these flags?  I have poked around but not found a way to do it yet.

[ShaunR]

Are you using URI filenames?

 

Try that, with the most conservative flagset of SQLITE_OPEN_URI | SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_PRIVATECACHE and also the most permissive permissions SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE then deprivileging with the "mode" parameter on the URI. You'll also need to call sqlite3_open_v2 rather than sqlite3_open

 

My hunch is that these settings could be sufficient without recompiling SQLite.

 

The issue isn't so much accessing SQLite on network drives; I's concurrency. I can quite happily read and and write to my NAS boxes, but woe betide if you try and share the DB amongst clients.

 

Just for giggles, I ran the Speed example and pointed it to my NAS box (over a wifi connection) and it achieved about 0.5 secs to insert and read 10,000 records. .

[John Lokanis]

WooHoo!  The two PRAGMA settings seem to fix it.  No Disk I/O errors and no speed issues (so far).  I'll have to test a bit more of course.  Thanks everyone!  LAVA FTW again!