I have just updated my cnf properties to add the following:
init_connect = 'SET collation_connection = utf8_unicode_ci; SET NAMES utf8;'
character-set-client = utf8
character-set-server = utf8
collation-server = utf8_unicode_ci
skip-character-set-client-handshake
My system variables after restarting mysql:
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+----------------------+-----------------+
So then I ran the following query to find a table that I knew had been built in utf_general_ci:
select t.table_name, c.column_name,round(((data_length + index_length) / 1024 / 1024), 2) 'Size in MB',count(c.column_name), c.character_set_name,c.collation_name
from columns c
inner join tables t on t.table_schema=c.table_schema and t.table_name=c.table_name
where t.table_schema='db' and
(c.collation_name like '%general%' or c.character_set_name like '%general%') and
(c.column_type like 'varchar%' or c.column_type like 'text') and
t.table_collation not like '%latin%' and t.table_name in ('table_name') group by t.table_name, c.column_name;
So I took a dump of the table and reimported it into my database, but it stays in utf8_general_ci!!?!?!??
Why is this? I know if I run an alter it will change it, but why didn't the dump and load resolve the problem?
Additionally, when I run an alter to convert to utf8_unicode_ci, all the columns in the table have "COLLATE utf8_unicode_ci" listed in them.
