Setting up MariaDB for Unblu
MariaDB is a fork of MySQL and largely compatible with MySQL. The configuration in Unblu is slightly different though.
Before creating the users and database in MariaDB, you may need to make changes to the MariaDB configuration. Read the following section before creating the database users and Unblu database. |
Preparation
There are four issues that can occur when using the default MySQL or MariaDB configuration on both Linux and Windows.
Before creating the schema you must override the MySQL or MariaDB defaults according to the instructions below.
Character set
This change is required because emojis require 4 bytes and the default MySQL character set can only handle 3 bytes. Without it, an SQL exception occurs when users enter emojis in a chat.
my.cnf
(Unix) or my.ini
(Windows)
[mysqld]
character-set-server=utf8mb4
Max allowed packet size
Ensure that the value of max_allowed_packet
is slightly more than twice as large as the Unblu setting com.unblu.filemanager.maximumUploadSize. This is necessary because of MySQL’s text-based wire protocol, which encodes binary values as text.
To support uploading larger files to Unblu, set max_allowed_packet
to a higher value. The maximum file size is 1 Gigabyte (1G
).
my.cnf
(Unix) or my.ini
(Windows)
[mysqld]
max_allowed_packet=<size> (Example: 16M)
You can check the current maximum file size by running the following query:
SHOW VARIABLES LIKE 'max_allowed_packet';
Network write timeout
To support uploading large files to Unblu, set net_write_timeout
to a higher value. The default timeout is 60 seconds. In our test environment, this wasn’t sufficient to handle files 15MB large.
my.cnf
(Unix) or my.ini
(Windows)
[mysqld]
net_write_timeout=<seconds> (Example: 180)
You can check the current timeout with the following query:
SHOW VARIABLES LIKE 'net_write_timeout';
SQL mode
Unblu requires that the MySQL parameters NO_ZERO_IN_DATE
and NO_ZERO_DATE
NOT be active. If they’re active, an error occurs during setup and/or migration.
my.cnf
(Unix) or my.ini
(Windows)
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
In the example above, remove the entries NO_ZERO_IN_DATE
and NO_ZERO_DATE
. (If you’re running MySQL 8, you must also remove NO_AUTO_CREATE_USER
, since it isn’t supported by MySQL 8.) Then restart the MySQL server.
Run the query below to check if the problem exists in your system:
SHOW VARIABLES LIKE 'sql_mode';
MariaDB database setup
To create the unblu
database and users on MariaDB, run the following commands:
unblu
database and users
CREATE DATABASE unblu DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE utf8_general_ci;
GRANT SELECT,UPDATE,INSERT,DELETE ON unblu.* TO 'unblu'@'%' IDENTIFIED BY 'secret';
GRANT SELECT,UPDATE,INSERT,DELETE,CREATE,ALTER,DROP,INDEX,REFERENCES ON unblu.* TO 'unblu_admin'@'%' IDENTIFIED BY 'admin_secret';
Next, configure Unblu to use your MariaDB database:
com.unblu.storage.database.platform=org.eclipse.persistence.platform.database.MySQLPlatform
com.unblu.storage.database.driver=org.mariadb.jdbc.Driver
com.unblu.storage.database.url=jdbc\:mysql\://<server>\:3306/unblu (1)
com.unblu.storage.database.jdbcProperties=connectTimeout\=60000,socketTimeout\=60000,useUnicode\=yes,characterEncoding\=UTF-8,useLegacyDatetimeCode\=true,serverTimezone\=UTC,useSSL\=false
com.unblu.storage.database.user=unblu
com.unblu.storage.database.password=<user-pwd> (2)
com.unblu.storage.database.schema=unblu
com.unblu.storage.database.liquibaseSchema=unblu
com.unblu.storage.database.adminUser=unblu_admin
com.unblu.storage.database.adminPassword=<admin-pwd> (3)
1 | Replace <server> with your database hostname or IP address. |
2 | Replace <user-pwd> with the password of the unblu user. |
3 | Replace <admin-pwd> with the password of the unblu_admin user. |