Wrong collation

[Jeroen Elias]

The instance health check reports: "The database collation 'SQL_Latin1_General_CP1_CI_AS' and table collation 'SQL_Latin1_General_CP1_CI_AS' are not supported by JIRA".

This appears after I had installed JiRA on our own server and used it for the first time. It would have been nice to know this requirement before I connected JiRA to my database. Because this is an error and not a warning, it must be fixed.
In my opinion I have 2 options. Either change the database collation, or start all over with the installation and connect to a proper database.

The first option brings me to hell if I beleive all the stories on many boards about changing collations in a database, so I prefer the second option. But that would result in a new license key request.

I'm not sure what to do.

Do you have any suggestions?

Regards,
    Jeroen Elias

[Shannon S · Accepted solution]

Hi Jeroen,

I assume you are using MS SQL? In our Database Setup for SQL Server documentation it does let you know that the collation needs to be SQL_Latin1_General_CP1_CS_AS (case sensitive). Did you have a chance to review this for the exact steps to create a database in MS SQL? If not, I would recommend reviewing it as there may be other settings that have been missed.

In terms of correcting it, you can follow this article:

How to fix the collation of an MS SQL database

I'm not sure what stories you're referring to exactly, but you will need to recreate the database with the proper settings, and migrate your data over to it.

You mentioned you needed a new license key request, but this migration won't require that you generate a new license key.

Let me know if you have any questions.

Shannon

[Jeroen Elias]

I have applied the procedure as described in your reference "How to fix the collation of an MS SQL database'.

In the end I try to open JiRA but get nothing in my browser.

In one of the log files (log\atlassian-jira.log), I see lots of error messages saying the entity definition does not match the database definition.

Example:

Column "SCHED_NAME" of table "dbo.JQUARTZ_BLOB_TRIGGERS" of entity "JQRTZBlobTriggers" is of type "NVARCHAR(120)" in the database, but is defined as type "VARCHAR(120)" in the entity definition.

But that's exactly what needed to be done in the describing document.

Now I'm confused.

JiRA does not start up any more.

[Shannon S]

Hi Jeroen,

It appears that your columns are defined with VARCHAR instead of NVARCHAR. 

To move forward, I recommend the following procedure to migrate your database to a database where Jira has created the tables to avoid any issues.

We will be working off of the Switching databases documentation, scenario #2. Please make sure to follow exactly:

1. Create an export of your data as an XML backup. See Backing up data for details.
2. Create a new database on your new database server to house JIRA's data. See the appropriate database configuration guide in the Connecting JIRA to a database section for the database creation instructions.
3. Shut down your JIRA server.
4. Make a backup of your JIRA home directory and JIRA installation directory.
5. Delete the dbconfig.xml file in your JIRA home directory.
6. Restart JIRA and you should see the first step of the JIRA setup wizard for configuring your database connection.
7. Configure JIRA's connection to your new database (created in step 2 above) and click the 'Next' button.
8. On the 'Application Properties' setup page, click the 'import your existing data' link and restore your data from the XML backup created in step 1 above.

Please also take care to verify if you're using a supported version of SQL when migrating. If you are unsure, you can let me know your Jira and SQL version, and I can confirm for you.

Regards,

Shannon

[Jeroen Elias]

Hi Shannon,

I got it running again, using collation 'SQL_Latin1_General_CP437_CI_AI' and the option 'Is Read Committed Snapshot On'

That went well.

Did you know I had to enter the license key again (although there was a different ServerID) ?

Anyway, thanks for your help!

Regards,
    Jeroen Elias

[Shannon S]

Jeroen,

Thank you for letting me know. I'm happy you were able to get it working again. As your collation is not recommended, if you run into issues again you will need to attempt the migration as recommended earlier.

During a new setup you would have to enter the same license again, but there should be no need to generate a whole new one. If it asks you to do that again please let us know and we can help you get around that.

Shannon

[Jeroen Elias]

Ok thanks!