Bug 409234

Created attachment 121187 [details]
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...

akonadi-qt-5.13-psql-case-sensitivity.patch fixes the issue.
Akonadi successfully starts and runs.

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

This causes a regression in mysql for some users: https://bugs.kde.org/show_bug.cgi?id=409753

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

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.

(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.

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.

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?

(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