What is the risk if we keep collation 'SQL_Latin1_General_CP1_CI_AS' in MS SQL Server DB for Confluence 5?

[Deleted user]

We are running Confluence against MS SQL Server with collation 'SQL_Latin1_General_CP1_CI_AS'

from the beginning (starting with 3.5.1 and upgrading to 4.3.2 w/o a problem)

Now on upgrade to Confluence 5.4 we are affected by the the bug that the watchers are gone after the upgrade, but for newly set watches the function is going on as before.

There is no tooling from Atlassian to change the collation, but the hint to 'Collation Changer' tool w/o guarantee.

I see no other malfunction in Confluence while keeping the collation as it is.

Interestingly Atlassian recommends 'SQL_Latin1_General_CP1_CI_AS' for Jira also!

So why should I change the Collation to 'SQL_Latin1_General_CP1_CS_AS'.

[Deleted user · Accepted solution]

Ok. We'll try the xml backup w/o Attachments.

Thanks for clearifying.

[Deividi Luvison]

Hey Harold,

Regarding the collation on JIRA side, yes JIRA collation is case insensitive which is different from confluence collation (case sensitive), it's very easy to make a mistake and set the wrong collation :(.

Regarding the risks, the first one is to fail during the upgrade attempt to 5 series due the user mapping table (Confluence will be unable to create the table due the differences on the collation), the second is that the upgrade will be automatically blocked from Confluence 5.5.2 to onwards (a checker was added that blocks the upgrade in case the user is able to pass version 5.2.*) and the last one is the bug you hit.

We do not guarantee the collation changer tool because it's a third party tool, however we never got a negative feedback on it, so in case you do not have a allocated DBA it's a option to set up a test instance, apply the tool and check for data loss. Again we never saw this miss behaviour but we need to warn customers since the creator of the tool give this warning in code project.

There is another option to fix the collation using the xml import method which I can explain to you depending on how much users you have.

Thanks and Regards,

David|Atlassian Support

[Deleted user]

Does that mean that we will not be able to upgrade from 5.4 to 5.5 now? Or ist it just blocking

Upgrade 4.x to 5.5?

We cannot re-do the upgrade from 4.3.2 to 5.4 because we detected the watcher bug first one week after the upgrade and the instance is highly productive so we would loose more then a week of work!

We are running 5.4 now with the case insensitive collation! I cannot see any trouble about the usermapping. Search for users works fine! (Were currently using Crowd as user directory, but will go back to direct Confluence LDAP mapping by the end of the year!)

I cannot upgrade doing xml export since we have at least one space that is way bigger then 2GB and failing on xml export.

What do you suggest?

[Deividi Luvison]

Due to the new constrant checker you will not be able to go to latest version on 5 series.

Usually the xml import fails because of the attachments, if you save the attachments in the files system theres one idea you can try in a test instance:

1. Generate a xml backup without attachments.
2. Install a Confluence in a test insatance in the same version of your production and make sure that you have selected the same directory structure as production.
3. Once you start that Confluence shut it down in the moment you get the license page.
4. Increase the memory as much as you can.
5. Start the test Confluence again.
6. Hook this confluence to a correct configured database (right collation)
7. Try importing again.
8. If the import works then shutdown that Confluence and copy the attachments folder from prod to test <Confluence Home> directory so we can validate everything is showing up.

If you save your attachments in the database then your remaining options would be the collaiton changer tool, a DBA or a atlassian expert.

Being honest with you I would go with the collation chaneger tool in a test instance, It worked in several collation change cases and I never saw a problem (including for this guy that had a database with 35GB).

The ideal solution would be for you to get a DBA/atlassian expert but I understand that depending on your company size or budget you have alocated for your team that is not feasible to pay for that service.

Thanks and Regards,

David|Atlassian Support

[j]

Hi, i have JIRA running MS SQL Server with collation 'SQL_Latin1_General_CP1_CI_AS' with versions 5.2.9, 6.1, 6.2, 6.3, 6.4 it worked fine

i just upgrade to 7.2.0 version and now i found the message:

the database collation 'SQL_Latin1_General_CP1_CI_AS' and table collation ''SQL_Latin1_General_CP1_CI_AS' are not supported by JIRA.

also, if i search documentation abount installation of JIRA with sqlserver2012 i found that the recomendation is CASE INSENSITIVE!!!

https://confluence.atlassian.com/adminjiraserver071/connecting-jira-applications-to-sql-server-2012-802592195.html

so what do i have to do in this case?