Error on loading Spatialite 537 times

[Benedikt Rothe]

SQLITE/Spatialite seems to leak ressources.

We face this problem in a bigger application, but it can be reproduced with the attached minimal program.

In the 537th run the program terminates when loading the mod_spatialite-DLL with this message:

ERROR ON LOAD EXTENSION

Eine DLL-Initialisierungsroutine ist fehlgeschlagen.

Setting:

* 64 Bit - Compiled with Debug-Mode

* Sqlite-Amalgam 3.35.2

* Prebuild Spatialite: mod_spatialite-5.0.1-win-amd64

As far as I can see, this problem is reproducable.

Does anybody have any advice?

Benedikt

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

#include "sqlite3.h"

#include <stdio.h>

#include <stdlib.h>    

int main() {

   sqlite3* db;

   auto loadSpatialite= [&]() {

      char* errMsg = 0;

      sqlite3_enable_load_extension(db, 1);

      int rc = sqlite3_load_extension(db, "mod_spatialite", 0, &errMsg);

      if (rc != SQLITE_OK && errMsg != 0) {

         printf("ERROR ON LOAD EXTENSION\n%s", errMsg);

         exit(1);

      }

   };

   const char* fName = "bsplinbf.sqlite";

   for (int i = 1; i < 1000; i++) {

      printf("Run: %d\n",i);

      int rc = sqlite3_open(fName, &db);

      loadSpatialite();

      sqlite3_close(db);

   }

}

[mj10777]

On Sunday, 28 March 2021 at 15:14:01 UTC+2 benedik...@gmail.com wrote:

SQLITE/Spatialite seems to leak ressources.

We face this problem in a bigger application, but it can be reproduced with the attached minimal program.

In the 537th run the program terminates when loading the mod_spatialite-DLL with this message:

ERROR ON LOAD EXTENSION

Eine DLL-Initialisierungsroutine ist fehlgeschlagen.

I'm surprised it got so far. 

For multitasking support, information about each connection must be stored. 

Therefore a static amount of connection information is reserved.  (I don't remember the exact amount, possibly still 64). 

Spatiakite will bail out when this amount is exceeded. 

Check the source for 'max connections' . 

[Benedikt Rothe]

I asked this question also in the SQLite-Forum (https://sqlite.org/forum/forumpost/21355ed4b1)

It is problably something in Spatialite and not in SQLite.

I cannot reproduce with older versions of Spatialite: mod_spatialite-4.3.0a-win-amd64

[mj10777]

On Sunday, 28 March 2021 at 22:18:00 UTC+2 benedik...@gmail.com wrote:

I asked this question also in the SQLite-Forum (https://sqlite.org/forum/forumpost/21355ed4b1)

It is problably something in Spatialite and not in SQLite.

Yes, it is within spatialite and will happen during the loading of  of the extension. 

This should also happen in 4.3.0.

[Benedikt Rothe]

The testprogram does *not* exit with an error, if I switch to  SQLite 3.27.2 (but stick with mod_spatialite-5.0.1-win-amd64.)

There seems to be some changes in the way SQLite initializes/closes extension.

[a.fu...@lqt.it]

just a very prelimary and immature report:

1. confirmed: at least on Windows 10 the test loop stops exactly
at iteration #537 as Benedikt reports

2. we can absolutely exclude any fault in libsqlite3; I've
tested the same loop using their own "uuid" extension
and it regularly stops at iteration #1000 without any
trouble.

3. Mark is only partially correct when he suspects that
SpatiaLite can only support a limited number of
connections. It was a workaraound required by older
versions of GEOS that were only imperfectly thread-safe,
buf the problem has been solved since several years.

4. the same test nicely works on Linux; however Valgrind
detects few very negligible memory leaks caused by
missing proper finalization of libxml2 before closing
the DB connection.

I strongly suspect that this could the most probable cause
of troubles under Windows.
I'm still investigating on this, and I'll inform you in
the next days about any further progress.

note: properly debugging such Windows-specific oddities
is a real PITA because Win has nothing like Valgrind
... so, please patiently wait ;-).

bye Sandro

[Benedikt Rothe]

Thank you, Sandro. 

For now I solve this issue for me by falling back to  SQLite 3.27.2, but will test anything new from you.

===

From outside the memory-footprint of the application seems stable.

===

For older Visual-Studio-Versions "Visual Leak Detector" worked nice for me.

Benedikt

[Jürgen E. Fischer]

Hi Sandro,

On Sun, 28. Mar 2021 at 23:07:27 +0200, a.fu...@lqt.it wrote:
> I strongly suspect that this could the most probable cause
> of troubles under Windows.

BTW works fine with OSGeo4W testing. https://github.com/qgis/QGIS/issues/41890
has more info about the used versions, but is otherwise unrelated.


Jürgen

--
Jürgen E. Fischer norBIT GmbH Tel. +49-4931-918175-31
Dipl.-Inf. (FH) Rheinstraße 13 Fax. +49-4931-918175-50
Software Engineer D-26506 Norden https://www.norbit.de

[Alessandro Furieri]

Hi Benedikt, 

here is my final report after a really boring 5 hours debugging session.

the Valgrind memory analyzer detected on Linux a small memory

leak due to incomplete finalization of libxml2; after patching

this issue Valgrind stops reporting any possible memory leak.

unhappily, this is far from being resolutive on Windos 10;

there is only a very marginal improvement, now the loop

reaches iteration #1072 instead of #537, but still continues

to prematurely abort reporting the same error.

yesterday I was wrong when I stated that on Windows there

in nothing similar to Valgrind. This morning I've discovered 

that MrMemory [1] aka DynamoRIO [2] has become a fairly decent 

memory checker for Windows and now supports also 64 bit code.

[1] https://dynamorio.org/drmemory_docs/index.html

[2] https://en.wikipedia.org/wiki/DynamoRIO 

here are the findings emerging from MrMemory analysis

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

1. there isn't even a single byte leaking from SpatiaLite

   itself.

   

2. however there are several MB leaking from the MinGW

   C++ runtime (libstdc++-6.dll)

   the total amount of all leaks after thousands of 

   iterations is not really impressive, and I strongly 

   doubt it can cause serious damages.

   

3. the really bad new is that there are many thousands of

   leaking HANDLEs, and if I correctly remember the hard

   limit usually is 10K handles per process. 

   it seems to  be a very reasonable suspect.

   all leaking HANDLEs seems to be caused by obscure

   internal interactions occurring between libgeos-3-9-1.dll,

   libstdc++-6.dll and libwinpthread-1.dll)

   

I've carefully checked, and SpatiaLite properly takes any

required action so to completely finalize the GEOS library

when terminating a DB connection, so it seems to be a bug

affecting GEOS (or may be libstd++ or libwinpthread).

in all cases it's something far beyond any possible

patch on the SpatiaLite's side.

   

   

possible workarounds

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

A) adopt a different program workflow not requiring to

   dynamically load thousand times the mod_spatialite 

   extension. e.g. by implementing a connection cache

   allowing to recycle already existing connections.

   

B) avoid using LOAD_EXTENSION and adopt if possible 

   the classic approach based on static or dynamic

   linkage so to completely skip the need to invoke

   more and more times dynamic loading at run time.

   

bye Sandro

[Benedikt Rothe]

Sandro - first of all I'd like to thank you for your efforts. I gratefully appreciate your advice on this issue.

Regarding your workaround-suggestions: As a newbie to sqlite I don't know the  "classic approach". Could you give me a hint?

Thanks!

Benedikt

[Dominique Pellé]

Alessandro Furieri <alessandr...@gmail.com> wrote:

> Hi Benedikt,
>
> here is my final report after a really boring 5 hours debugging session.
>
> the Valgrind memory analyzer detected on Linux a small memory
> leak due to incomplete finalization of libxml2; after patching
> this issue Valgrind stops reporting any possible memory leak.
>
> unhappily, this is far from being resolutive on Windos 10;
> there is only a very marginal improvement, now the loop
> reaches iteration #1072 instead of #537, but still continues
> to prematurely abort reporting the same error.
>
> yesterday I was wrong when I stated that on Windows there
> in nothing similar to Valgrind. This morning I've discovered
> that MrMemory [1] aka DynamoRIO [2] has become a fairly decent
> memory checker for Windows and now supports also 64 bit code.
>
> [1] https://dynamorio.org/drmemory_docs/index.html
> [2] https://en.wikipedia.org/wiki/DynamoRIO

Nowadays asan (address sanitizer, which also does leak checking
among other things) should work on Windows. See:
https://devblogs.microsoft.com/cppblog/addresssanitizer-asan-for-windows-with-msvc/
I have not used it personally on Windows though.

Unrelated, the example program that reproduces the leak
does this:

for (int i = 1; i < 1000; i++) {
printf("Run: %d\n",i);
int rc = sqlite3_open(fName, &db);
loadSpatialite();
sqlite3_close(db);
}

I strongly advise to add error checking in
all sqlite functions that return an error code
(printing the error on stderr).
If somehow there is a silent error, it could
explain memory leaks.

Regards
Dominique

[a.fu...@lqt.it]

On Tue, 30 Mar 2021 07:24:05 -0700 (PDT), Benedikt Rothe wrote:
> Regarding your workaround-suggestions: As a newbie to sqlite I don't
> know the "classic approach". Could you give me a hint?
>

Hi Benedikt,

note that this approch only works on C and C++ and is not available
on Python, Java, PHP and alike that can only support the LOAD_EXTENSION
mechanism.

I see from your code samples that you are well familiar with C/C++,
so in your case it could be a feasible solution.

step 1: initializing SpatiaLite
--------------------------------
immediately after establishing a new connection(sqlite3_open) you
must register the spatialite extension. nothing complex, just
two lines of code.

--------------------
#include <sqlite3.h>
#include <spatialite/gaiageo.h>
#include <spatialite.h>

const char *db_path = "something.sqlite";
int ret;
sqlite3 *handle;
void *cache;

ret = sqlite3_open_v2 (path, &handle, SQLITE_OPEN_READONLY, NULL);
if (ret != SQLITE_OK)
{
... some appropriate error handling ...
}
cache = spatialite_alloc_connection ();
spatialite_init_ex (handle, cache, 0);
--------------------

after this your connection will be able to support
the extened functionalities provided by SpatiaLite.
void *cache simply is an opaque block of memory
internally required by each SpatiaLite enabled
connection.


step 2: finalizing SpatiaLite
--------------------------------
when closing the DB connection you must free the
SpatiaLite's internal cache associated to that
connection.

------------------------
sqlite3_close (handle);
spatialite_cleanup_ex (cache);
------------------------


step 3: building the executable binary
----------------------------------------
you must directly link both libsqlite3 and
libspatialite to your own binary code.
details vary depending on the compiler/linker
you are using. on Linux or MinGW it usually
is something like:

gcc myprog.c -o myprog.exe \
-lspatialite -lsqlite3

it could be eventually required to link all
the other depending libraries such ad GEOS,
PROJ, libxml2 and so on (it's strongly compiler
specific).


usefull resources
---------------------
download the source tarballs and study what
spatialite_gui or the CLI tools do when opening
or closing a DB connection.
even more simple, on the "examples" folder
of libspatialite you'll find several small
C sources (demoX.c) exemplifying all that
is supposed to be needed.

bye Sandro

[Benedikt Rothe]

OK - I'll give it a try.

Actually I don't want to link statically. 

My approach will be:

* One time for the lifespan of the program step 0: 

      Load the DLL with LoadLibrary and find the functions spatialite_alloc_connection , spatialite_init_ex, spatialite_cleanup_ex (GetProcAddress)

* Each time a connection is made:

     "step 1" and "step 2" as you described

Currently LoadLibrary is called each time a connection is established and the error occures when LoadLibrary is called.

Your (modified) approach load's the dll just once. Could work ...

Thanks

Benedikt

[a.fu...@lqt.it]

On Tue, 30 Mar 2021 08:59:48 -0700 (PDT), Benedikt Rothe wrote:
> OK - I'll give it a try.
>
> Actually I don't want to link statically.
>
> My approach will be:
> * One time for the lifespan of the program step 0:
> Load the DLL with LoadLibrary and find the functions
> spatialite_alloc_connection , spatialite_init_ex,
> spatialite_cleanup_ex (GetProcAddress)
>

Benedikt,

there is no real reason for this. you can avoid static
linkage in a much simpler way.

don't confuse dynamic linkage and dynamic loading at
run time.
- in the first case the linker will arrange your
executable in such a way that any depending DLL
will be automatically loaded when your program
starts (early dynamic binding)

- in the second case your program itself will directly
take care of loading any requireded DLL only when it's
really required (late dynamic binding).
this is much more complex scenario, and is usually
adopted only when deploying a plugin architecture.

> Currently LoadLibrary is called each time a connection is established
> and the error occures when LoadLibrary is called.
> Your (modified) approach load's the dll just once. Could work ...
>

It works for sure :-D

the modified source below sucessfully ran for
1 Mega iterations without any trouble.
a cleat confirmation that now anything is
absolutely stable and safe.

bye Sandro

---------------------
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <spatialite.h>

int main()
{
void *cache;
sqlite3* db;
const char* fName = ":memory:";
for (int i = 1; i < 1024 * 1024; i++)
{

int rc = sqlite3_open(fName, &db);

if (rc != SQLITE_OK)
{
printf("ERROR ON OPEN\n%s", fName);
exit(1);
}
/* initializing SpatiaLite */
cache = spatialite_alloc_connection ();
spatialite_init_ex (db, cache, 0);

printf("Run: %d\n",i);

sqlite3_close(db);
/* finalizing SpatiaLite */
spatialite_cleanup_ex (cache);
}
return 0;
}
---------------------

[Alessandro Furieri]

Hi Jürgen,

the SQL function ST_Area() has two different execution paths:

1. if it receives just a single argument as in ST_Area(geom)

   then the calculation is delegated to GEOS

2. if it receives two arguments as in ST_Area(geom, use_ellipsoid)

   then the calculation is delegated to rt_topo.

   

going in further depth: when rt_topo is called so to get a metric

measure calculated by applying geodesic formulas it requires

to specify the length of both ellipsoid's axes.

note that this task will require to query the underlying

database, most specifically the spatial_ref_sys table;

if any error is encountered in this phase a NULL will

be returned.

let's see in full detail all the steps one by one.

step #1

-------

SELECT proj4text FROM spatial_ref_sys WHERE srid = ?;

this query is intended to retrieve the PROJ geodesic

string for the given SRID (the one internally declared 

by the passed Geometry); if the query fails for any

reason the result of ST_Area() will be NULL.

step #2

-------

on success the PROJ geodesic string will be parsed

and inspected: if a term "+proj=longlat" is actually

found it will be assumed a CRS of the expected Geographic 

type, otherwise NULL will be returned.

step #3

-------

now a "+ellps=????" term will be searched; if found a 

list of well-known ellipsoids will be searched in order 

to retrieve both axes.

step #4

-------

if the previous fails then a "+datum=???" term will

be searched, and once again a list of well-known

ellipsoids will be searched.

step #5

-------

if the previous fails a last desperate effort will

be made so to search for "+a=???" and "+b=???"

terms. if this continues to fail a NULL will be

returned.

-----------------

conclusion: carefully check if the unexpected

ST_Area() NULLs could be eventually caused by

some issue affecting a badly initialized

spatial_ref_sys table.

bye Sandro

[Benedikt Rothe]

Sandro,

> don't confuse dynamic linkage and dynamic loading at run time  

OK - I did and I should not! :-)

But sorry - still does not work for me ...

* So far I took spatialite from http://www.gaia-gis.it/gaia-sins/windows-bin-amd64.

* I created a lib-stub around  mod_spaitalite.dll, compiled and linked your sample-program .

* This DLL was obviously compiled with

    #define LOADABLE_EXTENSION

* Thefore spatialite_cleanup_ex and spatialite_init_ex are missing ind this DLL

* Therefore it cannot be used in the described way

Question: Is there a precompiled binary for spatialite without "#define LOADABLE_EXTENSION"?

I'd like to avoid going through  http://www.gaia-gis.it/gaia-sins/msvc_how_to.html. (Some years ago I managed to compile umn-mapserver on windows - but it was ... inconvenient)

Benedikt

[a.fu...@lqt.it]

On Wed, 31 Mar 2021 09:43:02 -0700 (PDT), Benedikt Rothe wrote:
> But sorry - still does not work for me ...
>
> * So far I took spatialite from
> http://www.gaia-gis.it/gaia-sins/windows-bin-amd64.
>
> * I created a lib-stub around mod_spaitalite.dll, compiled and linked
> your sample-program .
>
> * This DLL was obviously compiled with
>

> _#define LOADABLE_EXTENSION_
>

Hi Benedikt,

note: mod_spatialite.dll and libspatialite.dll are almost the same
thing but they differ for a critical aspect:

- mod_spalitalite.dll can only be initialized by LOAD_EXTENSION
and can never be linked to a program.
- libspatialite.dll is intended to be linked and can never
be initialized by LOAD_EXTENSION.

if you are looking for a more elaborate explication, please read:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite

>
> Question: Is there a precompiled binary for spatialite without
> "#define LOADABLE_EXTENSION"?
>
> I'd like to avoid going through
> http://www.gaia-gis.it/gaia-sins/msvc_how_to.html. (Some years ago I
> managed to compile umn-mapserver on windows - but it was ...
> inconvenient)
>

OSGeo4W is a nice distribution of Windows binaries supporting many
gis-related open source libraries (including libspatialite and all
its dependencies).

https://www.osgeo.org/projects/osgeo4w/

if you are searching for the most recent versions you
probably should look into the "experimental" branch.

bye Sandro

[Benedikt Rothe]

Hi Sandro,

* Difference between  mod_spalitalite.dll and  libspatialite.dll  became clear

* Using binaries from osgeo4w worked for me - the example with "spatialite_alloc_connection etc." works

* Nice: This approach reduces connections-times significantly

* Unfortunately:  osgeo4w  comes with Spatialite 4.3 - I made the switch to 5.x because of this fix: https://www.gaia-gis.it/fossil/libspatialite/tktview?name=75f611d860 

Again: Thank's for your support!

I don't want to appear to eager: I do like your distribution from http://www.gaia-gis.it/gaia-sins/ because it's quite lean. Maybe you could think about adding libspatialite.dll there on the long run?

Benedikt

[i-s-o]

As Sandro mentioned in his last message, the experimental osgeo4w branch (a.k.a. testing/reboot/next generation) has the most recent libspatialite version. You need to use a different osgeo4w setup file to get it. There is a link to testing branch's network installer and a short description on QGIS download page, which cautions against mixing it with the current stable branch (look for OSGeo4W testing on the download page):
https://qgis.org/en/site/forusers/download.html