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-enterprise-4.0.2-standalone.tar.gz
[tmp ~]$ mv atlassian-jira-enterprise-4.0.2-standalone.tar.gz
[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)

Copy the JDBC driver

[jira ~]$ cp ../mysql-connector-java-5.1.10-bin.jar 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?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"

Jira を起動します。

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

エラーの有無の確認

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.

トラブルシューティング

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]

例:

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

Child pages