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 '='
IN tblName VARCHAR(64),
IN ndxName VARCHAR(64))
MODIFIES SQL DATA
DECLARE IndexColumnCount INT;
DECLARE SQLStatement VARCHAR(256);
DECLARE dbName VARCHAR(64);
SET dbName = 'quentin_v3';
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;
DEALLOCATE PREPARE s;
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.