Database creation error

[da-woods]

Before posting

Please follow the steps below and check the boxes with [x] once you did the step.

• I checked the issue tracker for similar issues
• I checked the changelog if the issue is already resolved
• [] I tried the latest Clementine build from here

I haven't done the last step (there isn't a build for my distribution and I'm already using the latest update from git)

System information

Please provide information about your system and the version of Clementine used.

• Operating System: Linux (Opensuse Tumbleweed) x86_64
• Clementine version: git 25326b4 (committed on 2016-03-12; most recent commit, at time of writing) built from source

Qt 4.8.7
sqlite version 3.11.0

Expected behaviour / actual behaviour

I'd been having issues with the search function not working for the past month or so (when I typed anything in the library search box no results came up). To try to fix this I removed my ~/.config/Clementine folder in the hope that it would rebuild the database. On trying to start Clementine I get the following output and the program closes

17:50:38.277 INFO main:331 Clementine 1.2.3
17:50:38.319 WARN Database:274 Couldn't register FTS3 tokenizer
17:50:38.320 INFO Database:282 Creating initial database schema
17:50:38.320 DEBUG Database:444 Applying database schema update 0 from ":/schema/schema.sql"
17:50:38.400 DEBUG Database:444 Applying database schema update 1 from ":/schema/schema-1.sql"
17:50:38.465 DEBUG Database:444 Applying database schema update 2 from ":/schema/schema-2.sql"
17:50:38.532 DEBUG Database:444 Applying database schema update 3 from ":/schema/schema-3.sql"
17:50:38.588 DEBUG Database:444 Applying database schema update 4 from ":/schema/schema-4.sql"
17:50:38.680 DEBUG Database:444 Applying database schema update 5 from ":/schema/schema-5.sql"
17:50:38.791 DEBUG Database:444 Applying database schema update 6 from ":/schema/schema-6.sql"
17:50:38.903 DEBUG Database:444 Applying database schema update 7 from ":/schema/schema-7.sql"
17:50:39.015 DEBUG Database:444 Applying database schema update 8 from ":/schema/schema-8.sql"
17:50:39.127 DEBUG Database:444 Applying database schema update 9 from ":/schema/schema-9.sql"
17:50:39.239 DEBUG Database:444 Applying database schema update 10 from ":/schema/schema-10.sql"
17:50:39.321 DEBUG Database:444 Applying database schema update 11 from ":/schema/schema-11.sql"
17:50:39.399 DEBUG Database:444 Applying database schema update 12 from ":/schema/schema-12.sql"
17:50:39.488 DEBUG Database:444 Applying database schema update 13 from ":/schema/schema-13.sql"
17:50:39.489 ERROR Database:573 db error: QSqlError(1, "Unable to fetch row", "unknown tokenizer: unicode")
17:50:39.489 ERROR Database:574 faulty query: "CREATE VIRTUAL TABLE songs_fts USING fts3(
ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsgenre, ftscomment,
tokenize=unicode
)"
17:50:39.489 ERROR Database:575 bound values: QMap()
17:50:39.489 ERROR unknown Unable to update music library database
Aborted (core dumped)

Subsequent attempts to start give a shorter version of the same output (essentially missing the "Applying database schema..." lines)

In case it's relevant, the console output when I try searching (for the letter "h") using my old database file looks like

17:52:22.656 ERROR Database:573 db error: QSqlError(-1, "Parameter count mismatch", "")
17:52:22.656 ERROR Database:574 faulty query: "SELECT DISTINCT effective_albumartist FROM songs INNER JOIN songs_fts AS fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH ? AND +effective_compilation = 1 AND unavailable = 0 LIMIT 1"
17:52:22.656 ERROR Database:575 bound values: QMap((":f", QVariant(QString, "h* ") ) )
17:52:22.656 ERROR Database:573 db error: QSqlError(-1, "Parameter count mismatch", "")
17:52:22.656 ERROR Database:574 faulty query: "SELECT DISTINCT effective_albumartist FROM songs INNER JOIN songs_fts AS fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH ? AND +effective_compilation = 0 AND unavailable = 0"
17:52:22.656 ERROR Database:575 bound values: QMap((":f", QVariant(QString, "h* ") ) )

Steps to reproduce the problem (only for bugs)

Start Clementine with a clean configuration.

[da-woods]

A bit more searching reveals this KaOSx/apps#47 (and a forum post that linked to it ) implying the issue may be a lack of tokenizer support in sqlite 3.11.0 so this may not exclusively be a Clementine issue. It looks like sqlite has disabled their support for user implemented tokenizers in FTS3 (at least without compile flags) (see http://www.sqlite.org/changes.html for 3.11.0) and this may be an ongoing problem with many distributions.

[Arfrever]

You can switch to FTS5 or use SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER to re-enable support for custom FTS3/FTS4 tokenizers.

--- src/core/database.cpp
+++ src/core/database.cpp
@@ -265,6 +265,16 @@
   StaticInit();

   {
+#ifdef SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER
+    QVariant v = db.driver()->handle();
+    if (v.isValid() && qstrcmp(v.typeName(), "sqlite3*") == 0) {
+      sqlite3* handle = *static_cast<sqlite3**>(v.data());
+      if (handle) {                                                                                                                                         
+        sqlite3_db_config(handle, SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, 1, NULL);                                                                          
+      }                                                                                                                                                     
+    }                                                                                                                                                       
+#endif                                                                                                                                                      
+                                                                                                                                                            
     QSqlQuery set_fts_tokenizer("SELECT fts3_tokenizer(:name, :pointer)", db);
     set_fts_tokenizer.bindValue(":name", "unicode");
     set_fts_tokenizer.bindValue(

[Arfrever]

About FTS5:
https://sqlite.org/fts5.html
https://sqlite.org/fts5.html#section_7_1
FTS5 was introduced in SQLite 3.8.11 (released on 2015-07-27).

[da-woods]

Thanks. To clarify, my understanding is that this fixes Clementine with SQLite 3.12.0 (probably due in the next month or so) and also places that backport the config option to 3.11? Clementine with unpatched SQLite 3.11 remains an broken, but that is most likely unavoidable and only a temporary issue?

[Arfrever]

Yes. (Or switch to FTS5 as mentioned previously.)

[demmm]

Since SQLite 3.12 is now out (and compiled locally with FTS5 enabled), not clear yet how to fix Clementine.
Custom line to start a new database? Recompile with a cmake flag enabling FTS5? Patching src/core/database.cpp to use FTS5 instead of FTS3?

[davidgfnet]

Hey! Do we have a patch for FTS5? I cannot get it to work due to my sqlite having fts5 instead of 3/4.
Thanks!

[pavelvat]

see #5669