pyrocms/pyrocms — Issue #1066: Illegal mix of collations for tags / keywords
Author: defdefpublic
Created: 2012-01-07 20:21:29+00:00
State: open

On the front end when i clicked on a tag i got error:

"A Database Error Occurred

Error Number: 1267

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

SELECT COUNT(*) AS `numrows` FROM (`istomagia_blog`) JOIN `istomagia_keywords_applied` ON `istomagia_keywords_applied`.`hash` = `istomagia_blog`.`keywords` JOIN `istomagia_keywords` ON `istomagia_keywords`.`id` = `istomagia_keywords_applied`.`keyword_id` WHERE `istomagia_keywords`.`name` = 'myssq' AND `status` = 'live'

Filename: E:\www\pyrocms2\site\system\codeigniter\database\DB_driver.php"

Solution:
The following db create statement in module keywords is wrong:
$keywords_applied = "
CREATE TABLE " . $this->db->dbprefix('keywords_applied') . " (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`hash` char(32) NOT NULL,
`keyword_id` int unsigned COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
";

The create statement should be:

` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`hash` char(32) CHARACTER SET utf8 NOT NULL,
`keyword_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci