Efcore: SQLite: Exists returns false positive when SQLITE_CANTOPEN_ENOENT

[NPadrutt]

we have an app written with xamarin for android who uses Entity Framework Core. It works on our test devices from Wiko (Android 6.0), HTC (Android 7.1) and Sony(6.0). But on Samsung devices (6.0) we get the following exception on startup:

   java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1120)
Caused by: java.lang.reflect.InvocationTargetException
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:1230)
    ... 1 more
Caused by: android.runtime.JavaProxyThrowable: Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1294: 'unable to open database file'.
  at Microsoft.EntityFrameworkCore.Migrations.HistoryRepository.Exists () [0x00000] in <f97b8874924247a39fca91e98b024cf6>:0 
  at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate (System.String targetMigration) [0x00030] in <f97b8874924247a39fca91e98b024cf6>:0 
  at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate (Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade databaseFacade) [0x00010] in <f97b8874924247a39fca91e98b024cf6>:0 
  at FriendsAppDate.Business.AppStart.Start (System.Object hint) [0x0001b] in <c1a86397b9cc451297593cc8cc1289f7>:0 
  at MvvmCross.Droid.Views.MvxSplashScreenActivity.TriggerFirstNavigate () [0x00005] in <f6aebf863dc84be7b380cfec8d459508>:0 
  at MvvmCross.Droid.Views.MvxSplashScreenActivity.InitializationComplete () [0x00009] in <f6aebf863dc84be7b380cfec8d459508>:0 
  at MvvmCross.Droid.Platform.MvxAndroidSetupSingleton.InitializeFromSplashScreen (MvvmCross.Droid.Views.IMvxAndroidSplashScreenActivity splashScreen) [0x00029] in <f6aebf863dc84be7b380cfec8d459508>:0 
  at MvvmCross.Droid.Views.MvxSplashScreenActivity.OnResume () [0x00018] in <f6aebf863dc84be7b380cfec8d459508>:0 
  at Android.App.Activity.n_OnResume (System.IntPtr jnienv, System.IntPtr native__this) [0x00009] in <fca2c02347db4086aed81b5475a315fd>:0 
  at (wrapper dynamic-method) System.Object:b336e942-d75d-4699-a108-3115233929aa (intptr,intptr)
    at mvvmcross.droid.views.MvxSplashScreenActivity.n_onResume(Native Method)
    at mvvmcross.droid.views.MvxSplashScreenActivity.onResume(MvxSplashScreenActivity.java:39)
    at android.app.Instrumentation.callActivityOnResume(Instrumentation.java:1286)
    at android.app.Activity.performResume(Activity.java:6987)
    at android.app.ActivityThread.performResumeActivity(ActivityThread.java:4144)
    at android.app.ActivityThread.handleResumeActivity(ActivityThread.java:4245)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1838)
    at android.os.Handler.dispatchMessage(Handler.java:102)
    at android.os.Looper.loop(Looper.java:158)
    at android.app.ActivityThread.main(ActivityThread.java:7224)
    ... 3 more

To reproduce it, I created a new solution, included the projects and removed as much as possible who isn't needed. You can find it under this link:
https://github.com/NPadrutt/SamsungIssueTestProject

Please open the AppStart.cs and make a breakpoint on line 48. When you go one step further from there a DBException is thrown and catched on line 51.
NOTE: I tested the test project on an Samsung Galaxy S5 and an HTC 10. The Issue only appears on the Samsung. The HTC works fine.

In the catch block is some code who executes sql statements to create the db manually and these do work on the Samsung aswell.

[bricelam]

What version of EF Core?
What version of SQLite? (can get using new SqliteConnection().ServerVersion)

[NPadrutt]

Oh I'm sorry. It seems I deleted this line again.

The EF Core version and EF Core Sqlite Versions are both 1.1.1.
The Sqlite Database on the Samsung devices is 3.8.10.2. On my HTC (which worked) it's 3.9.2.

This seems to correlate with the android version. I will check the other devices we have with android 6.0.1 wo worked. But in theory they should have the same SQL version as the Samsung devices.
https://developer.android.com/reference/android/database/sqlite/package-summary.html

[bricelam]

Is the location of the database file what you expect? If you're using relative paths in the connection string, they'll be rooted at AppContext.BaseDirectory.

[NPadrutt]

We use this as the DB Path on Android:
Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), Constants.DB_NAME);

And assign it with:
FadContext.DataBasePath = Mvx.Resolve().DbPath;

Maybe to point out: I can create the database, when I execute the Create Statements instead of
db.Database.Migrate();
or
await db.Database.MigrateAsync();

[bricelam]

Ah, that's the missing piece I needed.

It looks like an extended error code is being returned which we don't handle in the Exists check. This is a bug.

[ajcvickers]

@bricelam to add the workaround.

[bricelam]

Workaround

It's ugly, but you can work around this by turning off the extended result codes before calling Migrate.

First, import this native function.
```C#
[DllImport("sqlite3", CallingConvention = CallingConvention.Cdecl)]
static extern int sqlite3_extended_result_codes(IntPtr db, int onoff);

Then add a handler to use it when the connection is opened.

```C#
StateChangeEventHandler handler =
    (sender, e) =>
    {
        if (e.CurrentState != ConnectionState.Open)
            return;

        var connection = (SqliteConnection)sender;

        sqlite3_extended_result_codes(connection.Handle, 0);
    };

db.Database.GetDbConnection().StateChange += handler;
db.Database.Migrate();
db.Database.GetDbConnection().StateChange -= handler;

[bricelam]

Digging into this more, it looks like the database is returning the result code SQLITE_CANTOPEN_ENOENT (1294) which is not a standard SQLite error code, but something that has been added to a customized version of SQLite for Android.

SQLite has no way to enable extended error codes when opening a connection (they can only be turned on after a connection is open).

In summary, this behavior is incompatible with the official SQLite codebase and I think we can consider it an external issue.

This has been mitigated in 2.0.0 where SQLitePCL.raw will provide a version of SQLite for Android that doesn't do this.

[NPadrutt]

Thanks for the update. Is there any timeframe for release of 2.0.0?

[bricelam]

The roadmap currently says Q3 2017. We should have our first prerelease within a month or so. You can always try the nightly builds.

[NPadrutt]

Ah, great. Thank you very much :)

[DamirLisak]

FYI:
Thank you for this workaround, but it works only if db.Database.OpenConnection(); is called before db.Database.Migrate();!
Otherwise the exception is thrown before the StateChanged-Event is raised.