Connecting JIRA to MySQL

These instructions will help you connect JIRA to a MySQL 5.1.x database.

Note: A Linux-specific version of these instructions is available.

On this page:

1. Before you begin:

1.1 Export your existing JIRA data

If you are already using JIRA, create an export of your data as an XML backup. You will then be able to transfer data from your old database to your new database, as described in Switching databases.

1.2 Shut down JIRA

1.3 Known issues with MySQL

If you are using a MySQL database with any of the following:

• JIRA 3.13 Standalone or above,
• version 5.5.25 or higher of Tomcat 5,
• version 6.0.13 or higher of Tomcat 6,

you may experience problems with your connections dropping out (see http://jira.atlassian.com/browse/JRA-15731 for details). Please read Surviving Connection Closures for information on the changes required to your application server to address this.

If you experience any other issues with using JIRA and MySQL, please refer to our additional notes for more information.

2. Configure MySQL

1. Create a database user which JIRA will connect as (e.g. jirauser).
2. Create a database for JIRA to store issues in (e.g. jiradb). The database must have a character set of UTF8. Enter the following command from within the MySQL command client:

   create database jiradb character set utf8;

   (if you want your database to be named jiradb).
3. Ensure that the user has permission to connect to the database, and permission to create and populate tables.
4. Ensure MySQL is using a transactional database. Because parts of JIRA use transactions, you need to ensure that your MySQL database uses the InnoDB storage engine.

   Avoid using MyISAM as the storage engine for your JIRA database as this can lead to data corruption - JRA-24124.

   
   
   Be aware that JIRA uses the 'READ-COMMITTED' transaction isolation level with MySQL, which currently only supports row-based binary logging. If you use JIRA with a MySQL database whose storage engine is InnoDB AND require MySQL's binary logging features, you must configure MySQL's binary logging format to be 'row-based'. If not, you may encounter problems when creating issues in JIRA. For more information, please refer to JIRA Cannot Create Issues when Using MySQL with Binary Logging.

3. Copy the MySQL driver to your application server

1. Download the MySQL Connector/J JDBC driver v5.1

   A user has reported encountering problems using the Resin JDBC driver for MySQL. However, the Connector/J driver from MySQL works correctly

2. Add the MySQL JDBC driver jar (mysql-connector-java-5.x.x-bin.jar) to the lib/ directory.

4. Use the JIRA Configuration Tool

1. Click the 'Database' tab.
2. From the 'Database type' drop-down choose 'MySQL'.
   
3. Fill in the connection details for your MySQL database:
   • Hostname — The name or IP address of the machine that the MySQL DB is installed on.
   • Port — The TCP/IP port that the MySQL server is listening on. You can leave this blank to use the default port.
   • Database — The database that you want to save your data in.
   • Username — The user to connect to the MySQL server as.
   • Password — The password to use to authenticate with the MySQL server.
4. After typing in your settings, use the 'Test Connection' button to test the connection settings. The tool will attempt to connect to the database, and give a message with the results.
5. Click 'Save' to save your settings when you are done.
6. Restart JIRA in order for your new settings to take effect.

Congratulations — you have finished!

5. Configure the database connection manually

5.1. Configure your application server to connect to MySQL

1. Edit conf/server.xml (if you are using JIRA Standalone) and customise the username, password, driverClassName and url parameters for the Datasource. (If you are using JIRA WAR/EAR, edit the appropriate file on your application server; e.g. for Tomcat, edit conf/Catalina/localhost/jira.xml).

   The URL in the XML below assumes a UTF-8 database — i.e. created with create database jiradb character set utf8;. If you don't specify character set utf8 you risk getting 'Data truncation: Data too long for column' errors when importing data or corruption of non-supported characters. See storing non-ASCII characters in MySQL for details.

   Note: if entered into an XML file, escape the '&' by adding 'amp;' to the end of it, as follows:

   <Server port="8005" shutdown="SHUTDOWN">
   
     <Service name="Catalina">
   
       <Connector port="8080"
         maxHttpHeaderSize="8192" maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
         enableLookups="false" redirectPort="8443" acceptCount="100"
         connectionTimeout="20000" disableUploadTimeout="true" />
   
       <Engine name="Catalina" defaultHost="localhost">
         <Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true">
   
           <Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false">
             <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
               username="[enter db username]"
               password="[enter db password]"
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost/jiradb?useUnicode=true&amp;characterEncoding=UTF8"
               [ delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis params here ]
               maxActive="20"
               [ If maxActive is not set it uses the default number of the connections (8), which may be too little and can cause performance issues ]
               validationQuery="select 1"/>
   
             <Resource name="UserTransaction" auth="Container" type="javax.transaction.UserTransaction"
               factory="org.objectweb.jotm.UserTransactionFactory" jotm.timeout="60"/>
             <Manager pathname=""/>
           </Context>
   
         </Host>
       </Engine>
     </Service>
   </Server>
   

   (Note: if you can't find this section at all, you've probably got the wrong file - search for mentions of 'jira' in the files under conf/.)
2. If you are using JIRA Standalone, edit conf/server.xml, and delete the minEvictableIdleTimeMillis and timeBetweenEvictionRunsMillis attributes (which are only needed for HSQL, and degrade performance otherwise).
3. If you want to set up connection validation for your application server (i.e. your database connections are dropping out), you will need to add the validationQuery parameter to the Datasource and set it to "select 1". See the XML above for an example of this.

5.2 Configure the JIRA Entity Engine

1. Edit atlassian-jira/WEB-INF/classes/entityengine.xml (if you are using JIRA Standalone) or edit-webapp/WEB-INF/classes/entityengine.xml (JIRA WAR/EAR), and change the field-type-name attribute to mysql. (If you forget to do this and start JIRA, it may create database tables incorrectly. See this page if this happens to you). Also delete the schema-name="PUBLIC" attribute, if it exists:

   <!-- DATASOURCE - You will need to update this tag for your installation.
   
   -->
     <datasource name="defaultDS" field-type-name="mysql"[ delete this, if it exists: schema-name="PUBLIC" ]
         helper-class="org.ofbiz.core.entity.GenericHelperDAO"
         check-on-start="true"
         use-foreign-keys="false"
         ...
       

   If you are using JIRA WAR/EAR, your application server may require other changes to entityengine.xml (e.g. to customise the jndi-jdbc tag).

5.2 Next steps

You should now have an application server configured to connect to a database, and JIRA configured to use the correct database type. If you are using JIRA Standalone, start it up and watch the logs for any errors. If you are using the JIRA WAR/EAR distribution, rebuild and redeploy the webapp in your application server.

Installation notes

Please see JIRA and MySQL.