We have a legacy application that is still using RealSqlDatabase files; however, we have started building it in the newest version of xojo. We are starting to get a number of calls who store their files on a network and they are getting Disk I/O errors because xojo is leaving an sqlite journal file behind when the file is closed.
The files are NOT being shared. Only one user can access the files at a time, but because the journal file is being left behind it’s causing a disk i/o errors.
We do not issue any pragma statements to change the journaling mode, but did something change in XOJO where this would be the default (on) and we need to turn it off?
That would seem relevant except this program has been in use since 2012 WITHOUT this issue by thousands of users.
The only thing that has changed is perhaps the way a Windows server is treating these files due to a recent update or something that’s changed in xojo.
For the most part, the way we handle our data files has not changed in years.
If you’ve moved to the latest Xojo, then you will be using an SQLite database rather than a RealSqlDatabase. In addition tio the page referenced by @brian_franco , you might want to look more generally at the SQLite web site and find out more about journal files and whether a correctly closed database should leave one behind. Changing the database like this is, I would have said, a major change in the way you handle your data files.
Tim, thank you for your reply. As I stated, this is a legacy application and really quite large. We are still using the RealSqlDatabase, not the SqliteDatabase, so I’m hoping that internally they did not change the database class to do something different that would affect this.
I’m aware that the RealSqlDatabase is deprecated, but as I said, it’s a very large application and would take months to update to use the SqliteDatabase class with it’s inherent changes.
will compile, but you’ll be using SQLite 3.7.14.1 rather than the current version 3.40.x. I’d stick with the oldest version of Xojo you can. I’ve got an old machine here with old software just to run our slide scanner. It lives in a box on the shelf along with the scanner.
As an ex-engineer, i’m going to restate what several people have said already… modern versions of SQLite do not work well on network drives and if you’re using a new-ish version of Xojo, the version of SQLite that’s used under the hood is similarly new-ish.
My recollection is also that RealSQLDatabase and SQLiteDatabase use the same engine under the hood and that the name change was simply an artifact of the product name change, but only someone @xojo could say for sure.
Anyone who has transitioned an application that used the RealSqlDatabase class to the more modern SqliteDatabase class would understand functions written that are passed a references to a RealSqlDatabase will not work if the database passed to it is an SqlDatabase class.
That is the issue with updating a legacy application. You can’t just expect to change the instantiation of the database, you also have to change all the places that class is used in functions, of which, we have many.
The underlying version of the table may very well be the latest version of sqlite because you are using the latest version of XOJO, which we are, and what I’m saying is perhaps that is where the issue comes in.
For example, the RealSqlDatabase class does not require a “begin transaction” when adding or editing records; however, it is required should you use the SqliteDatabase class. This is NOT controlled by the version of the sqlite table, but by XOJO.
We’ve run into other subtle differences in some of our new applications that are using the SqliteDatabase class. I believe these are changes made in the database class itself and not necessarily in the version of the Sqlite table.
That has not at all been my experience with SQLite over a network drive (on Windows OS). I have numerous applications deployed, some of which are high transaction and multi-user. The notes on the SQLite database site about network files servers are mentioning outdated and obsolete OSs with known file locking issues, nothing modern.
I did so much extensive testing on this that I could not create a failed or corrupted database using SQLite over a networked drive in a multi-user database. This would be very easy to test for. In my case I deployed 20 instances of an app that simply read and write to the DB at random times and let it run for WEEKS. I logged collisions (locked file messages) DB errors, etc. After millions of records written to the file I gave up. No corruption. Our deployed apps have been running for years, similar to John’s experience without issue. I can’t simply be that lucky.
The database file on SQLite is simply a file that is locked by the OS when written to. If one user is writing to the file it is locked, if another user tries to access, they will get a file locked error from the DB engine. I think a lot of issues with corruption is simply bad programming and not doing appropriate error checking.
The reality is not every deployment location can use a true database , e.g. MSSQL, for numerous reasons so SQLite is a good option in many cases.
Having said that, I’d love to understand the science and actual mechanics behind these data corruption issues over a network drive.
No, if you don’t do a ‘begin transaction’ then SQLite will do one for you. The only place in my app where I do one is where I am doing a number of updates; keeping them all in a transaction improves performance and ensures that the set of updates is atomic. Most of my updates, however, can be done by themselves and have no need of an explicit transaction.
AIUI, one issue is that consumer grade disks, needing to be cheap while also needing to be fast, report that a write is completed when, in fact, it is not. The data is in the disk’s cache but is not yet stored. If the drive loses power at that moment, there may be a problem. Whether such considerations also apply for SSD I know not.
I agree a power loss during a write to any file, regardless of shared or even local has the potential to cause corruption. But I’m not sure that specific case is isolated to SQLite files versus any other type of file (MS Word, Excel, etc.).
Perhaps your not really using an SqliteDatebase. Here’s a screenshot that shows what happens if I comment out the “begin transaction” and then try to save the record. Notice the database exception - “cannot commit - no transaction is active”.
One glaring difference is that you are now using a RowSet instead of the old RecordSet. RecordSet.Edit did an implicit transaction. I don’t know if RowSet does or not, but it would appear not. Try the same code with a RecordSet, using SQLSelect instead of SelectSQL. It doesn’t solve your problem, but it could provide more info.
The difference is that you are not using SQL directtly for your update. You are using an extra layer provided by Xojo - editing and saving a row. I’ve never used that and have no idea how that works with transactions. For your example above (but limiting to two columns for brevity), I would do:
Var sql as String
sql = "update mytable set REFNO=?1, DESC=?2 where id=?3"
app.DataDB.ExecuteSQL (sql, tfRefNo.Text, tfDesc.Text, someid)
where id and the value in someid identify the row. No explicit begin/end transaction is needed for this.
