JIRA throws table already exist error due to incorrect MySQL Schema

Still need help?

The Atlassian Community is here for you.

Ask the community

Problem

  1. Certain JIRA functions may fail with SQL Exceptions such as could not create table as the table already exist.
  2. JIRA may fail to run altogether.
  3. Errors appear during JIRA startup as part of the database checklist.

The following appears in the atlassian-jira.log:

2013-04-15 14:13:20,273 localhost-startStop-1 WARN      [core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database
2013-04-15 14:13:20,280 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create table "jiradb.jiraaction"
2013-04-15 14:13:20,280 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE jiradb.jiraaction (ID DECIMAL(18,0) NOT NULL, issueid DECIMAL(18,0), AUTHOR VARCHAR(255), actiontype VARCHAR(255), actionlevel VARCHAR(255), rolelevel DECIMAL(18,0), actionbody LONGTEXT, CREATED DATETIME, UPDATEAUTHOR VARCHAR(255), UPDATED DATETIME, actionnum DECIMAL(18,0), CONSTRAINT PK_jiraaction PRIMARY KEY (ID))
Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jiraaction' already exists
2013-04-15 14:13:20,280 localhost-startStop-1 WARN      [core.entity.jdbc.DatabaseUtil] Entity "Application" has no table in the database
2013-04-15 14:13:20,281 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create table "jiradb.cwd_application"
2013-04-15 14:13:20,281 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE jiradb.cwd_application (ID DECIMAL(18,0) NOT NULL, application_name VARCHAR(255), lower_application_name VARCHAR(255), created_date DATETIME, updated_date DATETIME, active DECIMAL(9,0), description VARCHAR(255), application_type VARCHAR(255), credential VARCHAR(255), CONSTRAINT PK_cwd_application PRIMARY KEY (ID))

Cause

JIRA is using a MySQL database and has a schema specified, where it should not, as below:

___ Database Configuration _________________
     Loading entityengine.xml from                 : file:/opt/atlassian/jira/atlassian-jira/WEB-INF/classes/entityengine.xml
     Entity model field type name                  : mysql
     Entity model schema name                      : jiradb
     Database Version                              : MySQL - 5.1.41-3ubuntu12.8
     Database Driver                               : MySQL-AB JDBC Driver - mysql-connector-java-5.1.10 ( Revision: ${svn.Revision} )
     Database URL                                  : jdbc:mysql://allurbase:3306/jiradb?useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine=InnoDB
     Database JDBC config                          : mysql jdbc:mysql://allurbase:3306/jiradb?useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine=InnoDB
tip/resting Created with Sketch.

Despite this configuration being incorrect the Database Configuration check during JIRA startup will pass. There is a request for better handling this within JIRA tracked as part of  JRA-32590 - Getting issue details... STATUS  


Resolution

  1. Stop JIRA.
  2. Remove the entire line containing the <schema-name> tag from the dbconfig.xml:

    <schema-name>jiradb</schema-name>
  3. Restart JIRA and confirm the database tables are created successfully by confirming no similar errors are present in the logs during startup.

Last modified on Nov 12, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.