[MediaWiki-l] [CentralAuth] Illegal mix of collations (latin1_bin, IMPLICIT) and (utf8_general_ci, COERCIBLE)
info at till-kraemer.com
Wed Sep 21 15:33:16 UTC 2016
I'm running a little pool wiki for files, a data wiki for, well data,
and several language wikis (MediaWiki 1.27.1).
Users টিল and Till have accounts on enwiki. I can create a local user
টিল on poolwiki via createLocalAccount.php with no problems. I also can
create a local user Till on datawiki without any trouble, but when I try
to create টিল on datawiki, I'm getting the following error:
A database query error has occurred.
FROM `globaluser` LEFT OUTER JOIN `localuser` ON ((gu_name=lu_name) AND
lu_wiki = 'datawiki') WHERE gu_name = 'টিল' LIMIT 1
Error: 1267 Illegal mix of collations (latin1_bin,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' (127.0.0.1)
Table globaluser on database centralauth looks like this:
| Field | Type | Collation |
| gu_id | int(11) | NULL |
| gu_name | varchar(255) | latin1_bin |
| gu_enabled | varchar(14) | latin1_swedish_ci |
| gu_enabled_method | enum('opt-in', | latin1_swedish_ci |
| |'batch','auto', | |
| |'admin') | |
| gu_home_db | varchar(255) | latin1_bin |
| gu_email | varchar(255) | latin1_bin |
| gu_email_authenticated | char(14) | latin1_bin |
| gu_salt | varchar(16) | latin1_bin |
| gu_password | tinyblob | NULL |
| gu_locked | tinyint(1) | NULL |
| gu_hidden | varbinary(255) | NULL |
| gu_registration | varchar(14) | latin1_bin |
| gu_password_reset_key | tinyblob | NULL |
| gu_password_reset_expiration | varchar(14) | latin1_bin |
| gu_auth_token | varbinary(32) | NULL |
| gu_cas_token | int(10) unsigned | NULL |
I tried to do things like ALTER TABLE `globaluser` MODIFY
`gu_password_reset_expiration` varchar(14) CHARACTER SET utf8 COLLATE
utf8_bin; but that doesn't work with gu_name:
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes
I can do gu_name varchar(200), but I'm probably not supposed to do that.
I also can change gu_name varchar(255) to utf8_bin in a freshly created
centralauth database, but not in the one that is already populated with
Exporting the old centralauth database, changing gu_name to utf8_bin in
an editor and importing it, doesn't work either: same error as mentioned
How can I fix this?
I'm running MariaDB 10.0.25 which seem to use XtraDB by default. Would
it help to switch to InnoDB?
And: my latest language wikis have $wgDBTableOptions = "ENGINE=InnoDB,
DEFAULT CHARSET=binary"; so I changed the old wikis from
$wgDBTableOptions = "TYPE=InnoDB"; in LocalSettings.php to the
settings of the new wikis. I don't know if that's a problem.
Aside from the problems mentioned above, should I change every database
and table of the old wikis from latin1_bin to utf8_bin, so that they are
in line with the new MediaWiki installations?
Any help is more than welcome!
Thanks and cheers,
More information about the MediaWiki-l