354536 2015-10-29 07:48:52 +0000 Akonadi fails to migrate database from version 30 (foreign key constraint fails) 2016-03-11 00:13:26 +0000 1 1 3 Frameworks and Libraries Akonadi Migration GIT (master) Kubuntu Linux RESOLVED FIXED NOR normal --- 0 lukas.karas pim-bugs-null ab4bd auxsvr dvratil martin tpr http://commits.kde.org/akonadi/989f7eba0d1b093e00730a8444a9c088a3805ffd 15.12.3 1 oldest_to_newest 1553314 0 lukas.karas 2015-10-29 07:48:52 +0000 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. 1553365 1 lukas.karas 2015-10-29 10:37:29 +0000 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? 1553624 2 95224 lukas.karas 2015-10-30 12:42:24 +0000 Created attachment 95224 dbupdate.patch This patch with extra update queries fixes migration for me. 1563197 3 martin 2015-12-13 20:37:51 +0000 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) 1565047 4 auxsvr 2015-12-21 11:08:31 +0000 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). 1566638 5 tpr 2015-12-30 15:20:30 +0000 I can confirm this on Arch with Mariadb, with Akonadi 15.12.0-1 and Mariadb 10.1.10-1. 1567188 6 dvratil 2016-01-03 17:23:00 +0000 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) 1567273 7 lukas.karas 2016-01-04 08:46:27 +0000 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... 1581740 8 dvratil 2016-03-11 00:10:45 +0000 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 1581741 9 dvratil 2016-03-11 00:13:26 +0000 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! 95224 2015-10-30 12:42:24 +0000 2015-10-30 12:42:24 +0000 dbupdate.patch akonadi.patch text/plain 4107 lukas.karas ZGlmZiAtLWdpdCBhL3NyYy9zZXJ2ZXIvc3RvcmFnZS9kYnVwZGF0ZS54bWwgYi9zcmMvc2VydmVy L3N0b3JhZ2UvZGJ1cGRhdGUueG1sCmluZGV4IGVkNDhiM2UuLjRlMGI4NTQgMTAwNjQ0Ci0tLSBh L3NyYy9zZXJ2ZXIvc3RvcmFnZS9kYnVwZGF0ZS54bWwKKysrIGIvc3JjL3NlcnZlci9zdG9yYWdl L2RidXBkYXRlLnhtbApAQCAtMjU2LDQgKzI1Niw2NCBAQAogICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICBXSEVSRSBQaW1JdGVtVGFibGUuaWQgSVMgTlVMTCkKICAgICA8L3Jhdy1zcWw+ CiAgIDwvdXBkYXRlPgorIDwhLS0KKyAgIFVwZGF0ZSAzMSBmaXhlcyBtaXNzaW5nIGNvbnN0cmFp bnRzIG9uIHJlbGF0aW9uIHRhYmxlcyBpbiBNeVNRTC4KKyAgIFdlIGNhbm5vdCBqdXN0IGxldCBE YkluaXRpYWxpemVyIGNyZWF0ZSB0aGUgZm9yZWlnbiBrZXlzIHRoZXJlLCBiZWNhdXNlIGRhdGEK KyAgIGluIHRoZSByZWxhdGlvbiB0YWJsZXMgbWlnaHQgcmVmZXJlbmNlIHJvd3MgZnJvbSBvdGhl ciB0YWJsZXMgdGhhdCBubyBsb25nZXIKKyAgIGV4aXN0LgorICAtLT4KKyAgPHVwZGF0ZSB2ZXJz aW9uPSIzMSIgYWJvcnRPbkZhaWx1cmU9ImZhbHNlIj4KKyAgICA8IS0tIFBpbUl0ZW1GbGFnUmVs YXRpb24gLS0+CisgICAgPHJhdy1zcWwgYmFja2VuZHM9Im15c3FsIj5ERUxFVEUgRlJPTSBQaW1J dGVtRmxhZ1JlbGF0aW9uIFdIRVJFIHBpbUl0ZW1faWQgSU4gKAorICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICBTRUxFQ1QgaWQgRlJPTSAoCisgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgIFNFTEVDVCBwaW1JdGVtX2lkIEFTIGlkIEZST00gUGltSXRlbUZsYWdSZWxhdGlvbgor ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBMRUZUIEpPSU4gUGltSXRlbVRhYmxlIE9O IFBpbUl0ZW1GbGFnUmVsYXRpb24ucGltSXRlbV9pZCA9IFBpbUl0ZW1UYWJsZS5pZAorICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICBXSEVSRSBQaW1JdGVtVGFibGUuaWQgSVMgTlVMTCkg eCkKKyAgICA8L3Jhdy1zcWw+CisgICAgPHJhdy1zcWwgYmFja2VuZHM9Im15c3FsIj5ERUxFVEUg RlJPTSBQaW1JdGVtRmxhZ1JlbGF0aW9uIFdIRVJFIGZsYWdfaWQgSU4gKAorICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICBTRUxFQ1QgaWQgRlJPTSAoCisgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIFNFTEVDVCBmbGFnX2lkIEZST00gUGltSXRlbUZsYWdSZWxhdGlvbgorICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICBMRUZUIEpPSU4gRmxhZ1RhYmxlIE9OIFBpbUl0 ZW1GbGFnUmVsYXRpb24uZmxhZ19pZCA9IEZsYWdUYWJsZS5pZAorICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICBXSEVSRSBGbGFnVGFibGUuaWQgSVMgTlVMTCkgeCkKKyAgICA8L3Jhdy1z cWw+CisgICAgPCEtLSBQaW1JdGVtVGFnUmVsYXRpb24gLS0+CisgICAgPHJhdy1zcWwgYmFja2Vu ZHM9Im15c3FsIj5ERUxFVEUgRlJPTSBQaW1JdGVtVGFnUmVsYXRpb24gV0hFUkUgcGltSXRlbV9p ZCBJTiAoCisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFNFTEVDVCBpZCBGUk9NICgK KyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgU0VMRUNUIHBpbUl0ZW1faWQgQVMgaWQg RlJPTSBQaW1JdGVtVGFnUmVsYXRpb24KKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg TEVGVCBKT0lOIFBpbUl0ZW1UYWJsZSBPTiBQaW1JdGVtVGFnUmVsYXRpb24ucGltSXRlbV9pZCA9 IFBpbUl0ZW1UYWJsZS5pZAorICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBXSEVSRSBQ aW1JdGVtVGFibGUuaWQgSVMgTlVMTCkgeCkKKyAgICA8L3Jhdy1zcWw+CisgICAgPHJhdy1zcWwg YmFja2VuZHM9Im15c3FsIj5ERUxFVEUgRlJPTSBQaW1JdGVtVGFnUmVsYXRpb24gV0hFUkUgdGFn X2lkIElOICgKKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgU0VMRUNUIGlkIEZST00g KAorICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBTRUxFQ1QgdGFnX2lkIEFTIGlkIEZS T00gUGltSXRlbVRhZ1JlbGF0aW9uCisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIExF RlQgSk9JTiBUYWdUYWJsZSBPTiBQaW1JdGVtVGFnUmVsYXRpb24udGFnX2lkID0gVGFnVGFibGUu aWQKKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgV0hFUkUgVGFnVGFibGUuaWQgSVMg TlVMTCkgeCkKKyAgICA8L3Jhdy1zcWw+CisgICAgPCEtLSBDb2xsZWN0aW9uTWltZVR5cGVSZWxh dGlvbiAtLT4KKyAgICA8cmF3LXNxbCBiYWNrZW5kcz0ibXlzcWwiPkRFTEVURSBGUk9NIENvbGxl Y3Rpb25NaW1lVHlwZVJlbGF0aW9uIFdIRVJFIGNvbGxlY3Rpb25faWQgSU4gKAorICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBTRUxFQ1QgaWQgRlJPTSAoCisgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIFNFTEVDVCBjb2xsZWN0aW9uX2lkIEFTIGlkICBGUk9NIENvbGxlY3Rp b25NaW1lVHlwZVJlbGF0aW9uCisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIExFRlQg Sk9JTiBDb2xsZWN0aW9uVGFibGUgT04gQ29sbGVjdGlvbk1pbWVUeXBlUmVsYXRpb24uY29sbGVj dGlvbl9pZCA9IENvbGxlY3Rpb25UYWJsZS5pZAorICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICBXSEVSRSBDb2xsZWN0aW9uVGFibGUuaWQgSVMgTlVMTCkgeCkKKyAgICA8L3Jhdy1zcWw+ CisgICAgPHJhdy1zcWwgYmFja2VuZHM9Im15c3FsIj5ERUxFVEUgRlJPTSBDb2xsZWN0aW9uTWlt ZVR5cGVSZWxhdGlvbiBXSEVSRSBtaW1lVHlwZV9pZCBJTiAoCisgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIFNFTEVDVCBpZCBGUk9NICgKKyAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgU0VMRUNUIG1pbWVUeXBlX2lkIEFTIGlkIEZST00gQ29sbGVjdGlvbk1pbWVUeXBlUmVs YXRpb24KKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgTEVGVCBKT0lOIE1pbWVUeXBl VGFibGUgT04gQ29sbGVjdGlvbk1pbWVUeXBlUmVsYXRpb24ubWltZVR5cGVfaWQgPSBNaW1lVHlw ZVRhYmxlLmlkCisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFdIRVJFIE1pbWVUeXBl VGFibGUuaWQgSVMgTlVMTCkgeCkKKyAgICA8L3Jhdy1zcWw+CisgICAgPCEtLSBDb2xsZWN0aW9u UGltSXRlbVJlbGF0aW9uIC0tPgorICAgIDxyYXctc3FsIGJhY2tlbmRzPSJteXNxbCI+REVMRVRF IEZST00gQ29sbGVjdGlvblBpbUl0ZW1SZWxhdGlvbiBXSEVSRSBjb2xsZWN0aW9uX2lkIElOICgK KyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgU0VMRUNUIGlkIEZST00gKAorICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICBTRUxFQ1QgY29sbGVjdGlvbl9pZCBBUyBpZCBGUk9N IENvbGxlY3Rpb25QaW1JdGVtUmVsYXRpb24KKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgTEVGVCBKT0lOIENvbGxlY3Rpb25UYWJsZSBPTiBDb2xsZWN0aW9uUGltSXRlbVJlbGF0aW9u LmNvbGxlY3Rpb25faWQgPSBDb2xsZWN0aW9uVGFibGUuaWQKKyAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgV0hFUkUgQ29sbGVjdGlvblRhYmxlLmlkIElTIE5VTEwpIHgpCisgICAgPC9y YXctc3FsPgorICAgIDxyYXctc3FsIGJhY2tlbmRzPSJteXNxbCI+REVMRVRFIEZST00gQ29sbGVj dGlvblBpbUl0ZW1SZWxhdGlvbiBXSEVSRSBwaW1JdGVtX2lkIElOICgKKyAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgU0VMRUNUIGlkIEZST00gKAorICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICBTRUxFQ1QgcGltSXRlbV9pZCBBUyBpZCBGUk9NIENvbGxlY3Rpb25QaW1JdGVt UmVsYXRpb24KKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgTEVGVCBKT0lOIFBpbUl0 ZW1UYWJsZSBPTiBDb2xsZWN0aW9uUGltSXRlbVJlbGF0aW9uLnBpbUl0ZW1faWQgPSBQaW1JdGVt VGFibGUuaWQKKyAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgV0hFUkUgUGltSXRlbVRh YmxlLmlkIElTIE5VTEwpIHgpCisgICAgPC9yYXctc3FsPgorICA8L3VwZGF0ZT4KIDwvdXBkYXRl cz4K