Changing Database Collation/Ctype to UTF-8 breaks pretty links

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Summary

Confluence database collation/ctype should be set to UTF-8. If Confluence resides on a database with an ASCII (C) collation/ctype we advise that this is changed to UTF-8, as described in: How to fix the collation of a Postgres Confluence database

There is however a potential problem for instances using a non-Latin characters that have capitalisations, such as Cyrillic or Greek, that may manifest in ways such as pretty links being broken.

The cause is that an ASCII (C) ctype, will ignore non-Latin characters, as it won't know how to treat them. That means that character manipulation functions, such as those used to change capitalisation, won't be applied to them.

Confluence relies on those functions to populate the lower* columns in its database. Such lowertitle in CONTENT, or lowerspacekey in SPACES. As a result of the failure to apply those functions, when using non-ASCII characters, the columns will be populated by the values as provided, including capitalisations. 

Please note that Space Keys must be in Latin characters, so the column lowerspacekey should not, under normal circumstances, be affected by this problem.

Once UTF-8 collation/ctype is applied, Confluence will be able to use those functions and when looking in those columns, it won't be able to properly see the content that has mixed capitalisations. It will treat it as separate entities. Thus functions like pretty links, that rely on those columns will break.

Solution

The solution is to change the case-sensitive columns to lower, right after changing collation/ctype.

To do that in PostgresSQL you should execute the following query:

update content set lowertitle=lower(lowertitle);

Please make sure you have taken a backup of your database before any intervention. Please test any interventions on a non-production environment first.

Please note the above was tested on Postgres, but similar activity should be available on other databases




Last modified on Oct 26, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.