Memory leak when using libspatialite 5.1.0 with sqlite3 3.51.1

[Pim]

Hi list members,

I am running into a memory leak issue when using libspatialite 5.10 with sqlite3 3.51.1, specifically when using the SpatialIndex virtual table. The memory leak does not occur with sqlite3 3.50.4. These are installed as archlinux packages.

See this link (https://pastebin.com/MthGvZat) for a sqlite dot-command file to reproduce the memory leak. Basically it spatially joins 1000 points to 100 geometries 100 times using the SpatialIndex virtual table.

Results:

• mod_spatialite (5.1.0) linked to SQLite (3.51.1) has memory usage ±250.168KB, increasing with each repeated query
• mod_spatialite (5.1.0) linked to SQLite (3.50.4) has memory usage ±23.644KB, stable with each repeated query

Hoping to see if someone can reproduce this, or if I am doing something wrong perhaps. Are there workarounds other than downgrading sqlite?

Thanks,
Pim

[sandro furieri]

Il 2026-01-09 15:36 Pim ha scritto:
> I am running into a memory leak issue when using libspatialite 5.10
> with sqlite3 3.51.1, specifically when using the SpatialIndex virtual
> table. The memory leak does not occur with sqlite3 3.50.4.

> ..............

> Hoping to see if someone can reproduce this, or if I am doing
> something wrong perhaps.
>

Hello Pim,

Yes, I can confirm that the VirtualSpatialIndex module causes a memory
leak and that this only occurs using the most recent versions of SQLite
(3.51.x) while with any previous versions (<= 3.50.x) the issue does
not arise.

Quick explanation:
------------------
There was a bug in the VirtualSpatialIndex code.
For reasons I'm unable to explain, this flaw had no visible effect with
all previous versions of SQLite, while with more recent versions it
triggers a massive chain of memory leaks.
Evidently, some detail in the VirtualTable implementation of SQLite
has recently changed.

Long explanation:
-----------------
One of the fundamental rules of the SQLite C Interface is to terminate
every Prepared Statement that was previously created.
In other words, for every call to sqlite3_prepareXX() there must always
be a corresponding call to sqlite3_finalize()

When this does not happen, the Prepared Statement object remains
attached
to the DB connection, and this prevents it from closing properly.
And this is exactly what triggers the chain reaction that causes a
massive
memory leak, because the failed connection closure prevents SQLite from
freeing all of its working memory.
The memory leak directly related to the missing Prepared Statement
finalization may be very small; the real problem is that all the pages
in the SQLite's internale cache will remain allocated, which becomes a
major problem.

Just to complicate matters, your test script to reproduce the issue made
very extensive use of WITH RECURSION, which ultimately turned out to be
a very powerful mechanism for infinitely multiplying memory leaks.
I finally managed to identify the real cause of the problem thanks
to a very simple test on a Spatial Index of few lines; simplicity
almost always pays off.

Valgrind's help was absolutely invaluable, and in this case too
it proved to be a very powerful tool for identifying the source
of memory leaks.
Interestingly enough, even Valgrind couldn't pinpoint the exact
spot where all the hell was breaking loose, but it did help me
figure out that all the reported memory leaks were happening inside
SQLite and that most of them involved cache pages and other internal
structures.
Then I noticed that sqlite_close() was failing due to an unfinalized
Prepared Statement, and it was easy enough to figure out which one
it was and where it was created.
And sure enough, it all happened inside VirtualSpatialIndex; the
classic pebble that, when falling, triggers a catastrophic avalanche.

> Are there workarounds other than downgrading sqlite?
>

If you are able to build SpatiaLite from source (and on Linux it's
very easy) you simply need to apply this two-line patch:

SOURCE: ~/src/spatialite/virtualspatialindex.c
FUNCTION: vspidx_filter()
... near line 710 ...
==================================================

tic *= 2.0;
sqlite3_bind_double (stmt, 1, geom->MaxX + tic);
sqlite3_bind_double (stmt, 2, geom->MinX - tic);
sqlite3_bind_double (stmt, 3, geom->MaxY + tic);
sqlite3_bind_double (stmt, 4, geom->MinY - tic);
+ if (cursor->stmt != NULL)
+ sqlite3_finalize (cursor->stmt);
cursor->stmt = stmt;
cursor->eof = 0;

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

Apply the patch to the code, compile, install and you're done.

best regards,
Sandro

[Pim]

Hi Sandro,

Thank you for figuring out what is going wrong. My excuses for not providing a smaller test case, it was closer to my original dataset size than required. 

I will see what makes most sense for us to either downgrade the sqlite or patch libspatialite. Should I create a ticket for the issue or is it already being incorporated into the next libspatialite version?

Cheers,

Pim

[sandro furieri]

Il 2026-01-12 10:18 Pim ha scritto:
> Should I create a ticket for the issue or is it already
> being incorporated into the next libspatialite version?
>

The fix is ​​already part of the next version currentlu
under development.

Filing a ticket isn't strictly necessary, but it can
still be helpful.

bye Sandro

[Bas Couwenberg]

Any chance of a new release with these changes?

It's been a few years since the 5.1.0 release.

[sandro furieri]

Il 2026-01-13 07:06 Bas Couwenberg ha scritto:
> Any chance of a new release with these changes?
>
> It's been a few years since the 5.1.0 release.
>

Hi Bas,

The answer to this question will be long and
detailed, so I'd prefer to start a new thread
with a more appropriate title.

bye Sandro