Bug 409234 - akonadi with qt5 5.13 is unable to initialize database when using the PostgreSQL backend
Summary: akonadi with qt5 5.13 is unable to initialize database when using the Postgre...
Status: RESOLVED FIXED
Alias: None
Product: Akonadi
Classification: Frameworks and Libraries
Component: server (show other bugs)
Version: unspecified
Platform: Archlinux Linux
: NOR normal (vote)
Target Milestone: ---
Assignee: kdepim bugs
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-06-27 08:09 UTC by nl6720
Modified: 2020-03-22 19:26 UTC (History)
6 users (show)

See Also:
Latest Commit:
Version Fixed In: 5.11.3


Attachments
Akonadi patch to fix compatibility with Qt 5.13 QPSQL driver (671 bytes, patch)
2019-06-27 14:42 UTC, Daniel Vrátil
Details

Note You need to log in before you can comment on or make changes to this bug.
Description nl6720 2019-06-27 08:09:28 UTC
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
Comment 1 Daniel Vrátil 2019-06-27 14:42:03 UTC
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...
Comment 2 nl6720 2019-06-27 15:09:26 UTC
akonadi-qt-5.13-psql-case-sensitivity.patch fixes the issue.
Akonadi successfully starts and runs.
Comment 3 Daniel Vrátil 2019-06-28 14:04:56 UTC
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
Comment 4 Antonio Rojas 2019-07-16 13:28:18 UTC
This causes a regression in mysql for some users: https://bugs.kde.org/show_bug.cgi?id=409753
Comment 5 Daniel Vrátil 2019-07-26 11:32:43 UTC
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
Comment 6 v.pupillo 2019-11-17 12:54:53 UTC
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.
Comment 7 Christophe Giboudeaux 2019-11-17 13:38:58 UTC
(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.
Comment 8 v.pupillo 2019-11-18 21:02:19 UTC
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.
Comment 9 Robert-André Mauchin 2020-03-22 17:28:41 UTC
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?
Comment 10 Robert-André Mauchin 2020-03-22 19:26:50 UTC
(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