changing collation

[Vineetha]

There are a few confluence tables (we use MySQL) whose character set is set to utf8 in their create table statement - and collation is not set. For such tables, mysql sets the character set as utf8_general_ci, that is the default for utf8. This will be the case even if the character set/collation is set at the database level as utf8/utf8-bin.
When we upgraded from 3.5 to 4.3.7, the upgrade took almost 20 hrs - this was due to the collation mismatch between 'content' and 'cwd_user'. Once the collation of that joining column in 'content' was changed to utf8-bin, the upgrade finished in 1 hr.

I have attachments, attachmentdata, content,bodycontent, spaces and spacepermissions whose collation is utf8_general_ci - and want to change to utf8-bin. I hope the change will improve wiki performance.
Are there any known issues with this change ?

[Rodrigo Girardi Adami · Accepted solution]

Hi Vineetha,

MySQL has, in addition to the table and database collation, the columns collation as well. In the latest versions of confluence, all of them must be set to the same collation and engine, otherwise confluence will fail.

If you need a way to change the entire confluence tables and columns to the correct collation and engine, you can use this method: https://confluence.atlassian.com/display/CONFKB/Convert+from+MySQL+MyISAM+to+InnoDB+tables

That documentation have the steps to create a new database with the correct collation and engine, and put the confluence data on it, thus correcting any misconfigured table.

I also recommend to check the database and set manually in the my.ini configuration file the engine and collation utf8_bin to force the new tables and columns to be created in the correct format from now on.

I hope this helps!

[Dzintars]

I was using this comandline to convert existing database from utf8_general_ci to utf8_bin:

DB="your_database_name"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_bin;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;' ) | mysql "$DB"

 Open terminal, paste this in and hit enter. Wait little bit while job is done. TA-DAA!!

[Vineetha]

Oops - Please read : A few tables had their character set (only) set to utf8, at the table level. In such cases, Mysql used the default collation for utf8, that is utf8_general_ci.

[Rodrigo Girardi Adami]

Hi Vineetha,

As far as I know, there's no huge problems using the utf8_general_ci, however we recommend using the utf8_bin for all tables. There are problems using different collations for different tables in confluence.

Try to set the default collation for the MySQL database as utf8_bin using the setting below inside the my.ini configuration of MySQL.

[mysqld]

collation-server=utf8_bin

To change the existing tables to a new collation, you can use the alter table command:

ALTER TABLE <table_name>CHARACTER SET utf8 COLLATE utf8_bin;

Cheers,

Rodrigo

[Vineetha]

The default character set and collation is set to utf8 and utf8_bin already in our MySQL database. Our issue was : A few tables had their character set (only) set to utf8_bin, at the table level. In such cases, Mysql used the default collation for utf8, that is utf8_general_ci. Hence we had to manually change the collation to utf8_bin for these tables. May be Atlassian should add the collation to all the tables creation statements where they mention only the character set .