MySQL Bugs: #65000: Can't change collation_connection back from utf-16 to utf8

Bug #65000	Can't change collation_connection back from utf-16 to utf8
Submitted:	16 Apr 2012 21:30	Modified:	9 Jul 2012 23:15
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S3 (Non-critical)
Version:	5.1.60, 5.5.20, 5.5.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Setting the connection collation usually also changes the collection charset to the right charset for the new collation. After setting the collation to one of the utf16 or ucs2 related ones changing to a different collation is no longer possible, even if it is a collation of the same character set.

How to repeat:
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf16_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf8_unicode_ci';
ERROR 1273 (HY000): Unknown collation: 'utf8_unicode_ci'

mysql> set collation_connection='latin1';
ERROR 1273 (HY000): Unknown collation: 'latin1'

mysql> set collation_connection='utf16_bin';
ERROR 1273 (HY000): Unknown collation: 'utf16_bin'

Suggested fix:
Collation changes should always be possible, or at least when the character set stays the same

Thank you for the problem report. Verified with 5.5.22 on Mac OS X also:

macbook-pro:mysql-5.5.22-osx10.5-x86_64 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.22 MySQL Community Server (GPL)

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> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf8_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf16_unicode_ci';
Query OK, 0 rows affected (0.00 sec)

mysql> set collation_connection='utf8_unicode_ci';
ERROR 1273 (HY000): Unknown collation: 'utf8_unicode_ci'
mysql> set collation_connection='utf16_bin';
ERROR 1273 (HY000): Unknown collation: 'utf16_bin'

Noted in 5.6.1 changelog.

After setting collation_connection to one of the collations for the
ucs2 or utf16 character sets, it was not possible to change the
collation thereafter.