JIRA throws table already exist error due to incorrect MySQL Schema
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
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:
1
2
3
4
5
6
7
8
9
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:
1
2
3
4
5
6
7
8
___ 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=InnoDBDespite 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 JRASERVER-32590 - Database configuration incorrectly reports as OK for a MySQL DB where a schema is set.
Resolution
Stop JIRA.
Remove the entire line containing the <schema-name> tag from the
dbconfig.xml:1<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.
Was this helpful?