Efcore: Random unknown crashes from Microsoft.Data.Sqlite with Xamarin

[winstonpang]

We've been developing using the Microsoft.Data.Sqlite nuget package, version 2.10 for a few months now, but have now noticed more and more random crashes to our app and the stack trace is always the following:

=================================================================

Managed Stacktrace:

=================================================================

      at <unknown> <0xffffffff>
      at NativeMethods:sqlite3_step <0x000f1>
      at SQLitePCL.SQLite3Provider_sqlite3:SQLitePCL.ISQLite3Provider.sqlite3_step <0x00072>
      at SQLitePCL.raw:sqlite3_step <0x000c2>
      at Microsoft.Data.Sqlite.S
qliteCommand:ExecuteReader <0x01312>

We aren't doing anything special, in most cases, as you can see it all happens on ExecuteReader.

This always happens randomly. We tried upgrading to 2.2.4 as well, but that seems to cause it to happen more frequently, although still randomly.

Do we have any options for figuring out the cause of this could be? Could it be our usage of it on Xamarin?

We are also enabling WAL everytime we Open a connection through this code snippet:

```C#
var connection = new SqliteConnection(connectionString);

connection.Open();

using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "PRAGMA main.page_size = 4096; PRAGMA synchronous = NORMAL; PRAGMA main.cache_size = 25000;PRAGMA journal_mode=WAL;";
cmd.ExecuteNonQuery();
}
```

Using Visual Studio 2019 16.0.1

[jzabroski]

Did you verify your database is not corrupt?

https://www.sqlite.org/howtocorrupt.html

[winstonpang]

@jzabroski pretty certain, because I was running it on the iOS Simulator I was able to get a copy of the db from my mac. Opened the DB in a SQLite editor, can query on all data and also add new data as well. Pretty sure a corrupted DB couldn't do that unless I'm wrong.

[jzabroski]

Given the default page_size is 4096, remove that. https://www.sqlite.org/draft/pragma.html#pragma_page_size

Similarly, try to remove everything that is default just to pare down your problem.

Other than that, consider creating a project with a docker container that loads and runs the iOS simulator.

[ajcvickers]

@winstonpang Is it possible that the connection is being used concurrently from multiple threads?

[winstonpang]

@ajcvickers hmm I just had a look through our code, and there is indeed that possibility, there's a chance the IDBConnection could be consumed by multiple threads concurrently.

[jzabroski]

@winstonpang
There is also this scary warning in the doc I linked to:

8.3. I/O error while obtaining a lock leads to corruption
If the operating system returns an I/O error while attempting to obtain a certain lock on shared memory in WAL mode then SQLite might fail to reset its cache, which could lead to database corruption if subsequent writes are attempted.

[ajcvickers]

@winstonpang We discussed in triage, and while concurrent use of the DbConnection may not be the root cause of this, it is still something that is not supported by ADO.NET, and hence this should be eliminated first. Let us know if the issue persists after removing the concurrent usage.

[winstonpang]

@ajcvickers The way we had this implemented was a simple connection pool solution (since this implementation of sqlite had no connection pool built in). Each connection pool was scoped against the specific database and it was handing out a shared DbConnection that was constantly Open, as we noticed in our Xamarin solution, Opening and Closing connections often was slower than leaving it Open and sharing it.

This connection had the potential to be returned to callers on different thread's. As a result in the interim we've used ThreadStatic to address having a per DbConnection on a per thread basis, and it appears to have helped stop the error from occurring again.

Thank you and your team for your help.