|Summary:||Failed to update the database schema from version 5 to version 6|
|Product:||[Applications] digikam||Reporter:||Frederic Grelot <fredericg_99>|
|Component:||Database-Schema||Assignee:||Digikam Developers <digikam-bugs-null>|
|Severity:||major||CC:||fredericg_99, jaervosz, tuomas.avaruusmies, vivo75+kde|
|Latest Commit:||Version Fixed In:||2.0.0|
Description Frederic Grelot 2011-06-19 15:18:03 UTC
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.