| 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> |
| 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 | |
| Sentry Crash Report: | |||
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"...
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
Ok, then this bug is on the MySQL pile Hi, The problem with database migration seems actually different. I created bug #275687 to track it. fixed in 2.0.0-rc 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! (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 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 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 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 Any error messages when you start digikam from the console? Remember to enable debug messages before, 50003 in kdebugdialog 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. |
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.