[Digikam-devel] [Bug 279580] Unable to get MySQL to work after update to 2.0

[tomas benedykt]

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





--- Comment #3 from tomas benedykt <tomas benedykt centrumholdings com>  2011-08-08 22:23:23 ---
mysql schema - missing procedure "create_index_if_not_exists"

as a "dirty fix" you may use the following syntax (fixed from
/usr/share/apps/digikam/database/dbconfig.xml by adding delimiter tags)

thanks for 50003.log, it was very helpful

<sql>
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 ;
</sql>

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