[Akonadi] [Bug 354536] New: Akonadi fails to migrate database from version 30 (foreign key constraint fails)

[Lukáš Karas via KDE Bugzilla]

https://bugs.kde.org/show_bug.cgi?id=354536

            Bug ID: 354536
           Summary: Akonadi fails to migrate database from version 30
                    (foreign key constraint fails)
           Product: Akonadi
           Version: GIT (master)
          Platform: Kubuntu Packages
                OS: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Migration
          Assignee: kdepim-bugs at kde.org
          Reporter: lukas.karas at centrum.cz

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.

-- 
You are receiving this mail because:
You are the assignee for the bug.