Bug 354536

Summary:	Akonadi fails to migrate database from version 30 (foreign key constraint fails)
Product:	[Frameworks and Libraries] Akonadi	Reporter:	Lukáš Karas <lukas.karas>
Component:	Migration	Assignee:	kdepim bugs <kdepim-bugs>
Status:	RESOLVED FIXED
Severity:	normal	CC:	ab4bd, auxsvr, dvratil, martin+kde, tpr
Priority:	NOR
Version:	GIT (master)
Target Milestone:	---
Platform:	Kubuntu
OS:	Linux
Latest Commit:	http://commits.kde.org/akonadi/989f7eba0d1b093e00730a8444a9c088a3805ffd	Version Fixed In:	15.12.3
Attachments:	dbupdate.patch

Description Lukáš Karas 2015-10-29 07:48:52 UTC

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)




Reproducible: Always

Steps to Reproduce:
akonadictl start


Actual Results:  
database schema upgrade fails

Expected Results:  
akonadi is running

Migration tool should delete useless rows from PimItemFlagRelation before it adds new constrains.

Comment 1 Lukáš Karas 2015-10-29 10:37:29 UTC

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?

Comment 2 Lukáš Karas 2015-10-30 12:42:24 UTC

Created attachment 95224 [details]
dbupdate.patch

This patch with extra update queries fixes migration for me.

Comment 3 Martin Bednar 2015-12-13 20:37:51 UTC

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)

Comment 4 auxsvr 2015-12-21 11:08:31 UTC

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).

Comment 5 Teemu Rytilahti 2015-12-30 15:20:30 UTC

I can confirm this on Arch with Mariadb, with Akonadi 15.12.0-1 and Mariadb 10.1.10-1.

Comment 6 Daniel Vrátil 2016-01-03 17:23:00 UTC

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)

Comment 7 Lukáš Karas 2016-01-04 08:46:27 UTC

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...

Comment 8 Daniel Vrátil 2016-03-11 00:10:45 UTC

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.
FIXED-IN: 15.12.3

M  +60   -0    src/server/storage/dbupdate.xml

http://commits.kde.org/akonadi/989f7eba0d1b093e00730a8444a9c088a3805ffd

Comment 9 Daniel Vrátil 2016-03-11 00:13:26 UTC

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!