[arch-general] maria update to 10.4.6 breaks akonadi's db
Updated three of my KDE clients by terminal (not logged in by display manager/DM) and ran # systemctl restart mariadb.service && mariadb-upgrade -u root -p as stated by pacman and arch-acnnounce. All went fine so far. No client starts or uses a mariadb service. But after logging in at DM and starting Kontact the drama begins: New Mails are shown but no one can be read and journal is showing lots of Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: DATABASE ERROR: Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: Error code: "1452" Jun 28 05:49:17 zotac3 akonadiserver[5774]: 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)" Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: Error text: "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) QMYSQL3: Unable to execute statement" Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: Values: QMap((":0", QVariant(qlonglong, 345491))(":1", QVariant(qlonglong, 9))(":2", QVariant(QByteArray, "Return-Path: <receiver@bla.com>\r\nDelivered-To: copyrec@bla.com\r\nReceived: from localhost (localhost [127.0.0.1])\r\n\tby mail.sender.com (Postfix) with ESMTP id 6E9EF624B8;\r\n\tFri, 28 Jun 2019 05:49:16 +0200 (CEST)\r\nDKIM-Filter: OpenDKIM Filter v2.11.0 mail.sender.com 6E9EF624B8\r\nDKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sender.com;\r\n\ts=mail; [...] Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: Query: "INSERT INTO PartTable (pimItemId, partTypeId, data, datasize, version) VALUES (:0, :1, :2, :3, :4)" Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: Error during insertion into table "PartTable" "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) QMYSQL3: Unable to execute statement" Jun 28 05:49:17 zotac3 akonadiserver[5774]: org.kde.pim.akonadiserver: Error while handling command CreateItem on connection akonadi_imap_resource_10 (0x55f027a24a20) Jun 28 05:49:17 zotac3 akonadi_imap_resource[1543]: org.kde.pim.akonadicore: Creating/updating items from the akonadi database failed: "Failed to insert new part into database." Jun 28 05:49:17 zotac3 akonadi_imap_resource[1543]: org.kde.pim.akonadicore: Error during ItemSync: "Failed to insert new part into database." ~/.local/share/akonadi/db_data/mysql.err is showing continuously 2019-06-28 6:14:21 10 [ERROR] Transaction not registered for MariaDB 2PC, but transaction is active Stopping akonadi and therefore mariadb, then downgrading to mariadb* 10.3.16-1 helped. Looks like something's not really going together there yet. - Oliver
On Fri, 28 Jun 2019, at 07:41, Oliver Jaksch via arch-general wrote:
Updated three of my KDE clients by terminal (not logged in by display manager/DM) and ran
# systemctl restart mariadb.service && mariadb-upgrade -u root -p
This doesn't affect akonadi's data since it is located someplace else at a non-system location: /usr/bin/mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf --datadir=$HOME/.local/share/akonadi/db_data/ --s ocket=/tmp/akonadi-xxx.LdhzVw/mysql.socket --pid-file=/tmp/akonadi-xxx.LdhzVw/mysql.pids I suggest you look into actually upgrading akonadi's DB first. For that, you probably can pass --defaults-file and --datadir as-is to mariadb-upgrade (and the upgrade should be executed as the user akonadi is running as, not root).
On Friday, 28 June 2019, 07:49:16 CEST you wrote:
On Fri, 28 Jun 2019, at 07:41, Oliver Jaksch via arch-general wrote:
Updated three of my KDE clients by terminal (not logged in by display manager/DM) and ran
# systemctl restart mariadb.service && mariadb-upgrade -u root -p
This doesn't affect akonadi's data since it is located someplace else at a non-system location:
/usr/bin/mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf --datadir=$HOME/.local/share/akonadi/db_data/ --s ocket=/tmp/akonadi-xxx.LdhzVw/mysql.socket --pid-file=/tmp/akonadi-xxx.LdhzVw/mysql.pids
Yes, I know that, but I thought that akonadi would run a similar upgrade by itself when started and necessary. Wasn't that the case in the past?
I suggest you look into actually upgrading akonadi's DB first. For that, you probably can pass --defaults-file and --datadir as-is to mariadb-upgrade (and the upgrade should be executed as the user akonadi is running as, not root).
Thanks for that good starting point. But, alas, it gives me a # mysql_upgrade: the '--datadir' option is always ignored ...but this gives me hope for further investigations (=search engine). Will try and report; chances are good as it's friday and it's quiet today :)
Little progress... The man of mysql_upgrade states it: --datadir=path Old option accepted for backward compatibility but ignored So I did a fresh login and let akonadi/mariadb starts. I then stopped akonadi and re-used it's socket: # mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf -- datadir=$HOME/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-XXX.n8sdoz/ mysql.socket --pid-file=/tmp/akonadi-XXX.n8sdoz/mysql.pid I can connect to the mariadb instance now and let some commands fly, according to https://mariadb.com/kb/en/library/mysql_upgrade/ : # mysqlcheck --no-defaults --check-upgrade --auto-repair --all-databases -- socket=/tmp/akonadi-XXX.n8sdoz/mysql.socket # mysqlcheck --no-defaults --all-databases --fix-db-names --fix-table-names -- write-binlog --socket=/tmp/akonadi-XXX.n8sdoz/mysql.socket # mysqlcheck --no-defaults --check-upgrade --all-databases --auto-repair -- write-binlog --socket=/tmp/akonadi-XXX.n8sdoz/mysql.socket The output is always
akonadi.collectionattributetable OK akonadi.collectionmimetyperelation OK akonadi.collectionpimitemrelation OK akonadi.collectiontable OK akonadi.flagtable OK akonadi.mimetypetable OK akonadi.parttable OK akonadi.parttypetable OK akonadi.pimitemflagrelation OK akonadi.pimitemtable OK akonadi.pimitemtagrelation OK akonadi.relationtable OK akonadi.relationtypetable OK akonadi.resourcetable OK akonadi.schemaversiontable OK akonadi.tagattributetable OK akonadi.tagremoteidresourcerelationtable OK akonadi.tagtable OK akonadi.tagtypetable OK
But when ending this mariadb instance and restarting akonadi, the horror of errors from post #1 starts all over again :( On Friday, 28 June 2019, 08:55:24 CEST you wrote:
On Friday, 28 June 2019, 07:49:16 CEST you wrote:
On Fri, 28 Jun 2019, at 07:41, Oliver Jaksch via arch-general wrote:
Updated three of my KDE clients by terminal (not logged in by display manager/DM) and ran
# systemctl restart mariadb.service && mariadb-upgrade -u root -p
This doesn't affect akonadi's data since it is located someplace else at a
non-system location: /usr/bin/mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf
--datadir=$HOME/.local/share/akonadi/db_data/ --s ocket=/tmp/akonadi-xxx.LdhzVw/mysql.socket --pid-file=/tmp/akonadi-xxx.LdhzVw/mysql.pids
Yes, I know that, but I thought that akonadi would run a similar upgrade by itself when started and necessary. Wasn't that the case in the past?
I suggest you look into actually upgrading akonadi's DB first. For that, you probably can pass --defaults-file and --datadir as-is to mariadb-upgrade (and the upgrade should be executed as the user akonadi is running as, not root).
Thanks for that good starting point. But, alas, it gives me a # mysql_upgrade: the '--datadir' option is always ignored
...but this gives me hope for further investigations (=search engine). Will try and report; chances are good as it's friday and it's quiet today :)
The solution found at https://bbs.archlinux.org/viewtopic.php?id=184192 fixed 'em all. Tried tipp #1 (backup, restore, upgrade) on two machines, which worked impeccably, but resetted some settings of kmail to default. Tipp #2 (create db mysql, upgrade) on the 3rd machine was the easiest one and left akonadi usable in a sec. No more errors nor problems left; problem was the missing of db mysql/* inside ~/.local/share/akonadi/db_data/ On Friday, 28 June 2019, 10:12:36 CEST you wrote:
Little progress... The man of mysql_upgrade states it:
--datadir=path Old option accepted for backward compatibility but ignored
So I did a fresh login and let akonadi/mariadb starts. I then stopped akonadi and re-used it's socket: # mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf -- datadir=$HOME/.local/share/akonadi/db_data/ --socket=/tmp/akonadi-XXX.n8sdoz/ mysql.socket --pid-file=/tmp/akonadi-XXX.n8sdoz/mysql.pid
I can connect to the mariadb instance now and let some commands fly, according to https://mariadb.com/kb/en/library/mysql_upgrade/ :
# mysqlcheck --no-defaults --check-upgrade --auto-repair --all-databases -- socket=/tmp/akonadi-XXX.n8sdoz/mysql.socket
# mysqlcheck --no-defaults --all-databases --fix-db-names --fix-table-names -- write-binlog --socket=/tmp/akonadi-XXX.n8sdoz/mysql.socket
# mysqlcheck --no-defaults --check-upgrade --all-databases --auto-repair -- write-binlog --socket=/tmp/akonadi-XXX.n8sdoz/mysql.socket
The output is always
akonadi.collectionattributetable OK akonadi.collectionmimetyperelation OK akonadi.collectionpimitemrelation OK akonadi.collectiontable OK akonadi.flagtable OK akonadi.mimetypetable OK akonadi.parttable OK akonadi.parttypetable OK akonadi.pimitemflagrelation OK akonadi.pimitemtable OK akonadi.pimitemtagrelation OK akonadi.relationtable OK akonadi.relationtypetable OK akonadi.resourcetable OK akonadi.schemaversiontable OK akonadi.tagattributetable OK akonadi.tagremoteidresourcerelationtable OK akonadi.tagtable OK akonadi.tagtypetable OK
But when ending this mariadb instance and restarting akonadi, the horror of errors from post #1 starts all over again :(
On Friday, 28 June 2019, 08:55:24 CEST you wrote:
On Friday, 28 June 2019, 07:49:16 CEST you wrote:
On Fri, 28 Jun 2019, at 07:41, Oliver Jaksch via arch-general wrote:
Updated three of my KDE clients by terminal (not logged in by display manager/DM) and ran
# systemctl restart mariadb.service && mariadb-upgrade -u root -p
This doesn't affect akonadi's data since it is located someplace else at a
non-system location: /usr/bin/mysqld --defaults-file=$HOME/.local/share/akonadi/mysql.conf
--datadir=$HOME/.local/share/akonadi/db_data/ --s ocket=/tmp/akonadi-xxx.LdhzVw/mysql.socket --pid-file=/tmp/akonadi-xxx.LdhzVw/mysql.pids
Yes, I know that, but I thought that akonadi would run a similar upgrade by itself when started and necessary. Wasn't that the case in the past?
I suggest you look into actually upgrading akonadi's DB first. For that, you probably can pass --defaults-file and --datadir as-is to mariadb-upgrade (and the upgrade should be executed as the user akonadi is running as, not root).
Thanks for that good starting point. But, alas, it gives me a # mysql_upgrade: the '--datadir' option is always ignored
...but this gives me hope for further investigations (=search engine). Will try and report; chances are good as it's friday and it's quiet today :)
Le vendredi 28 juin 2019 12:10:43 CEST Oliver Jaksch via arch-general a écrit :
The solution found at https://bbs.archlinux.org/viewtopic.php?id=184192 fixed 'em all.
Just a sidenote. New users just can't use akonadi with this new mysql. Create a new user with an empty home directory, login and start akonadi in a console, there are tons of errors like : ... org.kde.pim.akonadiserver: DATABASE ERROR: org.kde.pim.akonadiserver: Error code: "1452" org.kde.pim.akonadiserver: DB error: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelatio n`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDAT E CASCADE)" org.kde.pim.akonadiserver: Error text: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelati on`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDA TE CASCADE) QMYSQL3: Unable to execute statement" org.kde.pim.akonadiserver: Values: QMap((":0", QVariant(qlonglong, 2))(":1", QVariant(qlonglong, 2))) org.kde.pim.akonadiserver: Query: "INSERT INTO CollectionMimeTypeRelation (Collection_id, MimeType_id) VALUES (:0, :1)" org.kde.pim.akonadiserver: Error during adding a record to table "CollectionMimeTypeRelation" org.kde.pim.akonadiserver: Failed to append mimetype "application/x- vnd.akonadi.calendar.event" to Collection 2 org.kde.pim.akonadiserver: Failed to append mimetypes ("application/x- vnd.akonadi.calendar.event") to new collection "Birthdays & Anniversaries " (ID 2 ) in resource "akonadi_birthdays_resource" org.kde.pim.akonadiserver: Error while handling command CreateCollection on connection akonadi_birthdays_resource (0x56188ad4b440) org.kde.pim.akonadiserver: DATABASE ERROR: org.kde.pim.akonadiserver: Error code: "1452" ... Until KDE devs consider this newer version of mysql. Regards.
Interesting...just tried that with my wife's account she never used on my box: I've started _kontact_, which askes users account detail an so on. After finishing that I receive the same errors - but if I use the 2nd "fix" of the given URL then, and after restarting akonadi (akonadictl restart), everything is up and okay. In short: # mysql --socket=/tmp/akonadi-USERID.XXXXXX/mysql.socket -e 'create database mysql' # mysql_upgrade --socket=/tmp/akonadi-USERID.XXXXXX/mysql.socket # akonadictl restart And yes, this should by fixed by the kde devs, definitively. On Sunday, 30 June 2019, 10:51:36 CEST you wrote:
Le vendredi 28 juin 2019 12:10:43 CEST Oliver Jaksch via arch-general a écrit
The solution found at https://bbs.archlinux.org/viewtopic.php?id=184192 fixed 'em all.
Just a sidenote.
New users just can't use akonadi with this new mysql. Create a new user with an empty home directory, login and start akonadi in a console, there are tons of errors like :
... org.kde.pim.akonadiserver: DATABASE ERROR: org.kde.pim.akonadiserver: Error code: "1452" org.kde.pim.akonadiserver: DB error: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelatio n`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDAT E CASCADE)" org.kde.pim.akonadiserver: Error text: "Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`collectionmimetyperelati on`, CONSTRAINT `collectionmimetyperelation_ibfk_1` FOREIGN KEY (`Collection_id`) REFERENCES `collectiontable` (`id`) ON DELETE CASCADE ON UPDA TE CASCADE) QMYSQL3: Unable to execute statement" org.kde.pim.akonadiserver: Values: QMap((":0", QVariant(qlonglong, 2))(":1", QVariant(qlonglong, 2))) org.kde.pim.akonadiserver: Query: "INSERT INTO CollectionMimeTypeRelation (Collection_id, MimeType_id) VALUES (:0, :1)" org.kde.pim.akonadiserver: Error during adding a record to table "CollectionMimeTypeRelation" org.kde.pim.akonadiserver: Failed to append mimetype "application/x- vnd.akonadi.calendar.event" to Collection 2 org.kde.pim.akonadiserver: Failed to append mimetypes ("application/x- vnd.akonadi.calendar.event") to new collection "Birthdays & Anniversaries " (ID 2 ) in resource "akonadi_birthdays_resource" org.kde.pim.akonadiserver: Error while handling command CreateCollection on connection akonadi_birthdays_resource (0x56188ad4b440) org.kde.pim.akonadiserver: DATABASE ERROR: org.kde.pim.akonadiserver: Error code: "1452" ...
Until KDE devs consider this newer version of mysql.
Regards.
Le dimanche 30 juin 2019 11:48:20 CEST Oliver Jaksch via arch-general a écrit :
In short: # mysql --socket=/tmp/akonadi-USERID.XXXXXX/mysql.socket -e 'create database mysql' # mysql_upgrade --socket=/tmp/akonadi-USERID.XXXXXX/mysql.socket # akonadictl restart
Tried that fix on my test account and it simply worked. I filed this bug regarding akonotes yesterday, and realize now that it's related to the new mysql version. Let's hope akonadi devs come around this issue.
On Sunday, 30 June 2019, 13:54:15 CEST you wrote:
Le dimanche 30 juin 2019 11:48:20 CEST Oliver Jaksch via arch-general a écrit
In short: # mysql --socket=/tmp/akonadi-USERID.XXXXXX/mysql.socket -e 'create database mysql' # mysql_upgrade --socket=/tmp/akonadi-USERID.XXXXXX/mysql.socket # akonadictl restart
Tried that fix on my test account and it simply worked. I filed this bug regarding akonotes yesterday, and realize now that it's related to the new mysql version. Let's hope akonadi devs come around this issue.
Great to hear, well done! (I thought about filing this bug, too, but wasn't sure _which_ package to complain...akonadi? Kontact? Kmail?)
participants (3)
-
Jens John
-
Oliver Jaksch
-
SET