By default JATOS uses an embedded H2 database and no further setup is necessary but it can be easily configured to work with a MySQL database.
Possible scenarios why one would use an external database are
- your JATOS will be used by more than a few users (e.g. several research groups or an institute-wide installation)
- your JATOS will run studies with many participants
- the expected traffic is rather high (the studies produce a lot of result data)
- you want to be able to do a regular database backup (with the embedded H2 database this would involve stopping JATOS)
- higher trust in the reliability of MySQL (although we had no problems with H2 so far)
One could install the external database on the same server as JATOS is running or on an extra server depending on ones need.
There are many manuals out there, e.g. this one. One way to set up MySQL:
Install MySQL, e.g. on Ubuntu
JATOS requires MySQL >= 5.7 (8.x is fine)
sudo apt install mysql-server
Log in to MySQL’s command line terminal:
mysql -u root -p
Create a user for JATOS:
You can use any username and password but have to remember them when configuring JATOS later on.
GRANT ALL PRIVILEGES ON *.* TO 'jatosuser'@'localhost' IDENTIFIED BY 'password';
Log out and log in with the newly created user:
mysql -u jatosuser -p
Create a database for JATOS:
Character set and collation are important - otherwise you won’t have full UTF-8 support
CREATE DATABASE jatos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Exit the database with
Appart from giving JATOS access to the database it is not necessary to create any tables - JATOS is doing this automatically.
Now you have to configure JATOS to use your MySQL.
There are three ways to set up JATOS to work with a MySQL database. If you are in doubt use ‘production.conf’.
Via JATOS config file which is in your JATOS folder in the
Change IP, port, username and password to your needs.
db.default.url="jdbc:mysql://127.0.0.1:3306/jatos?characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC" db.default.user="jatosuser" db.default.password="mypassword" db.default.driver=com.mysql.cj.jdbc.Driver
Always restart JATOS after making any changes to the configuration (e.g. with
- Via command-line arguments:
-DJATOS_DB_URL- specifies the URL to the database
-DJATOS_DB_USERNAME- set your username
-DJATOS_DB_PASSWORD- set your password
E.g. to connect to a MySQL running on 127.0.0.1 and port 3306 use (but change username and password):
loader.sh start -DJATOS_DB_URL='jdbc:mysql://127.0.0.1:3306/jatos?characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC' -DJATOS_DB_USERNAME=sa -DJATOS_DB_PASSWORD=sa -DJATOS_DB_DRIVER=com.mysql.cj.jdbc.Driver
Via environment variables (change IP, port, username and password)
export JATOS_DB_URL="jdbc:mysql://127.0.0.1:3306/jatos?characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC" export JATOS_DB_USERNAME=jatosuser export JATOS_DB_PASSWORD='mypassword' export JATOS_DB_DRIVER=com.mysql.cj.jdbc.Driver
You can confirm that JATOS is accessing the correct database by looking in the logs. One of the lines after JATOS starts should look like this (with your database URI):
14:06:01.760 [info] - p.a.d.DefaultDBApi - Database [default] initialized at jdbc:mysql://localhost/jatos?characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
Done. Your JATOS uses your MySQL now.
[Optional] Deactivate MySQL’s binary log
MySQL’s binary logs (also called binlogs) serve two purposes: replication and data recovery. More can be found in MySQLs documentation.
The problem with binary logs is that they can take up quite some disk space depending on the experiments you run on your JATOS. The location of those log files is specified in MySQL’s config but on many systems they are under
/var/lib/mysql. If you have a single MySQL instance (and therefore do not use replication) and you do not need MySQL’s data recovery (e.g. have a different backup mechanism) than it is safe to deactivate the binary logs.
skip-log-bin to the end of your MySQL config (details). On many Linux systems the config is in
The part of your ‘mysqld.cnf’ that configures the binary logs could then look similar to this:
# The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. # server-id = 1 # log_bin = /var/log/mysql/mysql-bin.log # binlog_expire_logs_seconds = 2592000 # max_binlog_size = 100M # binlog_do_db = include_database_name # binlog_ignore_db = include_database_name skip-log-bin
You have to restart MySQL for the changes to take effect.
[Optional] Increase ‘max_allowed_packet’ size in older MySQLs
If you have an older MySQL (< 8.x.x) and your experiments will have large resut data you might want to increase the ‘max_allowed_packet’ size. If your result data is larger than the ‘max_allowed_packet’ JATOS will just return an ‘internal server error’. In JATOS’ log in will look similar to this:
[ERROR] - g.ErrorHandler - Internal JATOS error [ERROR] - o.h.e.j.s.SqlExceptionHelper - Packet for query is too large (5,920,824 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable. [WARN] - o.h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: S1000
From 8.x.x the ‘max_allowed_packet’ is by default 64MB and this is usually more than enough. But in version smaller than 8.x.x it is just 4MB by default and before 5.6.6 it’s just 1MB.
To increase the ‘max_allowed_packet’ size just add it to the end of your MySQL config. On many Linux systems the config is in
/etc/mysql/mysql.conf.d/mysqld.cnf. E.g. to set it to 64MB:
You have to restart MySQL for the changes to take effect.