Hi. I had akonadi installed from Kubuntu repository from package version 15.08.2-0ubuntu1, then I tried to compile and install from git sources (current HEAD is 1d84aca004ae08bf954bd7b9f1e20efe26c910b7 ).
When I start new version of Akonadi, it exited in a moment with message:
Adding new foreign key constraints
"ALTER TABLE PimItemFlagRelation ADD FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE"
Updating index failed:
Sql error: Cannot add or update a child row: a foreign key constraint fails (`akonadi`.`#sql-4c21_3`, CONSTRAINT `#sql-4c21_3_ibfk_1` FOREIGN KEY (`PimItem_id`) REFERENCES `pimitemtable` (`id`) ON DELETE CASCADE ON UPDATE CASCADE) QMYSQL: Unable to execute query
Query: ALTER TABLE PimItemFlagRelation ADD FOREIGN KEY (PimItem_id) REFERENCES PimItemTable(id) ON UPDATE CASCADE ON DELETE CASCADE
Unable to initialize database.
terminating service threads
terminating connection threads
stopping db process
Application 'akonadiserver' exited normally...
Full log: http://pastebin.com/DH1SntcY
It seems that previous version of Akonadi keeps some rows in PimItemFlagRelation table that don't match any row in PimItemTable...
mysql> select * from schemaversiontable ;
| version |
| 30 |
1 row in set (0,00 sec)
mysql> select count(*) from PimItemFlagRelation ;
| count(*) |
| 723468 |
1 row in set (0,18 sec)
mysql> select count(*) from PimItemTable ;
| count(*) |
| 230366 |
1 row in set (0,09 sec)
mysql> select count(*) from PimItemFlagRelation as r left join PimItemTable as p on r.PimItem_id = p.id where p.id is null ;
| count(*) |
| 485150 |
1 row in set (0,91 sec)
Steps to Reproduce:
database schema upgrade fails
akonadi is running
Migration tool should delete useless rows from PimItemFlagRelation before it adds new constrains.
This bug was probably solved for PostgreSQL backend by commit 54e1f0979a894d289110a1a71618ce7be54fe595 ( https://quickgit.kde.org/?p=akonadi.git&a=commit&h=54e1f0979a894d289110a1a71618ce7be54fe595 ). In commit message Dan mentions that it should not happen with mysql backend.
This problem did not affect MySQL which creates the FKs as part of CREATE TABLE
queries, or SQLite because we don't support FKs there (yet).
I don't know why, but I don't have these FK in my database... What changes that FK check works now? Should we add similar DELETE commands to dbupdate.xml for mysql backend? For which db version? 31?
Created attachment 95224 [details]
This patch with extra update queries fixes migration for me.
I think I just got hit by this.
Log here : https://paste.kde.org/paxixlmnn
Using a system-wide mysql server. (also used for web development)
I have the same problem now that I upgraded akonadi to 15.12 on openSUSE Leap 42.1. I fixed it by deleting the non-matching rows with the patch in comment #2 (by the way, the second delete misses "AS id" after flag_id).
I can confirm this on Arch with Mariadb, with Akonadi 15.12.0-1 and Mariadb 10.1.10-1.
Running "akonadictl fsck" will make sure that are no duplicate or invalid entriers. After that the update from revision 30 should be OK.
Note that although "akonadictl fsck" returns immediatelly, the actual task, which is running on the Server may take couple minutes to complete (this was fixed in 15.12)
Good to know that. What about automatic fsck operation before every schema upgrade? We can avoid similar situations in the future. But we should trust fsck code and keep it updated then...
Git commit 989f7eba0d1b093e00730a8444a9c088a3805ffd by Daniel Vrátil, on behalf of Lukáš Karas.
Committed on 11/03/2016 at 00:10.
Pushed by dvratil into branch 'Applications/15.12'.
Clean orphaned relations when upgrading DB schema to r31
Commit 54e1f097 introduced automatic recovery of missing foreign keys during
startup (this has been accompanied by DB schema upgrade to revision 31). If
they FKs were previously missing MySQL will fail to create the keys and Akonadi
startup will fail.
To make sure there are no inconsistencies in DB we manually delete all orphaned
relations before proceeding with the DB check.
M +60 -0 src/server/storage/dbupdate.xml
There seem to be more and more people affected by this as distros slowly adopt KF5-based KDE PIM and I now realized that you can't possibly run akonadictl fsck when Akonadi is not running because your DB is inconsistent :) so I merged the patch from Lukas.
I like the idea about automatically running fsck before schema upgrade, I'll see if we can implement it for 16.04.
Thanks for the patch Lukas!