Bug 42507 - SQLite crashing in 6.1.1.1
Summary: SQLite crashing in 6.1.1.1
Status: RESOLVED NOT_REPRODUCIBLE
Alias: None
Product: Android
Classification: Xamarin
Component: General ()
Version: unspecified
Hardware: PC Mac OS
: --- normal
Target Milestone: ---
Assignee: Jonathan Pryor
URL:
Depends on:
Blocks:
 
Reported: 2016-07-13 10:31 UTC by manuel
Modified: 2017-06-28 15:35 UTC (History)
7 users (show)

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


Attachments
sqlite.net that we use (99.92 KB, text/x-csrc)
2016-07-13 22:23 UTC, manuel
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 NOT_REPRODUCIBLE

Description manuel 2016-07-13 10:31:24 UTC
Hi guys

we are having some crashes caused by SQLite. The error arrives with several faces in different versions of the OS. Here are a few examples:

Device = motorola, XT1078, thea_umtsds; 
System Version = Level = M; 
Error = SQLite.SQLiteException: database disk image is malformed at SQLite.SQLite3.Prepare2 (IntPtr db, System.String query)

Device = BLU, BLU STUDIO 5.0 Ce, BLU STUDIO 5.0 Ce; 
System Version = Level = Kitkat; 
Error = SQLite.SQLiteException: Could not open database file: /storage/sdcard0/Android/data/com.xxxxx.xxxxx/cache/xxxxxx.bd (IOError)

Device = motorola, XT1033, falcon_umtsds; 
System Version = Level = LollipopMr1; 
Error = SQLite.SQLiteException: IOError at SQLite.SQLiteCommand.ExecuteNonQuery () <0xa234d520 + 0x00114> in :0 at SQLite.SQLiteConnection.Execute (System.String query, System.Object[] args) <0xa234d000 + 0x00097> in :0 at SQLite.SQLiteConnection.CreateTable (System.Type ty, CreateFlags createFlags) <0xa2349910 + 0x002a3> in :0 at SQLite.SQLiteConnection.CreateTable[T] (CreateFlags createFlags)

########
Xamarin Studio Business
Version 6.0.1 (build 9)
Installation UUID: 5f421f3f-40c4-4caa-8ae3-df7a8094edf4
Runtime:
	Mono 4.4.1 (mono-4.4.0-branch-c7sr0/4747417) (64-bit)
	GTK+ 2.24.23 (Raleigh theme)

	Package version: 404010000

Xamarin.Profiler
Version: 0.0.0.0
Location: /Users/nativolabs/Documents/profiler-mac-0.9-0/XamarinProfiler.Mac.app/Contents/MacOS/XamarinProfiler.Mac

Apple Developer Tools
Xcode 7.3.1 (10188.1)
Build 7D1014

Xamarin.Mac
Not Installed

Xamarin.Android
Version: 6.1.1.1 (Xamarin Business)
Android SDK: /Users/nativolabs/Library/Developer/Xamarin/android-sdk-mac_x86
	Supported Android versions:
		4.3    (API level 18)
		4.4    (API level 19)
		4.4.87 (API level 20)
		5.0    (API level 21)
		5.1    (API level 22)
		6.0    (API level 23)

SDK Tools Version: 25.1.7
SDK Platform Tools Version: 24
SDK Build Tools Version: 23.0.3

Java SDK: /usr
java version "1.8.0_60"
Java(TM) SE Runtime Environment (build 1.8.0_60-b27)
Java HotSpot(TM) 64-Bit Server VM (build 25.60-b23, mixed mode)

Android Designer EPL code available here:
https://github.com/xamarin/AndroidDesigner.EPL

Xamarin Android Player
Version: 0.6.5
Location: /Applications/Xamarin Android Player.app

Xamarin.iOS
Version: 9.8.1.4 (Xamarin Business)
Hash: 3cf8aae
Branch: c7sr0
Build date: 2016-06-20 16:09:58-0400

Xamarin Inspector
Version: 0.7.0.0
Hash: 2a5f6e9
Branch: master
Build date: Thu Mar 31 18:37:26 UTC 2016

Build Information
Release ID: 600010009
Git revision: e879ce52093257c5c386ad7e390dfaa937fa7f90
Build date: 2016-06-24 11:53:43-04
Xamarin addins: a9252e6df4851fbbed1f9c6228e7b6dd1b475ac5
Build lane: monodevelop-lion-cycle7-sr0

Operating System
Mac OS X 10.11.5
Darwin Nativos-MacBook-Pro.local 15.5.0 Darwin Kernel Version 15.5.0
    Tue Apr 19 18:36:36 PDT 2016
    root:xnu-3248.50.21~8/RELEASE_X86_64 x86_64

Enabled user installed addins
Xamarin Inspector 0.7.0.0
Comment 1 Jonathan Pryor 2016-07-13 18:27:28 UTC
@manuel: What API are you using to deal with SQLite? That doesn't look like Mono.Data.Sqlite.dll, and that doesn't look like Android.Database.Sqlite either.

Is it possible that you have a SQLite version mismatch somewhere?
Comment 2 manuel 2016-07-13 20:39:11 UTC
Hi Jon

we use SQLite.net. We have it for a long long time and never gave us problems.
It just started after some recent release of the Xamarin sdk for Android.

Manuel
Comment 3 Jonathan Pryor 2016-07-13 21:49:52 UTC
Related: https://developer.xamarin.com/releases/android/xamarin.android_6/xamarin.android_6.1/#mono-data-sqlite

Have you updated your SQLite-net version? What version are you using?

The future changes to Android Nougat have required that all assemblies which bind SQLite also distribute their own copies of SQLite, so -- if you've upgraded SQLite-net recently -- you may now be using a different libsqlite*.so version, which *could* have different file format implications.

This gets quite confusing so I'm not entirely sure what all is even *possible*, much less what to look for.

The reported IOErrors are also odd, in that they have no description attached. What's the IO Error? File not found? Directory doesn't exist? Something else?
Comment 4 manuel 2016-07-13 22:22:04 UTC
The version that we have is P/Invoking sqlite3, we did not update nothing and we have the source being compiled in our solution.
I'll send the source as we have it as an attachment.
Comment 5 manuel 2016-07-13 22:23:41 UTC
Created attachment 16658 [details]
sqlite.net that we use
Comment 6 Jonathan Pryor 2016-07-25 22:58:49 UTC
I still don't know why your app would be crashing. :-(

That said, how *exactly* are you using SQLite-net? Are you using the SQLite-net or SQLite-net-pcl packages, or just including the source into your app? The former should work on Android Nougat, as SQLitePCL.bundle_green includes native libraries; the latter will not.
Comment 7 manuel 2016-07-25 23:42:14 UTC
Jon, tomorrow i can look back to this issue in more detail.

We do compile SqLite.cs exactly as it is in the enclosed file.
I forgot to mention that we do have different behaviours if we compile just targeting armeabi-v7a and targeting armv64-v8a in 32 bit devices. 

For example in Google automatic tests we have a Nexus 9 (Native Platform armeabi-v7a) crashing. If we recompile the same exact project supporting arm64-v8a it passes the test. However we cannot send a version to production supporting 64 bits. It will crash with a lot of System.ExecutionEngineException SIGILL in 64 bit devices.

In this situation the crash that we have is in Mono.Data.Sqlite, but probably the error is a consequence of something in the creation which is carried out through SqLite.cs.
When you look to the list of crashes they all look related with access permissions. However this situation suggests that something different might be happening.
 
When the app is first installed we do the following 


using (var conn = new SQLite.SQLiteConnection (GetPermanentDBPath ())) {
	conn.CreateTable<StoryDB> ();
	conn.CreateTable<VersionDB> ();
	conn.CreateTable<UserInfoDB> ();
}


Later on the app crashes when we try to access the same database with Mono.Data.Sqlite with the following error.  

FATAL EXCEPTION: main
 Process: com.XXXX.XXXXX, PID: 10188
 java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
 	at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
 Caused by: java.lang.reflect.InvocationTargetException
 	at java.lang.reflect.Method.invoke(Native Method)
 	at java.lang.reflect.Method.invoke(Method.java:372)
 	at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
 	... 1 more
 Caused by: android.runtime.JavaProxyThrowable: Mono.Data.Sqlite.SqliteException: SQLite error
 no such table: StoryDB
   at Mono.Data.Sqlite.SQLite3.Prepare (Mono.Data.Sqlite.SqliteConnection cnn, System.String strSql, Mono.Data.Sqlite.SqliteStatement previous, UInt32 timeoutMS, System.String& strRemain) <0xdf96fe60 + 0x00614> in <filename unknown>:0 
   at Mono.Data.Sqlite.SqliteCommand.BuildNextCommand () <0xdf96fa68 + 0x00187> in <filename unknown>:0 
 	at md51970a6219cda397ba8bb14cc19400540.MyStoriesActivity.n_onResume(Native Method)
 	at md51970a6219cda397ba8bb14cc19400540.MyStoriesActivity.onResume(MyStoriesActivity.java:69)
 	at android.app.Instrumentation.callActivityOnResume(Instrumentation.java:1241)
 	at android.app.Activity.performResume(Activity.java:6019)
 	at android.app.ActivityThread.performResumeActivity(ActivityThread.java:2940)
 	at android.app.ActivityThread.handleResumeActivity(ActivityThread.java:2982)
 	at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2365)
 	at android.app.ActivityThread.access$800(ActivityThread.java:144)
 	at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1278)
 	at android.os.Handler.dispatchMessage(Handler.java:102)
 	at android.os.Looper.loop(Looper.java:135)
 	at android.app.ActivityThread.main(ActivityThread.java:5221)
 	... 4 more
Comment 8 Jonathan Pryor 2016-07-26 01:04:17 UTC
> When the app is first installed we do the following 

*Where* is that done? Activity.OnCreate()? Activity.OnResume()?

> Caused by: android.runtime.JavaProxyThrowable: Mono.Data.Sqlite.SqliteException: SQLite error
> no such table: StoryDB
>   at Mono.Data.Sqlite.SQLite3.Prepare (Mono.Data.Sqlite.SqliteConnection cnn, System.String strSql, Mono.Data.Sqlite.SqliteStatement previous, UInt32 timeoutMS, System.String& strRemain) <0xdf96fe60 + 0x00614> in <filename unknown>:0 
>   at Mono.Data.Sqlite.SqliteCommand.BuildNextCommand () <0xdf96fa68 + 0x00187> in <filename unknown>:0 
> 	at md51970a6219cda397ba8bb14cc19400540.MyStoriesActivity.n_onResume(Native Method)

The above says that MyStoriesActivity.OnResume() was invoked, which tried to do *something* with the `StoryDB` table, which didn't exist at the time that MyStoriesActivity.OnResume() executed.
Comment 9 manuel 2016-07-26 01:18:09 UTC
Jon, exactly. The problem looks like it is happening here "conn.CreateTable<StoryDB> ();". This method is called 

public class XXXXX : Application {

...

public override void OnCreate()
{
	base.OnCreate ();

...

//protected by a "stupid" try catch

try{
	conn.CreateTable<StoryDB> ();
}
catch(Exception ex){
	Reports an Error
}

The key point is not Mono.Data.Sqlite.SQLite3.Prepare. The key point is the difference that we experience between 32 and 64 bit support.
Comment 10 manuel 2016-07-29 11:33:34 UTC
Jon, just another note

I was trying to compile our app with current android available in the beta channel and have a dll not found exception "libsqlite3_xamarin.so" being thrown.

This means that 

using (var conn = new SQLite.SQLiteConnection (GetPermanentDBPath ())) {
	conn.CreateTable<StoryDB> ();
	conn.CreateTable<VersionDB> ();
	conn.CreateTable<UserInfoDB> ();
}

is executed properly and 

var connStr = "URI=file:" + DataBaseManager.GetPermanentDBPath ();

using (var db = new Mono.Data.Sqlite.SqliteConnection (connStr)) {
	db.Open ();

...
}


fails with dll not found.


What dll is being used here [DllImport("sqlite3"...] and why is libsqlite3_xamarin.so missing in Mono.Data.Sqlite.SQLite3?

cheers
Manuel

  at (wrapper managed-to-native) Mono.Data.Sqlite.UnsafeNativeMethods:sqlite3_open_v2 (byte[],intptr&,int,intptr)
  at Mono.Data.Sqlite.SQLite3.Open (System.String strFilename, SQLiteOpenFlagsEnum flags, Int32 maxPoolSize, Boolean usePool) [0x00057] in /Users/builder/data/lanes/3540/4a7d97ac/source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLite3.cs:129 
  at Mono.Data.Sqlite.SqliteConnection.Open () [0x002be] in /Users/builder/data/lanes/3540/4a7d97ac/source/mono/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteConnection.cs:888 
  at XXXX.Data.StoryDB.GetAllAutoStories () [0x00033] in /Users/XXXX/Documents/Code/XXXXX/XXXX.Data/Story.cs:204 
  at XXXX.MyStoriesActivity.OnResume () [0x00013] in /Users/XXXX/Documents/Code/XXXX/XXXX.2.Droid/XXXX.2.Droid/Activities/MyStoriesActivity.cs:1129 
  at Android.App.Activity.n_OnResume (IntPtr jnienv, IntPtr native__this) [0x00009] in /Users/builder/data/lanes/3540/4a7d97ac/source/monodroid/src/Mono.Android/platforms/android-23/src/generated/Android.App.Activity.cs:3920 
  at (wrapper dynamic-method) System.Object:adea4d0c-04b4-4d61-a3f7-35d621bf5aa6 (intptr,intptr)



#######################################################


=== Xamarin Studio Business ===

Version 6.0.2 (build 70)
Installation UUID: 5f421f3f-40c4-4caa-8ae3-df7a8094edf4
Runtime:
	Mono 4.4.2 (mono-4.4.0-branch-c7sr1/b430435) (64-bit)
	GTK+ 2.24.23 (Raleigh theme)

	Package version: 404020008

=== Xamarin.Profiler ===

Version: 0.0.0.0
Location: /Users/nativolabs/Documents/profiler-mac-0.9-0/XamarinProfiler.Mac.app/Contents/MacOS/XamarinProfiler.Mac

=== Apple Developer Tools ===

Xcode 7.3.1 (10188.1)
Build 7D1014

=== Xamarin.iOS ===

Version: 9.8.2.19 (Xamarin Business)
Hash: a5ae61c
Branch: cycle7-sr1
Build date: 2016-07-20 23:23:58-0400

=== Xamarin.Android ===

Version: 6.1.2.20 (Xamarin Business)
Android SDK: /Users/nativolabs/Library/Developer/Xamarin/android-sdk-mac_x86
	Supported Android versions:
		4.3    (API level 18)
		4.4    (API level 19)
		4.4.87 (API level 20)
		5.0    (API level 21)
		5.1    (API level 22)
		6.0    (API level 23)

SDK Tools Version: 25.1.7
SDK Platform Tools Version: 24
SDK Build Tools Version: 23.0.3

Java SDK: /usr
java version "1.8.0_60"
Java(TM) SE Runtime Environment (build 1.8.0_60-b27)
Java HotSpot(TM) 64-Bit Server VM (build 25.60-b23, mixed mode)

Android Designer EPL code available here:
https://github.com/xamarin/AndroidDesigner.EPL

=== Xamarin Android Player ===

Version: 0.6.5
Location: /Applications/Xamarin Android Player.app

=== Xamarin.Mac ===

Not Installed

=== Xamarin Inspector ===

Version: 0.7.0.0
Hash: 2a5f6e9
Branch: master
Build date: Thu Mar 31 18:37:26 UTC 2016

=== Build Information ===

Release ID: 600020070
Git revision: 30f7c18e8acbca2124c88a2ba9014123097c53ab
Build date: 2016-07-21 16:55:52-04
Xamarin addins: 451cc4c4640551a72356d8a85a4f15ff55fcb661
Build lane: monodevelop-lion-cycle7-sr1

=== Operating System ===

Mac OS X 10.11.5
Darwin Nativos-MacBook-Pro.local 15.5.0 Darwin Kernel Version 15.5.0
    Tue Apr 19 18:36:36 PDT 2016
    root:xnu-3248.50.21~8/RELEASE_X86_64 x86_64

=== Enabled user installed addins ===

Xamarin Inspector 0.7.0.0
Comment 11 manuel 2016-08-06 01:57:40 UTC
Just a small note. I was trying now to recompile Android app and did have the dll not being added to the apk again.
It turns out that at least XS (not sure about VS) does not include the dll if it not referenced on the app project. We do have it referenced on a library and the dependency chain seems that it is not being solved now. At least for libsqlite3_xamarin.so.
The solution is to reference Mono.Data.Sqlite
Comment 12 Marco Tambalo 2016-08-10 06:47:14 UTC
I had the same experience with manuel@nativolabs.com (comment 11).

We had two projects in one solution, the "core" project and the "deployment" project. Initially only the core project referenced Mono.Data.Sqlite. When I also referenced it within the deployment project, the exception was not seen again.
Comment 13 manuel 2016-08-23 16:44:16 UTC
Jon, sorry to come back to this, but just updated to cycle 8 available in beta channel now and the issue with sqlite3 just came back to life.
Although we are not using Android N, it seems that we are not able to pinvoke sqlite3. It is not available as it was before.
This is not a problem with SQLite.cs that we have in our project. We just replaced sqlite3 by libsqlite3_xamarin like 

#if __ANDROID__
	const string LibraryPath = "libsqlite3_xamarin";
#else
	const string LibraryPath = "sqlite3";
#endif

#if !USE_CSHARP_SQLITE && !USE_WP8_NATIVE_SQLITE && !USE_SQLITEPCL_RAW
	[DllImport(LibraryPath, EntryPoint = "sqlite3_open", CallingConvention=CallingConvention.Cdecl)]
		

...

However, we do not have the same degrees of freedom with nuget packages and now Akavache is complaining with DllNotFoundException.
Of course that we can go to the source of the project and update it, but this starts to be a bit awkward

Is there a way to "redirect" library path?

cheers
Comment 14 manuel 2016-08-23 18:43:56 UTC
Taking Akavache as example, if we add a new file Akavache.Sqlite3.dll.config like 

<configuration>
  <dllmap dll="sqlite3" target="libsqlite3_xamarin.so" />
</configuration>

should't this solve the issue? I'm not able to test it now, but i will in a few hours.
Comment 15 manuel 2016-08-24 01:06:00 UTC
Did try it, but it is not working with DllNotFoundException saying hello. 
Akavache depends on SQLitePCL.raw.dll and did also try remapping this library with same .config.

Note : this remapping works on sqlite-net with LibraryPath = "sqlite3"
Comment 16 manuel 2016-08-24 16:03:53 UTC
dll.config with dllmap is a solution that works. You just have to guarantee that dll.config is placed in same folder as dll.
For the particular case of Akavache / SQLitePCL.raw.dll we used the following solution to have it working again.

Go to packages folder and added it to

SQLitePCL.raw_basic.0.8.6/build/MonoAndroid/pinvoke_packaged_sqlcipher/anycpu
SQLitePCL.raw_basic.0.8.6/build/MonoAndroid/pinvoke_packaged_sqlite3/anycpu
SQLitePCL.raw_basic.0.8.6/build/MonoAndroid/pinvoke_sqlite3/anycpu

Note: these folders are automatically managed by Xamarin Studio / Visual Studio, which means that if you update it or refresh it, within several other possible automatic scenarios, these .config files can be deleted.
 
Now why DllImport("sqlite3" is not able to arrive to sqlite native library... have no idea.
Comment 17 Gautam Jain 2017-02-22 06:39:33 UTC
Thanks @manuel.

I resolved this by referencing latest Mono.Data.Sqlite. The trick is that you need to reference System.Data.Sqlite assembly, 
which is actually Mono.Data.Sqlite. It will show as Mono.Data.Sqlite in Visual Studio.
Comment 18 John Miller [MSFT] 2017-06-28 15:35:53 UTC
Unfortunately, we’re unable to reproduce this report. If this issue is still occurring for you, please reopen this issue and attach a reproduction to the bug by starting with a clean Xamarin.Android project adding just the code necessary to demonstrate the issue.