Table doesn't exist error in Jira using MySQL
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
Jira either fails to startup, or most operations fail. In the atlassian-jira.log file, an error stating Table 'jiradb.tablename' doesn't exist appears repeatedly. In the example below, Jira is running on a database called jira and the table in question is OS_WFENTRY.
2013-01-16 11:34:02,904 http-bio-8080-exec-20 ERROR optimus.prime 694x61x1 1muiaf7 127.0.0.1 /browse/ABC-123 [opensymphony.workflow.basic.BasicWorkflow] Error checking available actions
com.opensymphony.workflow.StoreException: Could not find workflow instance #14204: root cause: SQL Exception while executing the following:SELECT NAME, INITIALIZED, STATE FROM OS_WFENTRY WHERE ID=? (Table 'jira.OS_WFENTRY' doesn't exist)
at com.opensymphony.workflow.spi.ofbiz.OfbizWorkflowStore.findEntry(OfbizWorkflowStore.java:168)
at com.opensymphony.workflow.AbstractWorkflow.getAvailableActions(AbstractWorkflow.java:67)
[.....]
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT NAME, INITIALIZED, STATE FROM OS_WFENTRY WHERE ID=? (Table 'jira.OS_WFENTRY' doesn't exist)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:605)
at org.ofbiz.core.entity.GenericDAO.select(GenericDAO.java:500)
at org.ofbiz.core.entity.GenericDAO.select(GenericDAO.java:467)
at org.ofbiz.core.entity.GenericHelperDAO.findByPrimaryKey(GenericHelperDAO.java:78)
at org.ofbiz.core.entity.GenericDelegator.findByPrimaryKey(GenericDelegator.java:541)
at com.opensymphony.workflow.spi.ofbiz.OfbizWorkflowStore.findEntry(OfbizWorkflowStore.java:163)
... 339 more
Root cause:
org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:SELECT NAME, INITIALIZED, STATE FROM OS_WFENTRY WHERE ID=? (Table 'jira.OS_WFENTRY' doesn't exist)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeQuery(SQLProcessor.java:605)Cause
This problem primarily affects Jira instances running on MySQL database. On Jira version 8.12 and later, the Connecting Jira applications to MySQL 8.0 guide specifies MySQL must use the utf8mb4_bin collation. As utf8mb4_bin is a case-sensitive collation, the database connector will not find tables with the incorrect case. For example, the error may report a table with a lower-case name is missing if it's stored as upper-case in the database.
Resolution
As table name case varies based on Jira version, database product, and third-party plugin usage, there is no official guide on table capitalization. The best approach is to rename all reported tables to the case specified in the error. So in this example, we would rename os_wfentry to OS_WFENTRY.
RENAME TABLE jira.os_wfentry TO jira.OS_WFENTRY;