** 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

Reply via email to