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

Question

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

nicholas waterson · Answer

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

nicholas waterson · Answer

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

Brant Schroeder · Answer

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?