I add tag T1 to pic1 and tag T2 to pic2. When I look for all pics with tag T1, I get pic1 and pic2 -> bug I did the following query on the database to figure out what's going on: select * from ImageTags,Tags where ImageTags.imageid = '326480' and ImageTags.tagid = Tags.id +---------+-------+-----+------+------------------+------+-----------------+-----+-----+ | imageid | tagid | id | pid | name | icon | iconkde | lft | rgt | +---------+-------+-----+------+------------------+------+-----------------+-----+-----+ | 326480 | 16 | 16 | 490 | Ski | 0 | NULL | 943 | 944 | | 326480 | 74 | 74 | 32 | Nathalie | 0 | emblem-favorite | 873 | 874 | | 326480 | 296 | 296 | 34 | Flumserberg | 0 | NULL | 257 | 258 | | 326480 | 435 | 435 | 428 | Color Label None | 0 | NULL | 44 | 45 | | 326480 | 445 | 445 | 428 | Pick Label None | 0 | NULL | 24 | 25 | | 326480 | 518 | 518 | 0 | T1 | 0 | NULL | 1 | 2 | +---------+-------+-----+------+------------------+------+-----------------+-----+-----+ and for the second picture mysql> select * from ImageTags,Tags where ImageTags.imageid = '326481' and ImageTags.tagid = Tags.id ; +---------+-------+-----+------+------------------+------+---------+-----+-----+ | imageid | tagid | id | pid | name | icon | iconkde | lft | rgt | +---------+-------+-----+------+------------------+------+---------+-----+-----+ | 326481 | 16 | 16 | 490 | Ski | 0 | NULL | 943 | 944 | | 326481 | 296 | 296 | 34 | Flumserberg | 0 | NULL | 257 | 258 | | 326481 | 435 | 435 | 428 | Color Label None | 0 | NULL | 44 | 45 | | 326481 | 445 | 445 | 428 | Pick Label None | 0 | NULL | 24 | 25 | | 326481 | 519 | 519 | 0 | T2 | 0 | NULL | 1 | 2 | +---------+-------+-----+------+------------------+------+---------+-----+-----+ then I was adding T3 to pic1 mysql> select * from ImageTags,Tags where ImageTags.imageid = '326480' and ImageTags.tagid = Tags.id; +---------+-------+-----+------+------------------+------+-----------------+-----+-----+ | imageid | tagid | id | pid | name | icon | iconkde | lft | rgt | +---------+-------+-----+------+------------------+------+-----------------+-----+-----+ | 326480 | 16 | 16 | 490 | Ski | 0 | NULL | 943 | 944 | | 326480 | 74 | 74 | 32 | Nathalie | 0 | emblem-favorite | 873 | 874 | | 326480 | 296 | 296 | 34 | Flumserberg | 0 | NULL | 257 | 258 | | 326480 | 435 | 435 | 428 | Color Label None | 0 | NULL | 44 | 45 | | 326480 | 445 | 445 | 428 | Pick Label None | 0 | NULL | 24 | 25 | | 326480 | 518 | 518 | 0 | test3 | 0 | NULL | 1 | 2 | | 326480 | 520 | 520 | 0 | test5 | 0 | NULL | 1 | 2 | +---------+-------+-----+------+------------------+------+-----------------+-----+-----+ The corresponding colums in the Tags table looks like mysql> select * from Tags where Tags.name like 'test%'; +-----+------+-------+------+---------+-----+-----+ | id | pid | name | icon | iconkde | lft | rgt | +-----+------+-------+------+---------+-----+-----+ | 519 | 0 | T2 | 0 | NULL | 1 | 2 | | 520 | 0 | T3 | 0 | NULL | 1 | 2 | | 518 | 0 | T1 | 0 | NULL | 1 | 2 | +-----+------+-------+------+---------+-----+-----+ it looks like lft,rgt are messed up, aren't they? What are they for? values of lft and rgt for older photos/tags are much higher. Now they're always 1 and 2 what's going on here? thanks! Kusi
No idea? can you reproduce this bug with current implementation from git/master ? It can be database file corruption. Can you run digiKam through a console and look if debug statement are printed. Don't forget to turn on digiKam debug space before using kdebugdialog. Gilles Caulier
when I run digikam from console with debug enabled and try to tag a photo with "test6", I get digikam(7146)/digikam (core) Digikam::TagEditDlg::createTAlbum: ("test6") digikam(7146)/digikam (core) Digikam::TagEditDlg::createTAlbum: "test6" :: "/test6" digikam(7146)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "UPDATE Tags SET rgt = rgt + 2 WHERE rgt > @myLeft;" Error messages: "QMYSQL3: Unable to execute statement" "The user specified as a definer ('digikam'@'%') does not exist" 1449 2 Bound values: () digikam(7146)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "UPDATE Tags SET lft = lft + 2 WHERE lft > @myLeft;" Error messages: "QMYSQL3: Unable to execute statement" "The user specified as a definer ('digikam'@'%') does not exist" 1449 2 Bound values: () digikam(7146)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "UPDATE Tags SET icon=? WHERE id=?;" Error messages: "QMYSQL3: Unable to execute statement" "The user specified as a definer ('digikam'@'%') does not exist" 1449 2 Bound values: (QVariant(qlonglong, 0) , QVariant(int, 521) ) what does that mean? thanks, Kusi
I came across this link http://www.phplogs.com/the-user-specified-as-a-definer/ so I dropped all 3 triggers I could find and created two of them again, according to what I've found in dbconfig.xml delimiter // CREATE TRIGGER delete_image AFTER DELETE ON Images FOR EACH ROW BEGIN DELETE FROM ImageTags WHERE imageid=OLD.id; DELETE From ImageHaarMatrix WHERE imageid=OLD.id; DELETE From ImageInformation WHERE imageid=OLD.id; DELETE From ImageMetadata WHERE imageid=OLD.id; DELETE From ImagePositions WHERE imageid=OLD.id; DELETE From ImageComments WHERE imageid=OLD.id; DELETE From ImageCopyright WHERE imageid=OLD.id; DELETE From ImageProperties WHERE imageid=OLD.id; DELETE From ImageHistory WHERE imageid=OLD.id; DELETE FROM ImageRelations WHERE subject=OLD.id OR object=OLD.id; DELETE FROM ImageTagProperties WHERE imageid=OLD.id; UPDATE Albums SET icon=null WHERE icon=OLD.id; UPDATE Tags SET icon=null WHERE icon=OLD.id; END; // and CREATE TRIGGER delete_tag AFTER DELETE ON Tags FOR EACH ROW BEGIN DELETE FROM ImageTags WHERE tagid=OLD.id; DELETE FROM TagProperties WHERE tagid=OLD.id; DELETE FROM ImageTagProperties WHERE tagid=OLD.id; END; // I couldn't find anything for move_tagtree, is that correct?
> I came across this link > http://www.phplogs.com/the-user-specified-as-a-definer/ > so I dropped all 3 triggers I could find and created two of them again, > according to what I've found in dbconfig.xml > > delimiter // > CREATE TRIGGER delete_image AFTER DELETE ON Images > FOR EACH ROW BEGIN > DELETE FROM ImageTags WHERE imageid=OLD.id; > DELETE From ImageHaarMatrix WHERE imageid=OLD.id; > DELETE From ImageInformation WHERE imageid=OLD.id; > DELETE From ImageMetadata WHERE imageid=OLD.id; > DELETE From ImagePositions WHERE imageid=OLD.id; > DELETE From ImageComments WHERE imageid=OLD.id; > DELETE From ImageCopyright WHERE imageid=OLD.id; > DELETE From ImageProperties WHERE imageid=OLD.id; > DELETE From ImageHistory WHERE imageid=OLD.id; > DELETE FROM ImageRelations WHERE subject=OLD.id > OR object=OLD.id; > DELETE FROM ImageTagProperties WHERE imageid=OLD.id; > UPDATE Albums SET icon=null WHERE icon=OLD.id; > UPDATE Tags SET icon=null WHERE icon=OLD.id; > END; // > > and > > CREATE TRIGGER delete_tag AFTER DELETE ON Tags > FOR EACH ROW BEGIN > DELETE FROM ImageTags WHERE tagid=OLD.id; > DELETE FROM TagProperties WHERE tagid=OLD.id; > DELETE FROM ImageTagProperties WHERE tagid=OLD.id; > END; // > > I couldn't find anything for move_tagtree, is that correct? Hi, that's correct, there is no move_tagtree currently, did you have it in the database? check that your dbconfig.xml is near to this: https://projects.kde.org/projects/extragear/graphics/digikam/repository/revisions/f7ff8f23a5d279973ec5b3fccf93de248b623c53/raw/data/database/dbconfig.xml.cmake it should have been dropped, also from your previous comment its visible that user "digikam@%" don't exist but it's what digikam is trying to use, maybe you had a user "digikam@localhost" instead. digikam does a database upgrade check at every start, and need permissions to modify the database schema Now to repair the tree, that's not an easy task, but it's doable, especially if tags are not too many, otherwise you can send me a dump of the table (possibly with obfuscated tags name) and I'll try to repair it for you.
Official digiKam 2.6.0 release is out since few days now : http://www.digikam.org/drupal/node/656 Please, check if this entry still valid, or update report accordingly. Thanks in advance. Gilles Caulier
Francesco, Problem still reproducible using last stable digiKam 4.9.0 ? Gilles Caulier
it was an upgrade problem, you could consider it fixed for 4.9.0
Thanks Francesco for the feedback Gilles