I was trying to help a client with an issue and we are now using sql lite files.
He has them out on his server. His network hiccuped and we got a disk I/O error.
I believe I read somewhere that the sql lite files are susceptible to this issue.
Is there any way at all around it? I have lots of clients storing their files on a shared folder.
Maybe copy the file locally as they are in it, then when they save, save local then copy to shared folder???
are the files on the “server” on a network share? if so, you have been bitten by the issue around why you cant do that.
PSA Please oh Please do not place sqlite files on a network share and use them from there. have them on a local disk only. please oh please from your local storage expert. thank you!
and @Tim Turner that PSA wasnt directed at you but everyone that attempts to put sqlite files on a network folder.
Unfortunately we have had our software for years and many users put the files on a network share.
Our new release switches the file format from old proprietary one to new sql lite.
I have no control over where people put the files.
Has anyone coded a workaround that might detect that and prevent them from opening?
(which would still tick off a lot of our clients)
Or copy local, edit, save, then copy back?
educate them?
[quote=314418:@Tim Turner]Has anyone coded a workaround that might detect that and prevent them from opening?
(which would still tick off a lot of our clients)
Or copy local, edit, save, then copy back?[/quote]
try doing a backup of the file to a local folder that stores a backup of the network shared one in case it gets corrupted like this one did. Also make sure you turn on multi-user support (http://documentation.xojo.com/index.php/SQLiteDatabase.MultiUser) to help prevent the corruption. wont prevent it, just will make it less often.
without know more about your app, I cant give good options on how to detect, and prevent users from hurting themselves.
Our user base consists of financial advisors who often have an assistant. Assistant often enters data, then has advisor pull up file to meet with client. Thus many of them share the files on a shared network folder. “educate them” not to do that may have to come because of the Disk I/O error occurring but I would need to suggest an alternative solution. I wonder if using Dropbox on their local computer would have the same Disk I/O problems.
That page says that “WAL does not work over a network filesystem”. I’m assuming that refers to a network share?
So I would have to only do that on a local copy. But if it’s local to the user’s windows pc, I don’t think multiple users would be accessing the database file.
Hey cool I am already doing that due to various data issues that we have seen over the years. However, I don’t have an easy way to “recover the backup” other than manually by going to backups folder on the hard drive. It would still be annoying if they get these I/O disk errors more than once or twice a year. Our customers are computer illiterate for the most part.
I was wondering if instead of just “make a backup”… I did the following:
You could also experiment with the various modes for journaling. There’s the “WAL” mode but also the older journal file, or even in-memory journaling. See https://www.sqlite.org/pragma.html#pragma_journal_mode
Yeah I learned the hard way. I had a complex program with five users all accessing a SQLite db on a network share (windows). It worked fine for over a year until one day it didn’t. Then I worked day and night to find a solution… 
thank you!
that is common. it works fine for a while then it doesnt. and it causes problems for everyone. I am sorry you got bit by this.
What was “the solution”???
PostgreSQL in my case. I tried SQLite Server by Valentina, but I just had too many strange issues.
https://forum.xojo.com/37126-sqlite-error-database-disk-image-is-malformed/p2#p305774
https://forum.xojo.com/37330-converting-to-postgresql-from-sqlite/p1#p308010
In your case if only one user needs to access the database and the database isn’t huge I would still use SQLite.
I would pull the entire database into memory using an attach. When you’re finished, any modified tables and can be written back to the database on file, by deleting all records from the db on file and dumping in the records from memory (again using attach).
If you have data you can afford to loose in case the app crashes, just use a timer to backup the in memory db at regular intervals. When you close the app delete the backup. Then when starting you could detect ‘unsaved changed’.
I use in memory databases for many things and they are very fast. I’m working on a combo box subclass that uses an SQLite db for storage. It is 4 times faster than the standard one!