changing collation

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 ?

3 answers

1 accepted

This widget could not be displayed.

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!

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!!

This widget could not be displayed.

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

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 .

This widget could not be displayed.

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.

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Posted 5 hours ago in Europe

Topic Tuesday: How did you learn about the AUG programme?

Back in 2010 I hadn't been using the Atlassian tools (JIRA, Confluence, Bamboo) that long and I started searching online to see if I could chat to other users primarily about how they used the produc...

13 views 0 0
View post

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you