Eating our own dog food – Running JIRA on MariaDB
A couple of weeks ago we announced that we were moving from a hosted instance of JIRA to our self hosted instance. The main reason was that we hit 2000 active users in the hosted instance of JIRA and that is the upper limit that it supports. We obviously wanted to allow more people to be active in reporting and commenting on bugs and features for MariaDB. That’s why we set up our own instance, which now is up and running at jira.mariadb.org.
Thank you Atlassian, the company behind JIRA, for providing the hosted instance of JIRA for the MariaDB project over the last three years! It has served us well! At the same time we’re of course happy that the MariaDB project has outgrown the hosted instance.
Atlassian does not yet officially support MariaDB as a database for JIRA although some installations already use MariaDB Server as the database for JIRA. There is also demand being built up for official support of MariaDB Server for JIRA. Check out the request(s) about it in Atlassian’s own JIRA. The “Support for MariaDB” request is found here. If you think it’s important you can vote and comment on that issue to give it some more attention.
For the tracking of the MariaDB development project itself we of course wanted to use MariaDB as the database beneath JIRA.
jira.mariadb.org has been up and running for a week now with MariaDB Server 10.1.12 as the database server and MariaDB’s Java Connector as the database driver. The setup has been very successful so far, i.e. we haven’t experienced any problems. Below are a few instructions on how to use MariaDB as the database server for JIRA.
First of all install MariaDB. If you’re on one of the major Linux distributions the MariaDB Repository Tool will help you install the latest version of MariaDB. It’s found here.
Start installing JIRA as you would following the installation instructions.
- Install JIRA executables on the server as described but do not yet open the Setup Wizard in the browser.
- Download MySQL Connector/J. You’ll need it temporarily during the setup process. At the time of writing I used the following commands to get it and put it in the right place:
wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.37.tar.gz tar -xvzf mysql-connector-java-5.1.37.tar.gz sudo cp mysql-connector-java-5.1.37/mysql-connector-java-5.1.37-bin.jar /opt/atlassian/jira/lib/
- Now continue in the browser with JIRA’s Setup Wizard, usually at this point found at http://localhost:8080 . On the first page make sure to choose the option I’ll set it up myself (for production environments). You’ll next be confronted with the screen Configure a database for JIRA. Do the following selections:
Database setup: My Own Database Database type: MySQL Hostname: localhost Database: [name of database] Username: [username] Password: [password]
Click Test connection to confirm it’s working and then Next. After clicking Next the database configuration file will be created in JIRA’s data directory. At this point don’t go any further in the wizard since now you’ll want to switch to MariaDB’s Java Connector.
- Now it’s time to install the MariaDB Java Connector. You’ll get the jar -file from https://mariadb.com/my_portal/download/java-client . Notice that you’ll need to log in. I used version 1.3.5 of the connector, but now 1.3.6 is available.
- Put the connector in JIRA’s directory for drivers/libraries, by default /opt/atlassian/jira/lib/
- Modify dbconfig.xml which is found in JIRA’s data directory, by default /var/atlassian/application-data/jira . Update driver-class to refer to the MariaDB Connector:
<driver-class>org.mariadb.jdbc.Driver</driver-class> - Restart JIRA, if you’re on Debian/Ubuntu as I am this is the command
sudo service jira stop sudo service jira start
- Now you can continue with the Setup Wizard in the browser and when you come to the end you should have JIRA running on MariaDB using the MariaDB Java connector.
The MariaDB JIRA is open to anyone and has surpassed the amount of 2000 active users. This means that it’s a very active JIRA instance where a lot is going on all the time. We’ll regularly update JIRA itself, but also MariaDB and the connector to make sure that everything is running smoothly in future versions as well. I hope this encourages others to also run JIRA on top of MariaDB.
Maybe the involvement of MariaDB can help to fix this bug:
https://jira.atlassian.com/browse/JRA-36135
It’d be great to see your my.cnf too
The my.cnf is pretty much default. The only changes are that datadir points to non-default place because we wanted the data files on a separate partition and then the following lines were commented out:
log_bin
log_bin_index
slow_query_log_file
long_query_time
log_slow_verbosity
Thank you for the write up. I’m considering doing exactly what this article describes. Our developers are interested in setting up a mission critical JIRA instance for QA and bug tracking. This translates to 1000 active users and several million bugs (eventually).
You mention having 2000 active JIRA users. I was wondering if you could share additional infrastructure details about your JIRA instance. How large is the production DB in terms of disk space? What are you using to backup the DB (xtradump, mysqldump, SAN snapshot)?
Rick,
I’ll try to give you some specs:
1) The disk space for the database is this
sudo du -sh jiradb
558M jiradb
2a) We use XtraBackup for database backups and rsync the backup files to a remote server
2b) We separately backup Jira’s data directory, which include the attachements and avatars directories. We just copy it over FTP to another server
3) The server itself has a Intel Xeon E5 E5-1620v2 CPU, 2TB of disk and 64GB of RAM.
Let me know if you have further questions.
Jira works almost perfectly with a mariadb cluster as database back-end, we were using the mysql connector and are testing the mariadb connector on our testing site.. The only thing we found is on busy days we found that there were some communication errors with the database: “The last packet successfully received from the server was 296,877 milliseconds ago. The last packet sent successfully to the server was 1 milliseconds ago”
However the communication with mysql is not interrupted. Normally the transaction is restarted except with incoming mail listeners. These are stopped and need a manual restart. Previously we have seen more of these errors but they were mostly resolved by updating to a new mysql connector. So we are hoping the mariadb connector will solve a lot of these problems as well..
These were not related to mariadb.. These were related to haproxy client/server timeout settings… It is all working beautifully right now
New link to Connector/J:
http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.39.tar.gz
With the mariadb driver I get an awfull lot of errors:
unexpected end of stream, read 0 bytes from 4)
These were not related to mariadb.. These were related to haproxy client/server timeout settings… It is all working beautifully right now
We have similiar problems with our environment. What have you done to fix your problems?
Hi,
Thanks for this very helpful post.
I got a question that might sound dumb for the DB experts you guys are…
When I start the config.sh script provided by Jira, it works well if I run it as a non root user, meaning using sudo also fails. The returned error says :
IO Exception occurred while trying to load settings.
Unknown database type name org.mariadb.jdbc.Driver
I checked all the configuration files I could but I cannot get even a clue…
Hope you can help me there,
Thanks!
Sounds like you haven’t copied the mariadb-java-client-1.4.6.jar file to the correct directory.
It should be in the $JIRA_HOME//lib/
Thanks for the reply Daniel.
The file is there, that is why it works when starting the script without sudo, however as soon as using sudo or directly root, then it would throw the error previously mentioned. Any difference, somewhere, between root and “normal” users? Something that a “normal” user could do but not root?
Could be the $JIRA_HOME env setting is not there ??
Checked too, it is there for both the sudo environment and root.
Try to chown everything in the JIRA_HOME dir to the correct jira user.. probably there is something like the maridb driver only readable for root
Are you missing something?
The downloaded *.jar file for the mariadb connector is named
“mariadb-java-client-.jar”
and in the dbconfig.xml you only state
“org.mariadb.jdbc.Driver”
How can then JIRA make the mapping between the jdbc driver and the name of the actual asset (“mariadb-java-client-.jar”) ?
Shouldn’t the name of the asset be stated somewhere?
So this works perfect for JIRA but not so much for confluence.
I did all the equivalent steps of adding the jdbc connector driver and changing the setting of which jdbc driver to use and I just get a white screen of death for confluence.
Scratch that, it does work with confluence. There was a different problem that I’m not 100% sure what was.
I rerean the latest confluence installer and my confluence returned. Then I switched everything to mariadb and it worked.
I did start both the mysql and mysqld services, not sure if that mattered.
What did you do to get it working with Confluence too?
Afaict there is no dbconfig.xml with confluence. I have been trying with both jdbc:mysql: and jdbc:mariadb: connection strings (with mariadb client in the lib dir) and the connection test works, but I get a 500 when actually creating the schema.
Thanks very much for this article, Jira is working well with MariaDB.
Does the same setup work for Confluence, that’s my next target
See the earlier comment, yes, it does work with Confluence.
I have hooked up our Jira instance to a MariaDB database and am getting this error:
Caused by: java.sql.SQLException: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
any ideas how to fix this?
Thanks,
Sarah
This issue is documented on Atlassian’s site, and they suggest changing the binlog format to row. https://confluence.atlassian.com/jirakb/mysql-binary-logging-problem-with-innodb-when-creating-a-workflow-196116927.html
Take a look at https://mariadb.com/kb/en/mariadb/binary-log-formats/ to read more about binary log formats and changing them.
Thank you!