9 ms·

SQLite and Android N (2016)

82 points by luu 10y ago

izacus 10y ago
Ugh, if you were linking against system sqlite you had problems RIGHT NOW before Android N. sqlite.so isn't part of public Android API surface, which means the OEMs will regularly replace it, break it, compile it with strange options and deal a lot of pain to you and your users. So if you weren't shipping your own native SQLite in NDK/Xamarin you already had problems, you just maybe didn't notice them. Android N is now actually actively preventing linking against system libraries which aren't part of public API surface, which is good - it'll result in less developers doing dumb things which break on minor updates.
- pawadu 10y ago
  > it'll result in less developers doing dumb things which break on minor updates. You can never stop the Facebook developers doing dumb things on Android. Google have tried for years, but Facebook always manages to find new ways to fuck up.
  - oblio 10y ago
    They're not dumb, they're challenges! Preferably challenges solved by creating new VMs, compilers and other interesting development projects ;)
    - pawadu 10y ago
      Yeah, challenges Facebook themselves created by writing the most bloat app ever written https://www.facebook.com/notes/facebook-engineering/under-the-hood-dalvik-patch-for-facebook-for-android/10151345597798920 https://www.facebook.com/notes/facebook-engineering/under-th...
      - iainmerrick 10y ago
        Facebook is one of the most feature-rich apps available for Android. With features like push notifications, news feed, and an embedded version of Facebook Messenger (a complete app in its own right) all working together in real-time, the complexity and volume of code creates technical challenges that few, if any, other Android developers face I've never understood why the Facebook client is so heavy. That isn't a long list of features! It seems like the requirements could be written on the back of an envelope. Obviously part of the reason is that they move fast and have a lot of developers, so there's much more pressure to add code than to keep it trimmed down. But the client still doesn't do all that much. Surely, surely most of the logic is handled on the server.
      - lxgr 10y ago
        I've noticed they are now doing a similar thing now for push notifications. Instead of using the platform-provided, battery efficient GCM (now FCM), they insist on using their own MQTT push solution. Not only does this require a constantly running background service and a TCP socket which has to be reopened on every connectivity change (e.g. a switch from wi-fi to mobile data): They do it for every single one of their apps. My phone has at least three of them (Instagram, Facebook, Messsenger), and I'm pretty sure that this is a major reason for battery life problems on Android.
        yegle 10y ago
        You can at least uninstall Facebook.app: use m.facebook.com in chrome, you can even get push notification via Chrome.
        j_s 10y ago
        Also: https://mbasic.facebook.com https://mbasic.facebook.com
        NoGravitas 10y ago
        The Facebook mobile web page has been going through so much churn lately, I feel like they are working to make it less desirable relative to the app. Disabling access to messages was one of the big ones, but they also regularly change how the sharing links work (to where you can or cannot share from a given action). One of the most recent and enjoyable changes is that the unread count badge on notifications updates in the background, but the actual notification drop down doesn't. You have to go to the notifications page (see all notifications) to see what the new notification actually is.
        parthdesai 10y ago
        And can use firefox to access messenger.
- lultimouomo 10y ago
  But the point is that you ship your own copy of SQLite you still have a problem. You need to make sure that a database is never accessed both through the android wrapper and you own sqlite copy, or you will face corruption. Seems trivial, but when you take into consideration things like Xamarin and third party libraries, it's not so clear-cut.
  - izacus 10y ago
    Hmm, I dunno, I kinda thought that not mixing sqlite versions would be a pretty obvious thing. Xamarin, Cordova and others are pretty much a cesspool of catastrophically poor libraries so I'm not sure why Google has to take the blame for that.
unsoundInput 10y ago
Kind of related: There is an outstanding request [0] on the Android issue tracker to offer an official support/compat library for Android's sqlite bindings, that would bridge Android's API (in a different java package) to a sqlite binary that you provide. There are already implementations [1][2] of this, but because it is not provided by the Android team it is hardly supported by ORMs, libraries, etc. [0] https://code.google.com/p/android/issues/detail?id=202658 https://code.google.com/p/android/issues/detail?id=202658 [1] https://www.sqlite.org/android/doc/trunk/www/index.wiki https://www.sqlite.org/android/doc/trunk/www/index.wiki [2] https://github.com/requery/sqlite-android https://github.com/requery/sqlite-android
- kiallmacinnes 10y ago
  Does this not just hit the "The Multiple SQLite Problem" described in the article?
  - unsoundInput 10y ago
    I don't think so but I might also interpret the article wrong. The problem - from what I understand - is that you used to be able to use the systems sqlite.so to access databases owned by your app from the native environment. With Android N you are no longer able to do this, you need to ship your own sqlite binary. This can lead to problems when you want to access the same sqlite files from both the Android platform APIs and native (e.g. use it from native for business logic, debug it with a tool like Stetho [0] that uses android.database.sqlite) because of a version mismatch. Would Google offer (a copy of) the Android API with the ability to plug in a different sqlite-compatible binaries, it would very likely find broad adoption. Then the problem stated by the article could be solved by shipping the app with a sqlite build that is used by both the native code and plugged into the compat library. [0] https://facebook.github.io/stetho/ https://facebook.github.io/stetho/
amluto 10y ago
One big danger seems to be that POSIX locks don't like having two fds for the same file in the same process. But Linux has a newish kind of lock that is backwards-compatible and works better. I would love for SQLite to use it. Does SQLite accept patches?
- kiallmacinnes 10y ago
  Patches are accepted, though.. SQLite is, by all accounts I've read, a very well written and tested piece of software, and I'd be surprised if there was an easy and safe win like this which hasn't already been proposed / considered. Though, my guess shouldn't stop you looking for previous suggestions to implement this, and if there are none, submitting it yourself :)
  - stuaxo 10y ago
    It's amazing how many quick wins are to be had in open source if you have the time to patch yourself.
- pjmlp 10y ago
  POSIX locks semantics are implementation defined, hence why they are pretty much useless unless all applications access the same set of files play by the same rules.
  - zurn 10y ago
    Or the apps all know which implemnetation they are running on (Linux in this case).
Zigurd 10y ago
How many of these accesses of sqlite.so are the result of using cross platform tools? All of them? Is there any reason to do this otherwise?
- gcp 10y ago
  If you have native code that wants to access a database, not round trip though Java, and not incur the size hit of shipping a separate copy OR (and this is key) want your Java code to be able to read the same database without writing your own Java->JNI SQLite wrapper.
  - Zigurd 10y ago
    Unless you are hammering on a jni in an inner loop, you won't notice.
    - iainmerrick 10y ago
      Another problem is explained in the feature request that another commenter linked to: https://code.google.com/p/android/issues/detail?id=202658 https://code.google.com/p/android/issues/detail?id=202658 You have no idea what version of sqlite the OS might be using, so you can't rely on consistent behavior. In many cases the vendor will have tinkered with it and messed it up. Comprehensively testing on all Android devices is basically impossible because there are so many models out there.
      - Zigurd 10y ago
        That sounds like an argument against direct access to sqlite.so.
        iainmerrick 10y ago
        Hmm, yeah, but only partly-- The reason they can tinker with it is that it's not public; if it were public it would (hopefully) be better-defined, locked down and stable, part of the Android compatibility test suite. That feature request would let people supply their own sqlite.so, and also redirect the Java API to use it. Seems like a very promising approach.
donatj 10y ago
Having had Nougat for a while now I have not seen any issues as an app consumer.
- BoorishBears 10y ago
  Because using the NDK only solves a very specific set of problems (and brings it's own can of worms in the wild), not that many apps will directly be affected by this.
iainmerrick 10y ago
The article finishes by asking: Would it really be so bad to include libsqlite in the NDK? Good question! Why is the NDK so underpowered? Why are they so reluctant (or unable) to publish some useful, stable native APIs? It's missing things like curl, sqlite, icu, OpenSSL. Most of these are built into the OS but not accessible via the NDK. (OpenSSL isn't a great library, I know, but it's patchable. And they could start now with something better.) The only useful native third-party libraries you get are: zlib, OpenGL ES, OpenSL ES. Apart from that, there are a bunch of wrappers for a small subset of Java APIs (AssetManager, Bitmap and so on). These are tedious to use, and seem to have been written by hand so in some cases they're buggy -- for example, incorrect string handling in AStorageManager: https://code.google.com/p/android/issues/detail?id=41983 https://code.google.com/p/android/issues/detail?id=41983
- ndesaulniers 10y ago
  Then they have to maintain those versions of those libs forever. Most devices don't have enough storage space for 9 copies of the past major versions of all of these libs.
  - iainmerrick 10y ago
    Well, that's the importance of stable APIs. You only need a new copy on major revisions, which are rare. libcurl, for example, claims to have had a stable API and ABI since 2006 (https://curl.haxx.se/libcurl/features.html#stableapi https://curl.haxx.se/libcurl/features.html#stableapi). Why do we only get zlib? I bet many Android devices already have at least 9 copies of sqlite, since so many apps ship their own copy.
- izacus 10y ago
  Most likely Google (and OEMs) want to keep the flexibility of changing implementations without having to code and patch those libraries you listed forever. NDK team doesn't look like it's expansive or prioritized at all, so I doubt they have resources or will to do that. TBH I'd rather they focus at improving the tooling and debugging, we can ship our own libraries with our own options compiled in. Heck even on iOS we have to ship a custom SQLite to get all the needed capability.
j_s 10y ago
Android and SQLite have had some adventures: pre-Honeycomb (v3/API 11/2011), corrupted databases were automagically deleted by the OS API used to open them! SQLite was also shaking out a (very) few bugs out during this time period; pretty sure this auto-delete implementation wiped any chance of semi-easily recovering my first phone's SMS db. https://stackoverflow.com/questions/7764943/what-can-be-done-about-the-fact-that-android-automatically-deletes-corrupt-sqlit https://stackoverflow.com/questions/7764943/what-can-be-done... "8. Bugs in SQLite" (2013) https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
vadiml 10y ago
I would suggest another approach: 1) Import the source android.database.sqlite package while renaming it to my.database.sqlite 2) modify it trivially to load your own instance of sqlite....
verytrivial 10y ago
Wouldn't symbol versioning[1] allow more than one SQLite in a Java process, even if it is just to munge the system shipped symbols into an Android specific subspace? (I do this, but with a large C++ application so I might be missing some finer details). 1. https://refspecs.linuxfoundation.org/LSB_4.0.0/LSB-Core-generic/LSB-Core-generic/symversion.html https://refspecs.linuxfoundation.org/LSB_4.0.0/LSB-Core-gene...
jeremy_zumero 10y ago
Here's the Android bug logged, which includes a repro app. https://code.google.com/p/android/issues/detail?id=213433 https://code.google.com/p/android/issues/detail?id=213433 Although I mentioned using dlopen and dlsym to find the SQLite functions in the current process, our actual production solution was to use JNI to call the android.database.sqlite classes.

82 points by luu 10y ago

Ugh, if you were linking against system sqlite you had problems RIGHT NOW before Android N. sqlite.so isn't part of public Android API surface, which means the OEMs will regularly replace it, break it, compile it with strange options and deal a lot of pain to you and your users. So if you weren't shipping your own native SQLite in NDK/Xamarin you already had problems, you just maybe didn't notice them. Android N is now actually actively preventing linking against system libraries which aren't part of public API surface, which is good - it'll result in less developers doing dumb things which break on minor updates.

pawadu 10y ago
> it'll result in less developers doing dumb things which break on minor updates. You can never stop the Facebook developers doing dumb things on Android. Google have tried for years, but Facebook always manages to find new ways to fuck up.
- oblio 10y ago
  They're not dumb, they're challenges! Preferably challenges solved by creating new VMs, compilers and other interesting development projects ;)
  - pawadu 10y ago
    Yeah, challenges Facebook themselves created by writing the most bloat app ever written https://www.facebook.com/notes/facebook-engineering/under-the-hood-dalvik-patch-for-facebook-for-android/10151345597798920 https://www.facebook.com/notes/facebook-engineering/under-th...
    - iainmerrick 10y ago
      Facebook is one of the most feature-rich apps available for Android. With features like push notifications, news feed, and an embedded version of Facebook Messenger (a complete app in its own right) all working together in real-time, the complexity and volume of code creates technical challenges that few, if any, other Android developers face I've never understood why the Facebook client is so heavy. That isn't a long list of features! It seems like the requirements could be written on the back of an envelope. Obviously part of the reason is that they move fast and have a lot of developers, so there's much more pressure to add code than to keep it trimmed down. But the client still doesn't do all that much. Surely, surely most of the logic is handled on the server.
    - lxgr 10y ago
      I've noticed they are now doing a similar thing now for push notifications. Instead of using the platform-provided, battery efficient GCM (now FCM), they insist on using their own MQTT push solution. Not only does this require a constantly running background service and a TCP socket which has to be reopened on every connectivity change (e.g. a switch from wi-fi to mobile data): They do it for every single one of their apps. My phone has at least three of them (Instagram, Facebook, Messsenger), and I'm pretty sure that this is a major reason for battery life problems on Android.
      - yegle 10y ago
        You can at least uninstall Facebook.app: use m.facebook.com in chrome, you can even get push notification via Chrome.
        j_s 10y ago
        Also: https://mbasic.facebook.com https://mbasic.facebook.com
        NoGravitas 10y ago
        The Facebook mobile web page has been going through so much churn lately, I feel like they are working to make it less desirable relative to the app. Disabling access to messages was one of the big ones, but they also regularly change how the sharing links work (to where you can or cannot share from a given action). One of the most recent and enjoyable changes is that the unread count badge on notifications updates in the background, but the actual notification drop down doesn't. You have to go to the notifications page (see all notifications) to see what the new notification actually is.
        parthdesai 10y ago
        And can use firefox to access messenger.
lultimouomo 10y ago
But the point is that you ship your own copy of SQLite you still have a problem. You need to make sure that a database is never accessed both through the android wrapper and you own sqlite copy, or you will face corruption. Seems trivial, but when you take into consideration things like Xamarin and third party libraries, it's not so clear-cut.
- izacus 10y ago
  Hmm, I dunno, I kinda thought that not mixing sqlite versions would be a pretty obvious thing. Xamarin, Cordova and others are pretty much a cesspool of catastrophically poor libraries so I'm not sure why Google has to take the blame for that.

unsoundInput 10y ago

Kind of related: There is an outstanding request [0] on the Android issue tracker to offer an official support/compat library for Android's sqlite bindings, that would bridge Android's API (in a different java package) to a sqlite binary that you provide. There are already implementations [1][2] of this, but because it is not provided by the Android team it is hardly supported by ORMs, libraries, etc. [0] https://code.google.com/p/android/issues/detail?id=202658 https://code.google.com/p/android/issues/detail?id=202658 [1] https://www.sqlite.org/android/doc/trunk/www/index.wiki https://www.sqlite.org/android/doc/trunk/www/index.wiki [2] https://github.com/requery/sqlite-android https://github.com/requery/sqlite-android

kiallmacinnes 10y ago
Does this not just hit the "The Multiple SQLite Problem" described in the article?
- unsoundInput 10y ago
  I don't think so but I might also interpret the article wrong. The problem - from what I understand - is that you used to be able to use the systems sqlite.so to access databases owned by your app from the native environment. With Android N you are no longer able to do this, you need to ship your own sqlite binary. This can lead to problems when you want to access the same sqlite files from both the Android platform APIs and native (e.g. use it from native for business logic, debug it with a tool like Stetho [0] that uses android.database.sqlite) because of a version mismatch. Would Google offer (a copy of) the Android API with the ability to plug in a different sqlite-compatible binaries, it would very likely find broad adoption. Then the problem stated by the article could be solved by shipping the app with a sqlite build that is used by both the native code and plugged into the compat library. [0] https://facebook.github.io/stetho/ https://facebook.github.io/stetho/

amluto 10y ago

One big danger seems to be that POSIX locks don't like having two fds for the same file in the same process. But Linux has a newish kind of lock that is backwards-compatible and works better. I would love for SQLite to use it. Does SQLite accept patches?

kiallmacinnes 10y ago
Patches are accepted, though.. SQLite is, by all accounts I've read, a very well written and tested piece of software, and I'd be surprised if there was an easy and safe win like this which hasn't already been proposed / considered. Though, my guess shouldn't stop you looking for previous suggestions to implement this, and if there are none, submitting it yourself :)
- stuaxo 10y ago
  It's amazing how many quick wins are to be had in open source if you have the time to patch yourself.
pjmlp 10y ago
POSIX locks semantics are implementation defined, hence why they are pretty much useless unless all applications access the same set of files play by the same rules.
- zurn 10y ago
  Or the apps all know which implemnetation they are running on (Linux in this case).

Zigurd 10y ago

How many of these accesses of sqlite.so are the result of using cross platform tools? All of them? Is there any reason to do this otherwise?

gcp 10y ago
If you have native code that wants to access a database, not round trip though Java, and not incur the size hit of shipping a separate copy OR (and this is key) want your Java code to be able to read the same database without writing your own Java->JNI SQLite wrapper.
- Zigurd 10y ago
  Unless you are hammering on a jni in an inner loop, you won't notice.
  - iainmerrick 10y ago
    Another problem is explained in the feature request that another commenter linked to: https://code.google.com/p/android/issues/detail?id=202658 https://code.google.com/p/android/issues/detail?id=202658 You have no idea what version of sqlite the OS might be using, so you can't rely on consistent behavior. In many cases the vendor will have tinkered with it and messed it up. Comprehensively testing on all Android devices is basically impossible because there are so many models out there.
    - Zigurd 10y ago
      That sounds like an argument against direct access to sqlite.so.
      - iainmerrick 10y ago
        Hmm, yeah, but only partly-- The reason they can tinker with it is that it's not public; if it were public it would (hopefully) be better-defined, locked down and stable, part of the Android compatibility test suite. That feature request would let people supply their own sqlite.so, and also redirect the Java API to use it. Seems like a very promising approach.

donatj 10y ago

Having had Nougat for a while now I have not seen any issues as an app consumer.

BoorishBears 10y ago
Because using the NDK only solves a very specific set of problems (and brings it's own can of worms in the wild), not that many apps will directly be affected by this.

iainmerrick 10y ago

The article finishes by asking: Would it really be so bad to include libsqlite in the NDK? Good question! Why is the NDK so underpowered? Why are they so reluctant (or unable) to publish some useful, stable native APIs? It's missing things like curl, sqlite, icu, OpenSSL. Most of these are built into the OS but not accessible via the NDK. (OpenSSL isn't a great library, I know, but it's patchable. And they could start now with something better.) The only useful native third-party libraries you get are: zlib, OpenGL ES, OpenSL ES. Apart from that, there are a bunch of wrappers for a small subset of Java APIs (AssetManager, Bitmap and so on). These are tedious to use, and seem to have been written by hand so in some cases they're buggy -- for example, incorrect string handling in AStorageManager: https://code.google.com/p/android/issues/detail?id=41983 https://code.google.com/p/android/issues/detail?id=41983

ndesaulniers 10y ago
Then they have to maintain those versions of those libs forever. Most devices don't have enough storage space for 9 copies of the past major versions of all of these libs.
- iainmerrick 10y ago
  Well, that's the importance of stable APIs. You only need a new copy on major revisions, which are rare. libcurl, for example, claims to have had a stable API and ABI since 2006 (https://curl.haxx.se/libcurl/features.html#stableapi https://curl.haxx.se/libcurl/features.html#stableapi). Why do we only get zlib? I bet many Android devices already have at least 9 copies of sqlite, since so many apps ship their own copy.
izacus 10y ago
Most likely Google (and OEMs) want to keep the flexibility of changing implementations without having to code and patch those libraries you listed forever. NDK team doesn't look like it's expansive or prioritized at all, so I doubt they have resources or will to do that. TBH I'd rather they focus at improving the tooling and debugging, we can ship our own libraries with our own options compiled in. Heck even on iOS we have to ship a custom SQLite to get all the needed capability.

j_s 10y ago

Android and SQLite have had some adventures: pre-Honeycomb (v3/API 11/2011), corrupted databases were automagically deleted by the OS API used to open them! SQLite was also shaking out a (very) few bugs out during this time period; pretty sure this auto-delete implementation wiped any chance of semi-easily recovering my first phone's SMS db. https://stackoverflow.com/questions/7764943/what-can-be-done-about-the-fact-that-android-automatically-deletes-corrupt-sqlit https://stackoverflow.com/questions/7764943/what-can-be-done... "8. Bugs in SQLite" (2013) https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html

vadiml 10y ago

I would suggest another approach: 1) Import the source android.database.sqlite package while renaming it to my.database.sqlite 2) modify it trivially to load your own instance of sqlite....

verytrivial 10y ago

Wouldn't symbol versioning[1] allow more than one SQLite in a Java process, even if it is just to munge the system shipped symbols into an Android specific subspace? (I do this, but with a large C++ application so I might be missing some finer details). 1. https://refspecs.linuxfoundation.org/LSB_4.0.0/LSB-Core-generic/LSB-Core-generic/symversion.html https://refspecs.linuxfoundation.org/LSB_4.0.0/LSB-Core-gene...

jeremy_zumero 10y ago

Here's the Android bug logged, which includes a repro app. https://code.google.com/p/android/issues/detail?id=213433 https://code.google.com/p/android/issues/detail?id=213433 Although I mentioned using dlopen and dlsym to find the SQLite functions in the current process, our actual production solution was to use JNI to call the android.database.sqlite classes.