During the last week or so we have been doing extensive testing with SQLITE on a MAC and having more than one user connect to the same database. What we have found is that with the default SQLITEDATABASE class you CANNOT have multiple connections to a database that resides on a network file system, such as Windows server, when the application is run on a MAC. It issues an exception that the “database is locked”. Oddly, if the connections are made on Windows, there is no exception generated even if the initial connection was made on a MAC.
We have some legacy applications that still use the RealSQLDatabase class for connections to the database and these have no issues with multiple connections to a database on a network file system whether the application is being run on a MAC or Windows. The fact that SQLiteDatabase does not allow multiple connections makes it impossible for us to bring these applications “up-to-date”.
We are aware that SQLITE is NOT a multiuser database and are not asking that it act like one, however, we have used multiple connections for read-only extraction of information and this is how we have presented this information to the users of our legacy applications.
Because the SqliteDatabase class has some improvements and enhancements, we would like to move to it, but unless there is a workaround that allows for multiple connections on a MAC, we would have to resort to MBS’s plugin which has been tested and has no issues in this regard.
The application we used for test purposes was created using xojo 2022r3.1 and one of our legacy applications was re-compiled using xojo 2022r3.1 as well.
See section 2. DO NOT use it on a network file system. This is true for any combination on network file system and client OS.
Further, you appear to have overlooked setting a retry timer on the connection, which will help avoid the LOCKED errors. A timeout value of 2 minutes means that if the target database appears to be locked, SQLite will retry several times within that 2-minute framework.
You might also try WAL mode. Indeed, I would recommend going to the SQLite website and reading up on some of these matters.
Multiple connections can be originated from the SAME computer to it’s LOCAL DB file in WAL mode. WAL mode enables one Writer, n Readers. Never try to share the DB in a networked path or a lock fail may corrupt your DB.
How CubeSQL overcomes it? They do exactly that, ONE CPU takes care of a LOCAL file, clients connect there and talk to a DB Manager.
Tim - as stated in XOJO documentation for WAL, it does not work on a network file system.
We are aware of the corruption issues and find that they are very infrequent. Our application has been in use by thousands of users going back to the Realbasic days and we’ve had less than 1% of 1% issues with data corruption. Admittedly, most of our users are on Windows, but we do have some Mac users.
Not sure if the retry timer will work with the “database is locked” error, but we’ll certainly give that a try.
We actually use CubeSQL for some internal applications and it works great.
However, the reason we chose SQLITE is because of it’s “no installation”. Many of our users do not have networks or if they do, they will only store their data on a network so that anyone can access it.
Again, this is not an issue in Windows and fortunately the vast majority of our users are on that OS.
The retry causes the connection that would otherwise fail with DATABASE IS LOCKED, to try again. It sleeps and then retries. The sleep time starts at some msec and doubles with each retry. Once the retry interval reaches 250msec, AIUI it sticks at 250 until the overall timeout period is reached. After that it gives up and you get the LOCKED error.
This is a bit like the old shared-medium ethernet backoff-and-retry approach.
What I’ve described there is the default retry mechanism you get just by specifying a timeout. The C API to SQLite allows one to replace that retry mechanism with one’s own callback method but it doesn’t like like Xojo provides that.
Tim - we’ll definitely see if the timeout makes a difference. Again, we are aware of the limitations of SQLITE, but at this point, we are just looking for consistency going forward and there appears to be a change in the RealSqlDatabase class vs the SqliteDatabase class with regards to this issue.
SqliteDatabase class is issuing the database is locked message and the RealSqlDatabase is not.
Being lucky until now its not the same as “it works fine”
The only real solution is to use an RDBMS.
If this is for simple data reading, you can make your own server app, reading the sqlite file locally and sending the info using sockets, you can use json over http.
Per the xojo documentation, WAL does NOT work on a network file system. We tested and it will not turn on wether using the Sqlitedatabase.writeaheadlogging = true property or using a pragma statement.
The problem is, no one is suggesting it. What I said is “WAL locally” as designed. Read what Ivan said. What he and I are saying is: forget trying to share such DB in a networked filesystem. Talk to some kind of REST server you need to create, and that REST server will read that DB file locally and exchange JSON data with your client based on the simple use you pointed, multiple “read-only” clients.
Rick, I appreciate your comments, but what we are really trying to do is prevent our users from getting a database exception if another user accesses the database. In other words, we don’t want to introduce an error that they have no experienced in the past just because we convert to the SQLiteDatabase class.
Since the bulk of our users are on a Windows OS, they don’t see the error because for some reason accessing an Sqlitedatabase on a network file system does not generate the error; however, on a Mac you do get the error.
It also seems that MBS’s SqliteDatabase class has somehow managed to avoid this issue and it works fine on a Mac - in other words multiple users accessing on a Mac does not generate a database is locked exception.
It also seems that something changed when xojo went from the RealSQLDatabase to the Sqlitdatabase class that now causes this exception.
This may not seem like a big deal to many developers since as has been repeated ad nauseam on this thread that Sqlite is not a multiuser database, but no where does it say that it can’t be accessed by multiple users for read-only access, which is what we point out to our users. What is a big deal is that you’ve been providing an application that works a certain way for 15+ years and now you take away a feature that perhaps is very important to them.
At this point, it looks like MBS may be the best option for us. Just another reason why we have relied on MBS to provide more consistent and better performance than inherent xojo features.
Just approaches. Xojo gone default full secure, and MBS gone “risky mode active”.
The explanation is:
SQLite uses POSIX advisory locks to implement locking on Unix. On Windows it uses the LockFile(), LockFileEx(), and UnlockFile() system calls. SQLite assumes that these system calls all work as advertised. If that is not the case, then database corruption can result. One should note that POSIX advisory locking is known to be buggy or even unimplemented on many NFS implementations (including recent versions of Mac OS X) and that there are reports of locking problems for network filesystems under Windows. Your best defense is to not use SQLite for files on a network filesystem.
SQLite uses the fsync() system call to flush data to the disk under Unix and it uses the FlushFileBuffers() to do the same under Windows. Once again, SQLite assumes that these operating system services function as advertised. But it has been reported that fsync() and FlushFileBuffers() do not always work correctly, especially with some network filesystems or inexpensive IDE disks. Apparently some manufactures of IDE disks have controller chips that report that data has reached the disk surface when in fact the data is still in volatile cache memory in the disk drive electronics. There are also reports that Windows sometimes chooses to ignore FlushFileBuffers() for unspecified reasons. The author cannot verify any of these reports. But if they are true, it means that database corruption is a possibility following an unexpected power loss. These are hardware and/or operating system bugs that SQLite is unable to defend against.
If a Linux ext3 filesystem is mounted without the “barrier=1” option in the /etc/fstab and the disk drive write cache is enabled then filesystem corruption can occur following a power loss or OS crash. Whether or not corruption can occur depends on the details of the disk control hardware; corruption is more likely with inexpensive consumer-grade disks and less of a problem for enterprise-class storage devices with advanced features such as non-volatile write caches. Various ext3 experts confirm this behavior. We are told that most Linux distributions do not use barrier=1 and do not disable the write cache so most Linux distributions are vulnerable to this problem. Note that this is an operating system and hardware issue and that there is nothing that SQLite can do to work around it. Other database engines have also run into this same problem.
If a crash or power failure occurs and results in a hot journal but that journal is deleted, the next process to open the database will not know that it contains changes that need to be rolled back. The rollback will not occur and the database will be left in an inconsistent state. Rollback journals might be deleted for any number of reasons:
An administrator might be cleaning up after an OS crash or power failure, see the journal file, think it is junk, and delete it.
Someone (or some process) might rename the database file but fail to also rename its associated journal.
If the database file has aliases (hard or soft links) and the file is opened by a different alias than the one used to create the journal, then the journal will not be found. To avoid this problem, you should not create links to SQLite database files.
Filesystem corruption following a power failure might cause the journal to be renamed or deleted.
The last (fourth) bullet above merits additional comment. When SQLite creates a journal file on Unix, it opens the directory that contains that file and calls fsync() on the directory, in an effort to push the directory information to disk. But suppose some other process is adding or removing unrelated files to the directory that contains the database and journal at the moment of a power failure. The supposedly unrelated actions of this other process might result in the journal file being dropped from the directory and moved into “lost+found”. This is an unlikely scenario, but it could happen. The best defenses are to use a journaling filesystem or to keep the database and journal in a directory by themselves.
