JIRA throws table already exist error due to incorrect MySQL Schema
Problem
- Certain JIRA functions may fail with SQL Exceptions such as could not create table as the table already exist.
- JIRA may fail to run altogether.
- 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
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-32590Getting issue details... STATUS
Resolution
- Stop JIRA.
Remove the entire line containing the <schema-name> tag from the
dbconfig.xml
:<schema-name>jiradb</schema-name>
- Restart JIRA and confirm the database tables are created successfully by confirming no similar errors are present in the logs during startup.