The Multiple SQLite Problem | Hacker News Reader

The Multiple SQLite Problem

ericsink.com

131 pointsaaronbrethorst45 comments

The 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.

Are you sure about that?

"A close() operation on one connection might unknowingly clear the locks on a different database connection, leading to database corruption."

By "a different database connection", they mean a different file descriptor referring to the same database file.

(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.

Thanks, 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).

Here's a question: how does your app know which SQLite it's using? Or how does it actively say "ok, use the OS SQLite instead of the bundled one?" Aren't the symbol names the same?

I imagine that it's mostly in the background via libraries. E.g.:

- Access system-level SQLite databases via system APIs using the OS-linked SQLite. For example, you want to access the system config settings, so you use the iOS/Android API for doing so.

- Access your app's SQLite libraries using (e.g.) SQLCipher. For example, you want to store your app's preferences in a SQLite db, so long as SQLCipher manages to use a single SQLite library internally, then you're golden.

An example of running into issues could be:

- Using an ORM to access your App's SQLite db to read/write changes locally.

- Using a separate library to sync your App's SQLite db to desktop/"the cloud"/whatever.

Those two libraries could be using separate SQLite libraries, and be accessing the same SQLite db file. Oops!

It could be possible to resolve said issue so long as you make sure that only one library is accessing the SQLite database at a time. Basically your own in-app DB locking mechanism. Yay!

This 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?

Part 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.

There's a lot of difference between knowing SQL (the 2nd example) and knowing what will happen at the MSBuild level.

I know the former but not the latter :) and while it probably will help me get better, I haven't really needed to know in depth what goes on behind the scenes yet (at the MSBuild level), and hasn't bit me in the ass :) , and I've shipped quite a lot of code. To use your terms, it's an abstraction that doesn't leak :) .

OTOH I've never met an ORM where SQL knowledge wasn't necessary (now that's a leaky abstraction if there was one).

I think knowledge of the full stack is required to get better, but I also believe in applying Pareto and studying the bits that will yield the bigger results :) .

I'm not 100% a developer anymore (I'm in an amorphous transition that really worries me between development, support, operations and management) so YMMV.

Edit: for app development, I think I'm on the OPs side, in that you currently need to know the full stack (for apps in particular). This doesn't mean it won't change in the near future.

But I don't endorse his other opinion - I think you have to do a cost-benefit analysis before using dedicated servers. Past a certain point, certainly, do use them, but 80% of the software out there doesn't need them. I know the one I'm currently developing doesn't (unless it scales beyond my expectations :) which would be a nice problem to have).

I think the answer to your conundrum is that the (business) users get what they are asking for. If you were using Hibernate/whathaveyou you are pretending you don't have a problem.

So much of this mess is caused by dumb decisions from people who don't understand what 'constraint' means - welcome to 2014, where business suits run the show, and technology is just plain broken as a result. Not that anything has really changed, the whole 'this is 2014' argument is a bit of a farce anyways.

Thats my take on it anyways, I think some people stopped reading your article once you started dumbing down what the app dev wanted to do.

I agree. It drives me nuts not to understand the stack I'm using. But sadly, our lower level tools are not good enough. We need higher level tools or a significant fraction of applications and tools will simply not exist for the foreseeable future.

It should be possible though, to bring at least some of that convenience to shallower stacks. Those tools are not as good as they could be. Static compilation instead of VMs (but still with a REPL), Type inference instead of full-on dynamic typing, libraries instead of frameworks, etc.

JNI is not always needed to access C libraries from Java, there is Bridj (https://code.google.com/p/bridj/) and similar libraries. They allow you to write Java code that access native APIs pretty easily.

The http://sqlitejdbcng.org project is a SQLite JDBC driver that uses Bridj to access a SQLite shared library. It's probably very similar to what the article author is doing with SQLitePCL.raw.

I think the answers to your questions are entirely dependent on the perspective and agenda of the audience they might be targeted to.

A certain segment of the world of software development cares for only one thing: pushing out as much product as fast as possible. The quality of the product's construction is (almost) irrelevant (or at the very least very low on the priorities list). This segment isn't going to place much emphasis on the importance of having developers with sufficient experience and expertise to understand the actual whole stack. That is, they'd answer "yes" to the last question in your comment.

I'm closer to what I suspect is your view: native all the way. There's rarely a good technical reason to prefer a non-native abstraction-on-an-abstraction language in the world of mobile development (I'd go one further--in the world of any software development), even when there are plenty of "business-y" justifications for it.

Any 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-...

I 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.

Yes 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/ -- 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.

Didn't realize there was a distinction between POSIX locks and flock, thanks!

The 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.

From 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.

We 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)

Couldn't you just compile your sqlite3 with private symbol visibility and statically link to that?

Yep. 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.

I'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.

What was your solve?

To 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.

I'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.

> 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).

Also, starting with SQLite 3.8.3, you can use WITH RECURSIVE (Common Table Expressions) and do some fancy things all in SQL.

And 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 (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

It's not deprecated, no, it's something worse. It is abandoned!

If you want SQLite use sql.js

sql.js is 2 MB big (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...

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!?

There is another option on iOS: use CoreData and don't sweat what's underneath it.

Core 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...

The 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.

If you use Core Data you are not accessing raw SQLite, so the last two years don't mean much. And there are more often good reasons to use Core Data than not.

There 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.

No 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).

.... 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.

Sorry 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.

I 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.

From the same process? Is there better documentation for this issue somewhere?

Reply on news.ycombinator.com