Character Set and Collation problem in Confluence After Space Import

[Shirley He]

I am trying to merge two confluence instances together. I was successful in importing a couple of spaces from the source instance into the target instance, however at some point in time I started getting warnings about the Character Set and Collation of the target confluence instance.

I checked the source instance afterwards and found that its character set and collation appeared to be different from the target instance. At the moment I just want to amend the error in the target instance.

I've tried changing the character set and collation in the database with MySQL but this just resulted in Confluence not being able to restart.

Any tips as to what I can do?

[Diego · Accepted solution]

Hello there Shirley!
Could you clarify a few points for us:

1. What collation is used in the original database?
2. What collation is used in the destination?
3. What version is the original Confluence instance?
4. What version is the destination Confluence instance?

If the destination database is a new one without any active content, you could take a look here and check if the database is compliant with Confluence requirements:

- Database Setup for PostgreSQL

You can check details about variables set for your database with the following command:

SHOW VARIABLES

If possible, share this information with us. Keep in mind to avoid sharing sensitive information, like passwords, usernames or any other kind of data that should not be public.

Looking forward to your findings Shirley!

[Shirley He]

1. The collation used in the original database (postgresql) is: en_GB.UTF8
   1. The character set used in the original database (postgresql) is: UTF8
2. The collation used in the destination database (mysql) is: latin1_swedish_ci
   1. The character set used in the destination database (mysql) is latin1
3. The version of the original Confluence instance is: 6.6.0
4. The version of the destination Confluence instance is: 6.7.2

The destination database is not a new one. We have been using it for quite a while.

When we change the collation of the tables back to latin1_swedish_ci, confluence is able to start up successfully, otherwise it cannot start up and gives us an error stating there are mismatched collations in the database.

[Diego]

Hi again Shirley! Currently, your destination has an incorrect collation and must be fixed. Incorrect database collation can cause a wide array of issues with Confluence. The following article can guide you through on how to fix it:

How to Fix the Collation and Character Set of a MySQL Database

After this fix, the database Collation should be compliant and good to go!

Please, if you have questions or is not sure on how to proceed with any points exposed there, come clarify it with us! The process can be quite delicate depending on how critical this instance is for your business.

Remember:

Always backup your database before applying changes to it

As a good measure, you can also backup <confluence-home> and <confluence-install> folders

[Shirley He]

Hello Diego,

I think that page and its associated links were the first things we found and tried. We changed database, table, column, etc. collation and character set to what was suggested (utf8_bin and utf8) but it resulted in Confluence not being able to start up.

We managed to fix it once, by changing the settings back to what was originally there, but now the same fix isn't working again. I think we might have to restore a previous backed up version of Confluence to get it working again, however we'll still have the problem of figuring out how to properly change the collation and character set to something compatible with Confluence before we can try importing again.

Regards,

Shirley

[Diego]

Hello there Shirley!

If the backup available is a .xml backup from your Confluence in production, we could proceed like this:

1. Create a New Database in PostgreSQL
2. Setup a New Confluence instance (make sure that this instance is isolated from production)
3. Point that new Confluence to the new database
4. After setup, check if Confluence starts
5. Restore the .xml backup
6. Check if everything is working after the process finishes

Important Links

- Database Setup for PostgreSQL

- Restoring a Site

Now, if the backup available is a Database Dump, we can try the following:

1. Create a New Database in PostgreSQL
2. Setup a New Confluence instance (make sure that this instance is isolated from production)
3. Point that new Confluence to the new database
4. After setup, check if Confluence starts
5. Shutdown Confluence
6. Drop the  database content created during the new Confluence setup
   
7. Restore the database dump into the newly created database (make sure that the encoding / collation is correct after the restore. Also check owner and login credentials for the database)
8. After the restore is done, Start Confluence
9. Check if content is present

* If you have attachments, you will need to manually migrate them to this new instance

If this new setup works as expected and can receive the space exports, you could make it your new production instance.  Remember, always backup / create an image of your application before changing it. This should allow you to rollback to a working point if needed.

[Shirley He]

Hello Diego,

We will try that. Thanks for the help :)!

Regards,
Shirley