Bug #65382 Unable to restore dump
Submitted: 21 May 2012 14:25 Modified: 16 Jan 2013 15:52
Reporter: Richard Kojedzinszky Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:5.1.64, 5.5.24 OS:Any (FreeBSD 9.0, Mac OS X)
Assigned to: CPU Architecture:Any

[21 May 2012 14:25] Richard Kojedzinszky
When restoring from an sql dump file, an illegal mix of collation errors stops the restore procedure. We have a table/view layout similar to as described below. On a freshly installed mysql server.

How to repeat:
$ mysql krichy
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27
Server version: 5.5.24 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create view v1 as select _latin2 'Abc' collate latin2_hungarian_ci AS a;
Query OK, 0 rows affected (0.05 sec)

mysql> create view v0 as select a from v1 union select _latin2 'QWE' collate latin2_hungarian_ci as a;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from v0;
| a   |
| Abc |
| QWE |
2 rows in set (0.00 sec)

mysql> Bye
[krichy@pi ~]$ mysqldump krichy > a
[krichy@pi ~]$ mysql krichy < a
ERROR 1267 (HY000) at line 56: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_hungarian_ci,EXPLICIT) for operation 'UNION'
[21 May 2012 14:38] Peter Laursen
This is almost same problem as what Richard reported here:

The VIEW across collations is legal.  But cross-collations are illegal with the 'dummy' temporary table generated by mysqldump (in order to handle 'VIEWs or VIEWs').

One more example that the 'dummy temporary table' from the very beginning (another example reported long ago is that a VIEW may be 'wider' than a MyISAM table and in such case the dump will also not restore).

(not a MySQL person)
[21 May 2012 15:58] Valeriy Kravchuk
Verified with 5.1.64 on Mac OS X also:

macbook-pro:5.1 openxs$ bin/mysql -uroot b < /tmp/b.sql 
ERROR 1267 (HY000) at line 56: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (latin2_hungarian_ci,EXPLICIT) for operation 'UNION'
[16 Jan 2013 15:52] Paul DuBois
Noted in 5.1.69, 5.1.31, 5.6.11, 5.7.1 changelogs.

If a dump file contained a view with one character set and collation
defined on a view with a different character set and collation,
attempts to restore the dump file failed with an "illegal mix of 
collations" error.