This also happens in Jira. IMO it is nuts that the database and other MS SQL options are not Flagged during the setup. This give a poor customer experience as they need to uninstall everything and recreate the database. For that matter, why can't the setup create the database with all the right setting?

Hi All,

We need the upgrade to fix some other issues with 5.4.4 but due to collation issue can't, comments from DBA that it is not easy/ preferable to change the collation due to dependencies, I am expecting some solutions from Atlassian on this. Looking at the size of database we have it's always a risk to use a 3rd party collation update tool or any other method( like , create db backup> drop database> create new database with updated collation> restore database.

Thanks,
Manish

Still no changes in this issue for the last 2 months, why is that ? When I tried to upgrade from 5.1.4 to 5.7 in my test environmment, imagine my not so welcome surprise with this problem. Arrg, the DBA tells me it is not so simple to fix this because it is not standard to change the collation. Any intention on your part to include a "fix" with the next version ??

Hey @dluvision,
Regarding "The change on the collation is from Confluence 3.5 and we added a note regarding the upgrade blocker being fixed on 5.5.3".
I (we) do not share your point of view. We upgraded to 5.5.2 with the wrong collation, without any Confluence complaints.
The blocking change - from a migration point of view - is starting at 5.5.3.
I have rechecked the 5.5.3 release note nor upgrade note; I do not find any clear mention of this blocking upgrade change in the collation checker.

Does that mean that - for legacy customers - it is safe to upgrade and be blocked at 5.5.2 until an official release includes the 'collation changer tool' as part of the upgrade process. This is the situation at my company: after facing troubles with latest released, we upgrade to the latest version which did not complain with our (not good anylonger) collation.
Regards,
Gilles

Hey david.grierson1

As I replied in our other page I don't believe that will happen (99% sure), it's a fact that version 4.3 will go EOL but the collation conflict usually screw up during upgrades and functions on Confluence 4 series (or when creating pages on Confluence 3.5 series). In case you have problems regarding upgrading Confluence we will give you all the support to upgrade to a supported version (even if in staging) and then troubleshoot any functions that are not working properly.

Thanks and Regards,
David|Confluence Support Engineer

In light of this issue and the resulting issues which large numbers of customers are having with fixing their collation issues it would be appropriate to adjust the Atlassian Support End of Life Policy for Confluence 4.3 beyond it imminent expiry at the end of January 2015.

Could someone from Atlassian please comment on whether this will be happening or not?

dbrossart,

The change on the collation is from Confluence 3.5 and we added a note regarding the upgrade blocker being fixed on 5.5.3:

https://confluence.atlassian.com/display/DOC/Confluence+5.5.3+Release+Notes

Although I do agree we could send a better warning regarding that. The error you mentioned might be some template or customization problem, which is not related to the database collation change, please raise a support ticket at support.atlassian.com so that can be better analysed.

Thanks and Regards,
David|Confluence Support Engineer

I ran that collation changer tool and it appeared to work successfully, however when starting Confluence it said that I needed to set the database to READ_COMMITTED. I did that, but now it's asking for a license. I tried applying the license by clicking the link in the error page, but it won't take it. $soyTemplateRendererHelper.getRenderedTemplateHtml("com.atlassian.auiplugin:aui-experimental-soy-templates", "aui.message.error.soy", {"content" : $error, "id" : "${fieldname}-error"})

I guess I'll waste more time getting this going. It's a little ridiculous that users are having to go through these steps to upgrade and there isn't even a mention of the change to collation on the Confluence upgrade page.

Hey sperdomo I'm afraid that is not possible, the previous versions worked because the checker were broken, the case sensitive of the collation is necessary to confluence architecture we know that it wasnt in there before however new core features were added which depends on that sensitivity in 5 series like the user mapping table.

The comments in here are the ones our dev's will see and as always please continue to add your thoughts in this ticket, the relevance of those comments are really important to continue to improve confluence.

We are also studying a way to generate some standard scripts to do a procedure similar to the collation changer tool, the conversion will takes time depending on the database size (in this aspect there is no other way around) once we are able to develop and fully test that I will update it our customers trough that page right away (you have my word on that).

Thanks and Regards,
David|Confluence Support Engineer| The SQL Server Guy

Like Kemal above, after rolling back we've had to hold off on upgrading due to the time and effort required. With our project load we have been simply unable to afford the time investment as yet.

In practice what this means is that we will probably stick with our current version until (a) something breaks horribly and forces an emergency version upgrade, or (b) we move away from Confluence altogether. I suspect many other users are in the same boat.

Like you said, Deividi, it is a pain. You say it is needed due to the architecture; however, it wasn't needed before. That Atlassian made this change whilst missing this potential consequence, and now just expects Support to hammer it out with users, speaks volumes. By Jove, this very issue (which is a simple upgrade check&block and not the fix we expect) still remains unassigned.

Atlassian, listen to your customers: We expect an installer that will work with the collation we have now, just like all your previous versions had done so far. Most of us feel it's not on us to bear this burden; it's on you to figure it out.

Deividi, can you please pass these comments on to the Development manager in charge of this area?

Hey dbrossart,

The two methods I usually recomend:

• For small instance: Take note of your third party plugins, generate a Confluence XML Backup, install a vanilla confluence on a properly configured MSSQL Server (with the proper collation), restore the generated xmlbackup and re install your third party plugins.
• For large Instances: Ask your DBA to migrate the data from the current database to a new database with the proper collation or use the collation changer tool which is listed in here.
  The collation changer tool was made to be used with mssql 2000 and the owner of the tool tells that you might get some data loss during the process, we never got this feedback from any custoemrs who used the tool (including a guy that had a 35 GB base), if you want to use that option you can also use the tool to generate scripts so you can validate that all the procedure the tool does is, generate scripts of all constraints, indexes and keys, drops those, alter the collation and re add the constraints.

The reason why changing in the database level does not work is that MSSQL will not convert the current collation of your columns.

We totally understand that this is a pain however we need that due to Confluence architectural . If you have further problems to address that or would like to further discuss, please raise a tickets at support.atlassian.com and we will be more than happy to validate your situation and see what we can do to help you on this process.

Thanks and Regards,
David|Confluence Support Engineer

we also have quite a large instance. we have 3 instances running. After upgrading the test instance, which failed, I exported the data of the acceptance instance.

Then I ordered a new database with the "new" collation and set up the test instance as new Installation ( we clone prod data from time to time). The Import was a bit tricky cause the 2 test instances are running on the same machine. So I stopped one and provided the other with 6 GB Ram so I managed to Import the XML Backup.

We have MS SQL databases running on virtualized Servers so I cannot easily change the collation. But creating an new db is working till now. So test phase just begun (TempDB et. al. ...)

I managed to get back to version 5.4.4 and I don't intend to try to upgrade until there's a hassle free method to do it. Really disappointed with Atlassian in this moment.

Is there a specific method that people are using to get this working? We went from 5.3 to 5.6.4 and are now getting the message about the collation mismatch (which would have been nice to have been warned about before we started the upgrade).

I've been snooping around and I've seen that you can change the collation of the DB, but that doesn't help with the columns. There was mention of exporting the XML, but how would you do that if you can't get into Confluence to run the export?

We're also currently stuck in the upgrade scenario described by @Saul Perdomo.

I have to agree with most of the comments above - this is a very poor customer experience and definitely not the kind of service we've come to expect from Atlassian.

I am upgrading from 5.1.3 to 5.6.1 on Linux using EAR/WAR. We have an extremely large global Confluence implementation. Thank you all for posting your work around this issue. That being said, I am extremely disappointed with atlassian.

1. Created a DB back in Confluence version 3.0 with the default collation, as it seems there was no requirement back then.
2. Goes through several successful version upgrades throughout the years.
3. After an upgrade from version 4.x to 5.1, data (e.g. watches) goes missing with no warning.
4. User is now stuck with being unable to further upgrade since, apparently, Confluence will now refuse to start because of the collation mismatch.
5. Now the user needs to export his DB of several GB in size to XML, but cannot since the process fails due to the large amounts of data.
6. The user is not annoyed. "Annoyed" is not a strong enough word here.

Agree with Saul Perdomo, big let down , especially for those with a big database.
Spent hours on troubleshooting this. I believe the path is the following:

1. shutdown current confluence.
2. create an empty database with the new collation.
3. generate the scripts.
   1. tables only.
   2. constraints / indexes.
4. create the tables only.
5. copy the data from the existing confluence database to one with the new collation.
   1. There are identity fields on the database so, if make sure to enable identity insert.
6. create the constraints/indexes.
7. start-up current confluence.
8. test browsing and if you can edit/save pages, etc.
9. upgrade to the latest version and it would not complain

For the comparison / data move I've used Redgate data compare, also Visual Studio data compare to move data around, and the SSIS also. There were a lot of rounds until get to the point it would work.

Hope this helps.

I hope developers who have not thought about this obvious check will suffer from Diarrhea 3 days long.

Yes, I think that most of use are in the scenario #2 described by 'Saul Perdomo'.
And I agree also on step 6.

An additional proposal is that you have a good plugin 'Atlassian Support Tool', with 'health checks' on
%confluence_url%/plugins/servlet/stp/view/
This check Java JDK, Application Server, ... and would benefit from checking the DB prerequisites too!

Another scenario if I may, Matthew:

1. The user created a DB back in Confluence version 3.0 with the default collation, as it seems there was no requirement back then.
2. Goes through several successful version upgrades throughout the years.
3. After an upgrade from version 4.x to 5.4, data (e.g. watches) goes missing with no warning.
4. User is now stuck with being unable to further upgrade since, apparently, Confluence will now refuse to start because of the collation mismatch.
5. Now the user needs to export his DB of several GB in size to XML, but cannot since the process fails due to the large amounts of data.
6. The user is not annoyed. "Annoyed" is not a strong enough word here.

Just to clarify:

The current check occurs on Confluence startup. That mean on a clean install what happens is:

1. The user creates a DB with the wrong collation.
2. Goes through the installer and configures the DB.
3. The installer creates all the tables in the DB.
4. Confluence tries to start-up and detects the wrong collation settings. At this point Confluence displays an error and refuses to start.
5. Now the user needs to nuke the DB and start again from step 1.
6. The user is annoyed.

So what we want to do is add a check after step 2 so the user knows there is an error right away and can fix it before all the tables are created with the wrong collation setting.