Problems loading mod_spatialite in PHP on CentOS

[Peter Hopfgartner]

My setup is CentOS 6 with the default SQLite 3.6.20.

I've compiled SptiaLite 4.2, but I'm not able to load SpatiaLite.

sqlite> SELECT load_extension('mod_spatialite');
Error: SQL logic error or missing database
sqlite> SELECT load_extension('mod_spatialite.so');
Error: SQL logic error or missing database
sqlite> SELECT load_extension('libspatialite.so');
Error: SQL logic error or missing database

Is there something obvious that I am missing?

Thanks,

Peter

--
Peter Hopfgartner
R3 GIS Srl - GmbH
web : http://www.r3-gis.com

[Peter Hopfgartner]

Sorry, PHP does not matter in this test, since I've reduced it to a
command line test, starting the sqlite3 sesion with:

sqlite3 test2.db

Some more infos:

[root@castor]# ls -l /usr/lib64/*spatial*
lrwxrwxrwx. 1 root root 22 Oct 23 22:22
/usr/lib64/libspatialite.so.7 -> libspatialite.so.7.0.0
-rwxr-xr-x. 1 root root 4714336 Oct 9 15:58
/usr/lib64/libspatialite.so.7.0.0
lrwxrwxrwx. 1 root root 23 Oct 23 22:22
/usr/lib64/mod_spatialite.so -> mod_spatialite.so.7.0.0
lrwxrwxrwx. 1 root root 23 Oct 23 22:22
/usr/lib64/mod_spatialite.so.7 -> mod_spatialite.so.7.0.0
-rwxr-xr-x. 1 root root 4778296 Oct 9 15:58
/usr/lib64/mod_spatialite.so.7.0.0

Regards,

Peter

[Brad Hards]

On Mon, 27 Oct 2014 09:53:16 AM Peter Hopfgartner wrote:
> Sorry, PHP does not matter in this test, since I've reduced it to a
> command line test, starting the sqlite3 sesion with:
>
> sqlite3 test2.db

What does "PRAGMA compile_options" at the command line show.

I'm guessing extension loading may be turned off in your build, but this is a
way to know for sure.

Brad

[Peter Hopfgartner]

Hmmm,

on CentOS, this does not return anything:

sqlite> PRAGMA compile_options;
sqlite>

The same on Ubuntu is more expressive:

sqlite> PRAGMA compile_options;
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_RTREE
ENABLE_UNLOCK_NOTIFY
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
TEMP_STORE=1
THREADSAFE=1
sqlite> PRAGMA compile_options;
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_RTREE
ENABLE_UNLOCK_NOTIFY
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
TEMP_STORE=1
THREADSAFE=1

Peter

--
Peter Hopfgartner
web : http://www.r3-gis.com

[Brad Hards]

On Mon, 27 Oct 2014 10:26:15 AM Peter Hopfgartner wrote:
> On 10/27/2014 10:10 AM, Brad Hards wrote:
> > On Mon, 27 Oct 2014 09:53:16 AM Peter Hopfgartner wrote:
> >> Sorry, PHP does not matter in this test, since I've reduced it to a
> >> command line test, starting the sqlite3 sesion with:
> >>
> >> sqlite3 test2.db
> >
> > What does "PRAGMA compile_options" at the command line show.
> >
> > I'm guessing extension loading may be turned off in your build, but this
> > is a way to know for sure.
> >
> > Brad
>
> Hmmm,
>
> on CentOS, this does not return anything:
>
> sqlite> PRAGMA compile_options;
> sqlite>

OK, maybe came much later. I looked at the source RPM, and it looks like it
should be built with load_extension support, but I can't think of anything
else likely.

Sorry.

Brad

[Jukka Rahkonen]

Peter Hopfgartner wrote 2014-10-27 10:50:
> My setup is CentOS 6 with the default SQLite 3.6.20.
>
> I've compiled SptiaLite 4.2, but I'm not able to load SpatiaLite.
>
> sqlite> SELECT load_extension('mod_spatialite');
> Error: SQL logic error or missing database
> sqlite> SELECT load_extension('mod_spatialite.so');
> Error: SQL logic error or missing database
> sqlite> SELECT load_extension('libspatialite.so');
> Error: SQL logic error or missing database
>
> Is there something obvious that I am missing?

Hi,

If the Xerial jdbc driver names refer to SQLite versions as I suppose
https://bitbucket.org/xerial/sqlite-jdbc/downloads
the oldest version that I have managed to use for loading mod_spatialite
is 3.7.15-SNAPSHOT2. SQLite site http://sqlite.org/ tells that "Version
3.8.7 of SQLite is recommended for all new development. Upgrading from
all other versions of SQLite
is recommended." Probably it is waste of time to play with 3.6.20.

-Jukka Rahkonen-

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

On Mon, 27 Oct 2014 09:50:00 +0100, Peter Hopfgartner wrote:
> My setup is CentOS 6 with the default SQLite 3.6.20.
>
> I've compiled SptiaLite 4.2, but I'm not able to load SpatiaLite.
>
> sqlite> SELECT load_extension('mod_spatialite');
> Error: SQL logic error or missing database
> sqlite> SELECT load_extension('mod_spatialite.so');
> Error: SQL logic error or missing database
> sqlite> SELECT load_extension('libspatialite.so');
> Error: SQL logic error or missing database
>
> Is there something obvious that I am missing?
>

Hi Peter,

SQLite introduced many relevant changes affecting "load_extension"
starting since version 3.7.17
more specifically: the name of the link symbol expected to identify
the extension's entry point is now changed.

older versions of libsqlite3 (e.g. 3.6.20) are surely unaware of
such recent changes and will consequently be unable to correctly
locate the entry point thus causing a failure.

anyway a backward compatible mechanism exists allowing "old versions"
to successfully load a "new style" extension; you are simply expected
to explicitly reference the entry point name as the second (optional)
argument passed to "load_extension", e.g.:

SELECT load_extension('mod_spatialite', 'sqlite3_modspatialite_init');

another relevant changed introduced starting since 3.7.17 to be kept
in debt account: recent versions are much more sophisticated and will
attempt to locate the extension module in any reasonable platform
directory supposed to contain shared libraries.
and will automatically append the appropriate platform suffix (.dll,
.so)
so in many cases just referencing "mod_spatialite" will be enough.

on the other hand obsolete versions (< 3.7.17) were very limited;
passing the full extension's pathname (including any suffix) could
possibly help: e.g. "/usr/local/lib/mod_spatialite.so"

bye Sandro

[Peter Hopfgartner]

Good morning, Sandro,

your reply is, as we are well used to, highly illuminating. In the end I
think that I should accelerate the switch of our platform to CentOS 7,
since the burden increases from day to day.

Thanks,

Peter

--
Peter Hopfgartner
R3 GIS Srl - GmbH

Via Johann Kravogl-Str. 2
I-39012 Meran/Merano (BZ)
web : www.r3-gis.com
mail : peter.ho...@r3-gis.com
phone: +39 0473 494949
fax : +39 0473 069902

ATTENZIONE! Le informazioni contenute nella presente e-mail e nei documenti eventualmente allegati sono confidenziali. La loro diffusione, distribuzione e/o riproduzione da parte di terzi, senza autorizzazione del mittente è vietata e può violare il D. Lgs. 196/2003. In caso di ricezione per errore, Vogliate immediatamente informare il mittente del messaggio e distruggere la e-mail.

ACHTUNG! Die in dieser Nachricht oder in den beigelegten Dokumenten beinhalteten Informationen sind streng vertraulich. Ihre Verbreitung und/oder ihre Wiedergabe durch Dritte ist ohne Erlaubnis des Absenders verboten und verstößt gegen das Legislativdekret 196/2003. Sollten Sie diese Mitteilung irrtümlicherweise erhalten haben, bitten wir Sie uns umgehend zu informieren und anschließend die Mitteilung zu vernichten.

WARNING! This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorised copying, disclosure or distribution of the material in this e-mail is strictly forbidden and could be against the law (D. Lgs. 196/2003)

[Peter Hopfgartner]

Hi Jukka,

thanks for the facts. Yes, probably an upgrade of SQLite is the best
solution.

Regards,

Peter

>>
>> Thanks,
>>
>> Peter
>>
>> --
>> Peter Hopfgartner
>> R3 GIS Srl - GmbH
>> web : http://www.r3-gis.com


--
Peter Hopfgartner
R3 GIS Srl - GmbH