6 ms·
The Multiple SQLite Problem
- bhouston 13y agoI think the issue is that one needs to change the design of SQLlite so that it can co-exist properly with other instances. There must be a way to do this. It may not fix old versions of SQLlite but it would prevent this issue in new versions.
- phunge 13y agoYes I'm surprised to learn that SQLite is relying on POSIX locks, they're basically impossible to use correctly -- by design. I wonder if things could be reengineered to use BSD locks (flock). I wonder if it's there for portability reasons. This is a clear explanation of the differences https://lwn.net/Articles/586904/ https://lwn.net/Articles/586904/ -- TLDR as an app developer is avoid POSIX locks unless you grok the odd semantics. Also, better things are coming -- I believe that the new lock type mentioned in that article is getting merged for Linux 3.15.
- awda 13y agoDidn't realize there was a distinction between POSIX locks and flock, thanks!
- SQLite 13y agoThe underlying problem is that POSIX Advisory Locks are broken by design. They are per-process, rather than per-file-descriptor locks. So, if you have two file descriptors open on the same file in the same process, a lock on one file descriptor is unable to control access to the file from the second file descriptor. POSIX locks only work if the two file descriptors are in separate processes. There is a large of code in SQLite that works around this bug. And that code works well. But that code requies access to global variables. The problem that Eric describes comes up when you link in two separate copies of SQLite, and thus have two distinct sets of global variables for managing the locks. These two separate copies of SQLite have no why of knowing about each other, and hence have no way of coordinating their lock behavior in order to avoid problems.
- rimantas 13y agoThere is another option on iOS: use CoreData and don't sweat what's underneath it.
- sehugg 13y agoCore Data uses SQLite as its backend by default, so you may still potentially run into these kind of problems -- if say, an ad network decided to statically link in its own SQLite version. I have been frustrated by persistent corruption in a production app that solely uses CD, though I don't know if it's related to multiple SQLite instances. EDIT: An ad library is probably a bad example since it wouldn't create files that are normally touched by the user app. Unless you were making an app to visualize ad library requests...
- deleted 13y ago[deleted]
- scott_karana 13y agoYes, I think sehugg understood that, and the point was that even if you are diligent about solely using CoreData, someone else, like your ad provider, might introduce another version of SQLite in their library, so you'd be screwed anyways.
- pdpi 13y agoI fail at reading comprehension.
- simscitizen 13y agoNo you wouldn't. You'd only be screwed if you used that other copy of SQLite to open the SQLite file used by CoreData to store data. Which you shouldn't be doing anyway.
- teraflop 13y agoThe article gives quite a few reasons why you might not want to do that. For instance, if you want to encrypt your data at rest, or you want the new features and performance improvements from the last 2 years of SQLite development.
- digitalsushi 13y agoWe had a huge issue with sqlite3-cipher using the same symbol prefixes as the regular sqlite3 project. We had to learn enough low level stuff to change the symbol names because we couldn't replace the OS libraries. Sqlite3-cipher changed their symbol names to just sqlcipher, effectively making it an unrelated project. If this had been the case at the start, we would have saved a few weeks of learning enough about this little universe we don't normally go into. Academically it was great for us to get our hands dirty, but that isn't the only thing that matters (unfortunately)
- FooBarWidget 13y agoCouldn't you just compile your sqlite3 with private symbol visibility and statically link to that?
- awda 13y agoYep. Build objects with -fvisibility=hidden, create a static library with `ar rcs libsqlite.a *.o`, and link your app with it and NOT dynamic libsqlite.
- kenrikm 13y agoI've run into a similar issue, I'm using FMDB with SQCipher and have finally got it to be reliable after way to much time digging into very low lvl SQLite stuff then I care to think about.
- azinman2 13y agoWhat was your solve?
- mrb 13y agoThe author forgets to mention that you need to access the same SQLite file within the same process (possibly different threads) using 2 different SQLite library instances, in order to trigger the problem. I would imagine in most cases a mobile application would use (1) the SQLite lib bundled with Android to access system-related SQLite databases, and (2) its own SQLite lib to access application-specific SQLite databases (where you need to use specific SQLite features or whatnot), in which case there is no problem at all doing that.
- unbeli 13y agoAre you sure about that? "A close() operation on one connection might unknowingly clear the locks on a different database connection, leading to database corruption."
- mrb 13y agoBy "a different database connection", they mean a different file descriptor referring to the same database file.
- ericsink 13y ago(a) Yes, the author (me) does forget to mention that. Oops. (b) Yes, it's true that there are plenty of uses cases which won't hit the problem. But for those that do, the consequences are pretty severe. And the distinction between those two classes of use cases is not obvious.
- deleted 13y ago[deleted]
- Pxtl 13y agoThanks, yes, the article was unclear about that - in its discussion of ownership/POSIX I was confused if it was talking about the individual database files or some kind of centralized tracking file. And yes, clarification of what cases I could expect both my connection and the OS connection to hit the same file would be good too. Because I wouldn't expect the OS to hit my own private sqllite dbs, and I wouldn't expect to be writing code that directly hits the OS dbs (instead of going through OS APIs to query them).
- simscitizen 13y agoThere is no problem unless you use different versions of the sqlite library to open connections to the same db file at the same time. This is because different versions of the sqlite library may use different locking strategies. But if you just want to use a different version of sqlite with your own sqlite files in your app's sandbox, then that's just fine. You may need to do some symbol munging since the system's sqlite symbols will clash with your copy's symbols.
- ericsink 13y agoNo offense intended, but that's not correct. :-( You can get this problem with two instances of the same version of SQLite (accessing the same file at the same time).
- Pxtl 13y ago.... that's very different from what's stated in the article, and is actually what I'd expect to be the real problem. Dwelling on versions seems to be beside the point.
- ericsink 13y agoSorry if the article is unclear. My intent was to clearly communicate that the problem can happen with any two instances, not necessarily of different versions. Like the nutshell summary at the top says. But I also think that talking about different versions of SQLite is relevant, since dealing with those issues is one of the things that can lead an app developer toward the problem.
- simscitizen 13y agoI was only considering the conflict between the system-provided SQLite and the private copy of SQLite in your binary, since that seemed to be what your article was about, and is the most common conflict. I hadn't actually considered that you would actually statically link two copies of the same version (or even different versions) of SQLite into your own binary and use those two different copies of SQLite to open connections to the same exact file. In any case, don't have two different versions (by versions I mean copies) of SQLite open the same file in the same process. This is because os_unix.c does deferred closing of fds based on refcounting the number of open connections to the same path. With multiple libraries opening connections to the same path, the refcount for that path won't be correct in either copy of the library.
- j_s 13y agoAny SQLite problem was critical on Android because until 3.0/Honeycomb, corrupted databases (which could often be automatically repaired) were deleted if developers used the normal data access layer to attempt to open it! http://stackoverflow.com/questions/7764943/what-can-be-done-about-the-fact-that-android-automatically-deletes-corrupt-sqlit http://stackoverflow.com/questions/7764943/what-can-be-done-...
- kenrikm 13y agoI've run into this issue linking against the iOS included SQLite. The apps were server backed so it was easy to just detect and correct but yeah it can be one of those one in 1000 edge case bugs that really get you. Now I use FMDB with SQCipher and it's been very reliable, more so then Core Data.
- frik 13y agoAnd WebSQL: We have the fabulous SQLite as API in HTML 5, yet Mozilla and Microsoft refuse to support it! http://en.wikipedia.org/wiki/WebSQL http://en.wikipedia.org/wiki/WebSQL (supported by Google Chrome, Opera, Safari and the Android Browser; Firefox ships with SQLite but doesn't expose the API) WebSQL is not deprecated, the W3C Working Group Note says: 'This specification is no longer in active maintenance and the Web Applications Working Group does not intend to maintain it further'. Recently, we had a discussion about that: https://news.ycombinator.com/item?id=7645726 https://news.ycombinator.com/item?id=7645726
- TazeTSchnitzel 13y agoIt's not deprecated, no, it's something worse. It is abandoned! If you want SQLite use sql.js
- frik 13y agosql.js is 2 MB big (https://github.com/kripken/sql.js/blob/master/js/sql.js https://github.com/kripken/sql.js/blob/master/js/sql.js ) And it's an in-memory database, not like WebSQL. So one has to store the data (array) to localStorage or rather IndexedDB (HTML 5 NoSQL storage). https://github.com/kripken/sql.js/wiki/Persisting-a-Modified-Database https://github.com/kripken/sql.js/wiki/Persisting-a-Modified... So imagine a Firefox user: He has to download an extra 2MB JS file, the huge file has to be run through asm.js JIT and the data is (for example) stored (offline mode) using IndexedDB. Firefox implements IndexedDB on top of SQLite. A SQLite instance runs on top of an NoSQL engine on top of another SQLite instance - wtf!?
- mwcampbell 13y agoThis kind of problem makes it clear to me that application developers need to understand and be responsible for the full stack of software that they ship, including bundled C libraries. One can't just use a high-level cross-platform runtimes like Mono/.NET and ignore everything underneath, because when the shit hits the fan, the app developer has to fix it, not pass the blame and wait for someone else to handle it. (For the same reason, I think it's better to rent dedicated servers than to use virtual machines or whatever-as-a-service. And colocation might be better still, if one can afford the up-front expense.) What isn't clear to me is whether we should still use a high-level runtime like Mono/.NET, for mobile apps in particular, even though we have to be responsible for lower layers. On the one hand, very few developers would want to mess with manual memory management and C-style error handling when writing business logic, database access routines, or UI code. On the other, if we have to grasp the whole stack anyway, then we can more easily do that if there's less of it. On iOS, cross-platform C code plus iOS-specific Objective-C code is less complex than cross-platform C code plus P/Invoke glue for the former plus cross-platform C# code plus UI code in C# (which will be foreign to iOS developers not familiar with Xamarin) plus glue between Mono and ObjC. Similar logic applies to Android (although there will always be JNI glue between native code and Java) and even Windows Phone (as of 8.1, which supports WinRT and native code using that). So, for a more comprehensible software stack since we have to be responsible for the whole thing anyway, should we just give up on higher-level languages and runtimes, acknowledge that we're ultimately dealing with C machines, and stick to C or maybe C++ for cross-platform code? Would that be putting too much emphasis on hypothetical debuggability, and not enough on other things like developer productivity and approachability to less-than-expert developers?
- ericsink 13y agoPart of me agrees with you and wants to rant about it at length. It is invariably true that developers who understand the full stack get along better. They know what's going on "under the hood". When they click a checkbox in the Visual Studio properties dialog, they understand what will happen at the MSBuild level, and how that translates to a difference in the command line invocation of the compiler, and what the compiler does differently because of that, and what it means in terms of interaction with the OS. It would drive them nuts to not understand this. So much so that they're probably only using Visual Studio instead of vim/cygwin/bash because somebody is forcing them to. :-) OTOH, lots of developers let their tooling or platform do things for them that they don't understand. Those developers often struggle, especially during the latter stages of shipping a product. (I just wanted to not learn SQL! How was I supposed to know that NHibernate is so slow?!?) But it is also true that expecting all developers to understand everything from their ORM down to the x86 microcode is neither realistic nor efficient. It's just not gonna happen.
- malkia 13y agoTo give you a practical example. By default on Windows Qt (any version) would statically link SQLite as part of it's own QtSql's SQLite driver (which is a .dll - so that .dll would contain in itself sqlite). If you happen to link SQLite additionally because of some other lib, then you would end up with two different libs in the same code (but under different "namespaces" in a way). With static linking one of the libs would've took precedence, unless you've decided to embed "sqlite3.c" directly - then I think your copy would've took over Qt's one (static Qt5sql.lib) To avoid this problem, I manually built Qt5 and made sure most of the libraries are split out of Qt5 - angle, pcre, ucdn, sqlite, mysql, libpq (postgres), etc. - they are all dlls that both Qt and rest of our apps link to. This also allows us to use and share SQLite memory db (for the memory db to be shared it has to come from the same code), where we are letting coders to use QtSQL's approach to access the db, and then other access it other ways. It's non-standard on Windows - since you are basically eating whatever you have been served (in the Windows World), and you don't think much about picking (static/dynamic library, then linking to static/dynamic CRT, compiler version, etc.). Things are much better, say in debian (my experience) - installing qt5-sql would use the sqlite package from the system, and the rest of my tools would do that too.
- batbomb 13y agoAlso, starting with SQLite 3.8.3, you can use WITH RECURSIVE (Common Table Expressions) and do some fancy things all in SQL.
- azinman2 13y agoFrom the comments it seems like as long as only you're only pointing your version of sqlite at your own databases, then there isn't an issue? If so, then when would someone want to open the same database at the same time from two different sqlite engines? I didn't think that was even supported to begin with, and seems highly problematic.
- eridius 13y ago> And the SQLite instance built-in to iOS and Android is plain, with no support for encryption. This is misleading when it comes to iOS. SQLite may not have encryption, but iOS has what are called data protection classes, which allow you to ensure the SQLite file is encrypted on-disk with varying levels of security (i.e. accessible only while unlocked, accessible any time after the device has been unlocked once after a reboot, etc).