** 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 ]
+ 
+ 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
+     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" }'
+     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
+  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