Update SQLite to 3.40.1 or later (can't update)

Focus · August 28, 2024, 9:50am

I get this warning message after installing 2024.8.3.

However when I run

sudo apt install sqlite3

It says

sqlite3 is already the newest version (3.37.2-2ubuntu0.3)

Does anyone know what I should do?

francisp · August 28, 2024, 11:23am

Update Ubuntu first?

Focus · August 28, 2024, 6:16pm

I have Linux Mint 21.3, which is based on Ubuntu 22.04.

Many users will be on a 22.04 based system, which receives LTS (Long-Term Support) until 2027. One wonders why this wasn’t good enough, it includes v3.37.2 of sqlite

I’ve set the “Critical Repair prompt” to ignored for now.

Abdull · September 1, 2024, 9:37pm

@bdraco , as you seem to be involved in the upcoming SQLite dependency version bump, could you provide insights?

I’m surprised about this warning in general. The SQLite client is pulled in via pip, not as an OS-provided client, right? (e.g., I don’t have any sqlite apt packages installed in my operating system).

If that’s so, then it is out-of-scope for the Home Assistant administrator to take care of updating SQLite. Instead, it will take place via an upcoming Home Assistant update.

bdraco · September 1, 2024, 10:03pm

github.com/home-assistant/core

Comment by bdraco - Amend sqlite version requirement

home-assistant:dev ← CreaticDD:sqlite3_37_2

We picked the bookworm version https://packages.debian.org/bookworm/sqlite3 sinc…e thats what supervisor supports and all our images are using a much never version We already are stretched too thin with trying to keep older versions working that newer versions are not getting sufficient testing which is how the problems with the optimizer changes in newer SQLite went unnoticed with the [2024.7 base images and made it to a release](https://community.home-assistant.io/t/psa-2024-7-recorder-problems). Adding more versions to test manually will create significantly more burden.

We would need someone to make a long term commitment to testing and maintaining older versions until 2027, and dealing with writing any manual migrations that can’t be done on all versions to realistically be able to support old versions to make that happen.

bdraco · September 1, 2024, 10:20pm

SQLite is provided by the operating system in almost all cases. Python is generally linked against the SQLite provided by the operating system unless you’ve compiled your own custom python, with its statically linked to a different version

Abdull · September 1, 2024, 11:17pm

I didn’t know that. Thank you for this clarification.

My Home Assistant Core installation runs on Ubuntu 22.04.4 LTS jammy (Raspberry Pi 4B).

Because Ubuntu 22.04’s apt package archives only provide Python 3.10, and because previous HA upgrades bumped the minimum Python version requirements to Python 3.12 currently (2024.8.x), I leverage Python3.12 from the deadsnakes PPA.

Assuming I understand the deadsnakes Python3.12 build process correctly, it uses Ubuntu 22.04’s “original” libsqlite3-dev, which will probably stay at version 3.37.2.

Confirming command:

$ python3.12 -c 'import sqlite3;print(sqlite3.sqlite_version)'
3.37.2

# learned from https://discuss.python.org/t/will-sqlite3-be-updated-in-the-standard-library-to-address-fixed-cvss-vulnerabilities/48629

So in case the proposed amendment doesn’t make it, us Ubuntu 22.04 homelab users will have a long weekend in February 2025, or just shrug it off and peak/sunset at Home Assistant 2025.01.

charlespick · October 5, 2024, 6:17pm

I’ve realized I made a mistake to choose an LTS OS for my Home Assistant. I’m going to be migrating off of Ubuntu LTS personally. HA bumps dependencies way too quickly to work with an LTS OS properly. If you have to make it work with Deadsnakes (as I currently am as well) it defeats the purpose of LTS entirely.

risa2000 · October 5, 2024, 6:57pm

Does anyone know which is the “canonical” linux distro the devs are chasing with these version upgrades?
I have been running Raspbian (on RPi 3B+), but this seems like a futile attempt for past several years.

dmr · January 14, 2025, 9:49am

Is there a way to set which sqlite is used? I’ve got sqlite 3.47 installed in /usr/local/bin, and this is the default one on the command line:

>sudo su - homeassistant
$ sqlite3 -version
3.47.2 2024-12-07 20:39:59 2aabe05e2e8cae4847a802ee2daddc1d7413d8fc560254d93ee3e72c14685b6c (64-bit)

However, the Recorder code seems to use the 3.37 version installed at /usr/bin/sqlite3. I’d much rather point HA at the local version than change my system sqlite. I can’t see any way to do this in the Recorder docs (Recorder - Home Assistant) - they only allow specifying a DB via the url schema.

dmr · January 14, 2025, 11:41am

Small update on this - I’ve managed to get the installed python to work with sqlite3:

(homeassistant) homeassistant@xxx:/srv/homeassistant$ python3
Python 3.13.1 (main, Dec  4 2024, 08:54:14) [GCC 11.4.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import _sqlite3
>>> _sqlite3.sqlite_version
'3.47.2'

However, this isn’t picked up by homeassistant - anything I would need to do?

(for anyone else trying to do this, I’ve installed from source (SQLite Download Page) into /usr/local, and then added this to the bin/activate script in /srv/homeassistant:

...
export LD_LIBRARY_PATH=/usr/local/lib

dmr · January 14, 2025, 12:23pm

OK, I now have things working!

Procedure:

Install recent sqlite version from source. After a standard compile && make && sudo make install, it puts the new sqlite in /usr/local.
I looked at the second answer here about checking and setting sqlite versions: sqlite - How to upgrade sqlite3 in python 2.7.3 inside a virtualenv? - Stack Overflow
This says that if we set LD_LIBRARY_PATH=/usr/local/lib, it should work OK. So:
- Can test it on the command line: export LD_LIBRARY_PATH=/usr/local/lib and then run hass
- If that works, then it can go into the bin/activate script, but that doesn’t seem to be picked up by my current systemd setup, so I’ve added it to the systemd service definition as suggested here: https://www.sobyte.net/post/2023-02/systemd-binary-service-dynamic-linking/:

...
Environment="LD_LIBRARY_PATH=/usr/local/lib"

Now, I’ve got HA running happily on Ubuntu 22.04.5 LTS.