How to upgrade SQLite to the latest shared library version

About

When you install php on a distribution, you cannot go to the last version of Sqlite because each distribution repository just does not update its library.

For instance, if you are on CentOs 7, you will stay at the version 3.7.17

And this version does not accept the json extension.

Below is a step by step guide that will show you how to create a sqlite shared library and update it on a CentOS 7 distribution.

Steps

Creation of the SQLite shared library

Get the compile options that your actual Sqlite installation

PRAGMA compile_options;

In our case, we got the following compile option

DISABLE_DIRSYNC
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_RTREE
ENABLE_UNLOCK_NOTIFY
SECURE_DELETE
TEMP_STORE=1
THREADSAFE=1

Download the latest amalgame (amalgame is the source in one big file)

# download
wget https://www.sqlite.org/2021/sqlite-amalgamation-3340100.zip
# unzip
unzip sqlite-amalgamation-3340100.zip
# cd
cd sqlite-amalgamation-3340100

Compile. To create your compile command, take the compile option and add the prefix SQLITE. The below code will produce the file libsqlite3.so

gcc \
-Wl,-soname,libsqlite3.so.0 \
-DSQLITE_DISABLE_DIRSYNC \
-DSQLITE_ENABLE_COLUMN_METADATA \
-DSQLITE_ENABLE_FTS3 \
-DSQLITE_ENABLE_RTREE \
-DSQLITE_ENABLE_JSON1 \
-DSQLITE_ENABLE_UNLOCK_NOTIFY \
-DSQLITE_SECURE_DELETE \
-DSQLITE_TEMP_STORE=1 \
-DSQLITE_DTHREADSAFE=1 \
-shared \
-o libsqlite3.so \
-fPIC \
sqlite3.c

Locate the shared library that your php installation use

To do this, we will first discover the process id (PID) with the ps command for a command with php.

ps -ef | grep php

The below output shows a PID of 3007 for the master. There is also worker threads.

root      3007     1  0 13:25 ?        00:00:00 php-fpm: master process (/etc/php-fpm.conf)
www-user  3008  3007  1 13:25 ?        00:00:23 php-fpm: pool www
www-user  3009  3007  1 13:25 ?        00:00:30 php-fpm: pool www

List the shared library that use this process and filter it on sqlite

lsof -p 30070 | grep sqlite

The output shows the two php modules and the sqlite shared library located at /usr/lib64/libsqlite3.so.0.8.6

php-fpm 7993 root  mem       REG                8,1     33072  34216961 /usr/lib64/php/modules/pdo_sqlite.so
php-fpm 7993 root  mem       REG                8,1    753280     90544 /usr/lib64/libsqlite3.so.0.8.6
php-fpm 7993 root  mem       REG                8,1     54376  34216962 /usr/lib64/php/modules/sqlite3.so

Install the new shared library

Stop php

systemctl stop php-fpm

Make a backup

mv /usr/lib64/libsqlite3.so.0.8.6 /usr/lib64/libsqlite3.so.0.8.6.old

Copy our new sqlite shared livrary

mv libsqlite3.so /usr/lib64/libsqlite3.so.0.8.6

And start php

systemctl start php-fpm

Verification

Verify that the start was smooth with the journal (If not you can use the left arrow to navigate your log)

journalctl -u php-fpm

The version should visible with the php cli

php --ri sqlite3

sqlite3

SQLite3 support => enabled
SQLite Library => 3.34.1

Directive => Local Value => Master Value
sqlite3.extension_dir => no value => no value

Support

undefined symbol: sqlite3_column_table_name

You may have forgotten the SQLITE prefix in the gcc command

PHP Startup: Unable to load dynamic library 'sqlite3'

NOTICE: PHP message: PHP Warning:  PHP Startup: Unable to load dynamic library 'sqlite3' (tried: /usr/lib64/php/modules/sqlite3 )

The library and the php module seems to be hard-linked .

It means that the php module for instance /usr/lib64/php/modules/pdo_sqlite.so search the exact file /usr/lib64/libsqlite3.so.0.8.6.

Even LD_LIBRARY_PATH is not working. Be sure of the name of your file and of your path.