Bug 409224

Summary: kmail with >=mariadb-10.4.6: not able to refresh email list: Unable to append status flags
Product: [Frameworks and Libraries] Akonadi Reporter: Piotr <piorek>
Component: serverAssignee: kdepim bugs <kdepim-bugs>
Status: CONFIRMED ---    
Severity: normal CC: ao, arojas, asturm, brix, bugs, contact, devurandom, fischer, heiko.becker, j.filzmaier, kde, kishore96, lonelywoolf, luca, mark+kde, medhefgo, peter
Priority: NOR    
Version: 5.11.2   
Target Milestone: ---   
Platform: Gentoo Packages   
OS: Linux   
Latest Commit: Version Fixed In:

Description Piotr 2019-06-26 21:18:37 UTC
SUMMARY
For two days I have not launched kmail on my main computer running KMail 5.11.2 however meanwhile I've been using my company laptop with some old KMail version. Both computers have the same account configured over IMAP.
When I started KMail today on my main machine it can't refresh email list. I can't see new email and still see older mails in state unread when I have actually already read them on the second machine.

STEPS TO REPRODUCE
1. launch kmail
2. click "Check Mail" button

OBSERVED RESULT
1. mail list is not refreshed
2. in the status bars in the bottom right corner error "Unable to append status flags" can be seen


EXPECTED RESULT
New mail arrive and old change status

SOFTWARE/OS VERSIONS
Linux/KDE Plasma: Gentoo
(available in About System)
KDE Plasma Version: 5.16.1
KDE Frameworks Version: 5.59.0
Qt Version: Qt 5.12.3 (built against 5.12.3)

ADDITIONAL INFORMATION
In .local/share/akonadi/akonadiserver.error I can see error messages like this:

2019-06-26T23:12:31 [CRITICAL] org.kde.pim.akonadiserver: DATABASE ERROR:
2019-06-26T23:12:31 [CRITICAL] org.kde.pim.akonadiserver:   Error code: "1452"
2019-06-26T23:12:31 [CRITICAL] org.kde.pim.akonadiserver:   DB error:  "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`pimitemflagrelation`, CONSTRAINT `pimitemflagrelation_ibfk_1` FOREIGN KEY (`PimItem_id`) REFERENCES `pimitemtable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)"
2019-06-26T23:12:31 [CRITICAL] org.kde.pim.akonadiserver:   Error text: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`pimitemflagrelation`, CONSTRAINT `pimitemflagrelation_ibfk_1` FOREIGN KEY (`PimItem_id`) REFERENCES `pimitemtable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) QMYSQL3: Unable to execute statement"
2019-06-26T23:12:31 [CRITICAL] org.kde.pim.akonadiserver:   Values: QMap((":0", QVariant(qlonglong, 73282))(":1", QVariant(qlonglong, 1)))
2019-06-26T23:12:31 [CRITICAL] org.kde.pim.akonadiserver:   Query: "INSERT INTO PimItemFlagRelation (PimItem_id, Flag_id) VALUES (:0, :1)"
2019-06-26T23:12:31 [WARN ] org.kde.pim.akonadiserver: Failed to append flag "\\SEEN" to Items (QVariant(qlonglong, 73282))
2019-06-26T23:12:31 [WARN ] org.kde.pim.akonadiserver: Error while handling command CreateItem on connection akonadi_imap_resource_2 (0x563e53aa43e0)

When I looked with akonadiconsole indeed there was no item with ID 73282 in pimitemtable.

Where does it takes this ID from then?
How can I fix this so thath my KMail starts working again?
Comment 1 Mark Nowiasz 2019-06-27 15:02:54 UTC
Same here - since upgrade to 5.11.2 kmail stopped getting new mail via IMAP, I basically stopped working at all :-/ 

Using a different mailclient on the same IMAP acccount, everything is fine.

So, how can we make this work again? Right now kmail is dead in the water :-/


019-06-27T16:54:51 [CRITICAL] org.kde.pim.akonadiserver:   Error code: "1452"
2019-06-27T16:54:51 [CRITICAL] org.kde.pim.akonadiserver:   DB error:  "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`pimitemflagrelation`, CONSTRAINT `pimitemflagrelation_ibfk_1` FOREIGN KEY (`PimItem_id`) REFERENCES `pimitemtable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)"
2019-06-27T16:54:51 [CRITICAL] org.kde.pim.akonadiserver:   Error text: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`pimitemflagrelation`, CONSTRAINT `pimitemflagrelation_ibfk_1` FOREIGN KEY (`PimItem_id`) REFERENCES `pimitemtable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) QMYSQL3: Unable to execute statement"
2019-06-27T16:54:51 [CRITICAL] org.kde.pim.akonadiserver:   Values: QMap((":0", QVariant(qlonglong, 935132))(":1", QVariant(qlonglong, 1)))
2019-06-27T16:54:51 [CRITICAL] org.kde.pim.akonadiserver:   Query: "INSERT INTO PimItemFlagRelation (PimItem_id, Flag_id) VALUES (:0, :1)"
2019-06-27T16:54:51 [WARN ] org.kde.pim.akonadiserver: Failed to append flag "\\SEEN" to Items (QVariant(qlonglong, 935132))
2019-06-27T16:54:51 [WARN ] org.kde.pim.akonadiserver: Error while handling command CreateItem on connection akonadi_imap_resource_0 (0x562e30f4ba60)
Comment 2 Mark Nowiasz 2019-06-27 18:11:54 UTC
Could be related to mariadb 10.4.6, https://bugs.gentoo.org/688746
Comment 3 Mark Nowiasz 2019-06-28 06:18:57 UTC
> Where does it takes this ID from then?
> How can I fix this so thath my KMail starts working again?

Downgrading mariadb from 10.4.6 to 10.3.x provided a workaround for me - after downgrading kmail works again, so I guess kmail/akonadi is currently incompatible with mariadb 10.4.
Comment 4 Piotr 2019-06-28 14:14:48 UTC
Indeed downgrading MariaDB to 10.3.16 fixed the issue.
Thank you for that workaround.
Comment 5 Thomas Brix Larsen 2019-06-29 09:04:10 UTC
*** This bug has been confirmed by popular vote. ***
Comment 6 Thomas Brix Larsen 2019-06-29 09:07:08 UTC
Workaround for getting Akonadi working on mariadb-10.4.6 (tested on Arch):
mysql_upgrade --socket=/tmp/akonadi-${USER}.XXXXX/mysql.socket
Comment 7 Piotr 2019-07-01 18:30:53 UTC
Workaround wasn't working for me:

piorek@dreadnought ~ $ mysql_upgrade --socket=/tmp/akonadi-piorek.XXXXXX/mysql.socket
Version check failed. Got the following error when calling the 'mysql' command line client
ERROR 1049 (42000): Unknown database 'mysql'
FATAL ERROR: Upgrade failed

Had to create DB mysql first (CREATE DATABASE mysql;). This fixed the problem and upgrade succeeded. Looks like KMail is working fine so far.
Comment 8 Pavel 2019-07-02 18:45:48 UTC
Can these databases be dropped again after creating 'mysql' database and running upgrade tool?

| information_schema |
| mysql              |
| performance_schema |
Comment 9 LonelyWoolf 2019-07-16 17:18:37 UTC
Same problem. I think, that akonadi should check versions of mysql and run mysql_upgrade.
Comment 10 Thomas Fischer 2019-07-18 13:32:11 UTC
On Arch Linux and maybe other Linux distributions, the socket FSO 'mysql.socket' is not located in /tmp/akonadi-*/ but in /run/user/*/akonadi/default/ instead.

Thus, the two commands to run become:
mysql -S /run/user/$(id -u)/akonadi/default/mysql.socket  -e 'create database mysql' && mysql_upgrade -S /run/user/$(id -u)/akonadi/default/mysql.socket

At least for me, after running both commands KMail/Akonadi starts fetching and listing mails again.

(In reply to Piotr from comment #7)
> Workaround wasn't working for me:
> 
> piorek@dreadnought ~ $ mysql_upgrade
> --socket=/tmp/akonadi-piorek.XXXXXX/mysql.socket
> Version check failed. Got the following error when calling the 'mysql'
> command line client
> ERROR 1049 (42000): Unknown database 'mysql'
> FATAL ERROR: Upgrade failed
> 
> Had to create DB mysql first (CREATE DATABASE mysql;). This fixed the
> problem and upgrade succeeded. Looks like KMail is working fine so far.
Comment 11 Luca Weiss 2019-07-30 19:30:15 UTC
*** Bug 409403 has been marked as a duplicate of this bug. ***
Comment 12 Justin T. Frisch 2019-08-06 10:00:37 UTC
(In reply to Thomas Fischer from comment #10)
> mysql -S /run/user/$(id -u)/akonadi/default/mysql.socket  -e 'create
> database mysql' && mysql_upgrade -S /run/user/$(id
> -u)/akonadi/default/mysql.socket

I had the same issues with mariadb-10.4.6 under ARCH and running those commands fixed the problems for me aswell.
Comment 13 Christophe Marin 2021-03-18 15:44:04 UTC
Git commit 88a3e25fdaae6f0bd05f552e3f9300207ab751bf by Christophe Giboudeaux.
Committed on 18/03/2021 at 15:43.
Pushed by cgiboudeaux into branch 'release/21.04'.

Allow setting the MySQL scripts location

Some of the MySQL tools are simple shell scripts and may be installed in custom locations.
We now have a MYSQLD_SCRIPTS_PATH variable that can be set to point to the scripts dir.

M  +10   -0    CMakeLists.txt
M  +5    -0    src/server/CMakeLists.txt
M  +3    -0    src/server/storage/dbconfigmysql.cpp

https://invent.kde.org/pim/akonadi/commit/88a3e25fdaae6f0bd05f552e3f9300207ab751bf