Hello everyone,

This might be a little long so here is a tl;dr

Tl;dr: If you are having problems with old Tracker DB files (from before
the update to FTS 5), check if the SQLite version Tracker is linking
against was compiled with the flag SQLITE_OMIT_AUTOVACUUM. You can check
this by with the following SQL query: "PRAGMA auto_vacuum". If the reply is
NULL it means SQLite was compiled with this flag, if the reply is 0 it
means that it wasn't.

Building SQLite *without* SQLITE_OMIT_AUTOVACUUM *might* solve your issues.
I don't really understand exactly *why* just yet.
There is also an upcoming patch by Carlos G that *might* solve your issue,
which will avoid creating the fts5 table multiple times.

** end of tl;dr

So here is what happened to me, most of this was discussed in the IRC
Tracker channel. I use a slightly modified version of Tracker and while
testing the latest stable version (1.8.0 and sqlite 3.12.2) against some
old DB files I ran into some trouble.

The tracker-store process would raise an abort signal systematically when
started using these files. I got the following error every time:

Tracker:ERROR:tracker-fts.c:202:tracker_fts_create_table: code should not
be reached

What was going on was that these old DB files were created with Tracker
1.3.2 and SQLite This meant that there were 2 ontology files that
had been modified since (nco.ontology and nmo.ontology) and that these
files used the old FTS3/4. Each of these two files would trigger a call to
tracker_fts_alter_table() during the initialization of tracker-store. Each
of these calls would then call tracker_fts_create_table() so that the new
fts5 table is created. In my case, everything went well the first time this
function was called. However, when the second ontology file triggered a
call to tracker_fts_create_table(), the first sqlite3_exec() call returned
SQLITE_CORRUPT, which made tracker exit through g_assert_not_reached().

I suspected that this behaviour was caused by my tracker modifications so I
set to try and reproduce this behaviour using upstream tracker versions. I
was able to partially reproduce this behaviour reliably. The only
difference is that now I got the SQLITE_CORRUPT error a little further down
the executing, after tracker_fts_create_table() returned. Precisely, it
happened in https://git.gnome.org/browse/tracker/tree/src/libtracker-
fts/tracker-fts.c#n226 . This meant that the fts5_tmp was never renamed
correctly to just fts5.

To reproduce this problem I was able to narrow it down to the following

I tried using 3 different SQLite versions,, 3.12.2 and 3.14.2.
- Build autoconf sqlite version using the following command:
CFLAGS="-DSQLITE_OMIT_AUTOVACUUM" ./configure  --enable-shared
--disable-static --enable-threadsafe --enable-fts5 ; make
(If you build an SQLite version that include fts5 support, ensure that
extension loading is activated, I believe it is by default unless it is

- Build tracker version tagged as 1.3.2 linking against the sqlite version
you just compiled. This ensures that you'll build a version where fts5
wasn't supported yet by tracker and also that you'll use an ontology where
at least 2 files differ from the current upstream ontology (nco and nmo). I
tried both using the default config options and the ones I normally use, it
didn't seem to make any difference.

- Start the tracker-store process so that a new empty Database is created,
the stop the process. Save all these DB files (meta.db, onologies.gvdb,
etc...) so you can reuse them later.

- Build tracker from the upstream 1.8.0 branch linking against the sqlite
version you just compiled. You can try building a different version, it
doesn't really matter as long as you use the SQLITE_OMIT_AUTOVACUUM flag.

- Ensuring the the old DB files are where Tracker expects them to be, start
the tracker-store (export TRACKER_VERBOSITY=3 so you can see the errors).

In my environment, following these steps is enough to cause an
SQLITE_CORRUPT error in https://git.gnome.org/browse/
tracker/tree/src/libtracker-fts/tracker-fts.c#n226 the second time
tracker_fts_alter_table() is called.

If I build the same SQLite autoconf version without the
SQLITE_OMIT_AUTOVACUUM I don't get this error any more. Carlos G tried
running tracker against the empty  DB files I had generated and he didn't
get any errors either.

In my initial environment, I built SQLite without this flag as well and my
initial error (Tracker:ERROR:tracker-fts.c:202:tracker_fts_create_table:
code should not be reached) was gone as well.

Now, all this seems very fishy for various reasons. By default auto_vacuum
is set to 0 and in Tracker it is set explicitly to 0 on start-up. Building
SQLite with the SQLITE_OMIT_AUTOVACUUM flag and using this with Tracker
shouldn't really have an impact. The SQLite documentation states "If this
option is defined, the library cannot create or write to databases that
support auto_vacuum <https://www.sqlite.org/pragma.html#pragma_auto_vacuum>.
Executing a PRAGMA auto_vacuum
<https://www.sqlite.org/pragma.html#pragma_auto_vacuum> statement is not an
error (since unknown PRAGMAs are silently ignored), but does not return a
value or modify the auto-vacuum flag in the database file. If a database
that supports auto-vacuum is opened by a library compiled with this option,
it is automatically opened in read-only mode."

I've tried looking at all the SQLite queries done up to the error point
(excluding all SELECT queries) to try and figure out what is going on. So
far I suspect two possibilities:

- There is some obscure SQLite bug that means that having auto-vacuum set
to 0 is not the same as using the SQLITE_OMIT_AUTOVACUUM flag, which causes
some DB corruption in some corner cases

- During the creation of the FTS table there are some hooks to functions
defined in tracker-fts-tokenizer.c. Queries like 'INSERT INTO
fts5_TMP(fts5_TMP, rank) VALUES('rank', 'tracker_rank()')' will cause these
functions to be called (e.g. tracker_rank_function()). Maybe there is some
undefined behaviour in there that is being made apparent by the presence of
SQLITE_OMIT_AUTOVACUUM and going through these functions (
tracker_fts_alter_table() and tracker_fts_create_table()) multiple times on
one run.

It's either that or some voodoo magic going on in my environment.

In any case, there are two ways in which this issue can be solved:

1.- Avoid using an SQLite version that was compiled using
SQLITE_OMIT_AUTOVACUUM. Maybe someone has an insight on why this seems to
do the trick.
2.- The fts table doesn't really need to be created multiple times, once is
enough. Carlos Garnacho was kind enough to come up with a patch that does
just this, thus avoiding encountering this error altogether. I imagine that
it'll be pushed upstream eventually

It was a bit of a headache trying to hunt down the culprit flag and I'm not
particularly satisfied with not having found exactly what was going on
(SQLite complaining of a corrupt DB is a bit scary :p). However, on the off
chance that someone else comes across this same problem, I wanted to put
all of this information "out there".

José Miguel Arroyo
tracker-list mailing list

Reply via email to