nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

Michael Patschull

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!

All comments

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

Michael Patschull

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

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 Tondihal

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

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

server

confluence-server