Bug 322642

Summary:	MAINTENANCE : tools an optimizations to maintain database schema
Product:	[Applications] digikam	Reporter:	Philipp Hahn <pmhahn+kde>
Component:	Database-Schema	Assignee:	Digikam Developers <digikam-bugs-null>
Status:	RESOLVED FIXED
Severity:	wishlist	CC:	caulier.gilles, metzpinguin
Priority:	NOR
Version:	3.2.0
Target Milestone:	---
Platform:	Other
OS:	Linux
Latest Commit:		Version Fixed In:	7.10.0
Sentry Crash Report:
Attachments:	tag recovery procedure

Description Philipp Hahn 2013-07-21 06:59:41 UTC

After a crash of digikam the Tags table was corrupted: the lft and rgt values were inconsistend and several old tags were missing.
I was able to re-construct most of the table by hand.

Reproducible: Didn't try

Steps to Reproduce:
1. After GPS correlating several pictures I used the feature to reverse-tag those pictures. For some new cities multiple tags were created.
2. I removed some of those duplicate tags.
3. Digikam consumed 100% for a very long time, so I termincated it
4. After a restart several tags were missing: As one country tag was removed, all subtags were missing as well. They were still in the table, but unreachable.



The Tags-table corruption is similar to Bug 298929. I was able to re-insert most of the missing tags by hand and re-constructing the lft,rgt values by following <http://stackoverflow.com/questions/3623645/how-to-repair-a-corrupted-mptt-tree-nested-set-in-the-database-using-sql>

Bug 309061 describes a bug when deleting tags.

Comment 1 Philipp Hahn 2013-07-21 07:01:14 UTC

Created attachment 81232 [details]
tag recovery procedure

Comment 2 caulier.gilles 2013-10-31 07:52:24 UTC

digiKam 3.5.0 is out.

Can you give a fresh feedback about your report ? Crash still reproducible ?

Thanks in advance

Gilles Caulier

Comment 3 Philipp Hahn 2013-11-09 15:41:18 UTC

(In reply to comment #2)
> Can you give a fresh feedback about your report ? Crash still reproducible ?

I hope to never get in that situation again, as reconstructing the tree by hand did cost me several days. Perhaps killing digikam by "kill -9" while a tag-tree modification is in process can reproduce it?

Comment 4 caulier.gilles 2014-05-16 07:29:00 UTC

digiKam 4.0.0 is out :

http://www.digikam.org/node/713

Please check if this entry still valid with this new version.

Thanks in advance

Gilles Caulier

Comment 5 caulier.gilles 2014-08-14 10:08:58 UTC

A work around to maintain sqlite database file have been given in users mailing lits :

http://mail.kde.org/pipermail/digikam-users/2014-August/019738.html

http://mail.kde.org/pipermail/digikam-users/2014-August/019739.html

http://mail.kde.org/pipermail/digikam-users/2014-August/019740.html

These kind of maintenance operation must be done through a dedicated tool in digiKam.

Gilles Caulier

Comment 6 caulier.gilles 2014-08-21 08:19:19 UTC

Another optimization of SQlite database is given here :

http://neuntoeter.wordpress.com/2012/06/21/io-probleme-mit-digikam/

It's an performance tune of SQlite settings.

Gilles Caulier

Comment 7 Maik Qualmann 2022-12-04 18:40:44 UTC

This bug report is no longer relevant to the current database schema under MySQL. The tables lft and rgt no longer exist. As in SQLite, it was implemented with triggers and a TagTree table.

Maik