This documentation relates to the latest version of JIRA 4.0
If you are using an earlier version, please view the previous versions of the JIRA documentation and select the relevant version.

Setting up JIRA Standalone and MySQL on Linux

The latest official documentation on configuring JIRA with MySQL can be found here. This is a step-by-step supplement guide to installing the JIRA Standalone distribution with a MySQL database, to replace the default HSQLDB.

Unpack the JIRA Standalone distribution

[tmp ~]$ tar zxvf ~/apps/atlassian-jira-professional-3.11-standalone.tar.gz
[tmp ~]$ mv atlassian-jira-professional-3.11-standalone/ jira
[tmp ~]$ cd jira
[jira ~]$

Enable MySQL TCP/IP networking

Some Linux distributions (eg. Debian) disable MySQL's TCP/IP networking as a security precaution. You can test that MySQL is listening on the default port (3306) as follows:

jturner@teacup:~$ netstat -na | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:48211         127.0.0.1:3306          TIME_WAIT
tcp6       1      0 ::ffff:127.0.0.1:34785  ::ffff:127.0.0.1:3306   CLOSE_WAIT
Or if netstat isn't available:

jturner@teacup:~$ telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
D
5.0.13-rc-Debian_1-lo!X{$:;V#H!ju (press ctrl-] here)
telnet> quit
Connection closed.

On Debian, you can enable MySQL TCP connections by editing /etc/my.cnf, commenting out the 'skip-networking' flag, and restarting mysqld.

Create MySQL database and user

Create a MySQL user called 'jirauser' and database called 'jiradb':

jturner@teacup:~$ mysql --user=root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 559 to server version: 5.0.13-rc-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database jiradb character set utf8;
Query OK, 1 row affected (0.02 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on
  jiradb.* TO 'jirauser'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
The 'IDENTIFIED BY' phrase sets the password for the user (in this case, 'mypassword'). Your hostname may be different; you will find out in the next steps.

Now verify that user 'jirauser' can connect:

jturner@teacup:~$ mysql \--user=jirauser \--password=mypassword \--database=jiradb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 565 to server version: 5.0.13-rc-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
If you get errors like:
Access denied for user 'jirauser'@'localhost' (using password: YES)
You will need to adjust the 'host' field for the JIRA user record:
jturner@teacup:~$ mysql --user=root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 655 to server version: 5.0.13-rc-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| jirauser         | localhost |
| root             | localhost |
| root             | teacup    |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> update user set host='localhost.localdomain' where user='jirauser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
See also Atlassian's MySQL Tips.

If problems persist, see the MySQL Causes of Access Denied Errors page.

For more general information, see Adding New User Accounts to MySQL.

Copy the JDBC driver

[jira ~]$ cp ../mysql-connector-java-5.1.10-bin.jar common/lib/
[jira ~]$

Customise conf/server.xml

Customise conf/server.xml with MySQL details:

[jira ~]$ cp conf/server.xml /tmp
[jira ~]$ vim conf/server.xml
[jira ~]$ diff -u /tmp/server.xml conf/server.xml
--- /tmp/server.xml     2007-02-16 17:09:52.000000000 +1100
+++ conf/server.xml     2007-02-16 17:10:55.000000000 +1100
@@ -11,12 +11,10 @@

         <Context path="" docBase="${catalina.home}/atlassian-jira" reloadable="false">
           <Resource name="jdbc/JiraDS" auth="Container" type="javax.sql.DataSource"
-            username="sa"
-            password=""
-            driverClassName="org.hsqldb.jdbcDriver"
-            url="jdbc:hsqldb:${catalina.home}/database/jiradb"
-            minEvictableIdleTimeMillis="4000"
-            timeBetweenEvictionRunsMillis="5000"
+            username="jirauser"
+            password="mypassword"
+            driverClassName="com.mysql.jdbc.Driver"
+            url="jdbc:mysql://localhost/jiradb?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8"
+            maxActive="20"
             validationQuery="select 1" />

           <!-- NOTE: When a database server reboots or their is a network failure all the connections in the
The validationQuery parameter is required to prevent database connections from dropping out. This has been noted to occur when MySQL is used together with some Tomcat versions. JIRA 3.13 Standalone ships with one of the affected Tomcat versions (5.5.26). See JRA-15731 for further details.

Customise entityengine.xml

[jira ~]$ cp atlassian-jira/WEB-INF/classes/entityengine.xml /tmp
[jira ~]$ vim atlassian-jira/WEB-INF/classes/entityengine.xml
[jira ~]$ diff \-u /tmp/entityengine.xml atlassian-jira/WEB-INF/classes/entityengine.xml
--- /tmp/entityengine.xml       2007-02-16 17:11:49.000000000 \+1100
\++\+ atlassian-jira//WEB-INF/classes/entityengine.xml    2007-02-16 17:12:04.000000000 \+1100
@@ \-97,8 \+97,7 @@

PLEASE DO NOT set the use-foreign-key\* values to "true" as JIRA does not currently support this.
\-->
- <datasource name="defaultDS" field-type-name="hsql"
- schema-name="PUBLIC"
+    <datasource name="defaultDS" field-type-name="mysql"
helper-class="org.ofbiz.core.entity.GenericHelperDAO"
check-on-start="true"
use-foreign-keys="false"

Start JIRA

[jira ~]$ ./bin/startup.sh
....

Check for errors

Check the logs for any errors:16/02/2007 17:13:43 org.apache.coyote.http11.Http11BaseProtocol init
INFO: Initializing Coyote HTTP/1.1 on http-8080
16/02/2007 17:13:43 org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 1181 ms
16/02/2007 17:13:43 org.apache.catalina.realm.JAASRealm setContainer
INFO: Set JAAS app name Catalina
16/02/2007 17:13:43 org.apache.catalina.core.StandardService start
INFO: Starting service Catalina
16/02/2007 17:13:43 org.apache.catalina.core.StandardEngine start
INFO: Starting Servlet Engine: Apache Tomcat/5.5.20
16/02/2007 17:13:43 org.apache.catalina.core.StandardHost start
INFO: XML validation disabled
2007-02-16 17:13:48,209 main [core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database
2007-02-16 17:13:48,497 main [core.entity.jdbc.DatabaseUtil] Entity "ChangeGroup" has no table in the database
2007-02-16 17:13:48,671 main [core.entity.jdbc.DatabaseUtil] Entity "ChangeItem" has no table in the database
2007-02-16 17:13:49,110 main [core.entity.jdbc.DatabaseUtil] Entity "ColumnLayout" has no table in the database
2007-02-16 17:13:49,195 main [core.entity.jdbc.DatabaseUtil] Entity "ColumnLayoutItem" has no table in the database
....
2007-02-16 17:13:50,529 main [core.entity.jdbc.DatabaseUtil] Entity "WorkflowScheme" has no table in the database
2007-02-16 17:13:50,535 main [core.entity.jdbc.DatabaseUtil] Entity "WorkflowSchemeEntity" has no table in the database
2007-02-16 17:14:03,413 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl] Starting JIRA, Version: 3.X-#XXX
2007-02-16 17:14:03,413 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl] Configured to use database: mysql
2007-02-16 17:14:03,421 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl] Could not find Issue Cache Listener, adding.
2007-02-16 17:14:04,532 main WARN [ofbiz.core.entity.SequenceUtil] [SequenceUtil.SequenceBank.fillBank] first select failed: trying to add row, result set was empty for sequence: ListenerConfig
2007-02-16 17:14:04,666 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl] Could not find Mail Listener, adding.
2007-02-16 17:14:04,790 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl] Could not find Mail Queue Service, adding.
2007-02-16 17:14:04,805 main WARN [ofbiz.core.entity.SequenceUtil] [SequenceUtil.SequenceBank.fillBank] first select failed: trying to add row, result set was empty for sequence: ServiceConfig
2007-02-16 17:14:05,034 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl] Input Language has not been set.  Setting to 'English'
2007-02-16 17:14:05,038 main WARN [ofbiz.core.entity.SequenceUtil] [SequenceUtil.SequenceBank.fillBank] first select failed: trying to add row, result set was empty for sequence: OSPropertyEntry
2007-02-16 17:14:05,124 main INFO [atlassian.jira.upgrade.ConsistencyCheckImpl]

\****************************************************************************************************\*
JIRA 3.X build: XXX started. You can now access JIRA through your web browser.
\****************************************************************************************************\*

[Filter: profiling] Using parameter [jira_profile]
[Filter: profiling] defaulting to off [autostart=false]
[Filter: profiling] Turning filter off [jira_profile=off]
2007-02-16 17:14:07,726 main [webwork.dispatcher.ServletDispatcher] Unable to find 'webwork.multipart.saveDir' property setting. Defaulting to javax.servlet.context.tempdir
16/02/2007 17:14:08 org.apache.coyote.http11.Http11BaseProtocol start
INFO: Starting Coyote HTTP/1.1 on http-8080
16/02/2007 17:14:09 org.apache.catalina.storeconfig.StoreLoader load
INFO: Find registry server-registry.xml at classpath resource
16/02/2007 17:14:09 org.apache.catalina.startup.Catalina start
INFO: Server startup in 25881 ms
Again, if you see an 'Access denied' error:
Access denied for user 'jirauser'@'localhost.localdomain' (using password: YES)
Then you need to adjust your /etc/hosts so that 'localhost' comes before 'localhost.localdomain', and restart MySQL. This is a MySQL bug fixed in 5.0.11.

Run the Setup Wizard

Point a browser at http://localhost:8080/, and set up JIRA, as described in the Setup Wizard.

Troubleshooting

Q: I get the following error message in MySQL, "Attempted reconnect 3 times. Giving up." What should I do?
A:

jdbc:mysql://localhost/test?autoReconnect=true connection error :
Server connection failure during transaction.
Attempted reconnect 3 times. Giving up.
MySQL error message


To troubleshoot your MySQL connection, please follow the steps below:
1. Enter the following command to connect to MySQL:

mysql -p -u [dbuser] -h 127.0.0.1 [dbname]
For example,

mysql -p -u mydbuser -h 127.0.0.1 test

2. If you cannot connect to MySQL after entering your password, login to your mysql with the root account:

mysql -p -u root

And enter following command:

mysql> GRANT ALL PRIVILEGES ON <dbname>.* to <user>@127.0.0.1 identified by '<password>';
mysql> FLUSH PRIVILEGES;
where,
<dbname> is your database name,
<user> is your database user name,
<password> is your database password,

Don't forget the last command: 'FLUSH PRIVILEGES'

3. If you still cannot connect, please check that your MySQL is listening on the default port of 3306 and bind in your IP, 127.0.0.1 by running either of the following commands:

netstat -a |grep mysql
or,
netstat -a |grep 3306
If MySQL is listening, you should see the following message:

tcp 0 0 *:mysql *:* LISTEN

Alternatively, you also could check if your MySQL is listening on the default port by running this command:

telnet 127.0.0.1 3306
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

Comments (3)

 

  1. Feb 02, 2008

    Adam Fisk says:

    This is a great tutorial.  I've just posted a little script I made in part ...

    This is a great tutorial.  I've just posted a little script I made in part based on this, that automates the process of configuring JIRA for MySQL, including creating the database, configuring the jira user, unpacking the jira tgz, etc.  It's for JIRA standalone only, but it saves time.  It also allows you to easily customize your JIRA port if you wish.

    I write about it in more detail at on my blog.  Makes setting up JIRA for MySQL dead simple! 

  2. Jan 21, 2009

    Anonymous says:

    Please make sure to start mysqld with the option --default-table-type=InnoDB to ...

    Please make sure to start mysqld with the option --default-table-type=InnoDB to use InnoDB as default engine. MyISAM does not support transactions and will cause corrupt workflows on errors.

  3. about 7 hours ago

    Dirk Brilhart says:

    I agree with Adam above that this is an extremely helpful set of instructions fo...

    I agree with Adam above that this is an extremely helpful set of instructions for using standalone Jira on Linux with MySQL..  I recently migrated a Jira installation from one Red Hat server to another and upgraded from Jira 3.12 to 4.0.1.  Both Jira instances are standalone using MySQL databases.  In reading the Atlassian documentation, I found that the information I needed was spread across several different parts of the Installation and Upgrade Guide, and in some cases, the documentation made different suggestions at different places.  I thought I would post what worked for me:

    The task was to migrate a Jira 3.12 installation on one server to Jira 4.0.1 on a different server.  After installing JDK 1.6 and MySQL 5.0, these are the steps that we followed:

    - as the jira user, install the new version of jira with the default configuration

    - start jira to make sure all is well.  Connect to host:8080 and also check out the catalina.out log file.

    - create an empty mysql database and add a user as mentioned above

    - copy the jdbc connector jar file to common/lib

    - update the Jira configuration to match the old version ... don't copy files, edit the files.  This includes entityengine.xml, osuser.xml, jira-application.properties, and server.xml.

    - export the old jira data as XML and copy the file to the new machine

    - restart jira.  If all is well, when you go to localhost:8080, you will get the configuration set up page with an option to import data from an existing installation.  Go with the import data option.

    - enter a Jira 4.0 license key that is associated with the OLD installation's server id

    - import the XML file ... it takes several minutes to load.

    - Reload localhost:8080 and check the logs to make sure all is working properly.