It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

changing collation

Vineetha Oct 29, 2013

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

0 votes
Answer accepted
Rodrigo Adami Atlassian Team Jan 01, 2014

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 Klavins Nov 22, 2017

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

0 votes
Rodrigo Adami Atlassian Team Nov 17, 2013

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 Dec 03, 2013

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 .

0 votes
Vineetha Dec 03, 2013

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
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Published in Next-gen

Introducing subtasks for breaking down work in next-gen projects

Teams break work down in order to help simplify complex tasks. This is often done iteratively, with tasks being broken down into smaller tasks and so on until the work is accurately captured in well-...

5,730 views 22 30
Read article

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you