Summary

After upgrading Jira or upgrading MySQL to any newer version, the instance starts to behave strangely.

Environment

Jira Server / Data Center with MySQL 5.7 or 8.0

Diagnosis

Errors on JIRA UI

• Some dashboards or features in Jira stops working as expected, as the example below:
  

• Some modules errors appears while browsing issues:
  

Specific SQL Exception on the logs

Looking at the logs, we can find this specific SQL Exception below:

• you have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

Exception during health check invocation com.atlassian.cache.CacheException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT ID, pname, URL, LEAD, DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FROM project ORDER BY pname (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', DESCRIPTION, pkey, pcounter, ASSIGNEETYPE, AVATAR, ORIGINALKEY, PROJECTTYPE FR' at line 1)

Errors on Jira Health Check results

• On the Jira's Health checking results, it will state unsupported collation like this: The database collation 'utf8mb3_unicode_ci' and table collation 'utf8mb4_bin' are not supported by Jira)

Attempt to fix with all these procedures below will NOT FIX it:

• Flush Plugin's cache ( deleting both JIRA_HOME/.bundled-plugins and JIRA_HOME/.osgi-plugins directories )
• Deleting all rows in pluginstate table
• Restart;
• Full Re-index;
• Database Integrity Checker;

Cause

It has been known to be from all these causes:

1. Incorrect / unsupported Database version
2. Incorrect / unsupported Database collation
   
3. Incorrect / unsupported Database JDBC driver
4. Incorrect configuration of JIRA_HOME/dbconfig.xml fiile

Solution

1. For Cause 1 you can check the supported databases for specific Jira versions at: Supported platforms
2. For Cause 2, you can fix the collation performing the steps described on this KB: How to Fix the Collation and Character Set of a MySQL Database manually 
3. For Causes 3 and 4, the fix is outlined at: Connecting Jira applications to MySQL 8.0 or Connecting Jira applications to MySQL 5.7.
   You can download the correct driver and also check the Database connection fields section and look at a Sample dbconfig.xml file to see how it's supposed to be configured.