[MediaWiki-l] [CentralAuth] Illegal mix of collations (latin1_bin, IMPLICIT) and (utf8_general_ci, COERCIBLE)

Till Kraemer 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
Function: CentralAuthUser::loadState
Error: 1267 Illegal mix of collations (latin1_bin,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=' (

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
my users.

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 mailing list