FS#63025 - [akonadi] "Cannot add or update a child row: a foreign key constraint[...]" with MariaDB 10.4.6

Attached to Project: Arch Linux
Opened by Marius (Martchus) - Thursday, 27 June 2019, 18:57 GMT
Last edited by Antonio Rojas (arojas) - Thursday, 04 July 2019, 21:32 GMT
Task Type Bug Report
Category Packages: Testing
Status Closed
Assigned To No-one
Architecture All
Severity Low
Priority Normal
Reported Version
Due in Version Undecided
Due Date Undecided
Percent Complete 100%
Votes 5
Private No

Details

Description:

Akonadi produces a lot of errors like when using MariaDB 10.4.6:
```
org.kde.pim.akonadiserver: DB error: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`parttable`, CONSTRAINT `parttable_ibfk_1` FOREIGN KEY (`pimItemId`) REFERENCES `pimitemtable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)"
```

This error prevents receiving any new mails via KMail.

Additional info:
* Package versions: akonadi 19.04.2-4, kmail 19.04.2-2, mariadb 10.4.6-1, qt5-base 5.13.0-4/5.13.0-3
* Seems like the same is happening under Geentoo: https://bugs.gentoo.org/688746
* Not sure whether it is an Akonadi bug or a MariaDB bug. Maybe just a missing database migration?

Steps to reproduce:

Have Akonadi/KMail configured as usual and MariaDB 10.4.6 installed. No new mails can be received
anymore. The error message is observable by restarting Akonadi via `akonadictl`.


Workaround: downgrade MariaDB to 10.3.16-1

```
sudo pacman -U $pacman_cache/mariadb-10.3.16-1-x86_64.pkg.tar.xz $pacman_cache/mariadb-clients-10.3.16-1-x86_64.pkg.tar.xz $pacman_cache/mariadb-libs-10.3.16-1-x86_64.pkg.tar.xz
akonadictl stop
akonadictl start
# dismiss mail notifications :-)
```
This task depends upon

Closed by  Antonio Rojas (arojas)
Thursday, 04 July 2019, 21:32 GMT
Reason for closing:  Fixed
Additional comments about closing:  Fixed with database migration
Comment by A. Bosch (progandy) - Friday, 28 June 2019, 08:29 GMT
Here is the upstream akonadi report:
https://bugs.kde.org/show_bug.cgi?id=409224

It seems that akonadi is not the only affected application:
https://github.com/home-assistant/home-assistant/issues/24721
Comment by Georg Wolfram (Gosi) - Friday, 28 June 2019, 22:59 GMT
> #create mysql DB if does not exist
> mysql --socket=/tmp/akodbbkp-userid-mysql.socket -e 'create database mysql'
> mysql_upgrade --socket=/tmp/akonadi-userid.XXXXX/mysql.socket
<https://bbs.archlinux.org/viewtopic.php?pid=1435801#p1435801>

This fixed it for me.
Comment by Sascha (Moxon) - Tuesday, 02 July 2019, 14:49 GMT
I have the same problem and successfully tried both workarounds:

. Downgrading works just fine
. upgrading again and using Georgs instructions worked fine too (after rebooting)
Comment by Marius (Martchus) - Tuesday, 02 July 2019, 14:56 GMT
Yes, it seems this does the trick. I guess I'm going to run the following on my machines then:

```
find /tmp -ipath "/tmp/akonadi-$USER.*/mysql.socket" -exec mysql --socket={} -e 'create database mysql' \; -exec mysql_upgrade --socket={} \;
akonadictl restart
```

There is no need to restart KMail.

So I guess this ticket can be closed. After all it was just a missing migration like I have already suspected. However, it would be nice if it was mentioned similar to the "regular" database migration (https://www.archlinux.org/news/mariadb-104x-update-requires-manual-intervention) because only executing `systemctl restart mariadb.service && mariadb-upgrade -u root -p` does not affect the MariaDB instance used by Akonadi.

Loading...