Setting up MySQL for Unblu

Before creating the users and database in MySQL, you may need to make changes to the MySQL 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 configuration on both Linux and Windows.

Before creating the schema you must override the MySQL 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, NO_ZERO_DATE, and NO_AUTO_CREATE_USER. 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';

MySQL database setup

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

Listing 8. Create the unblu database and users for MySQL

CREATE DATABASE unblu DEFAULT CHARACTER SET = utf8mb4 DEFAULT COLLATE utf8mb4_unicode_ci;

CREATE USER 'unblu'@'%' IDENTIFIED BY 'secret';
CREATE USER 'unblu_admin'@'%' IDENTIFIED BY 'admin_secret';

GRANT SELECT,UPDATE,INSERT,DELETE ON unblu.* TO 'unblu'@'%';
GRANT SELECT,UPDATE,INSERT,DELETE,CREATE,ALTER,DROP,INDEX,REFERENCES ON unblu.* TO 'unblu_admin'@'%';

Set the following Unblu configuration properties:

Listing 9. Unblu MySQL configuration

com.unblu.storage.database.platform=org.eclipse.persistence.platform.database.MySQLPlatform
com.unblu.storage.database.driver=com.mysql.jdbc.Driver
com.unblu.storage.database.url=jdbc\:mysql\://<server>\:3306/unblu (1)
com.unblu.storage.database.jdbcProperties=connectTimeout\=60000,socketTimeout\=60000,useUnicode\=true,characterEncoding\=UTF-8,useLegacyDatetimeCode\=false,serverTimezone\=UTC,autoReconnectForPools\=true (2)
com.unblu.storage.database.user=unblu
com.unblu.storage.database.password=<user-pwd> (3)
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> (4)

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.

In MySQL, a schema is synonymous with a database. You can substitute the keyword SCHEMA for DATABASE in MySQL SQL syntax, for example using CREATE SCHEMA instead of CREATE DATABASE.