How to Change All Columns’ Collation to utf8_bin in MySQL

Created by Foogie Sim [Atlassian], last modified by Felipe Alencastro [Atlassian] on May 16, 2014

Users that hit into Confluence Does Not Start with ‘Detected tables with non-default character encoding/collation’ Message , may be wondering why, even after the server and table collation have been set correctly, that they are still getting the error in the KB. Reason may be that the following query still returns some rows:
SELECT * FROM information_schema.COLUMNS WHERE table_schema = '' AND collation_name != 'utf8_bin';
SELECT * FROM information_schema.TABLES WHERE table_schema = '' AND table_collation != 'utf8_bin';

If the query above returns even 1 row,  above will fail to startup completely

Step-by-step guide

Before you proceed with the following, please take a backup of your Confluence database:
Identify the tables highlighted in the SELECT query above
Run this query in the Confluence Database:
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_bin;
SET foreign_key_checks = 0;
alter table convert to character set utf8 collate utf8_bin;

Or, if the above fails this one:
ALTER TABLE CHARACTER SET utf8 COLLATE utf8_bin;

If the diagnostics query returns more than 1 result, you can use below query to generate the alter table query for all the affected tables:
select concat('alter table ', table_name, ' convert to character set utf8 collate utf8_bin;') from information_schema.tables where table_schema='' and table_collation != 'utf8_bin' group by table_name;

After running the query for all affected tables (alternatively, you can run this for all tables in the Confluence Database), verify that this query now returns 0 rows:
SELECT * FROM information_schema.COLUMNS WHERE table_schema = '' AND collation_name != 'utf8_bin';

Run this query to verify that all tables are configured to use utf8_bin as its TABLE COLLATION.
SELECT * FROM information_schema.TABLES WHERE table_schema = '' AND table_collation != 'utf8_bin';

If the above query does not return 0 rows, take note of the tables name it returns and execute this query.
ALTER TABLE DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Go back to step 4 to ensure all table are converted.

Revert MySQL foreign constraint checking by executing this SQL query.
SET foreign_key_checks = 1;

(549)