** Description changed:
[ Impact ]
On Noble, sqlite3 3.45.1-1ubuntu2.9 changed `ROWID` resolution. Tracker
3.7.1's full-text view uses `GROUP BY ROWID`, which now fails with
`ambiguous column name: ROWID`. Consequently, Tracker cannot update its
database, file search becomes stale, and tracker-miner/extract may retry
continuously, consuming CPU and generating enough logs to fill the disk.
The upload backports the upstream fix to qualify the grouping column,
plus the corresponding database-version bump needed to recreate views in
existing databases.
-
[ Test Plan for sqlite3 ]
The sqlite3 fix is a revert of 3.45.1-1ubuntu2.9, therefore reopening
LP: #2171337.
The test plan is two fold:
a) Observe that tracker no longer fills the logs with error messages about
"ambiguous column name: ROWID"
- b) Run steps 1 and 2 from the tracker test plan below, and confirm that step
(2) no longer produces the same error message that was filling the logs.
+ b) Run steps 1 and 2 from the tracker test plan below, and confirm that step
(2) no longer produces the same error message that was filling the logs:
+
+ $ export D=$(mktemp -d)
+ $ tracker3 endpoint -d "$D" -o nepomuk
+ Opening database at /tmp/tmp.X5RFmYULoa?
+
+ And now:
+ With sqlite3 2.9:
+ $ tracker3 sparql -d "$D" -u -q 'INSERT DATA { <urn:x> a nfo:FileDataObject ;
nfo:fileName "tracker-sru-test" }'
+ Could not run update, ambiguous column name: ROWID
+
+ With sqlite3 2.11 from proposed:
+ $ tracker3 sparql -d "$D" -u -q 'INSERT DATA { <urn:x> a nfo:FileDataObject ;
nfo:fileName "tracker-sru-test" }'
+ Done
[ Test Plan for tracker ]
1. On an up-to-date Noble system with libsqlite3-0 3.45.1-1ubuntu2.9 and
unpatched Tracker, create a test database:
```
D=$(mktemp -d)
tracker3 endpoint -d "$D" -o nepomuk
```
2. Run:
```
tracker3 sparql -d "$D" -u -q 'INSERT DATA { <urn:x> a nfo:FileDataObject
; nfo:fileName "tracker-sru-test" }'
```
Expected before the fix: `Could not run update, ambiguous column name:
ROWID`.
3. Enable noble-proposed and install tracker from noble-proposed. Repeat
step 2 using the **same database**. This tests migration of an existing
database. Expected: `Done`.
4. Verify the data:
```
tracker3 sparql -d "$D" -q 'SELECT ?name WHERE { ?s nfo:fileName ?name }'
```
Expected: exactly one `tracker-sru-test`.
5. Repeat steps 1, 2, and 4 with a new temporary directory to test
creation of a fresh database.
6. Restart the file indexer:
```
systemctl --user restart tracker-miner-fs-3.service
```
If this is the first restart after the upgrade, wait for the one-time FTS
rebuild to finish and CPU/I/O activity to settle before continuing.
Create a dedicated directory and explicitly tell Tracker to index it:
```
TESTDIR="$HOME/tracker-sru-test"
TOKEN="trackersru$(date +%s)"
mkdir -p "$TESTDIR"
tracker3 index --add "$TESTDIR"
printf 'Tracker SRU verification token: %s\n' "$TOKEN" >
"$TESTDIR/$TOKEN.txt"
```
Wait 30 seconds, then search for the unique token:
```
tracker3 search "$TOKEN"
```
Expected: the output includes exactly one `$TESTDIR/$TOKEN.txt` and a
snippet containing the token.
A small file should normally be indexed within a few seconds on an
idle system. If it is not found after 30 seconds, retry every 10 seconds
for up to **2 minutes**, allowing for startup, migration, or other
indexing activity. Failure to appear after 2 minutes should be
investigated as a verification failure.
Remove the file:
```
rm "$TESTDIR/$TOKEN.txt"
```
Again wait 30 seconds and run:
```
tracker3 search "$TOKEN"
```
Expected: `No results were found matching your query`. As above, retry for
up to 2 minutes if necessary.
Check recent messages:
```
journalctl --user \
-u tracker-miner-fs-3.service \
-u tracker-extract-3.service \
--since "10 minutes ago"
```
There should be no new `ambiguous column name: ROWID` errors or
continuously repeating indexing failures. Tracker processes should not remain
at sustained high CPU usage.
Finally, remove the test directory from Tracker's persistent indexing
configuration:
```
tracker3 index --remove "$TESTDIR"
rmdir "$TESTDIR"
```
Note the duration of the first database open after upgrade,
particularly on a large existing index. Subsequent opens must not repeat
the migration.
[ Where problems could occur ]
Incorrect grouping could produce missing or duplicated full-text search
results. Steps 2–6 verify that updates succeed and that the unique
test data is returned exactly once
The version bump triggers a one-time FTS view/table rebuild on the first
writable open, for the main database and each graph. A failure could
prevent the database from opening or leave search unavailable. Large
indexes may experience temporary CPU/I/O usage and startup delay. This
is an FTS rebuild, not a filesystem reindex.
Migrated databases have a newer internal version and cannot be opened by
older Tracker versions; downgrading Tracker may therefore require
recreating the index.
[ Other Info ]
Reverting the sqlite3 update was rejected because it would reintroduce
the nested-join segmentation fault fixed by LP: #2087772.
[ Original Description ]
Note: this machine now runs 3.45.1-1ubuntu2.8 with apt-mark hold as a
workaround. The bug was observed on 3.45.1-1ubuntu2.9 from 2026-11-05
until the downgrade on 2026-11-11, and the reproducer below was run
against both versions on 2026-11-10.
After libsqlite3-0 was updated from 3.45.1-1ubuntu2.8 to
3.45.1-1ubuntu2.9 (noble-updates), every SPARQL update in Tracker 3.7.1
(libtracker-sparql-3.0-0 3.7.1-1build1, tracker-miner-fs
3.7.1-1ubuntu0.1) fails with:
Could not run update, ambiguous column name: ROWID
The 2.9 upload (LP: #2087772) backports upstream check-in 7a3d0027f8 from
SQLite 3.45.3 (SQLITE_ALLOW_ROWID_IN_VIEW handling). The same behaviour change
in 3.45.3 broke Tracker upstream in 2024 and was fixed in Tracker by
https://gitlab.gnome.org/GNOME/tracker/-/merge_requests/665 . Arch and Gentoo
hit the same breakage when they shipped 3.45.3:
-
https://gitlab.archlinux.org/archlinux/packaging/packages/tracker3/-/issues/2
- https://bugs.gentoo.org/931015
-
https://discourse.gnome.org/t/tracker-sparql-affected-by-behaviour-change-in-sqlite-3-45-3/20772
Noble's tracker 3.7.1 does not carry the MR 665 fix, so the sqlite3
backport breaks it.
[ Steps to reproduce ]
On Ubuntu 24.04 with libsqlite3-0 3.45.1-1ubuntu2.9:
D=$(mktemp -d)
timeout 5 tracker3 endpoint -d "$D" -o nepomuk
tracker3 sparql -d "$D" -u -q 'INSERT DATA { <urn:x> a nfo:FileDataObject
; nfo:fileName "test" }'
Actual (2.9): Could not run update, ambiguous column name: ROWID
Expected (2.8): Done
Verified by running the identical commands against the 2.8 library
extracted from the noble-security .deb via LD_LIBRARY_PATH: 2.8 prints
"Done", 2.9 fails.
Downgrading to 2.8 and restarting tracker-miner-fs-3 stops the ROWID
errors; vanishing files are then handled once without retries.
3.45.1-1ubuntu2.10 in noble-proposed (LP: #2171337) is built on 2.9 and
its changelog does not mention the ROWID change, so it likely carries
the same regression (not tested).
[ Impact ]
tracker-miner-fs-3 logs "Could not execute sparql: ambiguous column name:
ROWID" from the first boot after the update. It can no longer record that a
file failed extraction ("Failed to update ignored file"), so files that
disappear before extraction (e.g. atomic-write temp files) are retried forever.
On this machine tracker-extract-3 then logged about 55,000 warnings per minute;
/var/log/syslog grew by ~110 GB/day and reached 242 GB in four days, nearly
filling the root disk. The loop persisted across reboots.
Timeline (UTC+7):
- 2026-11-05 21:29 libsqlite3-0 upgraded 2.8 -> 2.9
- 2026-11-05 23:24 first ever "ambiguous column name: ROWID" (first boot
after upgrade)
- 2026-11-06 09:53 first ever "Failed to update ignored file"; flood begins
- None of these messages appear in syslog from 2026-10-11 to 2026-11-05.
[ Workaround ]
apt install libsqlite3-0=3.45.1-1ubuntu2.8 && apt-mark hold libsqlite3-0
ProblemType: Bug
DistroRelease: Ubuntu 24.04
Package: libsqlite3-0 3.45.1-1ubuntu2.8
ProcVersionSignature: Ubuntu 7.0.0-42.42~24.04.1-generic 7.0.14
Uname: Linux 7.0.0-42-generic x86_64
ApportVersion: 2.28.3-0ubuntu0.1
Architecture: amd64
CasperMD5CheckResult: pass
CurrentDesktop: ubuntu:GNOME
Date: Fri Nov 13 11:24:34 2026
Dependencies:
gcc-14-base 14.2.0-4ubuntu2~24.04.1
libc6 2.39-0ubuntu8.12
libgcc-s1 14.2.0-4ubuntu2~24.04.1
libidn2-0 2.3.7-2build1.1
libunistring5 1.1-2build1.1
InstallationDate: Installed on 2025-10-18 (391 days ago)
InstallationMedia: Ubuntu 24.04.3 LTS "Noble Numbat" - Release amd64
(20250805.1)
RebootRequiredPkgs: Error: path contained symlinks.
SourcePackage: sqlite3
UpgradeStatus: No upgrade log present (probably fresh install)
--
You received this bug notification because you are a member of Ubuntu
Bugs, which is subscribed to Ubuntu.
https://bugs.launchpad.net/bugs/2174360
Title:
3.45.1-1ubuntu2.9 regression: Tracker 3.7.1 updates fail with
"ambiguous column name: ROWID"
To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/sqlite3/+bug/2174360/+subscriptions
--
ubuntu-bugs mailing list
[email protected]
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs