[Digikam-devel] [Bug 271924] digikam fail to create/update mysql database on startup [PATCH]

[Francesco Riosa]

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


Francesco Riosa <francesco at pnpitalia.it> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
  Attachment #59413|0                           |1
        is obsolete|                            |




--- Comment #9 from Francesco Riosa <francesco pnpitalia it>  2011-05-06 00:55:57 ---
Created an attachment (id=59678)
 --> (http://bugs.kde.org/attachment.cgi?id=59678)
digikam-easy-dbupdate.patch

working on bug #258409 I've noticed a bug in stored procedure
create_index_if_not_exists (my patch).

dunno where the collation of the connection is set to:
SET collation_connection = 'utf8_general_ci'
But default for database is 'utf8_unicode_ci', so when migrating it result in
the error:
#1267 - Illegal mix of collations (utf8_general_ci,IMPLICIT) and
(utf8_unicode_ci,IMPLICIT) for operation '='

the procedure should set explicitly collations where needed, empiric results
show it's needed only in the first query of the S.P.

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 table_name = table_name_vc COLLATE utf8_general_ci
      AND index_name = index_name_vc COLLATE utf8_general_ci
);

IF IFNULL(@Index_cnt, 0) = 0 THEN
    set @index_sql = concat('ALTER TABLE ',table_name_vc,' ADD INDEX
',index_name_vc,'(',field_list_vc,');');
    PREPARE stmt FROM @index_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;
END;

the new stored procedure has been tested, the patch instead is manually
modified and never applied

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