
The Multiple SQLite Problem - aaronbrethorst
http://www.ericsink.com/entries/multiple_sqlite_problem.html
======
mrb
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.

~~~
orthecreedence
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?

~~~
pyre
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!

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

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

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

------
j_s
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-...](http://stackoverflow.com/questions/7764943/what-can-be-done-about-
the-fact-that-android-automatically-deletes-corrupt-sqlit)

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

~~~
phunge
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/](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
Didn't realize there was a distinction between POSIX locks and flock, thanks!

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

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

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

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

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

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

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

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

------
frik
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](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
It's not _deprecated_ , no, it's something worse. It is abandoned!

If you want SQLite use sql.js

~~~
frik
sql.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...](https://github.com/kripken/sql.js/wiki/Persisting-a-Modified-
Database)

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

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

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

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

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

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

