Making php 8 use the latest version of sqlite3 on Ubuntu 20.04 LTS

Update: A cleaner way to do this without manually replacing the shared libraries.

When installing php 8.1 on your ubuntu machine running 20.04 LTS you’re limited, at the time of writing, to sqlite3 3.31.1 since it’s the version that comes with the distribution. We’re going to make php use the latest sqlite version(3.37.0) by:

  1. getting the current sqlite compile options
  2. downloading and compiling the latest sqlite from source
  3. replacing the sqlite3 shared library
  4. verifying the change

Get compile options

First off, let’s find out what compilation options we have with our current version of sqlite by jumping into the sqlite3 command line interface and getting the options:

sqlite> PRAGMA compile_options;

# my output
COMPILER=gcc-9.3.0
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS3_TOKENIZER
ENABLE_FTS4
ENABLE_FTS5
ENABLE_JSON1
ENABLE_LOAD_EXTENSION
ENABLE_PREUPDATE_HOOK
ENABLE_RTREE
ENABLE_SESSION
ENABLE_STMTVTAB
ENABLE_UNKNOWN_SQL_FUNCTION
ENABLE_UNLOCK_NOTIFY
ENABLE_UPDATE_DELETE_LIMIT
HAVE_ISNAN
LIKE_DOESNT_MATCH_BLOBS
MAX_SCHEMA_RETRY=25
MAX_VARIABLE_NUMBER=250000
OMIT_LOOKASIDE
SECURE_DELETE
SOUNDEX
THREADSAFE=1
USE_URI

Download and compile latest sqlite

Next up, we’re going to download the latest sqlite C source code as an amalgamation:

# download the latest amalgamation
wget  https://www.sqlite.org/2021/sqlite-amalgamation-3370000.zip

# unzip it
unzip sqlite-tools-linux-x86-3370000.zip

#  and cd into the folder
cd sqlite-tools-linux-x86-3370000

Next, we’re going compile the shared sqlite3 library file. We’re going to use gcc to compile the file.

You can install gcc with:

sudo apt install gcc

For the compilation command we’re gonna get the sqlite compile options we got before add the prefix: -DSQLITE_ to add the compile-time options alongside the rest of the command.

# inside sqlite-tools-linux-x86-3370000/
gcc \
-Wl,-soname,libsqlite3.so.0 \
-DSQLITE_ENABLE_COLUMN_METADATA \
-DSQLITE_ENABLE_DBSTAT_VTAB \
-DSQLITE_ENABLE_FTS3 \
-DSQLITE_ENABLE_FTS3_PARENTHESIS \
-DSQLITE_ENABLE_FTS3_TOKENIZER \
-DSQLITE_ENABLE_FTS4 \
-DSQLITE_ENABLE_FTS5 \
-DSQLITE_ENABLE_JSON1 \
-DSQLITE_ENABLE_LOAD_EXTENSION \
-DSQLITE_ENABLE_PREUPDATE_HOOK \
-DSQLITE_ENABLE_RTREE \
-DSQLITE_ENABLE_SESSION \
-DSQLITE_ENABLE_STMTVTAB \
-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION \
-DSQLITE_ENABLE_UNLOCK_NOTIFY \
-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
-DSQLITE_HAVE_ISNAN \
-DSQLITE_LIKE_DOESNT_MATCH_BLOBS \
-DSQLITE_MAX_SCHEMA_RETRY=25 \
-DSQLITE_MAX_VARIABLE_NUMBER=250000 \
-DSQLITE_OMIT_LOOKASIDE \
-DSQLITE_SECURE_DELETE \
-DSQLITE_SOUNDEX \
-DSQLITE_THREADSAFE=1 \
-DSQLITE_USE_URI \
-shared \
-o libsqlite3.so \
-fPIC \
sqlite3.c

Replace shared library

Now we should have the shared library file called libsqlite3.so. Let’s find where’s the file the php interpreter is using and then replace it with the one we just compiled.

First, we need the PID of the master process for the php-fpm:

ps -ef | grep php

# output
root      421802       1  0 14:34 ?        00:00:00 php-fpm: master process (/etc/php/8.1/fpm/php-fpm.conf)
www-data  421813  421802  0 14:34 ?        00:00:00 php-fpm: pool www
www-data  421814  421802  0 14:34 ?        00:00:00 php-fpm: pool www
potato    424098  417313  0 18:39 pts/0    00:00:00 grep --color=auto php

The master process has PID of 421802, let’s use this to see the shared libraries:

sudo lsof -p 421802 | grep sqlite

# output
php-fpm8. 421802 root  mem       REG                8,0    67848  262731 /usr/lib/php/20210902/sqlite3.so
php-fpm8. 421802 root  mem       REG                8,0  1757464 1053945 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6
php-fpm8. 421802 root  mem       REG                8,0    39176  262730 /usr/lib/php/20210902/pdo_sqlite.so

Our shared library is located in /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6, let’s replace it with the one we compiled:

# stop php
sudo systemctl stop php8.1-fpm.service

# make a backup
sudo mv /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6 /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6.old

# overwrite the library
sudo mv libsqlite3.so /usr/lib/x86_64-linux-gnu/libsqlite3.so.0.8.6

# restart php
sudo systemctl restart php8.1-fpm.service

Verify that everthing went smoothly

If everything went alright we should now have php using the latest version of sqlite3. We can quickly verify it with:

 php --ri sqlite3

# output
sqlite3

SQLite3 support => enabled
SQLite Library => 3.37.0

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

You can also check the logs to verify if everything went smoothly:

sudo journalctl -u php8.1-fpm
Topics: UBUNTU SQLITE PHP