SQLite no longer seems to work on xamarin android

Question

We have a Xamarin.Forms project that needed to use the sqlite local db to store date. EF Core's sqlite library was used to set this up and by different developers from different PCs (vs 2019). Initially, it was used with the Database.EnsureCreated() function and later with ef core's migrations. All went smooth for more than a month.

Last week all of a sudden the android app wouldn't start on any one's PC due to some error with sqlite. It showed the following error:

Fatal signal 11 (SIGSEGV), code 1 (SEGV_MAPERR)

I spent a while trying all kinds of fixes and rollbacks thinking it was an issue with the code. This included the following:

1. Deleted obj and bin folders, cleaned and rebuilt for all below steps.
2. Downgraded the version of ef to 2.2.3 (the version we started with)
3. Rolled back to git commits up to a week back
4. Upgraded the versions of dependencies of ef core
5. Removed the past few migrations
6. Downgraded xamarin forms to 3.6.x

After trying the above and several other fixes, finally upgrading the versions of java and android SDK worked last Friday (on all PCs). Once this fix worked everything went smooth that day. On Tuesday once again the issue was back (no library updates or code changes). A deeper look at EF Cores logging shows that it crashes the moment it attempts to connect to a db.

The issue can be replicated on the android devices but not the emulators. I am not sure if there is some new permission in android we need to request for.

I finally created a new xamarin forms project with sqlite setup. I used the pcl library and ef core. I still found the same error.

Here is the git hub that replicates the issue https://github.com/neville-nazerane/xamarin-site

Update

Just something i noticed. eariler my database file was named "main.db". Now no matter what i change this file name to or no matter what variables i change. it always shows database name as "main" in logs. Not sure if changing the db name would fix the issue. However, never found a way to change this db name. I tried different connection strings, it just said "database" and "db" were unknown keys

Update

Steps to replicate:

using (var db = new AppDbContext())
{
    db.Add(new Person {
        Age = 55, 
        Name = "Neville"
    });
    db.SaveChanges();
    Person[] alldata = db.People.ToArray();
}

The definitions of Person and AppDbContext are quite obvious. So, with the spirit of not making the question too long, I am not posting it here. However, if required I can post them too.

Answer 1

This is a bug with the Xamarin.Forms and Mono.

It was detected since a couple of months ago, it was fixed but then there was some regression (with VS 2019 v16.1).

Even with the latest release (v16.1.2) the bug still happens, so we need to wait for a fix.

Sources:

https://github.com/mono/mono/issues/14170

https://github.com/xamarin/xamarin-android/issues/3112

https://github.com/xamarin/xamarin-android/issues/2920

Answer 2

Alright mate, I don't understand what issue you are facing. It might be an issue with your machine, I'd suggest using another computer/laptop.

I took the exact code that you shared on the Github. I was able to build it on my Mac computer in VS 2019 and installed the application in debug mode on my phone. I was able to add a date successfully, as you can see in the picture, and I placed an Exception Catchpoint and faced no exceptions.

I then proceeded to add another entry with the same details and it errored out with the message that you can see here

I would also suggest using Xamarin Profiler or any other Android logger to see the Stack Trace that you aren't able to see in your application output. It will give you details of the error, that you can share here for us to understand better.

Answer 3

Due to slight differences of the particular file systems on the native side, I would suggest creating an interface to handle the actual database file handling on the native application level.

So here is how I implemented SQLite using the nuget package SQLite.Net-PCL:

In the shared project, create a new interface, for instance FileHandler.cs

public interface IFileHandler
{
    SQLite.SQLiteConnection GetDbConnection();
}

You may want to extend that with more methods to save or retrieve various files, but for now we will just have the GetDbConnection Method to retrieve a working SQLite Connection.

Now in your Android implementation, we add the native implementation to that interface:

Create a new class called FileHandler.cs in your android project:

[assembly: Dependency(typeof(FileHandler))]
namespace YourProjectName.Droid
{
    public class FileHandler : IFileHandler
    {
        public SQLite.SQLiteConnection GetDbConnection()
        {
            string sqliteFilename = "YourDbName.db3";
            string path = Path.Combine(GetPersonalPath(), sqliteFilename);
            SQLiteConnectionString connStr = new SQLiteConnectionString(path, true);
            SQLiteConnectionWithLock connection = new SQLiteConnectionWithLock(connStr, SQLiteOpenFlags.Create | SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.NoMutex);
            return connection;
        }

        private string GetPersonalPath()
        {
            return Environment.GetFolderPath(Environment.SpecialFolder.Personal);
        }
    }
}

Now back again in your shared code you can access that connection with the following code:

using (SQLiteConnection connection = DependencyService.Get<IFileHandler>().GetDbConnection())
{
    // Do whatever you want to do with the database connection
}