Unable to fix character encoding error after upgrade to confluence 8.0.0

[Michael Patschull]

Environment:

• Ubuntu 18.04.6 LTS
• MySql Server version: 5.7.40-0ubuntu0.18.04.1 (Ubuntu)
• mysql-connector-java-5.1.47-bin.jar
• Confluence Server 8.0.0 (self hosted)

Dear community,

I have just updated confluence server from 7.13.1 to 8.0.0.

At startup, Confluence first reported an error, saying that database tables should have utf8mb4 character set and utf8mb4_bin collation. (Unfortunately I did not write down the exact message and logs are purged).

I verified, server hat the correct character set but my database unfortunately not. So I followed the steps in the following document to fix this:

https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-744326173.html

I verified the database again with the SQL statements given in above document, as far as I can see, I have character set utf8mb4 and collation utf8mb4_bin everywhere in the MySQL confluence database now.

Sill, on startup I get the error:
Detected tables with non-default character encoding. See https://confluence.atlassian.com/x/TABrFw

Unfortunately the log file does not provide more information:

2022-12-03 11:21:19,634 ERROR [Catalina-utility-1] [atlassian.confluence.setup.BootstrapApplicationStartupListener] checkConfigurationOnStartup Detected tables with non-default character encoding. See https://confluence.atlassian.com/x/TABrFw

I am pretty lost now - was utf8mb4 wrong? How to proceed?

Kind regards,

Michael

[Michael Patschull · Accepted solution]

At the end, the solution was easy:

When following

https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-744326173.html

I had created a file with all the ALTER TABLE statements as recommended, and executed them as a batch of commands. Though, for an unknown reason, two of the ALTER TABLE statements seem to have failed - at least I got two columns as result when I checked again with

mysql> SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_bin', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
-> FROM information_schema.COLUMNS 
-> WHERE TABLE_SCHEMA = 'mauspedia'
-> AND DATA_TYPE != 'varchar'
-> AND
-> (
-> CHARACTER_SET_NAME != 'utf8mb4'
-> OR
-> COLLATION_NAME != 'utf8mb4_bin'
-> );

I executed the two returned ALTER TABLE statements again, then Confluence started without this error!

[Srinatha T]

Hi @Michael Patschull ,

Welcome to Atlassian Community. 

Confluence 8.0 does not support Mysql 7. The same is mentioned over here and i think that is the reason you are getting error . 

Thanks,

Srinath T

[Michael Patschull]

Hi Srinath,

yes, Confluence told me the same when I finally managed to start again and logged in as admin :-)

I had upgraded MySQL to version 8 already, when I found the not executed ALTER TABLE statements described above. So Murphy's law hit twice that day - but at least I have a running system with an up-to-date database now!

Kind regards,

Michael

[Alex Koxaras _Relational_]

Hi @Michael Patschull 

From the link you provided I see that supported version of utf8mb4 is on Confluence 7.3 and later, running on MySQL 5.7.9 or later. However I see that you have MySql Server version: 5.7.40. Perhaps you should update your MySQL to 5.7.9 or use utf8. 

[Michael Patschull]

Hi @Alex Koxaras _Relational_ , thanks for looking into this - I am afraid but 5.7.40 is the latest release of MySQL 5.7, which was published 2022-10-11. Still, maybe I should take this opportunity to try and upgrade MySQL to version 8.0, since version 5.7. will become EOL end of 2023 so or so.

I will have to test first, which problems this will raise, though.

[Alex Koxaras _Relational_]

Dang! You are right. I totally got confused and I compared a 5.7 to 5.9 version. Apologies. Yes, do try that, or change it to utf8