efcore: Sqlite Spatial: dotnet crashes when loading Spatial Extension (segmentation fault)

Just make sure not to file issues with homebrew if you install old versions this way since it isn’t supported.

Oh wait, if you’re using 2.2, you can just do this:

<PackageReference Include="SQLitePCLRaw.provider.sqlite3.netstandard11" Version="1.1.12" />

SQLitePCL.raw.SetProvider(new SQLite3Provider_sqlite3());

Take heart and have a look at https://github.com/ericsink/SQLitePCL.raw/blob/master/src/SQLitePCLRaw.nativelibrary/for_netstandard2.cs

Another workaround is to manually build and install SpatiaLite without PROJ support:

curl https://www.gaia-gis.it/gaia-sins/libspatialite-4.3.0a.tar.gz | tar -xz
cd libspatialite-4.3.0a

if [[ `uname -s` == Darwin* ]]; then
    # Minor patch required on Mac OS
    sed -i "" "s/shrext_cmds='\`test \\.\$module = .yes && echo .so \\|\\| echo \\.dylib\`'/shrext_cmds='.dylib'/g" configure
fi

./configure --disable-proj
make
make install

I was able to confirm my suspicions. This issue stems from PROJ using its own version of SQLite. Here are the relevant parts of the LD_DEBUG log:

633: calling init: /home/bricelam/Test/bin/Debug/netcoreapp3.1/runtimes/linux-x64/native/libe_sqlite3.so 633: 633: calling init: /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 633: 633: calling init: /usr/lib/x86_64-linux-gnu/libproj.so.13 633: 633: calling init: /usr/lib/x86_64-linux-gnu/mod_spatialite.so 633: 633: /usr/lib/x86_64-linux-gnu/mod_spatialite.so: error: symbol lookup error: undefined symbol: sqlite3_extension_init (fatal)

Calling load_extension(‘mod_spatialite’) from e_sqlite3 causes the system sqlite3 library to be loaded as a dependency. Then it tries to call sqlite3_extension_init() in mod_spatialite, but it can’t reconcile the two versions of SQLite and crashes.

If I take e_sqlite3 out of the picture and only use the system sqlite3 version, everything works as expected. (https://github.com/ericsink/SQLitePCL.raw/issues/325 would make this easier)

<ItemGroup>
  <PackageReference Include="Microsoft.Data.Sqlite.Core" Version="3.1.0" />
  <PackageReference Include="SQLitePCLRaw.provider.dynamic_cdecl" Version="2.0.2" />
</ItemGroup>

static void Main()
{
    SQLite3Provider_dynamic_cdecl
        .Setup("sqlite3", new NativeLibraryAdapter("libsqlite3.so.0"));
    SQLitePCL.raw.SetProvider(new SQLite3Provider_dynamic_cdecl());

    var connection = new SqliteConnection("Data Source=:memory:");
    connection.LoadExtension("mod_spatialite");

    var command = connection.CreateCommand();
    command.CommandText = "SELECT spatialite_version()";

    connection.Open();
    var version = command.ExecuteScalar();

    Console.WriteLine("SpatiaLite Version: " + version);
}

class NativeLibraryAdapter : IGetFunctionPointer
{
    readonly IntPtr _library;

    public NativeLibraryAdapter(string name)
        => _library = NativeLibrary.Load(name);

    public IntPtr GetFunctionPointer(string name)
        => NativeLibrary.TryGetExport(_library, name, out var address)
            ? address
            : IntPtr.Zero;
}

I didn’t get a response from SpatiaLite. I’m working on just building it myself without PROJ over in bricelam/mod_spatialite-NuGet

@bricelam Should be able to go to 5.2.0 via

brew uninstall --ignore-dependencies proj
brew uninstall --ignore-dependencies libspatialite
brew install --ignore-dependencies  https://raw.githubusercontent.com/Homebrew/homebrew-core/cc8b37d018e1dd29b9051fff899fa397239c59c4/Formula/proj.rb
brew install https://raw.githubusercontent.com/Homebrew/homebrew-core/835ae521c065524abdf66578e68032fa24bce514/Formula/libspatialite.rb

I tried this and while my own test suite continued to build, I didn’t have much luck with the one in the https://github.com/Homebrew/homebrew-core/issues/43203 example repository. Worth a try on your local machine though as there are many other things that might have caused problems on my old laptop.

Unfortunately, we are out of ideas here. We are going to put this on the backlog, but we are likely to need expert help to make real progress here.

It may (or may not) be related to the fact that our native SQLite library is named libe_sqlite3.dylib not libsqlite3.dylib

I don’t think so. I just need to repro it locally and dig into the failure.