| Summary: | MYSQL : database upgrade v5 to v6 failed | ||
|---|---|---|---|
| Product: | [Applications] digikam | Reporter: | Kusi <kusi> |
| Component: | Database-Mysql | Assignee: | Digikam Developers <digikam-bugs-null> |
| Status: | RESOLVED FIXED | ||
| Severity: | major | CC: | caulier.gilles, chris, creek, kde, kusi, msp, serge, tim, vivo75+kde |
| Priority: | NOR | ||
| Version: | 2.1.1 | ||
| Target Milestone: | --- | ||
| Platform: | openSUSE | ||
| OS: | Linux | ||
| Latest Commit: | http://commits.kde.org/digikam/74adf4f5dcdacc4b6574e61f55dac956fa4c7611 | Version Fixed In: | 5.0.0 |
| Sentry Crash Report: | |||
Hi, which version of mysql? /usr/*bin/mysqld --version /usr/sbin/mysqld Ver 5.1.59-log for pc-linux-gnu on x86_64 (Gentoo Linux mysql-5.1.59) /usr/sbin/mysqld Ver 5.5.15 for Linux on x86_64 (Source distribution) It's opensuse 12.1 beta Do you need the sql dump of my database? The problem should be that "images metadata" and "thumbnails" databases are not very well isolated in digikam code relevant to mysql. Waiting for a fix (which will take some time) you can either:
a) use the same schema for metadata and thumbnails
b) manually run the following queries (from dbconfig.xml) in the thumbnail schema
I would suggest a)
CREATE TABLE IF NOT EXISTS Thumbnails
(id INTEGER PRIMARY KEY AUTO_INCREMENT,
type INTEGER,
modificationDate DATETIME,
orientationHint INTEGER,
data LONGBLOB);
CREATE TABLE IF NOT EXISTS UniqueHashes
(uniqueHash VARCHAR(128),
fileSize INTEGER,
thumbId INTEGER,
UNIQUE(uniqueHash, fileSize));
CREATE TABLE IF NOT EXISTS FilePaths
(path LONGTEXT CHARACTER SET utf8,
thumbId INTEGER,
UNIQUE(path(255)));
CREATE TABLE IF NOT EXISTS CustomIdentifiers
(identifier LONGTEXT CHARACTER SET utf8,
thumbId INTEGER,
UNIQUE(identifier(255)));
CREATE TABLE IF NOT EXISTS Settings
(keyword LONGTEXT CHARACTER SET utf8 NOT NULL,
value LONGTEXT CHARACTER SET utf8,
UNIQUE(keyword(255)));
INSERT INTO Settings (keyword, value)
VALUES('DBThumbnailsVersionRequired','1')
ON DUPLICATE KEY
UPDATE value = GREATEST(VALUES(value), 1);
INSERT INTO Settings (keyword, value)
VALUES('DBThumbnailsVersion','2')
ON DUPLICATE KEY
UPDATE value = GREATEST(VALUES(value), 2);
DROP PROCEDURE IF EXISTS create_index_if_not_exists;
DELIMITER ;;
CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
SQL SECURITY INVOKER
BEGIN
set @Index_cnt = (
SELECT COUNT(1) cnt
FROM INFORMATION_SCHEMA.STATISTICS
WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING latin1)
AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
);
IF IFNULL(@Index_cnt, 0) = 0 THEN
set @index_sql = CONCAT(
CONVERT( 'ALTER TABLE ' USING latin1),
CONVERT( table_name_vc USING latin1),
CONVERT( ' ADD INDEX ' USING latin1),
CONVERT( index_name_vc USING latin1),
CONVERT( '(' USING latin1),
CONVERT( field_list_vc USING latin1),
CONVERT( ');' USING latin1)
);
PREPARE stmt FROM @index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;;
CALL create_index_if_not_exists('UniqueHashes','id_uniqueHashes','thumbId');
CALL create_index_if_not_exists('FilePaths','id_filePaths','thumbId');
CALL create_index_if_not_exists('CustomIdentifiers','id_customIdentifiers','thumbId');
Hello Francesco, with your hint I could make it run again, thanks! A few points, though:
1) I don't understand your solution a: thumbnail and metadata do have completely different schemas, so how can you use the same for both?
2) I tried solution b: first, before calling the procedure, you would need to execute "delimiter ;" again, right? Anyways, I still got the same error on launching digikam.
3) On closer look at the error message, I figured out that it was the metadata db, not the thumbdb which was missing the procedure:
<<Unable to execute query" "PROCEDURE digikamdb.create_index_if_not_exists does not exist>>
So I had a look in dbconfig.xml for the "create_index_if_not_exists" procedure for the main metadata db. Analog to your suggestion, I executed on the metadata db
delimiter ;;
CREATE PROCEDURE create_index_if_not_exists(table_name_vc varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024))
SQL SECURITY INVOKER
BEGIN
set @Index_cnt = (
SELECT COUNT(1) cnt
FROM INFORMATION_SCHEMA.STATISTICS
WHERE CONVERT(DATABASE() USING latin1) = CONVERT(TABLE_SCHEMA USING latin1)
AND CONVERT(table_name USING latin1) = CONVERT(table_name_vc USING latin1)
AND CONVERT(index_name USING latin1) = CONVERT(index_name_vc USING latin1)
);
IF IFNULL(@Index_cnt, 0) = 0 THEN
set @index_sql = CONCAT(
CONVERT( 'ALTER TABLE ' USING latin1),
CONVERT( table_name_vc USING latin1),
CONVERT( ' ADD INDEX ' USING latin1),
CONVERT( index_name_vc USING latin1),
CONVERT( '(' USING latin1),
CONVERT( field_list_vc USING latin1),
CONVERT( ');' USING latin1)
);
PREPARE stmt FROM @index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END;
delimiter;
CALL create_index_if_not_exists('Images','dir_index','album');
CALL create_index_if_not_exists('Images','hash_index','uniqueHash');
CALL create_index_if_not_exists('ImageTags','tag_index','tagid');
CALL create_index_if_not_exists('ImageTags','tag_id_index','imageid');
CALL create_index_if_not_exists('Images','image_name_index','name(996)');
CALL create_index_if_not_exists('ImageInformation','creationdate_index','creationDate');
CALL create_index_if_not_exists('ImageComments','comments_imageid_index','imageid');
CALL create_index_if_not_exists('ImageCopyright','copyright_imageid_index','imageid');
CALL create_index_if_not_exists('ImageHistory','uuid_index','uuid');
CALL create_index_if_not_exists('ImageRelations','subject_relations_index','subject');
CALL create_index_if_not_exists('ImageRelations','object_relations_index','object');
CALL create_index_if_not_exists('TagProperties','tagproperties_index','tagid');
CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_index','imageid, tagid');
CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_imageid_index','imageid');
CALL create_index_if_not_exists('ImageTagProperties','imagetagproperties_tagid_index','tagid');
That fixed my issue, everything works fine now, for me this bug is resolved. Please keep in mind: if this issue is related to the mysql update, then it would be great to commit a solution BEFORE ubuntu/opensuse roll out their fall update with mysql 5.5
(In reply to comment #4) > Hello Francesco, with your hint I could make it run again, thanks! A few > points, though: > > 1) I don't understand your solution a: thumbnail and metadata do have > completely different schemas, so how can you use the same for both? table names are different for the two schemas, so if the same name is given to the two it's possible to have one schemas containing the tables for both metadata and thumbnails (there is one table with the same name but also have same columns) > > 2) I tried solution b: first, before calling the procedure, you would need to > execute "delimiter ;" again, right? Anyways, I still got the same error on > launching digikam. correct > > 3) On closer look at the error message, I figured out that it was the metadata > db, not the thumbdb which was missing the procedure: > <<Unable to execute query" "PROCEDURE digikamdb.create_index_if_not_exists does > not exist>> > So I had a look in dbconfig.xml for the "create_index_if_not_exists" procedure > for the main metadata db. Analog to your suggestion, I executed on the metadata > db it should be the same procedure, but this is the first time we see it missing from the metadata db (instead of the thumbnails one), it's good to know > > > delimiter ;; > > CREATE PROCEDURE create_index_if_not_exists(table_name_vc > varchar(50), index_name_vc varchar(50), field_list_vc varchar(1024)) > SQL SECURITY INVOKER > BEGIN > > set @Index_cnt = ( > SELECT COUNT(1) cnt > FROM INFORMATION_SCHEMA.STATISTICS > WHERE CONVERT(DATABASE() USING latin1) = > CONVERT(TABLE_SCHEMA USING latin1) > AND CONVERT(table_name USING latin1) = > CONVERT(table_name_vc USING latin1) > AND CONVERT(index_name USING latin1) = > CONVERT(index_name_vc USING latin1) > ); > > IF IFNULL(@Index_cnt, 0) = 0 THEN > set @index_sql = CONCAT( > CONVERT( 'ALTER TABLE ' USING latin1), > CONVERT( table_name_vc USING latin1), > CONVERT( ' ADD INDEX ' USING latin1), > CONVERT( index_name_vc USING latin1), > CONVERT( '(' USING latin1), > CONVERT( field_list_vc USING latin1), > CONVERT( ');' USING latin1) > ); > PREPARE stmt FROM @index_sql; > EXECUTE stmt; > DEALLOCATE PREPARE stmt; > END IF; > END; > delimiter; > > CALL create_index_if_not_exists('Images','dir_index','album'); > CALL > create_index_if_not_exists('Images','hash_index','uniqueHash'); > CALL > create_index_if_not_exists('ImageTags','tag_index','tagid'); > CALL > create_index_if_not_exists('ImageTags','tag_id_index','imageid'); > CALL > create_index_if_not_exists('Images','image_name_index','name(996)'); > CALL > create_index_if_not_exists('ImageInformation','creationdate_index','creationDate'); > CALL > create_index_if_not_exists('ImageComments','comments_imageid_index','imageid'); > CALL > create_index_if_not_exists('ImageCopyright','copyright_imageid_index','imageid'); > CALL > create_index_if_not_exists('ImageHistory','uuid_index','uuid'); > CALL > create_index_if_not_exists('ImageRelations','subject_relations_index','subject'); > CALL > create_index_if_not_exists('ImageRelations','object_relations_index','object'); > CALL > create_index_if_not_exists('TagProperties','tagproperties_index','tagid'); > CALL > create_index_if_not_exists('ImageTagProperties','imagetagproperties_index','imageid, > tagid'); > CALL > create_index_if_not_exists('ImageTagProperties','imagetagproperties_imageid_index','imageid'); > CALL > create_index_if_not_exists('ImageTagProperties','imagetagproperties_tagid_index','tagid'); > > > That fixed my issue, everything works fine now, for me this bug is resolved. > Please keep in mind: if this issue is related to the mysql update, then it > would be great to commit a solution BEFORE ubuntu/opensuse roll out their fall > update with mysql 5.5 well it's workarounded not exactly resolved ;) it's a difficult bug, it goes deep in all digikam database managment, need many lines of code change and some good testing. P.S. it's independent from the mysql version, the question was me missing the real bug due to fast reading (In reply to comment #5) > table names are different for the two schemas, so if the same name is given to > the two it's possible to have one schemas containing the tables for both > metadata and thumbnails (there is one table with the same name but also have > same columns) ah, ok now I understand. I perform a backup of the metadata schema with mysqldump from time to time, so I prefer not having thumbnails in it there. A thumbnail schema dump is more than 500mb in my case. > > 3) On closer look at the error message, I figured out that it was the metadata > > db, not the thumbdb which was missing the procedure: > > <<Unable to execute query" "PROCEDURE digikamdb.create_index_if_not_exists does > > not exist>> > > So I had a look in dbconfig.xml for the "create_index_if_not_exists" procedure > > for the main metadata db. Analog to your suggestion, I executed on the metadata > > db > > it should be the same procedure, but this is the first time we see it missing > from the metadata db (instead of the thumbnails one), it's good to know if you need more information from my setup to figure out why that happened let me know. I already "fixed" once my digikam db directly on mysql-level. I had some spurious duplicates in the tags table, which wouldn't let me use digikam properly. Because of my "fix" (please dont call it breakage :), maybe I broke the condition for a successful schema update. > > well it's workarounded not exactly resolved ;) ...but my workaround should be ok, right? Or do I risk data corruption? True that this BUG cannot be set to resolved, though. > it's a difficult bug, it goes deep in all digikam database managment, need many > lines of code change and some good testing. Good luck then :) Keep up the great work for digikam! I am running DigiKam on Ubuntu 11.10, and just upgraded from 11.04. I do not know which version of DigiKam I had on 11.04, but since upgrading Ubuntu I am now getting the same error message at console "QSqlDatabasePrivate::removeDatabase: connection 'ConnectionTest' is still in use, all queries will cease to work.digikam(12435)/digikam (core): Schema update to V6 failed! " and in DigiKam can see my tags and what images are linked to which tag but no albums or thumbnails or actual images showing (just empty folder icons). The album directory has been changed from Home/Pictures/Photos to Home/Pictures in DigiKam. Whatever upgrades to DigiKam were done happened as part of the Ubuntu upgrade. The current release of DigiKam is 2.1.1 build 23 Sep. What can I do to get albums and thumbnails back without losing tags etc.? *** Bug 288599 has been marked as a duplicate of this bug. *** *** Bug 288839 has been marked as a duplicate of this bug. *** Similar problem from Debian/GNU Linux user: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=669387 From: John Flinchbaugh <john@hjsoft.com> To: Debian Bug Tracking System <submit@bugs.debian.org> Subject: digikam: upgrades database from v5 to v6 first time, then tries again and fails on next launch Date: Thu, 19 Apr 2012 10:01:54 -0400 Package: digikam Version: 4:2.6.0~beta3-1 Severity: normal Dear Maintainer, *** Please consider answering these questions, where appropriate *** * What led up to the situation? * What exactly did you do (or not do) that was effective (or ineffective)? * What was the outcome of this action? * What outcome did you expect instead? *** End of the template - remove these lines *** I just got this version of digikam from unstable update, and upon launch, it said it was updating the database (which is the default sqlite in my case) from v5 to v6. Upon finishing the upgrade, digikam showed me all my images, and seemed fine. I shutdown digikam and then started it again a day later, and it said again it was "Scanning collection" and quickly popped an error that it failed to upgrade database from v5 to v6. Upon continuing, no images were shown in digikam. I restored my digikam sqlite files from backups, and reproduced the behavior -- upgrade worked, then upgrade tried to apply again on next start and failed. To work around the issue, I opened the digikam4.db -> Settings table, and found DBVersion still set to 5. I bumped it up to 6, and digikam started OK for me, showed me my images, and I'm importing images again -- it seems OK now. -- System Information: Debian Release: wheezy/sid APT prefers unstable APT policy: (500, 'unstable') Architecture: amd64 (x86_64) Kernel: Linux 3.3.2 (SMP w/2 CPU cores; PREEMPT) Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8) Shell: /bin/sh linked to /bin/bash Versions of packages digikam depends on: ii digikam-data 4:2.6.0~beta3-1 ii kde-runtime 4:4.7.4-2 ii libc6 2.13-30 ii libgcc1 1:4.7.0-3 ii libgomp1 4.7.0-3 ii libgphoto2-2 2.4.14-1 ii libgphoto2-port0 2.4.14-1 ii libjasper1 1.900.1-13 ii libjpeg8 8d-1 ii libkdcraw20 4:4.7.4-2 ii libkdecore5 4:4.7.4-4 ii libkdeui5 4:4.7.4-4 ii libkdewebkit5 4:4.7.4-4 ii libkexiv2-10 4:4.7.4-2 ii libkfile4 4:4.7.4-4 ii libkhtml5 4:4.7.4-4 ii libkio5 4:4.7.4-4 ii libkipi8 4:4.7.4-2 ii libknotifyconfig4 4:4.7.4-4 ii libkparts4 4:4.7.4-4 ii liblcms1 1.19.dfsg-1+b1 ii liblensfun0 0.2.5-2 ii liblqr-1-0 0.4.1-1.1 ii libmarblewidget12 4:4.7.4-2 ii libnepomuk4 4:4.7.4-4 ii libopencv-core2.3 2.3.1-8 ii libopencv-highgui2.3 2.3.1-8 ii libopencv-imgproc2.3 2.3.1-8 ii libopencv-legacy2.3 2.3.1-8 ii libopencv-objdetect2.3 2.3.1-8 ii libphonon4 4:4.6.0.0-1 ii libpng12-0 1.2.49-1 ii libqjson0 0.7.1-6 ii libqt4-dbus 4:4.7.4-3 ii libqt4-network 4:4.7.4-3 ii libqt4-qt3support 4:4.7.4-3 ii libqt4-sql 4:4.7.4-3 ii libqt4-sql-sqlite 4:4.7.4-3 ii libqt4-xml 4:4.7.4-3 ii libqtcore4 4:4.7.4-3 ii libqtgui4 4:4.7.4-3 ii libqtwebkit4 2.2.0-5 ii libsolid4 4:4.7.4-4 ii libsoprano4 2.7.5+dfsg.1-1 ii libstdc++6 4.7.0-3 ii libtiff4 3.9.6-3 ii libx11-6 2:1.4.4-4 ii phonon 4:4.6.0.0-1 Versions of packages digikam recommends: ii chromium [www-browser] 18.0.1025.151~r130497-1 ii epiphany-browser [www-browser] 3.2.1-2 ii iceweasel [www-browser] 10.0.3esr-3 ii kipi-plugins 4:2.6.0~beta3-1 ii konqueror [www-browser] 4:4.7.4-2 ii lynx-cur [www-browser] 2.8.8dev.12-2 ii mplayerthumbs 4:4.7.4-2 Versions of packages digikam suggests: ii digikam-doc 4:2.6.0~beta3-1 -- no debconf information 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 ---------- Forwarded Message ---------- Subject: [Bug 283502] database upgrade v5 to v6 failed Date: Fri, 22 Jun 2012, 18:53:23 From: Gilles Caulier <caulier.gilles@gmail.com> To: msp@debian.org https://bugs.kde.org/show_bug.cgi?id=283502 --- Comment #11 from Gilles Caulier <caulier.gilles@gmail.com> --- 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 *** Bug 319420 has been marked as a duplicate of this bug. *** Kusi, This bug still reproducible using 3.x release ? Gilles Caulier I've never experienced this issue anymore, but I cannot judge if the bug is gone. Reading the comments from Francesco (comment #3 and comment #5), the issue is still present. This bugreport only presents a workaround New digiKam 4.11.0 is available with official PKG installer for OSX. https://www.digikam.org/node/740 Can you reproduce the problem with this release ? Git commit 74adf4f5dcdacc4b6574e61f55dac956fa4c7611 by Gilles Caulier. Committed on 19/11/2015 at 14:38. Pushed by cgilles into branch 'master'. First stage to fix Mysql support: - For Internal server: Use the current system user to run mysql_installdb program to init local database and server tables. The database is always installed in user account (~/.local/share/digikam/). The goal is to set this install path configurable as with SQlite. Check the value returned to start init and sever program with QProcess. If something is wrong abort the sequence and do not try to populate/use the database. This will fix a crash if DB backend is null at digiKam shutdown. Fix the default embeded mysql server configuration to not need grant privilege with index creation. - For remote server: Fix the index creation procedure SQL statements to not check security rules. No more grant privilege is required, excepted for the DB tables and the standard DB user dedicated for digiKam use. Important : for a remote server, the database creation need to be instanced by administrator, using following SQL statements (which will be put in DB setup page later as helper): * For a common Mysql database storage : CREATE DATABASE digikamdb; GRANT ALL PRIVILEGES ON digikamdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; with: digikamdb : the common database name (storing Core, Thumbnails, and Face databases). digikam/digikam : the name/password of user account used by digiKam to access on mysql server. * For separated Mysql database storages : CREATE DATABASE digikamcoredb; GRANT ALL PRIVILEGES ON digikamcoredb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; CREATE DATABASE digikamthumbsdb; GRANT ALL PRIVILEGES ON digikamthumbsdb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; CREATE DATABASE digikamfacedb; GRANT ALL PRIVILEGES ON digikamfacedb.* TO 'digikam'@'localhost' IDENTIFIED BY 'digikam'; FLUSH PRIVILEGES; with: digikamcoredb : the Core database name. digikamthumbsdb : the Thumbnails database name. digikamfacedb : the Face database name. digikam/digikam : the name/password of user account used by digiKam to access on mysql server. - The Face recognition database is now fully integrated in Mysql support. A new settings have been add to DB setup page. Related: bug 316747, bug 311041, bug 327062, bug 279580 FIXED-IN: 5.0.0 M +29 -15 databaseserver/databaseserver.cpp M +4 -0 databaseserver/databaseserver.h http://commits.kde.org/digikam/74adf4f5dcdacc4b6574e61f55dac956fa4c7611 |
Version: 2.1.1 (using KDE 4.7.1) OS: Linux I upgraded from digikam 1.9 to 2.1.1. My metadata is saved in a mysql database. When lauching digikam 2.1.1, I get the following error digikam(5640)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig: Loading SQL code from config file "/usr/share/kde4/apps/digikam/database/dbconfig.xml" digikam(5640)/digikam (core) Digikam::DatabaseConfigElementLoader::readConfig: false "1" 1 1 digikam(5640)/digikam (core) Digikam::SchemaUpdater::update: SchemaUpdater update digikam(5640)/digikam (core) Digikam::SchemaUpdater::startUpdates: Have a database structure version 5 digikam(5640)/digikam (core) Digikam::SchemaUpdater::makeUpdates: makeUpdates 5 to 6 digikam(5640)/digikam (core) Digikam::DatabaseCoreBackendPrivate::debugOutputFailedQuery: Failure executing query: "" Error messages: "QMYSQL: Unable to execute query" "PROCEDURE digikamdb.create_index_if_not_exists does not exist" 1305 2 Bound values: () digikam(5640)/digikam (core) Digikam::DatabaseCoreBackend::execDBAction: Error while executing DBAction [ "UpdateSchemaFromV5ToV6" ] Statement [ "CALL create_index_if_not_exists('ImageTags','tag_id_index','imageid');" ] digikam(5640)/digikam (core): Schema update to V6 failed! please let me know if you need more information best regards, kusi Reproducible: Always Steps to Reproduce: launch digikam 2.1.1 Actual Results: schema update failed Expected Results: database update works