python-build-standalone ships sqlite3 module missing Connection.serialize, deserialize

[dhandy2013]

I was really excited about switching away from poetry and pyenv to uv for building and testing our project.

However, our project relies on sqlite3.Connection.serialize and sqlite3.Connection.deserialize. The docs for these methods say:

Note This method is only available if the underlying SQLite library has the deserialize API.

My system Python (Python 3.12 on Ubuntu 24.04) and pyenv-supplied Python 3.11 (also on Ubuntu 24.04) have these methods. It seems like this is something that should normally be available.

I really hate having to switch back to pyenv because of this issue. Can you compile sqlite3 with the Connection.serialize and Connection.deserialize methods?

Steps to reproduce:

I tested with python-build-standalone cpython-3.11.11+20241206-x86_64-unknown-linux-gnu-install_only.tar.gz on Ubuntu 24.04.

Steps to reproduce with python-build-standalone Python 3.11.11:

Python 3.11.11 (main, Dec  6 2024, 20:02:44) [Clang 18.1.8 ] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.47.1'
>>> from sqlite3 import Connection
>>> Connection.serialize
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
AttributeError: type object 'sqlite3.Connection' has no attribute 'serialize'

Comparing with pyenv Python 3.11:

Python 3.11.9 (main, Aug  6 2024, 18:48:15) [GCC 13.2.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> sqlite3.sqlite_version
'3.45.1'
>>> from sqlite3 import Connection
>>> Connection.serialize
<method 'serialize' of 'sqlite3.Connection' objects>

[zanieb]

Similar to #309 / #375, happy to enable features like this.

Do you know what flag is needed to do so?

[zanieb]

Hm the docs say

SQLITE_ENABLE_DESERIALIZE

This option was formerly used to enable the sqlite3_serialize() and sqlite3_deserialize() interfaces. However, as of SQLite 3.36.0 (2021-06-18) those interfaces are enabled by default and a new compile-time option SQLITE_OMIT_DESERIALIZE is added to omit them.

[zanieb]

And we're on 3.47.1.0

[indygreg]

Check how CPython sniffs the feature. They may not be doing it robustly.

[zanieb]

This occurs at https://github.com/python/cpython/blob/ea578fc6d310c85538aefbb900a326c5c3424dd5/configure.ac#L4247-L4252

      AC_CHECK_LIB([sqlite3], [sqlite3_serialize], [
        AC_DEFINE(
          [PY_SQLITE_HAVE_SERIALIZE], [1],
          [Define if SQLite was compiled with the serialize API]
        )
      ])

[zanieb]

I actually can't reproduce this on macOS

❯ uv run -p 3.11.11 --python-preference only-managed -v python -c "import sqlite3; sqlite3.Connection.deserialize"
DEBUG uv 0.5.10 (37b11ddb2 2024-12-17)
DEBUG Found project root: `/Users/zb/workspace/uv`
DEBUG Project `uv` is marked as unmanaged
DEBUG No project found; searching for Python interpreter
DEBUG Searching for Python 3.11.11 in virtual environments or managed installations
DEBUG Found `cpython-3.12.6-macos-aarch64-none` at `/Users/zb/workspace/uv/.venv/bin/python3` (virtual environment)
DEBUG Skipping interpreter at `.venv/bin/python3` from virtual environment: does not satisfy request `3.11.11`
DEBUG Searching for managed installations at `/Users/zb/.local/share/uv/python`
DEBUG Skipping incompatible managed installation `cpython-3.13.1-macos-aarch64-none`
DEBUG Skipping incompatible managed installation `cpython-3.12.6-macos-aarch64-none`
DEBUG Skipping incompatible managed installation `cpython-3.11.10-macos-aarch64-none`
DEBUG Skipping incompatible managed installation `cpython-3.10.15-macos-aarch64-none`
DEBUG Skipping incompatible managed installation `cpython-3.9.20-macos-aarch64-none`
DEBUG Skipping incompatible managed installation `cpython-3.8.18-macos-aarch64-none`
DEBUG Skipping incompatible managed installation `cpython-3.8.12-macos-aarch64-none`
DEBUG Requested Python not found, checking for available download...
DEBUG Acquired lock for `/Users/zb/.local/share/uv/python`
DEBUG Using request timeout of 30s
INFO Fetching requested Python...
DEBUG Downloading https://github.com/astral-sh/python-build-standalone/releases/download/20241206/cpython-3.11.11%2B20241206-aarch64-apple-darwin-install_only_stripped.tar.gz to temporary location: /Users/zb/.local/share/uv/python/.temp/.tmp2TmPjU
DEBUG Extracting cpython-3.11.11%2B20241206-aarch64-apple-darwin-install_only_stripped.tar.gz
DEBUG Moving /Users/zb/.local/share/uv/python/.temp/.tmp2TmPjU/python to /Users/zb/.local/share/uv/python/cpython-3.11.11-macos-aarch64-none
DEBUG Released lock at `/Users/zb/.local/share/uv/python/.lock`
DEBUG Using Python 3.11.11 interpreter at: /Users/zb/.local/share/uv/python/cpython-3.11.11-macos-aarch64-none/bin/python3.11
DEBUG Running `python -c import sqlite3; sqlite3.Connection.deserialize`
DEBUG Command exited with code: 0

❯ /Users/zb/.local/share/uv/python/cpython-3.11.11-macos-aarch64-none/bin/python3.11 -c "import sqlite3; print(sqlite3.Connection.serialize)"
<method 'serialize' of 'sqlite3.Connection' objects>

[zanieb]

But I can reproduce on Linux

❯  docker run --platform linux/amd64 -it --rm ghcr.io/astral-sh/uv:0.5.10-bookworm uv run -p 3.11 --python-preference only-managed python -c "import sys; import sqlite3; print(sys.executable); print(sqlite3.Connection.serialize)"
Unable to find image 'ghcr.io/astral-sh/uv:0.5.10-bookworm' locally
0.5.10-bookworm: Pulling from astral-sh/uv
fdf894e782a2: Already exists 
5bd71677db44: Already exists 
551df7f94f9c: Already exists 
ce82e98d553d: Already exists 
bedb2b7d02b2: Pull complete 
Digest: sha256:f18fb7f94096ae71cdb6484a481f4a2938f62a10c5ef68c782373e7fe969a57f
Status: Downloaded newer image for ghcr.io/astral-sh/uv:0.5.10-bookworm
/root/.local/share/uv/python/cpython-3.11.11-linux-x86_64-gnu/bin/python3.11
Traceback (most recent call last):
  File "<string>", line 1, in <module>
AttributeError: type object 'sqlite3.Connection' has no attribute 'serialize'

[zanieb]

I can fix this with a CPython patch

diff --git a/configure.ac b/configure.ac
index 074e2ce3dd3..6d01927836d 100644
--- a/configure.ac
+++ b/configure.ac
@@ -4214,6 +4214,8 @@ dnl bpo-45774/GH-29507: The CPP check in AC_CHECK_HEADER can fail on FreeBSD,
 dnl hence CPPFLAGS instead of CFLAGS.
   CPPFLAGS="$CPPFLAGS $LIBSQLITE3_CFLAGS"
   LDFLAGS="$LIBSQLITE3_LIBS $LDFLAGS"
+  LIBS="$LIBS -lm"
+
 
   AC_CHECK_HEADER([sqlite3.h], [
     have_sqlite3=yes

I haven't figured out how to reproduce this cleanly with just the upstream yet to open a bug report.

[indygreg]

Huh. Libm will almost certainly be in the final library link set. I'm shocked this works.

[zanieb]

Libm will almost certainly be in the final library link set

Can you clarify what you mean here?

I want to spend some more time understanding what's going on before I engage upstream — I was hacking on a few things at once and need to confirm my findings.

Very briefly though, I noticed these failures in the autconf logs:

configure:15852: checking for sqlite3_value_double in -lsqlite3
configure:15875: clang -o conftest  -fdebug-default-version=4 -fPIC -I/tools/deps/include -I/tools/deps/include/ncursesw  -fdebug-default-version=4 -fPIC -I/tools/deps/include -I/tools/deps/include/ncursesw  -I$(srcdir)/Modules/_sqlite -lsqlite3  -L/tools/deps/lib -Wl,--exclude-libs,ALL -LModules/_hacl conftest.c -lsqlite3  -lpthread -ldl  >&5
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o): in function `xCeil':
sqlite3.c:(.text+0xd6143): undefined reference to `ceil'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o): in function `xFloor':
sqlite3.c:(.text+0xd6213): undefined reference to `floor'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o): in function `logFunc':
sqlite3.c:(.text+0xd62a9): undefined reference to `log'
/tools/host/bin/ld: sqlite3.c:(.text+0xd62fa): undefined reference to `log'
/tools/host/bin/ld: sqlite3.c:(.text+0xd633e): undefined reference to `log10'
/tools/host/bin/ld: sqlite3.c:(.text+0xd6352): undefined reference to `log2'
/tools/host/bin/ld: sqlite3.c:(.text+0xd6366): undefined reference to `log'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o): in function `fts5Bm25GetData':
sqlite3.c:(.text+0x13397e): undefined reference to `log'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x1fa8): undefined reference to `trunc'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2158): undefined reference to `exp'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x21a0): undefined reference to `pow'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x21e8): undefined reference to `pow'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2230): undefined reference to `fmod'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2278): undefined reference to `acos'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x22c0): undefined reference to `asin'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2308): undefined reference to `atan'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2350): undefined reference to `atan2'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2398): undefined reference to `cos'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x23e0): undefined reference to `sin'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2428): undefined reference to `tan'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2470): undefined reference to `cosh'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x24b8): undefined reference to `sinh'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2500): undefined reference to `tanh'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2548): undefined reference to `acosh'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2590): undefined reference to `asinh'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x25d8): undefined reference to `atanh'
/tools/host/bin/ld: /tools/deps/lib/libsqlite3.a(sqlite3.o):(.data+0x2620): undefined reference to `sqrt'
| char sqlite3_value_double ();
| return sqlite3_value_double ();

all the PY_CHECK_SQLITE_FUNC checks were failing because of this

ac_cv_lib_sqlite3_sqlite3_bind_double=no
ac_cv_lib_sqlite3_sqlite3_column_decltype=no
ac_cv_lib_sqlite3_sqlite3_column_double=no
ac_cv_lib_sqlite3_sqlite3_complete=no
ac_cv_lib_sqlite3_sqlite3_load_extension=no
ac_cv_lib_sqlite3_sqlite3_progress_handler=no
ac_cv_lib_sqlite3_sqlite3_result_double=no
ac_cv_lib_sqlite3_sqlite3_set_authorizer=no
ac_cv_lib_sqlite3_sqlite3_trace=no
ac_cv_lib_sqlite3_sqlite3_trace_v2=no
ac_cv_lib_sqlite3_sqlite3_value_double=no

Since we're in a WITH_SAVE_ENV block, I tried just including libm temporarily to fix the checks (which it looks like it did).

[indygreg]

Those missing math functions are found in libm. So linking against libm is necessary here.

I'm just surprised libm isn't being linked with. Usually libc and libm are both linked in any non-trivial program.

I need to verify this, but I suspect the SQLite pkg-config incorrectly omits libm. Or maybe our static SQLite build confuses CPython configure. It's got to be something specific to PBS because I refuse to believe this bug exists in CPython since people would have noticed.

[zanieb]

It's got to be something specific to PBS because I refuse to believe this bug exists in CPython since people would have noticed.

Yeah that makes sense. My next goal was to reproduce this with our static SQLite build and CPython's configure.

[zanieb]

@indygreg perhaps I'm misunderstanding how this works.. but in bafaac8 sqlite3.pc includes -lm in the private libs as I'd expect

sqlite> + cat sqlite3.pc
sqlite> # Package Information for pkg-config
sqlite> 
sqlite> prefix=/tools/deps
sqlite> exec_prefix=${prefix}
sqlite> libdir=${exec_prefix}/lib
sqlite> includedir=${prefix}/include
sqlite> 
sqlite> Name: SQLite
sqlite> Description: SQL database engine
sqlite> Version: 3.47.1
sqlite> Libs: -L${libdir} -lsqlite3
sqlite> Libs.private: -lm -ldl -lpthread 
sqlite> Cflags: -I${includedir}

AC_CHECK_LIB only includes -lsqlite3 when checking for functions and when dynamic linking is allowed it'll pull -lm automatically but since we are statically linking it's not allowed unless explicitly included, right? I don't think pkg-config is used at all in this context.

[zanieb]

Actually it looks like PKG_CHECK_MODULES is used which should invoke pkg-config but in our logs you can see

cpython-3.14> checking for sqlite3 >= 3.15.2...
cpython-3.14> no

which then falls back to just using -lsqlite3

PKG_CHECK_MODULES(
  [LIBSQLITE3], [sqlite3 >= 3.15.2], [], [
    LIBSQLITE3_CFLAGS=${LIBSQLITE3_CFLAGS-""}
    LIBSQLITE3_LIBS=${LIBSQLITE3_LIBS-"-lsqlite3"}
  ]
)

[zanieb]

You can also see that -lm is only included with the --static flag

$ pkg-config --static --libs sqlite3 
-L/usr/local/lib -lsqlite3 -lm 
$ pkg-config --libs sqlite3             
-L/usr/local/lib -lsqlite3 

but... that requires PKG_CHECK_MODULES_STATIC instead?

So either we need to set

PKG_CONFIG="$PKG_CONFIG --static"

or update the upstream to use PKG_CHECK_MODULES_STATIC when shared is disabled?

[zanieb]

I opened some coherent findings from reproducing this outside PBS at python/cpython#128321

Separately, we fail the initial pkg-config check (checking for sqlite3 >= 3.15.2... no), so fixing the bug I reported won't help. I need to investigate the cause of that still.

[indygreg]

The PBS patches do some questionable things to configure for extension modules to force static linking. Those patches predate CPython allowing you to statically link (all) extension modules. Wouldn't surprise me if we're footgunning ourselves.

[indygreg]

FWIW the linked CPython issue you filed makes sense to me: it seems there is a bug in CPython configure not handling static SQLite correctly. Not surprising since this won't be a common configuration.

[zanieb]

Thanks for taking a look Greg!

[erlend-aasland]

FWIW the linked CPython issue you filed makes sense to me: it seems there is a bug in CPython configure not handling static SQLite correctly. Not surprising since this won't be a common configuration.

There's no way to detect if SQLite was configured with --disable-shared or not, AFAIK. IMO, this is a bug in the generation of the SQLite .pc file.

[zanieb]

There's no way to detect if SQLite was configured with --disable-shared or not, AFAIK.

Agreed, I think this just has to be the responsibility of the person building downstream.

IMO, this is a bug in the generation of the SQLite .pc file.

I am relatively new to this, but my understanding is that the .pc file should always place the statically linked libraries in the Libs.private section, what are you suggesting should change?

[erlend-aasland]

Perhaps the .pc conventions does not align with this specific use case 🤷