Cloud
Data Center 8.20
Versions

Connecting Jira applications to MySQL 5.7

Connecting Jira applications to a database

On this page

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

These instructions will help you connect Jira to a supported MySQL database.

Before you begin

Here is some prerequisite information you should know about:

  • Check known issues.
  • If you are migrating Jira to another server, create an export of your data as an  backup. You will then be able to transfer data from your old database to your new database, as described in Switching databases.
  • If you plan to set up Confluence and Jira on the same MySQL server, read the Confluence MySQL setup guide. Confluence requirements are more strict than Jira's, so you should configure MySQL to suit Confluence. This configuration will work for Jira, too.
  • Shut down Jira before you begin, unless you are running the setup wizard.

1. Create and configure the MySQL database

When creating the database, remember your database name, user name, and port number, because you'll need them later to connect Jira to your database.


  1. Create a database user which Jira will connect as, for example jiradbuser.

  2. Create a database for Jira to store issues in, for example jiradb.

    The database must have a character set of UTF8. To set it, enter the following command from within the MySQL command client:

    CREATE DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

  3. Make sure the user has permission to connect to the database, and permission to create and populate tables. You can provide these permissions with the following commands.

    MySQL 5.7.0 - 5.7.5: 

    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;


    MySQL 5.7.6 and later (must also include the REFERENCES permission):

    GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;
  4. Edit the my.cnf or my.ini (Windows) file in your MySQL Server (for detailed instructions on editing these files, see MySQL Option Files).

  5. Locate the [mysqld] section in the file, and add or modify the following parameters:

    • Set the default storage engine to InnoDB:

      [mysqld]
...
default-storage-engine=INNODB
...

    • Specify the character set used by the database server:

      [mysqld]
...
character_set_server=utf8mb4
...

    • Set the default row format to DYNAMIC:

      [mysqld]
...
innodb_default_row_format=DYNAMIC
...

    • Enable the large prefix:

      [mysqld]
...
innodb_large_prefix=ON
...

    • Set the InnoDB file format to Barracuda:

      [mysqld]
...
innodb_file_format=Barracuda
...

    • Specify the value of innodb_log_file_size to be at least 2G:

      [mysqld]
...
innodb_log_file_size=2G
...

    • Ensure the sql_mode parameter does not specify NO_AUTO_VALUE_ON_ZERO

      // remove this if it exists
sql_mode = NO_AUTO_VALUE_ON_ZERO

  6. Restart your MySQL server for the changes to take effect.

    Windows...

    Use the Windows Services manager to restart the service.

    Linux...

    Run one of the following commands, depending on your setup:

    /etc/init.d/mysqld stop
    /etc/init.d/mysql stop
    service mysqld stop


    Then, run the same command, replacing stop with start.

2. Copy the MySQL JDBC driver

Copy the MySQL JDBC driver to the Jira installation directory.

  1. Download one of the recommended MySQL drivers:

  2. Copy the driver to the following directory:

    <Jira-installation-directory>/lib

    If you are installing Jira using the Windows installer, you will need to do this step after running the Windows installer, but  before  running the setup wizard.

  3. Restart the Jira service.

  4. If you are installing Jira, skip the rest of the instructions on this page and access Jira in your browser to run the setup wizard instead.

3. Configure Jira to connect to the database

There are two ways to configure your Jira server to connect to your MySQL database:

Setup wizard

Use the setup wizard if you have just installed Jira, and are setting it up for the first time. Your settings will be saved to the dbconfig.xml file in your Jira home directory.

Connect to MySQL with the setup wizard

The Jira setup wizard will display when you access Jira for the first time in your browser.

  1. In the first screen, 'Configure Language and Database', set Database Connection to My own database.
  2. Set Database Type to MySQL 5.7+.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.

Configuration tool

Use the configuration tool if you have an existing Jira instance. Your settings will be saved to the dbconfig.xml file in your Jira home directory.

Connect to MySQL with configuration tool
  1. Run the Jira configuration tool as follows:

  2. Navigate to the Database tab and set Database type to MySQL 5.7+.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
  5. Restart Jira.

4. Start Jira

You should now have Jira configured to connect to your MySQL database. The next step is to start it up!


Database connection fields

The table below explains database connection fields that you can find in the setup wizard, Jira configuration tool, or the dbconfig.xml file.

View database connection fields
Setup wizard / Configuration toolDescriptiondbconfig.xml
HostnameThe name or IP address of the machine that the MySQL server is installed on.

Located in the <url> tag. In the example below, dbserver.

<url>jdbc:mysql:/dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>


If you use an IPv6 address, the URL needs to look like this:

<url>jdbc:mysql://address=(protocol=tcp)(host=dbserver)(port=3306)/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>
PortThe TCP/IP port that the MySQL server is listening on. You can leave this blank to use the default port.

Located in the <url> tag. In the example below, 3306.

<url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>
DatabaseThe name of your MySQL database (into which Jira will save its data). You should have created this in Step 1 above.

Located in the <url> tag. In the example below, jiradb.

<url>jdbc:mysql://dbserver:3306/jiradb ?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>
UsernameThe user that Jira uses to connect to the MySQL server. You should have created this in Step 1 above.
<username>jiradbuser</username>
PasswordThe user's password — used to authenticate with the MySQL server.
<password>jiradbuser</password>
View sample dbconfig.xml file
  • For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file above, see Tuning database connections.
  • Both the Jira setup wizard and database configuration tool also add the element <validation-query>select 1</validation-query> to this file, which is usually required when running Jira with default MySQL installations. See Surviving connection closures for more information.
  • The database URL in the example below assumes a UTF-8 database — i.e. that your database was created using a command similar to create database jiradb character set utf8; If you do not specify character set utf8 when creating this database, you risk getting 'Data truncation: Data too long for column' errors when importing data or corruption of non-supported characters.
  • The database URL in the example below contains the sessionVariables=default_storage_engine=InnoDB parameter. We strongly recommend adding this parameter to avoid data corruption.


<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mysql57</database-type>
  <jdbc-datasource>
    <url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <username>jiradbuser</username>
    <password>password</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
  
    <pool-test-while-idle>true</pool-test-while-idle>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <validation-query-timeout>3</validation-query-timeout>
  </jdbc-datasource>
</jira-database-config>

Known issues

Here's a list of known issues for this database. Expand each of them for more details.

Health check displays a warning about an unsupported collation

The database health check displays the following warning despite configuring Jira to use MySQL 5.7:

Your mysql database is currently using an unsupported collation: utf8mb4_bin. You should change this to a supported collation: utf8_bin

Solution

This problem might occur if the dbconfig.xml file doesn't get updated with the proper database type, still using mysql instead of mysql57. You can fix this problem by manually editing the file:

  1. Go to Jira home directory, and edit the dbconfig.xml file.
  2. Change the database-type to mysql57.

You can also change this by using the Jira configuration tool, as described above.

Hostnames in permissions are compared as strings

If you grant permissions in MySQL to a hostname such as localhost, then you'll need to use the same string when connecting to the database from Jira. Using 127.0.0.1 won't work, even though it resolves to the same place. This mistake will result in warnings about tables not being found, because the JDBC connection didn't have permissions to create the new tables when Jira was set up.

Possible connection problems

If you are using a MySQL database with any of the following, you may experience problems with your connections dropping out (see JRA-15731 for details):

  • Jira 3.13 or later,
  • version 5.5.25 or higher of Tomcat 5,
  • version 6.0.13 or higher of Tomcat 6,

For more info on how to address this, see Surviving connection closures.

Special characters for database password are not supported

Special characters for database password are not supported, because Jira can't interpret them.

Binary logging

Jira uses the READ-COMMITED transaction isolation level with MySQL, which currently supports only row-based binary logging.

If you require MySQL's binary logging features, you must configure MySQL's binary logging format to be 'row-based'. Otherwise, you may encounter problems when creating issues in Jira.

Last modified on Jan 26, 2023

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.