| Summary: | MIGRATION: MySQL to SQLite fails about Tags tree [patch] | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | mhoppstaedter |
| Component: | Database-Migration | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | CC: | caulier.gilles, metzpinguin, mhoppstaedter, peter.m.bauer, philippe.roubach, richm+kde, swatilodha27 |
| Priority: | NOR | ||
| Version: | 4.11.0 | ||
| Target Milestone: | --- | ||
| Platform: | Kubuntu | ||
| OS: | Linux | ||
| Latest Commit: | https://commits.kde.org/digikam/9a17c743ea9a41c1ef050b08517343813ef6a9f4 | Version Fixed In: | 5.8.0 |
| Sentry Crash Report: | |||
| Attachments: |
screenshot from error message
To migrate from MySQL to SQLite |
||
*** Bug 329849 has been marked as a duplicate of this bug. *** *** Bug 361809 has been marked as a duplicate of this bug. *** This file still valid using last digiKam 5.0.0 ? Gilles Caulier digiKam 5.0.0 is per default compiled without the experimental MySQL support so the migration is not possible anyway. It would be very helpful to have a working migration to SQLite in version 4 so people can use Digikam with SQLite until the rewritten MySQL support is ready. Mysql support is just an option to turn on at compilation time through cmake... It's explained in README. Gilles Caulier Of course, but for e.g. Fedora 24 it is not turned on so the people currently using MySQL have to migrate to SQLite for 5.0.0. Is the "experimental" MySQL support supposed to be stable enough for performing the migration to SQLite or should the migration be done using version 4.x? Created attachment 100317 [details]
To migrate from MySQL to SQLite
Peter, Do not use 4.x to migrate. Swati currently to fix code relevant in 5.x Gilles Caulier Swati, With your patch, are you able to migrate code without error with Tags table ? Gilles Caulier Richard, Swati has proposed a simple patch for this file. Any viewpoint about ? Gilles Caulier (In reply to caulier.gilles from comment #10) > Richard, > > Swati has proposed a simple patch for this file. Any viewpoint about ? > > Gilles Caulier I've been discussing the issue with Swati via email today. I don't think that the patch is a fix for the problem. The TagsTree is getting migrated wrongly. The attempt to remove the triggers was just to show that the trigger was definitely causing the problem. I can see a number of issues: First off the Tags migration is potentially broken because to work properly with referential integrity the entries need to be migrated from the root of the tree outwards. Without that the parent references may not be present when a child is added. In most cases things will work out fine because the parent will likely have a lower numbered primary key than the child and hence sorting by id will make things better. But it is easy to see how things might go wrong if tags are moved around in the tree. It is easy to detect by looking for entries where pid > id. But at present things may be broken. Secondly it seems that either duplicate TagsTree entries are getting added or maybe somehow not getting added correctly. This needs investigation. It should be fairly simple to do because it seems that things can be reproduced easily so it will be possible to use the sqlite3 commandline to investigate the state of the database when things go wrong. I'm actually wondering if the add_tag (and other) trigger is getting added to the database twice. I think I remember that SQLite can have multiple triggers on one table so maybe there are two triggers trying to add an entry into the TagsTree table. Looking at the coreschemaupdater.cpp file there are two places where createTriggers() is called so that may be relevant. Note I only did a very quick look at the source so it might be a misleading idea. But I think there is more investigation that needs doing to find the cause of the problem. (In reply to caulier.gilles from comment #9) > Swati, > > With your patch, are you able to migrate code without error with Tags table ? Yes, code is migrated without error but there's issue with the TagsTree table since trigger is removed. So as Richard said, this is definitely a wrong way to remove insert_tagstree trigger. (In reply to Richard Mortimer from comment #11) ...(skip) > > Secondly it seems that either duplicate TagsTree entries are getting added > or maybe somehow not getting added correctly. This needs investigation. It > should be fairly simple to do because it seems that things can be reproduced > easily so it will be possible to use the sqlite3 commandline to investigate > the state of the database when things go wrong. > > I'm actually wondering if the add_tag (and other) trigger is getting added > to the database twice. I think I remember that SQLite can have multiple > triggers on one table so maybe there are two triggers trying to add an entry > into the TagsTree table. Using sqlite browser, I looked at the database tables for SQLite and found that in TagsTree table, each tag is actually added TWICE. I guess this is the reason why UNIQUE constraint fails while migrating to SQLite... > Looking at the coreschemaupdater.cpp file there are two places where > createTriggers() is called so that may be relevant. Note I only did a very > quick look at the source so it might be a misleading idea. But I think there > is more investigation that needs doing to find the cause of the problem. Yes, the first one while creating database, and second in a version update. But in the update step, trigger is first deleted and then again created, so I guess this is fine? Git commit 9a17c743ea9a41c1ef050b08517343813ef6a9f4 by Maik Qualmann. Committed on 25/11/2017 at 22:02. Pushed by mqualmann into branch 'master'. update DB to v9 and fix migration from SQLite to MySQL Related: bug 378838, bug 369083, bug 325654 FIXED-IN: 5.8.0 M +5 -1 NEWS M +129 -97 data/database/dbconfig.xml.cmake.in M +1 -1 data/database/mysql-global.conf M +1 -20 libs/database/coredb/coredbcopymanager.cpp M +5 -2 libs/database/coredb/coredbschemaupdater.cpp https://commits.kde.org/digikam/9a17c743ea9a41c1ef050b08517343813ef6a9f4 (In reply to Maik Qualmann from comment #14) > Git commit 9a17c743ea9a41c1ef050b08517343813ef6a9f4 by Maik Qualmann. > Committed on 25/11/2017 at 22:02. > Pushed by mqualmann into branch 'master'. > > update DB to v9 and fix migration from SQLite to MySQL > Related: bug 378838, bug 369083, bug 325654 > FIXED-IN: 5.8.0 > > M +5 -1 NEWS > M +129 -97 data/database/dbconfig.xml.cmake.in > M +1 -1 data/database/mysql-global.conf > M +1 -20 libs/database/coredb/coredbcopymanager.cpp > M +5 -2 libs/database/coredb/coredbschemaupdater.cpp > > https://commits.kde.org/digikam/9a17c743ea9a41c1ef050b08517343813ef6a9f4 Is there a reason why the committed patch removes all of the careful filtering of orphan records from old broken databases that were being migrated from pre-referential integrity schema? There was lots of careful filtering via LEFT JOIN and IS NOT NULL to ensure that everything that was migrated was connected to an active ALBUM root. I was actually thinking that the same needed doing when using a SQLite database as source and haven't seen anything to suggest that it was a bad idea. Indeed it fixed a lot of migration complaints. I'm also wondering why the migration has been changed to only migrate "Locale" from the settings? There is no reference to a bug/reason for that. I cannot tell if that slipped in by mistake or not. Hello Richard, the reason is because some tables, even though the data was correct, were not copied at all. These included ImageComments, ImageCopyright and ImageRelations. But I will test the filters step by step and then bring them back. The Settings table was not copied at all before because it was commented out in coredbcopymanager.cpp. DigiKam recreates the entries, at the moment I only consider the Locale as important. Maik |
Created attachment 93722 [details] screenshot from error message I want to convert my MySQL database to SQLite. But after a long time of conversion the migration breaks up with error 'Fehler beim Konvertieren der Datenbank. Details: UNIQUE constraint failed: Tags.name, Tags.pid'. I've started DK from command line but there is no hint what went wrong. snake@snakebox:~$ digikam digikam(10688)/digikam (core): Could not find parent with url: "/00_Fotos/00_Inc2/ABI 91/Lehrer" for: "/00_Fotos/00_Inc2/ABI 91/Lehrer/web" digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2011/0102/Filme/00006.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2011/0806 Abitreffen, Naturfreundehaus Spiesen/Filme/00019.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2011/1224-30/Filme/00022.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2011/1224-30/Filme/00037.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2012/0609 Geburtstagsfeier Lennox, EM Eroeffnungsspiel im Fanblock D/Filme/00112.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2012/Sandra/2012-12-05/mts/00019.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2013/2013-05-30/mts/00055.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2013/2013-12-05/mts/00161.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2013/2013-12-24/mts/00003.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2014/2014-06-06/mts/00000.MTS: The file contains data of an unknown image type digikam(10688)/KEXIV2: Cannot load metadata from file (Error # 11 : /home/snake/Bilder/00_Fotos/2014/2014-12-24/mts/00043.MTS: The file contains data of an unknown image type digikam(10688)/digikam (core) Digikam::DNotificationWrapper: parent is null digikam(10688)/digikam (core) Digikam::ImageInfo::fromLocalFile: No location could be retrieved for "" QFSFileEngine::open: No file name specified QFSFileEngine::open: No file name specified