-
Suggestion
-
Resolution: Fixed
NOTE: This suggestion is for JIRA Server. Using JIRA Cloud? See the corresponding suggestion.
Jira needs a specific collation to make things work.
We should have a check that for every DB Engine the proper collation is used.
If not a BIG message should be written in the logs stating that some functionalities are not going to work because of this.
For the different DBs the collations are :
- Oracle : BINARY
- Mysql: utf8_bin
- Postgres : C or POSIX
- SQL Server : SQL_Latin1_General_CP437_CI_AI or SQL_Latin1_General_CI_AI
- HSQLDB : TBD
- causes
-
JRASERVER-43761 Collation check for Oracle is looking incorrectly at NLS_CHARACTERSET instead of NLS_SORT
-
- Closed
-
- is related to
-
-
- Closed
-
- relates to
-
- Closed
- supersedes
-
-
- Closed
-
![[Extranet] Page](/images/icons/generic_link_16.png "[Extranet] Page"){kind=icon}
[JRASERVER-39970] Jira needs to check the collation of the DB before it starts
Евгений Ахметханов
added a comment - Get the same warning:
You are using an unsupported oracle collation: AL32UTF8. This may cause some functionality to not work. Please use BINARY as the collation instead
select sys_context ('userenv', 'nls_sort') from sys.dual;
BINARY
select * from v$nls_parameters where parameter like '%CHARACTERSET%';
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
JIRA 6.4.5 build: 64020
Евгений Ахметханов
added a comment - Get the same warning:
You are using an unsupported oracle collation: AL32UTF8. This may cause some functionality to not work. Please use BINARY as the collation instead
select sys_context ('userenv', 'nls_sort') from sys.dual;
BINARY
select * from v$nls_parameters where parameter like '%CHARACTERSET%';
NLS_CHARACTERSET AL32UTF8
NLS_NCHAR_CHARACTERSET AL16UTF16
JIRA 6.4.5 build: 64020 I get this warning, however as JIRA database user:
select sys_context ('userenv', 'nls_sort') from sys.dual;
SYS_CONTEXT('USERENV','NLS_SORT')
--------------------------------------------------------------------------------
BINARY
frederic.cilia - the encoding and collation are two separate things:
Encoding - how the characters are stored
Collation - how the characters are sorted
AL32UTF8 is only used for the encoding, the collation must be BINARY.
Hope that explains things.
Can you explain this ?
In your documentation :
The recommended encoding is Unicode UTF-8 (the equivalent for Oracle databases is AL32UTF8)
With this update :
**************************************************************************************************** You are using an unsupported oracle10g collation: AL32UTF8. This may cause some functionality to not work. Please use BINARY as the collation instead. ****************************************************************************************************
Where is the truth ?
Hi ayakovlev@atlassian.com. Thanks for reporting this.
amierzwicki found the same NPE when starting JIRA in OnDemand (See: https://jdog.jira-dev.com/browse/JDEV-32945). This is now fixed in the 6.4 stable branch and master. Here is the relevant pull request: https://stash.atlassian.com/projects/JIRA/repos/jira/pull-requests/3503/overview
There is a regression caused by this. Enabling Jndi-Datasource gives this error (I know, officially we don't support it, but i think NPE this is smth you will be interested in) . After switching back to jdbc-datasource it works fine
2015-05-13 11:43:27,189 localhost-startStop-1 INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Database Checklist Launcher
2015-05-13 11:43:27,231 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
****************************************************************************************************
2015-05-13 11:43:27,232 localhost-startStop-1 INFO [jira.appconsistency.db.CollationCheck] Exception when reading database collation
java.lang.NullPointerException
at com.atlassian.jira.database.DatabaseCollationReader.getDatabaseName(DatabaseCollationReader.java:100)
at com.atlassian.jira.database.DatabaseCollationReader.generateCollationQuery(DatabaseCollationReader.java:81)
at com.atlassian.jira.database.DatabaseCollationReader.findCollation(DatabaseCollationReader.java:41)
at com.atlassian.jira.appconsistency.db.CollationCheck.isOk(CollationCheck.java:60)
at com.atlassian.jira.startup.JiraDatabaseConfigChecklist.doStartupChecks(JiraDatabaseConfigChecklist.java:68)
at com.atlassian.jira.startup.JiraDatabaseConfigChecklist.startupOK(JiraDatabaseConfigChecklist.java:56)
at com.atlassian.jira.startup.DatabaseChecklistLauncher.start(DatabaseChecklistLauncher.java:41)
at com.atlassian.jira.startup.DefaultJiraLauncher$2.run(DefaultJiraLauncher.java:120)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:345)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseConfigured(DatabaseConfigurationManagerImpl.java:221)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:115)
at com.atlassian.jira.startup.DefaultJiraLauncher.access$100(DefaultJiraLauncher.java:33)
at com.atlassian.jira.startup.DefaultJiraLauncher$1.run(DefaultJiraLauncher.java:86)
at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:34)
at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:81)
at com.atlassian.jira.startup.LauncherContextListener.contextInitialized(LauncherContextListener.java:72)
at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4992)
at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5490)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1575)
at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1565)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
jdbc-datasource start, same DB, same instance.
2015-05-13 10:23:50,201 localhost-startStop-1 INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Database Checklist Launcher 2015-05-13 10:23:51,308 localhost-startStop-1 INFO [atlassian.jira.startup.DatabaseChecklistLauncher] JIRA database startup checks completed successfully. 2015-05-13 10:23:51,308 localhost-startStop-1 INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Post database-configuration launchers
Thanks for the info. I came here yesterday via GHS-11178 after spending a few hours diagnosing the problem (the warning about collation doesn't seem to work like it should even in the latest 6.6.41 version) so I spent some time looking for related tickets. If all the sorting is done via Lucene anyway, then the DB collation really doesn't matter except for Agile.
Unless you are directly querying the JIRA database, you don't need to worry about missing out on the language specific sorting index. When you use JIRA, it will search and sort using a Lucene index anyway. You can configure the Lucene indexing language in JIRA's General Configuration.
Right now Jira agile checks the collation in that column only and displays a warning.
If you change the collation in that table, everything should work normally.
If not, all queries to that table will use a collation check to guarantee order.
That was introduced I think in 6.6.0 of Jira Agile.
frederic.cilia, samhall, axmetishe,
I've verified that this is not working for Oracle so I've created a bug at
JRA-43761. It looks like it's checking NLS_CHARACTERSET instead of NLS_SORT for the encoding.