Bug 276052

Summary: Failed to update the database schema from version 5 to version 6
Product: digikam Reporter: Frederic Grelot <fredericg_99>
Component: Database-SchemaAssignee: Digikam Developers <digikam-bugs-null>
Status: RESOLVED FIXED    
Severity: major CC: fredericg_99, jaervosz, tuomas.avaruusmies, vivo75+kde
Priority: NOR    
Version: 2.0.0   
Target Milestone: ---   
Platform: Compiled Sources   
OS: Linux   
Latest Commit: Version Fixed In: 2.0.0

Description Frederic Grelot 2011-06-19 15:18:03 UTC
Version:           2.0.0 (using KDE 4.6.4) 
OS:                Linux

Since my upgrading from 1.9.0 to 2.0.0b6, when I launch digikam, I get the following error :
"Failed to update the database schema from version 5 to version 6. Please read the error messages printed on the console and report this error as a bug at bugs.kde.org."
There is nothing but this on the console :
>QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.
>digikam(20875)/digikam (core): Schema update to V6 failed! 
the first one (connection still in use) was already present before the upgrade.

Once digikam is started, my collections are lost. If I add them, the images get scanned (long process), and my collections are back. Fortunately, tags and ratings are not lost. However, I must do it at every launch and it lasts few minutes (between 2 and 5 minutes are necessary to scan my collections...).

I marked this bug as "causes data loss", since the collection list is lost... However, manually adding the collection back "recovers" the data...

Reproducible: Always

Steps to Reproduce:
Install and create everything with 1.9.0, then install 2.0.0b6 and start digikam

Actual Results:  
The above-mentioned message appears


I built and installed digikam 2.0.0b6 from tarball.
Comment 1 Frederic Grelot 2011-06-19 15:49:17 UTC
I suspected that the problem was related with my database, which is using MySQL.
Actually this proves to be true, since I activated the query log of the server, and watched it. I used the "Database migration" tool, source and target are on the mysql server, source tables are "digikam" and "thumbnails-digikam", while target tables are "digikam2" and "thumbnails-digikam2". This is what I get in the query log :
110619 15:37:01	   92 Connect	digikam@laptop on digikam
		   92 Init DB	digikam
		   92 Query	SET NAMES utf8
		   93 Connect	digikam@laptop on digikam2
		   93 Init DB	digikam2
		   93 Query	SET NAMES utf8
		   93 Query	DROP TABLE IF EXISTS AlbumRoots, Albums, DownloadHistory, FilePaths, ImageComments, ImageCopyright, ImageHaarMatrix, ImageInformation, ImageMetadata, ImagePositions, ImageProperties, ImageTags, Images, Searches, Settings, Tags, TagsTree, Thumbnails, UniqueHashes
		   94 Connect	digikam@laptop on digikam2
		   94 Init DB	digikam2
		   94 Query	SET NAMES utf8
		   94 Query	CREATE TABLE IF NOT EXISTS PrivCheck
        		(
 				   id   INT,
    			   name VARCHAR(35)
				)
		   94 Query	ALTER TABLE PrivCheck DROP COLUMN name
110619 15:37:02	   94 Query	CREATE TRIGGER privcheck_trigger AFTER DELETE ON PrivCheck
 				 FOR EACH ROW BEGIN
  				END
		   94 Query	DROP TRIGGER privcheck_trigger
		   94 Query	DROP TABLE PrivCheck
		   94 Query	DROP TABLE IF EXISTS PrivCheck
		   94 Quit	
		   93 Query	select table_name from information_schema.tables where table_type = 'BASE TABLE'
		   93 Query	SELECT value FROM Settings WHERE keyword='DBVersion'
		   93 Query	SELECT value FROM Settings WHERE keyword='DBVersionRequired'
		   92 Quit	
		   93 Quit	

If you run those manually (beware of the 3 parrallel connections), the last two queries fail, which is logical : the Settings table does not exist in "digikam2"...
Comment 2 Frederic Grelot 2011-06-19 15:54:57 UTC
My last message may have been wrong : the problem at database migration seems to be different with that of database upgrade.
At startup, when Digikam tries to upgrade the database scheme, the log is as follow :

110619 15:48:05	  191 Connect	digikam@laptop on digikam
		  191 Init DB	digikam
		  191 Query	SET NAMES utf8
		  191 Quit	
		  192 Connect	digikam@laptop on digikam
		  192 Init DB	digikam
		  192 Query	SET NAMES utf8
		  193 Connect	digikam@laptop on digikam
		  193 Init DB	digikam
		  193 Query	SET NAMES utf8
		  193 Query	CREATE TABLE IF NOT EXISTS PrivCheck
        		(
 				   id   INT,
    			   name VARCHAR(35)
				)
110619 15:48:06	  193 Query	ALTER TABLE PrivCheck DROP COLUMN name
		  193 Query	CREATE TRIGGER privcheck_trigger AFTER DELETE ON PrivCheck
 				 FOR EACH ROW BEGIN
  				END
		  193 Query	DROP TRIGGER privcheck_trigger
		  193 Query	DROP TABLE PrivCheck
		  193 Query	DROP TABLE IF EXISTS PrivCheck
		  193 Quit	
		  192 Query	select table_name from information_schema.tables where table_type = 'BASE TABLE'
		  192 Prepare	SELECT value FROM Settings WHERE keyword=?
		  192 Reset stmt	
		  192 Execute	SELECT value FROM Settings WHERE keyword='DBVersion'
		  192 Close stmt	
		  192 Prepare	SELECT value FROM Settings WHERE keyword=?
		  192 Reset stmt	
		  192 Execute	SELECT value FROM Settings WHERE keyword='DBVersionRequired'
		  192 Close stmt	
		  192 Query	BEGIN WORK
		  192 Query	CREATE TABLE IF NOT EXISTS ImageHistory
                            (imageid INTEGER PRIMARY KEY,
                             uuid VARCHAR(128),
                             history LONGTEXT CHARACTER SET utf8)
		  192 Query	CREATE TABLE IF NOT EXISTS ImageRelations
                            (subject INTEGER,
                             object INTEGER,
                             type INTEGER,
                             UNIQUE(subject, object, type))
		  192 Query	CREATE TABLE IF NOT EXISTS TagProperties
                            (tagid INTEGER,
                             property TEXT CHARACTER SET utf8,
                             value LONGTEXT CHARACTER SET utf8)
		  192 Query	CREATE TABLE IF NOT EXISTS ImageTagProperties
                            (imageid INTEGER,
                             tagid INTEGER,
                             property TEXT CHARACTER SET utf8,
                             value LONGTEXT CHARACTER SET utf8)
		  192 Query	CALL create_index_if_not_exists('ImageTags','tag_id_index','imageid')
		  192 Query	ROLLBACK
		  192 Prepare	REPLACE into Settings VALUES (?,?)
		  192 Reset stmt	
		  192 Execute	REPLACE into Settings VALUES ('DBVersion','5')
		  192 Close stmt	

Fortunately, the whole operation is ROLLBACK'ed at the end. I'm not sure where the problem is, but it may be related with the "create_index_if_not_exists" call.
This functions seems to be manually-created from the last comment on the following mysql doc page :
http://dev.mysql.com/doc/refman/5.1/en/create-index.html
Comment 3 Marcel Wiesweg 2011-06-19 19:19:26 UTC
Ok, then this bug is on the MySQL pile
Comment 4 Frederic Grelot 2011-06-21 08:49:27 UTC
Hi, 

The problem with database migration seems actually different.
I created bug #275687 to track it.
Comment 5 Francesco Riosa 2011-06-28 18:55:46 UTC
fixed in 2.0.0-rc
Comment 6 Sune Kloppenborg Jeppesen 2011-07-30 14:04:04 UTC
I just tried with 2.0.0-rc and 2.0.0 and the problem persist for me.

Manually creating the stored procedure create_index_if_not_exists as mentioned in comment #2 seems to work around the problem here.

It has to be done for both the main digikam database as well as the thumbnail database otherwise you might see the following error:

digikam(9446)/digikam (core): Schema upgrade in ThumbnailDB from V1 to V2 failed!
Comment 7 Francesco Riosa 2011-07-30 14:09:18 UTC
(In reply to comment #6)
> I just tried with 2.0.0-rc and 2.0.0 and the problem persist for me.
> 
> Manually creating the stored procedure create_index_if_not_exists as mentioned
> in comment #2 seems to work around the problem here.
> 
> It has to be done for both the main digikam database as well as the thumbnail
> database otherwise you might see the following error:
> 
> digikam(9446)/digikam (core): Schema upgrade in ThumbnailDB from V1 to V2
> failed!

I suppose it's related to the usage of splitted database images/thumbnails, need to be fixed like the settings table issue
Comment 8 Francesco Riosa 2011-07-31 16:39:06 UTC
Git commit d5eae51de889ba326b3d33771e7888cd97492932 by Francesco Riosa.
Committed on 31/07/2011 at 18:31.
Pushed by riosa into branch 'master'.

Migration: splitted mysql database fixes

Migration of a sqlite database to a mysql one didn't create
create_index_if_not_exists() stored procedure in the thumbnail database,
leading to failed update if index already existed.

Data of the thumbnail database is not copyed at all, but the content of
the "Settings" table are needed and checked. Workaround the problem
checking/inserting the two needed records at creation time.

CCBUG: 277242
CCBUG: 276052

M  +77   -32   data/database/dbconfig.xml.cmake

http://commits.kde.org/digikam/d5eae51de889ba326b3d33771e7888cd97492932
Comment 9 Francesco Riosa 2011-07-31 16:44:47 UTC
Sune Kloppenborg Jeppesen, there is still something I need to decide about migrating from sqlite to mysql if "images" and "thumbnails" databases are different, discussion will take place at bug#277242
Comment 10 tuomas.avaruusmies 2012-04-25 09:26:42 UTC
Sorry to bump into this old bug but I am really stuck. I would like to know what is the preferred way to get Digikam working again. I am sorry, that I do not have enough knowledge about databases and tools for modifying them to be able to sort it out with the information in this bug report.

I have about 1000 pictures that were stored tagged and commented using an older version 1.x of digikam. Now I have upgraded to Lubuntu 11.10 (Oneiric) that comes with digikam 2.1.1. and I get this error "Failed to update the database schema from version 5 to version 6". Following questions have arisen:

Should this error still occur in 2.1.1. or is this related with something else? Which version is the first one to contain the fix to this issue?

Will it be fixed by issuing the commands mentioned in this bug report? If so, could I have detailed instructions on what tools to use and exact commands to issue, please.

Should I rather downgrade to digikam 1.x to get things working and postpone the upgrade of digikam until I can upgrade to a version that contains a fix to this issue?

Is this the proper place to ask these questions?

Thanks
Comment 11 Marcel Wiesweg 2012-04-25 10:04:02 UTC
Any error messages when you start digikam from the console? Remember to enable debug messages before, 50003 in kdebugdialog
Comment 12 tuomas.avaruusmies 2012-04-27 19:39:45 UTC
Well...
Error messages: "Unable to fetch row" "attempt to write a readonly database" 8 1 

chmod a+w did help. Sorry for the bother.

Digikam 2.5.0 is working fine. The problem was probably the same with version 2.1.1

Thanks and sorry.