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

[garfbradaz]

I'm trying to test out the Spatial Types functionality in EF Core for SQLite In Memory provider.

Now when I run an Xunit test the dotnet executable crashes and I will receive a crash dump report (No application exceptions are thrown).

It seems to crash when trying to access the mod_spatialite extension. I followed the following advice from this issue #14402, as my original problem was with receiving a SQLite Exception with a no such function: InitSpatialMetaData error to begin with.

Steps to reproduce for Mac

1. git clone https://github.com/StratumFive/EFCore.SQLite.SpatialCrash.
2. brew install libspatialit
3. Change directory to the root solution.
4. dotnet test xunit
5. Receive error message:

The active test run was aborted. Reason: Test host process crashed

There is even a console application there to prove it isn't just a Xunit issue.

Further technical details

EF Core version: 2.2.6
Database Provider: Microsoft.EntityFrameworkCore.Sqlite / Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite
Operating system: Mac Mojave 10.14.5
IDE: Visual Studio Code
SQLite version: 3.24.0 2018-06-04 14:10:15
dotnet info:

.NET Core SDK (reflecting any global.json):
Version: 2.2.300
Commit: 73efd5bd87

Runtime Environment:
OS Name: Mac OS X
OS Version: 10.14
OS Platform: Darwin
RID: osx.10.14-x64
Base Path: /usr/local/share/dotnet/sdk/2.2.300/

Host (useful for support):
Version: 2.2.5
Commit: 0a3c9209c0

.NET Core SDKs installed:
2.2.105 [/usr/local/share/dotnet/sdk]
2.2.106 [/usr/local/share/dotnet/sdk]
2.2.107 [/usr/local/share/dotnet/sdk]
2.2.300 [/usr/local/share/dotnet/sdk]

.NET Core runtimes installed:
Microsoft.AspNetCore.All 2.2.3 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.All]
Microsoft.AspNetCore.All 2.2.4 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.All]
Microsoft.AspNetCore.All 2.2.5 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.All]
Microsoft.AspNetCore.App 2.2.3 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 2.2.4 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.AspNetCore.App 2.2.5 [/usr/local/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 2.2.3 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
Microsoft.NETCore.App 2.2.4 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]
Microsoft.NETCore.App 2.2.5 [/usr/local/share/dotnet/shared/Microsoft.NETCore.App]

[ajcvickers]

/cc @bricelam

[garfbradaz]

Updated above with SQLite version.

[garfbradaz]

Update

Added a Dockerfile to the repository here for my repo. It also crashes as well (Debian).

Steps to reproduce for Dockerfile (assumes docker is setup on machine)

1. git clone https://github.com/StratumFive/EFCore.SQLite.SpatialCrash.
2. Change directory to the root solution.
3. Build the image docker build . -t xunit-crash
4. Run the container and execute into it docker run --rm -it -p 5001:5001/tcp xunit-crash:latest
5. Run tests dotnet test xunit/EFCore.Spatial.Crash.csproj -c Release -r /TestResults --logger "trx;LogFileName=TestResults.trx"
6. Receive error:

The active test run was aborted. Reason:

[garfbradaz]

Also, here is my Mac spatialite Mac info:

spatialite
SpatiaLite version ..: 4.3.0a Supported Extensions:
- 'VirtualShape' [direct Shapefile access]
- 'VirtualDbf' [direct DBF access]
- 'VirtualXL' [direct XLS access]
- 'VirtualText' [direct CSV/TXT access]
- 'VirtualNetwork' [Dijkstra shortest path]
- 'RTree' [Spatial Index - RTree]
- 'MbrCache' [Spatial Index - MBR cache]
- 'VirtualSpatialIndex' [RTree metahandler]
- 'VirtualElementary' [ElemGeoms metahandler]
- 'VirtualXPath' [XML Path Language - XPath]
- 'VirtualFDO' [FDO-OGR interoperability]
- 'VirtualGPKG' [OGC GeoPackage interoperability]
- 'VirtualBBox' [BoundingBox tables]
- 'SpatiaLite' [Spatial SQL - OGC]
PROJ.4 version ......: Rel. 6.1.1, July 1st, 2019
GEOS version ........: 3.7.2-CAPI-1.11.2 b55d2125
TARGET CPU ..........: x86_64-apple-darwin18.2.0
the SPATIAL_REF_SYS table already contains some row(s)
SQLite version ......: 3.28.0
Enter ".help" for instructions
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

[garfbradaz]

@bricelam do you need anything else from me?

[bricelam]

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

[garfbradaz]

Can I help in anyway to dig into the failure @bricelam ? I know you guys are busy and this issue is blocking me.

[bricelam]

Does it repro with something as trivial as this:

using static SQLitePCL.raw;

class Program
{
    static void Main()
    {
        SQLitePCL.Batteries_V2.Init();

        sqlite3_open(":memory:", out var db);
        sqlite3_enable_load_extension(db, 1);
        sqlite3_exec(db, "SELECT load_extension('mod_spatialite')");
    }
}

[bricelam]

Looks like our CI has been hitting this too. 😁

[bricelam]

It appears to be in issue with the latest revision of the Homebrew package.

Workaround

Use the previous version:

brew install https://raw.githubusercontent.com/Homebrew/homebrew-core/835ae521c065524abdf66578e68032fa24bce514/Formula/libspatialite.rb

[bricelam]

@fxcoudert What's the proper channel for reporting this?

[bricelam]

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

[garfbradaz]

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

Amazing @bricelam - looking now :)

[garfbradaz]

Feedback

@bricelam - sorry for the delay.

I reverted the version back using the workaround, but this has caused a new set of errors explained now.

New Errors

• This runs fine without error.

using static SQLitePCL.raw;

class Program
{
    static void Main()
    {
        SQLitePCL.Batteries_V2.Init();

        sqlite3_open(":memory:", out var db);
        sqlite3_enable_load_extension(db, 1);
        sqlite3_exec(db, "SELECT load_extension('mod_spatialite')");
    }
}

• The following Xunit code found here now throws the following exception:

EFCore.Spatial.Crash.TestCrash.Test_Crash_for_EF_Team:
Outcome: Failed
Error Message:
Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'dlopen(mod_spatialite.dylib.dylib, 10): image not found'.
Stack Trace:
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Infrastructure.SpatialiteLoader.Load(DbConnection connection)
at EFCore.Spatial.Crash.TestCrash.Test_Crash_for_EF_Team() in /Users/garethb/code/EFCore.Spatial.Crash/xunit/TestCrash.cs:line 18
--- End of stack trace from previous location where exception was thrown ---

• The following console code found here now produces the following stack dump here.

Interestingly now when I run spatialite on the commandline I get:

dyld: Library not loaded: /usr/local/opt/proj/lib/libproj.13.dylib
Referenced from: /usr/local/opt/libspatialite/lib/libspatialite.7.dylib
Reason: image not found
[1] 21039 abort spatialite

Could it be some $PATH is missing or is expected? Here is my echo'd $PATH:

/usr/local/opt/sqlite/bin
/Users/garethb/.dotnet/tools
/usr/local/bin
/usr/bin
/bin
/usr/sbin
/sbin
/usr/local/share/dotnet
~/.dotnet/tools
/Library/Frameworks/Mono.framework/Versions/Current/Commands
/Applications/Xamarin Workbooks.app/Contents/SharedSupport/path-bin

I even updated my .csproj to reflect your test suite as your pipeline was successful, but the same error:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp3.0</TargetFramework>

    <IsPackable>false</IsPackable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.Sqlite" Version="3.0.0-preview7.19362.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.0.0-preview7.19362.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="3.0.0-preview7.19362.6" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite" Version="3.0.0-preview7.19362.6" />
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.3.0-preview-20190715-02" />
    <PackageReference Include="xunit" Version="2.4.0" />
    <PackageReference Include="xunit.runner.visualstudio" Version="2.4.0" />
    <PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="2.0.0-pre20190628101813" />
    <PackageReference Include="NETStandard.Library" Version="1.6.1" />
  </ItemGroup>

</Project>

[ajcvickers]

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.

[pond]

This is a critical blocker for us.

As a community effort, even with everyone trying their best, crash bugs thrown into the "too hard" bucket mean it can't be taken seriously as something a company's income depends upon. Hard-crash issues need proper engineering resources from Microsoft dedicating to them so that people who are actually able to diagnose and fix the issue are promptly assigned. Perhaps the bottom line is that although EntityFrameworkCore is cross-platform, a crash on a non-Windows platform isn't where the money is and isn't going to get much traction.

If there's anything that can be done to escalate this I'm sure others would appreciate it.

[ajcvickers]

@pond What version of EF Core are you using?

[bricelam]

@pond the crash is caused by changes made to the libspatialite Homebrew package. I've filed Homebrew/homebrew-core#43203. See my comment above for a workaround.

[pond]

@bricelam The work-around doesn't help I'm afraid - it was the first thing I tried. It doesn't matter what version of that package I install; the crash happens anyway. It looks likely to be a combination of specific versions of several packages. I have isolated a single machine which works, and will try to replicate that particular configuration on another machine as soon as I have time.

@ajcvickers Unsure how to get a version for that particular component; .Net SDK 2.2.4 ("latest download" at the time of writing) and again "most recent" Nuget packages related to EFC & SQLite... Looks like there are references to Microsoft.EntityFrameworkCore.Sqlite 2.2.6 and Microsoft.EntityFrameworkCore.Sqlite.NetTopologySuite 2.2.6 in the .csproj file.

[pond]

By carefully unpicking all newer versions and installing specific versions of the runtime and related libraries, I have my own test suite running on SQLite that used to crash. Notably, the version of libspatialite is not in my case responsible. Using the most recent Homebrew build works fine. It's other dependencies that seem to be the issue - at least in my case.

• However - even with rolling libspatialite back to 4.3.0a_6 as per the above workaround, and therefore having to roll proj back to 5.2.0 to avoid the related dylib load error, via brew install --ignore-dependencies https://raw.githubusercontent.com/Homebrew/homebrew-core/cc8b37d018e1dd29b9051fff899fa397239c59c4/Formula/proj.rb - I still can't get that tiny test case to pass. Our large company test suite runs now, strangely, but the small test case still crashes.

It's quite an ugly process. I imagine that most of these carefully chosen versions are not necessary, so I'll edit and update this answer as I narrow things down, until I run out of time for it anyway.

First:

• Uninstall any DotNetCore versions you don't want: https://docs.microsoft.com/en-us/dotnet/core/versions/remove-runtime-sdk-versions?tabs=macos
• Get DotNetCore SDK version 2.2.300: https://dotnet.microsoft.com/download/dotnet-core/2.2
• Important! Clean any relevant builds; dotnet clean or equivalent

Next, remove a few Homebrew packages:


brew uninstall postgis
brew uninstall gdal
brew uninstall libspatialite 
brew uninstall libgeotiff
brew uninstall proj
brew uninstall sqlite

...resolving dependencies (or ignoring them via --ignore-dependencies) with further uninstall commands if need be, taking note of them all for reinstallation later. Then, we cherry-pick versions that happen to work on my machine.

SQLite 3.28.0

brew install https://raw.githubusercontent.com/Homebrew/homebrew-core/4fa421cfc34548eb48bc236460584c9f4fa16b22/Formula/sqlite.rb


Proj 6.1.0

brew install https://raw.githubusercontent.com/Homebrew/homebrew-core/ca19c245aae7d6fa8c0d9d7314a4964a3a3b18c8/Formula/proj.rb

Libgeotiff seems unrelated, so we just install the most recent version. For this and further calls, Homebrew doesn't seem to recognise the from-SHA installations of dependencies so halts; you have to install with --ignore-dependencies. Rather unhelpfully, Homebrew will then warn you that this is an unsupported flag you're not supposed to use, but it's not as if there's any choice.

brew install --ignore-dependencies libgeotiff

Gdal 2.4.1_1

brew install --ignore-dependencies https://raw.githubusercontent.com/Homebrew/homebrew-core/37ce68573be9fe921ef65d421da4657e2f6d0270/Formula/gdal.rb

Libspatialite 4.3.0a_7 (the most recent version at the time of writing, but locking to SHA for safety here)

brew install --ignore-dependencies https://raw.githubusercontent.com/Homebrew/homebrew-core/1bbba12ca10e05e7194a6233d4bf5bf56677939f/Formula/libspatialite.rb


Postgis 2.5.2_2 (the most recent version at the time of writing, but locking to SHA for safety here)

brew install --ignore-dependencies https://raw.githubusercontent.com/Homebrew/homebrew-core/0a2e72798e72e3accead7e793d6cccf4ff89dd70/Formula/postgis.rb

[bricelam]

I too am starting to think this is a problem with PROJ. It looks like it links directly to libspatialite3-0 which breaks SQLite extensions.

[bricelam]

(From @michaeldtaylor on #18068)

If I take the SpatiaLiteLoader out into my own app I can get loading the module by explicitly setting the environment variable:

DYLD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu/

and doing:

SELECT load_extension('mod_spatialite.so')

[garfbradaz]

Any news on when this will be fixed for Macs?

[bricelam]

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]

Today I learned that our SpatialiteLoader won't actually work on Linux. Apparently modifying LD_LIBRARY_PATH after the first call to dlopen() has no affect. This drastically minimizes the usefulness of distributing Linux binaries of SpatiaLite in our NuGet package. This may also be true for macOS. I'm investigating other possible workarounds to the PROJ issue.

[bricelam]

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. (ericsink/SQLitePCL.raw#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;
}

[bricelam]

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

[ajcvickers]

After discussing this with @bricelam and the team, we have decided to close this as external. Brice has done extensive research into making the experience better, but ultimately this comes down to better documentation of workarounds.

[RamNow]

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;
}

@bricelam Thanks for all your research!
Do you have any suggestions on what to do when NativeLibrary isn't available because you are on a .Net Core 2.2 environment?

[bricelam]

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

[bricelam]

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());