Inconsistent behaviour with collations
Posted by: Scott Harman
Date: May 24, 2011 09:26PM

Hi all,
I've been trying to track down an issue with a stored procedure failing to successfully compare the index of a table with that of it's database.

I've noticed that with all the databases that have failed to work with this stored procedure (live, and at customer sites) have the collation set of both the database and it's tables explicitly defined with a collation of utf8_unicode_ci

The ones that don't have any issue, have no collation set defined, and seem to have an implicit collation of utf8_general_ci

In my connection and collation settings, it is reporting correctly that the default collation is utf8_unicode_ci

the error returned by the stored procedure is:
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

PROCEDURE quentin_v3.dropIndex(
  IN tblName VARCHAR(64),
  IN ndxName VARCHAR(64))
  DETERMINISTIC
  MODIFIES SQL DATA
BEGIN

  DECLARE IndexColumnCount INT;
  DECLARE SQLStatement VARCHAR(256);
  DECLARE dbName VARCHAR(64);
  SET dbName = 'quentin_v3';

  
  SELECT count(1)
INTO
  IndexColumnCount
FROM
  information_schema.statistics
WHERE
  table_schema = dbName
  AND table_name = convert(tblName USING utf8) COLLATE utf8_general_ci
  AND index_name = convert(ndxName USING utf8) COLLATE utf8_general_ci;

  IF IndexColumnCount > 0 THEN
    SET SQLStatement = CONCAT('ALTER TABLE `',dbName,'`.`',tblName,'` DROP INDEX`',ndxName,'`');
    SET @SQLStmt = SQLStatement;
    PREPARE s FROM @SQLStmt;
    EXECUTE s;
    DEALLOCATE PREPARE s;
  END IF;

END

If I explicitly define the collation for the table schema = dbName comparison, it will succeed for the 'bad' databases, but I'm not sure whether that will be the correct set.

Any ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
Inconsistent behaviour with collations
3468
May 24, 2011 09:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.