PostgreSQL SSL Configuration Causes Collation Exception on Startup

[john morrissey]

Running lastest jira in AWS using RDS postgresql.  The RDS was created using the default post gres parameter group in AWS.  THe errors.jsp is reporting a collation issue though it appears the jir log isn't (see below for more details)

our /secure/errors.jsp is reporting 

The following warnings should be fixed, but you can choose to ignore them and continue starting JIRA.

Database: Your database is using an unsupported collation

Your postgres72 database is currently using an unsupported collation: en_US.UTF-8. You should change this to a supported collation:

• POSIX.UTF-8
• C.UTF-8
• C
• POSIX

Review our documentation for more information on supported collations.

jira  log file 

Database configuration OK

___ Database Configuration _________________

Loading entityengine.xml from : file:/opt/atlassian/jira/atlassian-jira/WEB-INF/classes/entityengine.xml
Entity model field type name : postgres72
Entity model schema name : public
Database Version : PostgreSQL - 9.6.2
Database Driver : PostgreSQL Native Driver - PostgreSQL 9.4.1212
Database URL : jdbc:postgresql://removed.us-east-1.rds.amazonaws.com:5432/removed
Database JDBC config : postgres72 jdbc:postgresql://removed.us-east-1.rds.amazonaws.com:5432/removed

2017-10-04 08:10:53,298 JIRA-Bootstrap INFO [c.a.jira.startup.JiraStartupLogger]

[Andy Heinzer]

Hi John,

I took a review of this issue, and I wanted to clarify the source problem here.

Is your Jira instance connecting to this postgres database using an SSL connection?   You mentioned this in the title of this post originally, but we have never really returned that that detail here yet.  My concern here is to learn more about this setup.   Atlassian does not provide support for the configuration of SSL, be it for web connections (HTTPS) or be it in regards to the way Jira is connecting to the database.  My fear here that this specific problem might getting caused by the use of SSL to connect to the postgres instance.   I would like to clarify that point to understand if having Jira try to connect via an SSL connection to the database is at all a factor here.  I am interested to learn if connecting to the database in a non-SSL method works without this hang up.  This would be helpful for us to better understand the problem here.


When you start Jira, what URL are you first visiting on the site?  Is your web browser automatically redirecting you to your $jirabaseurl/secure/errors.jsp page?  Or are you manually navigating to that specific address?

Just a bit of background here, I manually setup a test instance of Jira Server 7.5.0 with a postgres database using the same collation as yours, en_US.UTF-8 (but I did so on a non-AWS instance).  When I started Jira several times, I was never redirected to that errors page, and in turn my instance appears to be up and running.  However when I manually navigated to /secure/errors.jsp I found that this page was indicating to me that the startup had problems.  However visiting this page manually in this situation is misleading, because the site is already up and running, and in my opinion this page over-states the severity of this problem.   Primarily this page is for time when Jira completely fails to startup.  In my experience, this page should be helpful for situations such as because of a problem plugin causing Jira to fail to load, or an unresponsive database during the startup of Jira.   In those cases, this page can be helpful to troubleshoot the problem.

In regards to the specific database collation here, I don't believe you are going to have any problems with using that existing collation in Jira.  Historically though, Atlassian has found in other databases, such as MySQL, the use of collations like 'latin1_swedish_ci' can have adverse affects in regards to how users enter character information into Jira and then in turn exactly how the database is store that data.  In your case, the use of the en_US.UTF-8 collation is extremely unlikely to cause problems with the way Jira is storing this data, but technically it is not among the list of supported collations for Jira.  

Moving forward, I don't think your collation here is something that absolutely needs to be changed to make Jira work, and work well.   However if you are still concerned about the inability to change this collation on the RDS platform, I would actually recommend installing postgresql separately and using that installed instance instead of the AWS RDS Postgres platform.   In turn, you would then need to follow the explicit steps in Connecting Jira applications to PostgreSQL in order to create this new database with the supported collation type.

Regards,
Andy

[Shannon S]

Hi John,

Despite Jira not showing the same error, can you please try to run the steps from the following article to see if you're able to get past that error?

How to fix the collation of a Postgres JIRA database

Let us know if you run into any issues.

Kind Regards,
Shannon

[john morrissey]

here's the output of the select

jiraprod=> SELECT datcollate

jiraprod-> FROM   pg_database

jiraprod-> WHERE  datname = Current_database();

 datcollate

-------------

 en_US.UTF-8

(1 row)

[john morrissey]

also for AWS RDS I don't believe you can run set commands as they're configured in parameter groups.  I can't seem to find the parameter to change collation so was hoping someone else has experienced this already.

[Shannon S]

Hi John,

You're on the right track; it does appear that it is set with the client_encoding option in the parameter groups. 

Here's the Amazon appendix in question:

• Common DBA Tasks Parameters

And it'll need to be set to one of the following, as you are aware:

• POSIX.UTF-8
• C.UTF-8
• C
• POSIX

Let us know if you have any trouble!

Kind Regards,
Shannon

[john morrissey]

Shannon is this the setting you're recommending changing?

client_encoding

if so I changed it to 

POSIX.UTF-8 

but the sql still returns 

 en_US.UTF-8

[Shannon S]

Thanks, John,

I had a look, and spoke to the rest of the team, and it appears actually the best way to set a new collation on a Postgres database is to create a new one:

• How to fix the collation of a postgres Jira database

You can follow the steps from that article on how to move to the new database once you create it in AWS with the proper collation.

Let me know if you have any trouble.

Kind Regards,
Shannon

[john morrissey]

hah I already did that and you seems you can't change collation was hoping you knew of a way or could test it out as RDS is very popular

[Shannon S]

Unfortunately not, but I was told by the team it could cause other issues if you try to change it that way.

Another recommendation from a colleague, if you wanted more control over your data, you could try installing postgres on the EC2 rather than using the ready-made service. However, this could cost more or less depending on the use.

Kind Regards,
Shannon

[john morrissey]

Below is from AWS RDS support as it appears you can't change the collation for postgresql but seems you have more options for mysql

the underlying Linux OS of RDS cannot be modified

On RDS mysql db instance the below collation settings can be changed
and they are @ a global level

character_set_client = utf8
character_set_connection = utf8
character_set_database = utf8
character_set_filesystem = utf8
character_set_results = utf8
character_set_server = utf8
collation_connection = utf8_general_ci
collation_server = utf8_general_ci

well except for client and connection

[john morrissey]

Also can I ask what cloud platform your cloud service runs on?  At this point we're thinking of switching to AWS RDS MYSQL for our Jira db.  I know your cloud runs postgresql so wondering what cloud vendor you use or if you host it yourself?

[Shannon S]

Hi John,

Cloud is on AWS: Cloud hosting infrastructure

So AWS RDS support told you that you cannot select a different collation for Postgres? The collation is absolutely important so if that is the case then you could try MySql. Odd that you wouldn't be able to change it, though.

Kind Regards,
Shannon

[john morrissey]

ok interesting and what I was hoping for can you see if they run on rds postgresql which if so (hopefully) can we get their db parameter group info? 

Seems your hosting group would be the experts and may the AWS engineer was just not familiar with what I was asking.

They can contact me directly if there's any info you don't want to post to this forum

[john morrissey]

it appears your cloud platfrom is running aws rds with postgresql is there someone there that can help us with what parameter group settings we need to change?

https://aws.amazon.com/quickstart/architecture/jira/

[Shannon S]

Hello,

Everything in Cloud is automated, but it appears from the AWS RDS documentation you can directly connect to the DB instance. From here, you can try to create the database itself with the required collation.

Kind Regards,
Shannon

[john morrissey]

Yes I know how to connect to the DB I'd like to work with somone on the rds parameter group as your cloud must be using a modified group which I just need to know the settings.

A screen shot of the parameter group or script for the rds instance creation should be all I need.

Please have someone give me a call so we can discuss this.

[Shannon S]

John,

This is not information that we're able to release, but as far as I can tell we may not even be using RDS specifically for our database setup.

Regarding the error you have, is it preventing Jira from working properly? You mentioned you hadn't seen the error in Jira so you may be able to continue using Jira for now. According to the error it says you can choose to ignore them.

If you continue to have issues, your best bet is still to contact the AWS support and explain to them what you need to change; there may be some other settings in the parameters you need to include, but unfortunately we do not have that information.

Thank you for your understanding.

Kind Regards,
Shannon

[john morrissey]

Shannon,
I appreciate your help but want to respond to your comments.

"we may not even be using RDS specifically for our database setup" - Your documentation for your AWS template states otherwise though you give no guidance on this in your documentation (e.g., what RDS version, and any custom configuration) please see https://aws.amazon.com/quickstart/architecture/jira/

"Regarding the error you have, is it preventing Jira from working properly?" - no that was previously pointed out which I believe you said we should fix it

"You mentioned you hadn't seen the error in Jira so you may be able to continue using Jira for now. According to the error it says you can choose to ignore them." - it's your error reported in your product so we reached out to you

"If you continue to have issues, your best bet is still to contact the AWS support and explain to them what you need to change" - we did and they state you can't change it in RDS postgresql so we're looking to you for help with your product as you recommend RDS in your AWS documentation

I'd like to have this escaltated to a support manager at this point?

[Shannon S]

John,

I was referring to the fact that you had asked if Atlassian Cloud architecture was using RDS.  The link you provided relates to Jira Data Center version specifically. For more details see Getting started with Jira Data Center on AWS and for a list of recommended parameters, here: Configuring Jira Data Center on AWS.

In regards to your error message, I had suggested that you change the collation to avoid future problems. However, it seems that you will be able to use Jira normally until then, according to the error message.

I understand that you are having difficulty changing the collation in AWS RDS, and despite having contacted Amazon, they let you know it is not possible to change. I haven't been able to find any further details on this. You're welcome to view the templates we have available for Jira Data Center:

• Quickstart Jira Template (for a new VPC)
• Quickstart in existing VPC

I'm sorry that I don't have any further information to provide you, as we are not able to support AWS tuning. I will still forward your case onto a support manager and they can reply to you shortly.

Kind Regards,
Shannon

[john morrissey]

Thanks Shannon just trying to setup your product correctly in AWS 

[Shannon S]

Hello John,

I understand. I have sent you an email to obtain your contact information so we will be able to have a support manager reach out to you next week.

Please reply to that email at your earliest convenience so I can ensure I have the correct details on file for you.

Kind Regards,
Shannon

[Shannon S]

Hi John,

I just wanted to let you know I also confirmed with one of the other sever engineers here. He let me know the reason that Amazon told you that you cannot change it is because you're on RDS and it's set according to your location.

This is the reason for the recommendation of EC2 where you would have more control of your data.

I would still say it's safe to ignore as the error message says. Otherwise, you could even run Postgres on your own server and use this instead, since you can then set your own collation.

Regarding Cloud, I can confirm it's our own platform based on AWS, but does not use RDS for database management.

I hope this is clear, but we can still arrange the call as you requested for next week.

Kind Regards,
Shannon