409234 2019-06-27 08:09:28 +0000 akonadi with qt5 5.13 is unable to initialize database when using the PostgreSQL backend 2020-03-22 19:26:50 +0000 1 1 3 Frameworks and Libraries Akonadi server unspecified Arch Linux Linux RESOLVED FIXED https://bugs.kde.org/show_bug.cgi?id=409753 NOR normal --- 0 bugs.kde.org pim-bugs-null arojas bugs.kde.org dvratil eclipseo heiko.becker v.pupillo https://commits.kde.org/akonadi/74aed9542ba1ff1e6e93a550c42869647c4eba8e 5.11.3 0 oldest_to_newest 1865834 0 bugs.kde.org 2019-06-27 08:09:28 +0000 SUMMARY After upgrading qt5 packages to 5.13, akonadiserver is "Unable to initialize database" when using PostgreSQL. The issue appears to be caused by https://github.com/qt/qtbase/commit/461ef575bcf778ba24b0be6b775098d4b80ae5e1 . STEPS TO REPRODUCE 1. Configure akonadi to use PostgreSQL. 2. run `akonadictl start`. OBSERVED RESULT Connecting to deprecated signal QDBusConnectionInterface::serviceOwnerChanged(QString,QString,QString) org.kde.pim.akonadiserver: Starting up the Akonadi Server... org.kde.pim.akonadiserver: Running DB initializer org.kde.pim.akonadiserver: "\nSql error: ERROR: column \"version\" of relation \"schemaversiontable\" already exists\n(42701) QPSQL: Unable to create query\nQuery: ALTER TABLE SchemaVersionTable ADD COLUMN version INTEGER NOT NULL DEFAULT 0" org.kde.pim.akonadiserver: Unable to initialize database. waiting for server to shut down.... done server stopped org.kde.pim.akonadiserver: Shutting down AkonadiServer... org.kde.pim.akonadicontrol: Application '/usr/bin/akonadiserver' exited normally... EXPECTED RESULT org.kde.pim.akonadiserver: Starting up the Akonadi Server... org.kde.pim.akonadiserver: Running DB initializer org.kde.pim.akonadiserver: DB initializer done ... SOFTWARE/OS VERSIONS Operating System: Arch Linux KDE Plasma Version: 5.16.2 KDE Frameworks Version: 5.59.0 Qt Version: 5.13.0 akonadi 19.04.2-3 qt5-base 5.13.0-4 postgresql 11.4-1 ADDITIONAL INFORMATION Arch Linux bug: https://bugs.archlinux.org/task/62997 1865901 1 121187 dvratil 2019-06-27 14:42:03 +0000 Created attachment 121187 Akonadi patch to fix compatibility with Qt 5.13 QPSQL driver Would you (or anyone else using PSQL and Qt 5.13) be able to build Akonadi with the attached patch and test it? I don't have access to a machine with Qt 5.13 right now, so I'm just guessing from the Qt commit. If the patch doesn't work I'll try to update to 5.13 over the weekend... 1865911 2 bugs.kde.org 2019-06-27 15:09:26 +0000 akonadi-qt-5.13-psql-case-sensitivity.patch fixes the issue. Akonadi successfully starts and runs. 1866073 3 dvratil 2019-06-28 14:04:56 +0000 Git commit 74aed9542ba1ff1e6e93a550c42869647c4eba8e by Daniel Vrátil. Committed on 28/06/2019 at 14:04. Pushed by dvratil into branch 'Applications/19.04'. Adapt DBInitializer to behavior change of QPSQL driver in Qt 5.13 Summary: Since Qt 5.13 the QPSQL driver escapes table names, which makes them case-sensitive, therefore QSqlDatabase::record() must be called with a lower-case table name, otherwise it won't match. FIXED-IN: 5.11.3 Test Plan: Akonadi doesn't attempt to re-initialize all tables on each start when using Qt 5.13 Reviewers: vkrause Reviewed By: vkrause Subscribers: kde-pim Tags: #kde_pim Differential Revision: https://phabricator.kde.org/D22133 M +1 -1 src/server/storage/dbintrospector.cpp https://commits.kde.org/akonadi/74aed9542ba1ff1e6e93a550c42869647c4eba8e 1869238 4 arojas 2019-07-16 13:28:18 +0000 This causes a regression in mysql for some users: https://bugs.kde.org/show_bug.cgi?id=409753 1871036 5 dvratil 2019-07-26 11:32:43 +0000 Git commit 8b8db29d10b2ef92deb2d87ff613f3d7f39af34e by Daniel Vrátil. Committed on 26/07/2019 at 11:32. Pushed by dvratil into branch 'Applications/19.04'. Fix compatibility with QMYSQL driver after 74aed95 Summary: QPSQL requires the table name passed to QSqlDatabase::record() to be lowercase since Qt 5.13, but doing that breaks QMYSQL, so only adjust the name when using QPSQL. Reviewers: #kde_pim, vkrause Reviewed By: #kde_pim, vkrause Subscribers: kde-pim Tags: #kde_pim Differential Revision: https://phabricator.kde.org/D22677 M +4 -1 src/server/storage/dbintrospector.cpp https://commits.kde.org/akonadi/8b8db29d10b2ef92deb2d87ff613f3d7f39af34e 1892021 6 v.pupillo 2019-11-17 12:54:53 +0000 Same Issue here after upgrade from Fedora 30 to Fedora 31 ( It appears the akonadi version in Fedora 31 is missing this commit) . I'm use a MariaDB server with many database, for my work. Usually I use the same server for Akonadi, Digikam and all the application that can use mysql/maridb as database. lower_case_table_names=1 breakes the others db. It's not possible to migrate the tables name from "CamelCase" to a "snake_case"? This solution works in all database server I knowns, even Oracle, DB2 and so on. 1892026 7 christophe 2019-11-17 13:38:58 +0000 (In reply to v.pupillo from comment #6) > Same Issue here [...] . I'm use a MariaDB server with many database, for my work. Hardly the same issue. This report is about the Postgres backend. 1892260 8 v.pupillo 2019-11-18 21:02:19 +0000 Yes, the backend is different but the problem is the same, it's a case problem. After upgrade to Fedora 31 (with lower_case_table_names=0), Akonadi said: org.kde.pim.akonadiserver: "Sql error: Duplicate column name 'id' QMYSQL: Unable to execute query\nQuery: ALTER TABLE CollectionTable ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY" org.kde.pim.akonadiserver: Unable to initialize database. Then I dropped the akonadi database and created a new one. With lower_case_table_names=0, Akonadi start and recreate the database. Work fine for a day. The next day Akonadi says: org.kde.pim.akonadiserver: "\nSql error: Duplicate column name 'id' QMYSQL: Unable to execute query\nQuery: ALTER TABLE CollectionTable ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY". Then I tried with lower_case_table_names=1, akonadi says: org.kde.pim.akonadiserver: "\nSql error: Can't create table `akonadi_vin_new`.`collectiontable` (errno: 121 \"Duplicate key on write or update\") QMYSQL: Unable to execute query\nQuery: CREATE TABLE CollectionTable (id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, remoteId VARBINARY(255), remoteRevision VARBINARY(255), name VARBINARY(255) NOT NULL, parentId BIGINT, resourceId BIGINT NOT NULL, enabled BOOL NOT NULL DEFAULT true, syncPref TINYINT DEFAULT 2, displayPref TINYINT DEFAULT 2, indexPref TINYINT DEFAULT 2, referenced BOOL NOT NULL DEFAULT false, cachePolicyInherit BOOL NOT NULL DEFAULT true, cachePolicyCheckInterval INTEGER NOT NULL DEFAULT -1, cachePolicyCacheTimeout INTEGER NOT NULL DEFAULT -1, cachePolicySyncOnDemand BOOL NOT NULL DEFAULT false, cachePolicyLocalParts VARBINARY(255), queryString VARBINARY(32768), queryAttributes VARBINARY(255), queryCollections VARBINARY(255), isVirtual BOOL DEFAULT false, FOREIGN KEY (parentId) REFERENCES CollectionTable(id) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (resourceId) REFERENCES ResourceTable(id) ON UPDATE CASCADE ON DELETE CASCADE) COLLATE=utf8_general_ci DEFAULT CHARSET=utf8" This is why I suggest to switch to snake_case tables and columns name, works with all the databases. 1917492 9 eclipseo 2020-03-22 17:28:41 +0000 This issue is marked as fixed but I have this bug on Fedora 32 using akonadictl 5.13.2 (19.12.2) org.kde.pim.akonadiserver: Starting up the Akonadi Server... 18:19:24 org.kde.pim.akonadiserver: Running DB initializer org.kde.pim.akonadiserver: "\nSql error: ERROR: column \"version\" of relation \"schemaversiontable\" already exists\n(42701) QPSQL: Unable to create query\nQuery: ALTER TABLE SchemaVersionTable ADD COLUMN version INTEGER NOT NULL DEFAULT 0" org.kde.pim.akonadiserver: Unable to initialize database. Can anyone explain why? 1917543 10 eclipseo 2020-03-22 19:26:50 +0000 (In reply to Robert-André Mauchin from comment #9) > This issue is marked as fixed but I have this bug on Fedora 32 using > akonadictl 5.13.2 (19.12.2) > > org.kde.pim.akonadiserver: Starting up the Akonadi Server... > 18:19:24 > org.kde.pim.akonadiserver: Running DB initializer > org.kde.pim.akonadiserver: "\nSql error: ERROR: column \"version\" of > relation \"schemaversiontable\" already exists\n(42701) QPSQL: Unable to > create query\nQuery: ALTER TABLE SchemaVersionTable ADD COLUMN version > INTEGER NOT NULL DEFAULT 0" > org.kde.pim.akonadiserver: Unable to initialize database. > > > Can anyone explain why? Fixed by backporting https://codereview.qt-project.org/c/qt/qtbase/+/277205 to qt5-qtbase 5.13.2 121187 2019-06-27 14:42:03 +0000 2019-06-27 14:42:03 +0000 Akonadi patch to fix compatibility with Qt 5.13 QPSQL driver akonadi-qt-5.13-psql-case-sensitivity.patch text/plain 671 dvratil ZGlmZiAtLWdpdCBhL3NyYy9zZXJ2ZXIvc3RvcmFnZS9kYmludHJvc3BlY3Rvci5jcHAgYi9zcmMv c2VydmVyL3N0b3JhZ2UvZGJpbnRyb3NwZWN0b3IuY3BwCmluZGV4IDIyNGZhZjdmOC4uNzBkOTUy YTZmIDEwMDY0NAotLS0gYS9zcmMvc2VydmVyL3N0b3JhZ2UvZGJpbnRyb3NwZWN0b3IuY3BwCisr KyBiL3NyYy9zZXJ2ZXIvc3RvcmFnZS9kYmludHJvc3BlY3Rvci5jcHAKQEAgLTc2LDcgKzc2LDcg QEAgYm9vbCBEYkludHJvc3BlY3Rvcjo6aGFzQ29sdW1uKGNvbnN0IFFTdHJpbmcgJnRhYmxlTmFt ZSwgY29uc3QgUVN0cmluZyAmY29sdW1uTmEKICAgICBRU3RyaW5nTGlzdCBjb2x1bW5zID0gbV9j b2x1bW5DYWNoZS52YWx1ZSh0YWJsZU5hbWUpOwogCiAgICAgaWYgKGNvbHVtbnMuaXNFbXB0eSgp KSB7Ci0gICAgICAgIGNvbnN0IFFTcWxSZWNvcmQgdGFibGUgPSBtX2RhdGFiYXNlLnJlY29yZCh0 YWJsZU5hbWUpOworICAgICAgICBjb25zdCBRU3FsUmVjb3JkIHRhYmxlID0gbV9kYXRhYmFzZS5y ZWNvcmQodGFibGVOYW1lLnRvTG93ZXIoKSk7CiAgICAgICAgIGNvbnN0IGludCBudW1UYWJsZXMg PSB0YWJsZS5jb3VudCgpOwogICAgICAgICBjb2x1bW5zLnJlc2VydmUobnVtVGFibGVzKTsKICAg ICAgICAgZm9yIChpbnQgaSA9IDA7IGkgPCBudW1UYWJsZXM7ICsraSkgewo=