| Summary: | MySQL Schema upgrade from V7 to V8 fails [patch] | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | Ian Dall <ian> |
| Component: | Database-Mysql | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | caulier.gilles, metzpinguin, swatilodha27 |
| Priority: | NOR | ||
| Version: | 5.0.0 | ||
| Target Milestone: | --- | ||
| Platform: | Compiled Sources | ||
| OS: | Linux | ||
| Latest Commit: | http://commits.kde.org/digikam/882411060361ff183b770d63db1ded8c55fe0643 | Version Fixed In: | 5.1.0 |
| Sentry Crash Report: | |||
| Attachments: |
Modified dbconfig.xml
Patch for testing schema2.patch To move from V7 to V8 |
||
Maik, Could you please tell if it's reproducible for you? With the same queries, I was able to make an update from v7 to v8, which works fine now. Swati Yes, it is definitely reproducible. It might be MySQL version specific though. I can see it is at least possible that later versions of MySQL would allow you to use DROP KEY to drop a key and any constraints associated with it. (In reply to Ian Dall from comment #2) > Yes, it is definitely reproducible. It might be MySQL version specific > though. I can see it is at least possible that later versions of MySQL would > allow you to use DROP KEY to drop a key and any constraints associated with > it. I created a random test database with just 2 tables and foreign key constraint. I tried "DROP KEY" and it didn't work for me. I use MariaDB 10.1 Strange, that it didn't cause any error while switching from v7 to v8.... Created attachment 100463 [details]
Patch for testing
Created attachment 100472 [details]
schema2.patch
I have created a new patch from the dbconfig.xml. This patch works here. Swati, look at the end of the patch where it failed with KEY u_tag. I can the KEY u_tag throughout DB schema not find?
Maik
Created attachment 100480 [details]
To move from V7 to V8
Maik,
I tested your last patch. It works fine for me too. I've successfully moved from version 7 to 8.
Won't the UNIQUE constraint be required for existing DB, upgrading version?
Swati, Maik, 5.1.0 will be released normally tomorrow evening. Any chance to see this patch applied to git/master before the release ? Gilles Since this patch works, I think this could be applied. Still I would like Maik to confirm the same. I just check the patch again step by step and will then apply it. Maik Git commit 882411060361ff183b770d63db1ded8c55fe0643 by Maik Qualmann. Committed on 06/08/2016 at 19:47. Pushed by mqualmann into branch 'master'. add changes from Ian Dall to fix MySQL schema upgrade from V7 to V8 FIXED-IN: 5.1.0 M +2 -1 NEWS M +44 -10 data/database/dbconfig.xml.cmake.in http://commits.kde.org/digikam/882411060361ff183b770d63db1ded8c55fe0643 I think I can add some insight into why this worked for some and not others. I noticed in the MySQL documentation that ROLLBACK does not roll back ALTER commands! V7 did not have any foreign key constraints. In my case, the upgrade failed, probably due to constraint violations (eg ImageInformation rows referring to Image rows which had been deleted), but the ALTER commands to create the foreign constraint constraints up to that point worked, and were not rolled back. The next time I tried to upgrade, the foreign key constraint already existed, so DROP KEY didn't work and the the script failed early. Someone else, without cruft in their V7 database, would have succeeded first time. I am unsure whether my patches work with a clean V7 database. I guess dropping a constraint that doesn't exist may fail. I would test this, (I did back up my V7 database before doing all this) but I would have to clean the cruft again, which is a non-trivial process because I did it by hand. Sounds like that could probably be an issue. You could refer to comments in other Bugzilla entry https://bugs.kde.org/show_bug.cgi?id=355831#c73 .Go through 2-3 attachments that might help you to detect invalid data before updating. |
Created attachment 100460 [details] Modified dbconfig.xml After compiling and installing 5.1.0, running it throws up an error, "Failed to update the database schema from version 7 to version 8." I have been able to make this work with some changes to dbconfig.xml and also some manual tweaking to the database. The issues with dbconfig.xml are: you can't use (for example) "DROP KEY albumRoot" to delete a foreign key constraint, you have to use "DROP FOREIGN KEY Albums_AlbumRoots" for example; some tables already have a foreign key but there is no DROP FOREIGN KEY (or DROP KEY) statement; there is a typo in the code to update MIN(lft) and MAX(rgt) values for the Tags table; and finally for MySQL prior to 5.6 (I've got 5.5) you need to do the DROP FOREIGN KEY in a separate statement to adding the new foreign key constraint. In my case, the database was a bit of a mess with (for example) ImageInformation rows for Images which no longer exist, so the foreign key constraint is violated. I fixed this by hand, but it would be possible to make a helper script which could find these violations and give the user the option to fix them by deleting the offending entries.