MySQL Bugs: #65382: Unable to restore dump

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:	None
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

Description:
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
owners.

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'

This is almost same problem as what Richard reported here:
http://bugs.mysql.com/bug.php?id=65379

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).

Peer
(not a MySQL person)

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'

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.