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

[Francesco Riosa]

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





--- Comment #5 from Francesco Riosa <francesco+kde pnpitalia it>  2011-10-08 12:54:12 ---
(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

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