[Digikam-devel] [Bug 283502] database upgrade v5 to v6 failed

[Kusi]

https://bugs.kde.org/show_bug.cgi?id=283502


Kusi <kusi at forum.titlis.org> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |kusi at forum.titlis.org




--- Comment #4 from Kusi <kusi forum titlis org>  2011-10-07 21:56:37 ---
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

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.