Incorrect database type specified

Background

JIRA needs to know what kind of database it will be using, in order to generate database tables of the correct data types, and to generate correctly formatted SQL. The database type is specified in atlassian-jira/WEB-INF/classes/entityengine.xml (JIRA Standalone), or edit-webapp/WEB-INF/classes/entityengine.xml (JIRA WAR/Webapp):

...
    <datasource name="defaultDS" field-type-name="hsql"
        helper-class="org.ofbiz.core.entity.GenericHelperDAO"
        check-on-start="true"
        use-foreign-keys="false"
        use-foreign-key-indices="false"
        ...

In this example, JIRA expects to use HSQLDB (the default for JIRA Standalone).

If you've got it wrong ...

If you forgot to edit the entityengine.xml file (see the documentation), then follow these steps to recover:

1. Fix the type in entityengine.xml

• If you are using JIRA Standalone, edit atlassian-jira/WEB-INF/classes/entityengine.xml, and correct the database type (valid types are listed in entityengine.xml). See the Using External Databases with JIRA Standalone guide to check if you haven't made other mistakes.
• If you're running JIRA as an EAR/WAR distribution, you need to:
  • Edit edit-webapp/WEB-INF/classes/entityengine.xml and fix the type, as above.
  • Run build.bat or build.sh in the root, to rebuild the JIRA webapp.
  • Deploy the rebuilt webapp into your app server.

2. Fix the database

Is this the first time you have run JIRA?

If so, the database has been created incorrectly. Specifically, table columns have been created with incorrect data types, and you will see warnings like these in the logs:

2005-02-10 12:24:33,307 [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE jiraaction (ID BIGINT NOT NULL, issueid BIGINT, AUTHOR VARCHAR, actiontype VARCHAR, actionlevel
  VARCHAR, actionbody VARCHAR, CREATED TIMESTAMP, actionnum BIGINT, CONSTRAINT PK_jiraaction PRIMARY KEY (ID))
Error was: java.sql.SQLException: 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 ' actiontype VARCHAR, actionlevel VARCHAR, actionbody VARCHAR, C
2005-02-10 12:24:33,308 [core.entity.jdbc.DatabaseUtil] Entity "ChangeGroup" has no table in the database
2005-02-10 12:24:33,309 [core.entity.jdbc.DatabaseUtil] Could not create table "changegroup"
2005-02-10 12:24:33,309 [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE TABLE changegroup (ID BIGINT NOT NULL, issueid BIGINT, AUTHOR VARCHAR, CREATED TIMESTAMP, CONSTRAINT
  PK_changegroup PRIMARY KEY (ID))
Error was: java.sql.SQLException: 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 ' CREATED TIMESTAMP, CONSTRAINT PK_changegroup PRIMARY KEY (ID))
2005-02-10 12:24:33,310 [core.entity.jdbc.DatabaseUtil] Entity "ChangeItem" has no table in the database
2005-02-10 12:24:33,310 [core.entity.jdbc.DatabaseUtil] Could not create table "changeitem"

The solution is to drop (delete) and recreate the database. When next restarted with the correct data types, JIRA will recreate the tables correctly.

Upgrading JIRA?

This situation is potentially problematic, because the newer version of JIRA may have added tables or columns with incorrect data types to your existing database schema.

The safest solution is to start a new database, and import an XML backup made before the upgrade.

If for some reason, you cannot import an XML backup (eg. your upgraded instance has been in production for a few days and contains new data), it is generally possible to patch the database by hand with SQL 'alter table' statements. Please review the log files for information on what types JIRA expects, and what is actually present. JIRA will print this information every time it starts up. If in doubt, attach the logs and other relevant information to a support request on our support system.

Other situations

If this is not the first time JIRA has loaded, and you are not upgrading, you probably do not need to fix the data. After fixing the entityengine.xml file, restart and check the logs for errors. If there are none, the database is fine.

Need help?

Please create a support request and attach the startup logs, your current entityengine.xml file, and any other information relevant.