A table with two ENUM fields in MySQL, strange behavior. This is mistake?

Question

after upgrading to MySQL 5.5, we noticed a strange error that occurred in the default MySQL configuration, it uses the default utf16 setting (at least it seems to me)

The strange behavior is as follows.

Create a table with the following two fields

CREATE TABLE `aa` ( `a` ENUM('on','off') NOT NULL DEFAULT 'off', `b` ENUM('on','off') NOT NULL DEFAULT 'off' ) CHARACTER SET 'utf16' COLLATE 'utf16_general_ci'; 

Dump the table and the structure will be next

 CREATE TABLE `aa` ( `a` ENUM('o','o') NOT NULL DEFAULT 'o', `b` ENUM('o','o') NOT NULL DEFAULT 'o' )ENGINE=InnoDB CHARACTER SET 'utf16' COLLATE 'utf16_general_ci'; 

system configuration

 Ubuntu 12.04.1 LTS Linux host 3.2.0-30-virtual #48-Ubuntu SMP Fri Aug 24 17:12:24 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux mysql-server-5.5 5.5.24-0ubuntu0.12.04.1 MySQL database server binaries and system database setup 

Can anyone confirm this?

It doesn't seem to happen with the UTF8 setting, which can be disabled by default in my.cnf

 [mysqld] character-set-server = utf8 collation-server = utf8_unicode_ci init-connect='SET NAMES utf8' character-set-server = utf8 

Answer 1

This may be the answer (from the documentation - Unicode Support ):

Client applications that must communicate with the server using Unicode must set the appropriate character set of the client; for example, by issuing the SET NAMES 'utf8' statement. ucs2, utf16 and utf32 cannot be used as a client character set, which means that they do not work for SET NAMES or SET CHARACTER SET. (See Section 10.1.4, “Connecting Character Sets and Sorts.”)

And one more note (from Character Mapping and Character Sorting ):

ucs2, utf16, and utf32 cannot be used as a client character set, which means that they do not work for SET NAMES or SET CHARACTER SET.

It looks like utf16 characters were not saved correctly.