[Digikam-users] MySQL upgrade failed

[Sven Flossmann]

Hi,

I had the same problems as well. Today evening I took the time to perform
the upgrade manually. For those who have the problems not solved, yet, here
is what I did:

- Use an Editor and a Mysql Client of your choice (e.g. Kate and MySQL Query
Browser).
- Execute the following in the Query Browser:
                    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;

- Open core/data/database/dbconfig.xml.cmake in an editor
- Scroll to line 1462: "<!-- Migration from DB Version 5 (0.10 - 1.4) to
Version 6 (1.5-) -->"
"
- For each <statement> tag within the <dbaction> tag: Copy the SQL statement
to e.g. Mysql Query Browser and execute the statement.
- If you get an error (usually happens if triggers are created), enclose the
SQL statement in between:
DELIMITER //;
<SQL-Statement>
DELIMITER;
And remove tha last ";" from the <SQL-Statement>.

- Afterwards one has to ensure that the Settings table is correct. Execute
the following:
UPDATE Settings SET value=2 WHERE keyword = 'DBThumbnailsVersion';
UPDATE Settings SET value=6 WHERE keyword = 'DBVersion';

- Tell digikam to use  MySQL again, and enjoy.


No warranty whether this is correct and works for everyone.

Please backup your database before making any changes.

Good luck.

Sven

2011/8/3 Ignatius Reilly <ignatius.reilly at free.fr>

> Hi,
>
> I just upgraded a DK 1.9 installation with MySQL on Natty
>
> Got the following error at DK startup:
> <
> 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.
> >
>
> Any idea where I can find the MySQL schema upgrade script?
>
> Thx
> Ignatius
> _______________________________________________
> Digikam-users mailing list
> Digikam-users at kde.org
> https://mail.kde.org/mailman/listinfo/digikam-users
>



-- 
Sven Flossmann
Friedensstr. 13B
85622 Feldkirchen
Germany

Tel: +49 89 17998529
Mobile: +49 173 2016002

PGP available
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.kde.org/pipermail/digikam-users/attachments/20110927/befdc54d/attachment.html>