Bug 12406 - SqliteConnection.SetConfig() call raises SQLite exception "library used incorrectly: misuse"
Summary: SqliteConnection.SetConfig() call raises SQLite exception "library used incor...
Status: RESOLVED INVALID
Alias: None
Product: Android
Classification: Xamarin
Component: General ()
Version: 4.6.x
Hardware: PC Mac OS
: High normal
Target Milestone: ---
Assignee: Marek Habersack
URL:
Depends on:
Blocks:
 
Reported: 2013-05-27 08:40 UTC by Maxim
Modified: 2014-11-27 12:30 UTC (History)
7 users (show)

Tags:
Is this bug a regression?: ---
Last known good build:


Attachments
Test case (35.70 KB, application/zip)
2013-07-22 11:55 UTC, Chris Hardy [MSFT]
Details


Notice (2018-05-24): bugzilla.xamarin.com is now in read-only mode.

Please join us on Visual Studio Developer Community and in the Xamarin and Mono organizations on GitHub to continue tracking issues. Bugzilla will remain available for reference in read-only mode. We will continue to work on open Bugzilla bugs, copy them to the new locations as needed for follow-up, and add the new items under Related Links.

Our sincere thanks to everyone who has contributed on this bug tracker over the years. Thanks also for your understanding as we make these adjustments and improvements for the future.


Please create a new report on Developer Community or GitHub with your current version information, steps to reproduce, and relevant error messages or log files if you are hitting an issue that looks similar to this resolved bug and you do not yet see a matching new report.

Related Links:
Status:
RESOLVED INVALID

Description Maxim 2013-05-27 08:40:32 UTC
The problem is like Sebastien have on http://stackoverflow.com/questions/15434777/mono-data-sqlite-sqliteconnection-setconfig-throws-a-library-used-incorrectly-ex.

I want to grant that multiple threads of application do not broke SQLite database. So, like in iOS version of application, I call:

SqliteConnection.SetConfig(SQLiteConfig.Serialized);

and gets SQLite exception "library used incorrectly: misuse".

No matter where to call it: in Android.Application subclass, OnCreate of starting activity or just button click. Error occured.
Comment 1 Patrik Ahlenius 2013-06-14 14:33:23 UTC
I had the same issue (both on Android and iOS), but got past it by calling shutdown() prior to the config call, and then by calling initialize() after the config call
Comment 2 Maxim 2013-06-14 14:44:02 UTC
Could you provide code snippet please?
Comment 3 Patrik Ahlenius 2013-06-15 06:55:09 UTC
Unfortunately I don't use SqliteConnection anymore (switched over in favour to https://github.com/praeclarum/sqlite-net), but the issue should be the same, it appears the sqllite initialize function is called automagically on application startup.

 Unsure if SqliteConnection has bindings to the shutdown and initialize functions though :/, maybe they need to be added.
Comment 4 Maxim 2013-06-16 12:29:31 UTC
I could not understand you. SQLite-Net uses the same Mono.Data.Sqlite library, so calling SqliteConnection.SetConfig(SQLiteConfig.Serialized); is necessary to avoid problem of multi-threads write access with SQLite-dabase.
Comment 5 Chris Hardy [MSFT] 2013-07-22 11:55:44 UTC
Created attachment 4391 [details]
Test case
Comment 6 brian 2013-08-01 02:45:30 UTC
It's really critical that this gets solved for us.  It's holding up a huge Android project.  Without this fix we'll need to spend weeks rewriting our database system.  Thanks!
Comment 7 brian 2013-08-01 02:51:43 UTC
This won't work for me since I'm using sqlite-net rather than Mono.Data.Sqlite.  That was one of the options we looked into before, but it's not ADO.NET compliant, so it would mean rewriting all of our database code, which would take weeks.
Comment 8 brian 2013-08-01 02:53:45 UTC
Sorry, my last comment was a reply to Patrik's comment of 2013-06-14 regarding a workaround.  This won't work for me since I'm using sqlite-net rather than Mono.Data.Sqlite.
Comment 9 andrew 2013-08-17 15:50:32 UTC
Maxim, not sure if it helps but I worked around this problem using 

        [DllImport("libsqlite.so")]
        internal static extern int sqlite3_shutdown();
        [DllImport("libsqlite.so")]
        internal static extern int sqlite3_initialize();
        [DllImport("libsqlite.so")]
        internal static extern int sqlite3_config(int configOption);

then:

            sqlite3_shutdown();
            sqlite3_config(3);
            sqlite3_initialize();

Seems to be working for me now - no database corruptions, with a shared connection between multiple threads.
Comment 10 folex 2013-09-05 02:16:40 UTC
I have the same error on Xamarin.iOS, and the cause of it was that OS called sqlite_initialize() by itself, so my call to sqlite_config() was illegal and raised SQLITE_MISUSE error. It was solved by calling SqliteConnection.SetConfig(SQLiteConfig.Serialized); ASAP after application start (e.g. first line of FinishedLaunching)
Comment 11 Jon Goldberger [MSFT] 2014-03-14 16:51:12 UTC
With regards to the workaround in Comment 9, it doesn't work quite as stated, instead I did not import the sqlite3_config() method, but used this:

[DllImport("libsqlite.so")]
internal static extern int sqlite3_shutdown();

[DllImport("libsqlite.so")]
internal static extern int sqlite3_initialize();

Then, at the very beginning of your OnCreate() method, add:
sqlite3_shutdown();
SqliteConnection.SetConfig(SQLiteConfig.Serialized);
sqlite3_initialize();

This resolved the error in the test case posted earlier and for a client who was experiencing this same issue.
Comment 12 andrew 2014-03-18 12:16:16 UTC
Radek,

Jon told me that replacing

sqlite3_config(3);

with

SqliteConnection.SetConfig(SQLiteConfig.Serialized);

in the workaround of Comment 9 causes the test case to pass and also fixes problems on a customer site.

Are you able to shed any light on why Jon's change would have any effect in this case?

Andrew
Comment 13 Radek Doulik 2014-07-30 11:06:14 UTC
Marek knows sqlite better, so reassigning to him.
Comment 14 Marek Habersack 2014-11-27 07:34:51 UTC
There's good and bad news. I'll start with the bad ones - there's no easy fix for this bug. The good ones - workaround from comment 11 is the way to go.

Let me elaborate on the bad news first. The fact that one uses either SQLite-Net or Mono.Data.SQlite directly doesn't matter since the error reported here comes from a level below either of the two above, from the native library shipped with Android. SQLite is bundled with Android and used by the system and the framework APIs in many places. If you create a simple Hello World app from Xamarin.Android template and inject the following code in the main activity's OnCreate method:

Console.WriteLine ("---------- MAPS START -----------");
Console.WriteLine (System.IO.File.ReadAllText ("/proc/self/maps"));
Console.WriteLine ("---------- MAPS END -----------"); 

you will be able to spot libsqlite.so mapped into your application process. The library is loaded and initialized by Android on behalf of the application, as part of the app's initialization (it might be different on different Android versions, but that's what happens on 4.4+). Consider then the SQLite documentation:

[from https://sqlite.org/c3ref/config.html]
"The sqlite3_config() interface is not threadsafe. The application must insure that no other SQLite interfaces are invoked by other threads while sqlite3_config() is running. Furthermore, sqlite3_config() may only be invoked prior to library initialization using sqlite3_initialize() or after shutdown by sqlite3_shutdown(). If sqlite3_config() is called after sqlite3_initialize() and before sqlite3_shutdown() then it will return SQLITE_MISUSE. Note, however, that sqlite3_config() can be called as part of the implementation of an application-defined sqlite3_os_init()."

This explains why the work-around from comment 11 works and considering that OnCreate is called after libsqlite.so has been initialized it also explains why we get the SQLITE_MISUSE (https://sqlite.org/rescode.html#misuse) error code in the log.

In theory it would be possible for Xamarin.Android to work around this by including code in our startup sequence to shut sqlite down, raise event which can be used by the user to do their initialization, and re-initialize sqlite. But this might do more harm than good because we have no way of knowing the usage patterns of all the applications out there and SQLite being part of the Android framework we could break something unbeknownst to us and without any means to trace the resulting errors. 

I will instead ask the documentation team to add an article describing the workaround and recommend it as the right course of action in this case.