Contact usRequest a demo

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.

Listing 1. 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).

Listing 2. 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:

Listing 3. Access the current maximum file size 
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.

Listing 4. my.cnf (Unix) or my.ini (Windows) 
[mysqld]

net_write_timeout=<seconds> (Example: 180)

You can check the current timeout with the following query:

Listing 5. Access the current network write timeout 
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.

Listing 6. 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:

Listing 7. Query to run to determine MySQL SQL modes 
SHOW VARIABLES LIKE 'sql_mode';

MariaDB database setup

To create the unblu database and users on MariaDB, run the following commands:

Listing 8. Create the 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:

Listing 9. Unblu MariaDB configuration 
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.