Cannot get Jira working collation and health check warnings on clean install

[nicholas waterson]

I have been running confluence with no issues. I recently decided to add jira to my set up.

I have been round the installation/db creation a couple of times and still cannot get jira working.

I am using Jira v7.13.0#713000 with MySql  5.5.40 and mysql-connector-java-5.1.47

on a Windows box

Using MySQl workbench I see jiradb.. but info says there are no tables... yet during the startup pages I connected successfully to mysql and I was able to create a project.. but browsing to it throws a 'Oops - an error has occurred' and a massive stack trace:

Referer URL: http://xxxxxxxxxxxxx/secure/BrowseProjects.jspa?selectedCategory=all&selectedProjectType=all

java.lang.RuntimeException: java.lang.RuntimeException: javax.servlet.ServletException: com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.atlassian.plugins.base-hipchat-integration-plugin-api]

java.lang.RuntimeException: java.lang.RuntimeException: javax.servlet.ServletException: com.atlassian.activeobjects.internal.ActiveObjectsInitException: bundle [com.atlassian.plugins.base-hipchat-integration-plugin-api]
	at com.atlassian.web.servlet.plugin.DynamicAuthorizationServletForwarder.forward(DynamicAuthorizationServletForwarder.java:55) [?:?]
	at com.atlassian.web.servlet.plugin.DynamicAuthorizationServletForwarder.forwardSafely(DynamicAuthorizationServletForwarder.java:83) [?:?]
	at com.atlassian.web.servlet.plugin.SanitizingServletForwarder.forwardSafely(SanitizingServletForwarder.java:48) [?:?]
	at com.atlassian.web.servlet.plugin.RememberingServletForwarder.forwardSafely(RememberingServletForwarder.java:75) [?:?]
	at com.atlassian.web.servlet.plugin.ResolvingServletForwarder.forwardSafely(ResolvingServletForwarder.java:50) [?:?]
	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingResponse.lambda$null$0(RedirectInterceptingResponse.java:50) [?:?]
	at java.util.Optional.map(Optional.java:215) [?:1.8.0_202-ea]
	at com.atlassian.web.servlet.plugin.request.RedirectInterceptingResponse.lambda$forward$1(RedirectInterceptingResponse.java:50) [?:?]

looking under 'Instance Health checks' I see a collation error

The database collation 'utf8_general_ci' and table collation 'latin1_swedish_ci' are not supported by JIRA.

But again when I use mysql workbench the schema and tables show utf8_bin

Log analyser shows:

JIRA throws table already exist error due to incorrect MySQL Schema

Can anyone shed some light on what might be going on here. This was a clean install and I followed the install instructions as far as I know to the letter.

Many thanks for any help

[Brant Schroeder]

Did you follow the steps here to resolve this issue?  

https://confluence.atlassian.com/jirakb/health-check-database-collation-in-mysql-943951422.html

https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-744326173.html

[nicholas waterson]

I tried but it fails at the ALTER tables stage...

Like I said above what puzzles me is the db is there and it seemed to go through set up OK... but when I browse using mysql workbench there are no tables (hence I expect the above fails). If I look at my confluence db I can browse those tables ok.

I have gone round the loop various times...so uninstall app... drop jiradb.. clean registry and app data to ensure nothing left behind... then recreate the db, install the app... go through the set up stages and each time I end up in teh same place.

[Brant Schroeder]

When you try to ALTER the table is the JIRA instance turned off?  Also does the user that you are using have the proper permissions to ALTER table?  Have you tried terminating all MySQL connections before performing the ALTER table change?  

[nicholas waterson]

Tried it with both Confluence and jira instances on and off. still didnt work. I am logged in as root so I should have permissions.

Using:

SELECT CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'jiradb'
AND
(
    C.CHARACTER_SET_NAME != 'utf8'
    OR
    C.COLLATION_NAME != 'utf8_bin'
);

I am returned

CONCAT('ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_bin;')

I asseeume it should return me a list of these ALTER Tables for each table in the db

[nicholas waterson]

By the way... scanning through the exception mentioned earlier I see:

Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
    Database:
     - name:MySQL
     - version:5.5.40
     - minor version:5
     - major version:5
    Driver:
     - name:MySQL Connector Java
     - version:mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 )
    
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ao_5fb9d7_aohip_chat_link' already exists