Upgrading from Jira 6 to 6.4 having issue swith database CollationCheck

[Dave Robertson]

Had working copy 6.0 need to upgrade to 6.4 on CentOS Linux

Come across the following problem when running version 6.4:

2019-04-12 13:32:47,665 localhost-startStop-1 INFO [atlassian.jira.startup.JiraHomeStartupCheck] The jira.home directory '/home/jira' is validated and locked for exclusive use by this instance.
2019-04-12 13:32:47,670 localhost-startStop-1 INFO [jira.config.database.SystemDatabaseConfigurationLoader] Reading database configuration from /home/jira/dbconfig.xml
2019-04-12 13:32:47,706 localhost-startStop-1 INFO [atlassian.jira.startup.JiraStartupLogger] Running JIRA startup checks.
2019-04-12 13:32:47,706 localhost-startStop-1 INFO [atlassian.jira.startup.JiraStartupLogger] JIRA pre-database startup checks completed successfully.
2019-04-12 13:32:48,095 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Product Name is PostgreSQL
2019-04-12 13:32:48,095 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Product Version is 8.4.22
2019-04-12 13:32:48,095 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Driver Name is PostgreSQL Native Driver
2019-04-12 13:32:48,095 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Driver Version is PostgreSQL 9.3 JDBC4 (build 1102)
2019-04-12 13:32:48,952 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Product Name is PostgreSQL
2019-04-12 13:32:48,952 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Product Version is 8.4.22
2019-04-12 13:32:48,952 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Driver Name is PostgreSQL Native Driver
2019-04-12 13:32:48,952 localhost-startStop-1 INFO [core.entity.jdbc.DatabaseUtil] Database Driver Version is PostgreSQL 9.3 JDBC4 (build 1102)
2019-04-12 13:32:49,673 localhost-startStop-1 INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Database Checklist Launcher
2019-04-12 13:32:49,687 localhost-startStop-1 WARN [jira.appconsistency.db.CollationCheck]

****************************************************************************************************
The database collation could not be read. An unsupported collation could cause some functionality to not work
****************************************************************************************************

Changed collation from :

Name | Owner | Encoding | Collation | Ctype | Access privileges
------------+----------+----------+-------------+-------------+-----------------------
fisheye | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
jira | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

To:

List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
------------+----------+----------+-------------+-------------+-----------------------
fisheye | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
jira | postgres | UTF8 | C | C |

No effect.

___ Environment _____________________________

JIRA Build : 6.4.14#64029-sha1:ae256fe0fbb912241490ff1cecfb323ea0905ca5
Build Date : Mon Jul 18 00:00:00 PDT 2016
JIRA Installation Type : EAR/WAR
Application Server : Apache Tomcat/8.5.6 - Servlet API 3.1
Java Version : 1.8.0_131 - Oracle Corporation
Current Working Directory : /
Maximum Allowable Memory : 910MB
Total Memory : 631MB
Free Memory : 495MB
Used Memory : 136MB
Memory Pool: Code Cache : Code Cache: init = 2555904(2496K) used = 8724544(8520K) committed = 11075584(10816K) max = 251658240(245760K)
Memory Pool: Metaspace : Metaspace: init = 0(0K) used = 20486536(20006K) committed = 21102592(20608K) max = -1(-1K)
Memory Pool: Compressed Class Space : Compressed Class Space: init = 0(0K) used = 2396840(2340K) committed = 2490368(2432K) max = 1073741824(1048576K)
Memory Pool: PS Eden Space : PS Eden Space: init = 134742016(131584K) used = 40508416(39559K) committed = 254803968(248832K) max = 257425408(251392K)
Memory Pool: PS Survivor Space : PS Survivor Space: init = 22020096(21504K) used = 15803624(15433K) committed = 49283072(48128K) max = 49283072(48128K)
Memory Pool: PS Old Gen : PS Old Gen: init = 358088704(349696K) used = 85882552(83869K) committed = 358088704(349696K) max = 716177408(699392K)
JVM Input Arguments : -Djava.util.logging.config.file=/usr/local/tomcat/conf/logging.properties -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Dorg.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER=true -Dmail.mime.decodeparameters=true -Datlassian.plugins.enable.wait=300 -Xms512m -Xmx1024m -XX:MaxPermSize=256m -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Djava.library.path=/usr/local/apr/lib -Dcatalina.base=/usr/local/tomcat -Dcatalina.home=/usr/local/tomcat -Djava.io.tmpdir=/usr/local/tomcat/temp
Java Compatibility Information : JIRA version = 6.4.14, Java Version = 1.8.0_131

[Alex Gallien]

Hi Dave, as per How to fix the collation of a Postgres JIRA database, it looks like the valid collation types are 

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

Not sure what query running above, but what does

SELECT datcollate FROM pg_database WHERE datname='<JIRA database name>';

return  for you?

[Dave Robertson]

Hello Alex,

As in original post, it was  'en_US.UTF-8' and working with version 6.06

After upgrade to 6.4 and receiving the error, I have changed it to 'C' but still getting the same error.

[Alex Gallien]

Could you send the result of that query to double check what it returns? What you showed looked right, but Postgres is really finicky about having settings juuust right.

[Dave Robertson]

Hello Alex,

If you check the first line of the error, you will notice that the error was thrown during startup, see the line '[atlassian.jira.startup.JiraHomeStartupCheck'

I do not know what query is executed at that time.