Configuring MySQL8 Databases

Version

Function Description

If an external database is configured for the BI system, the BI system will become more stable in cluster environments or situations with large amounts of data.

You can configure external databases during initialization or MySQL8 databases on the platform.

Note:

If the external database is configured, do not modify the username and password of the external database. Otherwise, project startup will fail.

If you need to modify them, see Modifying the Account and Password of the External Database.

Database Version

The following table describes the types and versions supported by the external database.

Hardware Configuration

Note:

1. You need to modify the occupied space according to the usage rate. You can configure the space to be one to two times larger than the size of the finedb folder in webapps/webroot/WEB-INF/embed (estimated size based on the actual size).

2. You are advised to use the memory with the same size of the memory reserved for the project configuration library.

3. The external database should be in the same network segment as the BI project to avoid network fluctuations.

Database Creation

Note:

Different FineBI projects cannot share the same external database. Otherwise, data may become disordered.

Create a database to store platform-related data.

The standard database creation statement is CREATE DATABASE `finedb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin.

You are advised to set the default character set of the database to utf8 and the default sort rule to utf8_bin.

Note:

1. You are advised to set the character set sort rule of the database to utf8_bin (case-sensitive) to prevent permission anomalies caused by case insensitivity. For details, see "Sort Rule Modification" in this document.

2. For MySQL 8.0.28 and later versions, you are advised to set the default character set of the database to utf8mb3 and the default sort rule to to utf8mb3_bin.

Database name:

The name can contain only numbers, letters, underscores, and periods (.).

Character set:

(1) When connecting the platform to an MySQL database, you need to set the character set sort rule (case-sensitive).

(2) You are advised to use utf8 (UTF-8 Unicode) encoding.

(3) You cannot use utf8mb4 encoding.

Sort rule:

Multiple sort rules for utf8 encoding exist in MySQL. The following describes the two most common sort rules:

(1) utf8_bin (recommended)

Definition: Each character in a character string is stored as binary data (case-sensitive).

Example: Alex and alex are considered to be two different values.

(2) utf8_general_ci

Definition: ci is short for case insensitive. This rule is the default utf8 encoding rule.

Example: Alex and alex are considered to be the same value.

External Database Configuration Entry

The three external database configuration entries are as follows:

(1) You can configure an external database for the BI system upon login as the super administrator for the first time.

(2) For systems that use built-in databases, you can log in to the BI system as the admin, choose System Management > System Setting > General > External Database > To Be Configured, and configure the external database for the system for the first time.

(3) For systems for which external databases have been enabled, you can log in to the BI system as the admin, choose System Management > System Setting > General > External Database > Configured, and migrate data to a new external database for the system.

External Database Configuration

After entering the external database configuration page, select the database type and enter the corresponding information of the actual database.

Setting Item

The following table describes each setting item.

Setting Item	Description
Database Type	Select MySQL.
Driver	Manually modify the value to com.mysql.cj.jdbc.Driver.
Database Name	For details, see the database name in section "Database Creation." Note: Your project must not share the database with other projects. You are advised to create a database. The database name can contain only numbers, letters, underscores, and periods (.).
Username/Host/Password/Port	Set these parameters according to the actual information of the local database. Note: The host name can contain only numbers, letters, underscores, hyphes (-), and periods (.). You need to have the create, delete, alter, update, select, insert, and index permissions.
Database Connection URL	Manually modify the value in the following the format (where IP address, Port number, Database name, and serverTimezone need to be set manually): jdbc:mysql://IP address:Port number/Database name?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=xxx/xxx&zeroDateTimeBehavior=CONVERT_TO_NULL In the above command, the value of serverTimezone must be in the xxx/xxx format, for example, Asia/Shanghai, America/Chicago, and Canada/Central. For details about the supported time zones, see https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.

Migrating Data to the Database to Be Enabled

You can determine whether to select Migrate Data to Database to Be Enabled based on your own needs. The following describes the details:

(1) Select Migrate Data to Database to Be Enabled when the new external database is empty.

After you click Enable New Database, the platform data of the original FineDB database will be migrated to the new external database.

(2) Select Migrate Data to Database to Be Enabled when the platform data already exists in the new external database.

After you click Enable New Database, the message "The platform data already exists in the database. Before data import, the original platform data will be cleared. Sure to connect to the database?" pops up.

After you click OK, the original platform data will be cleared, and the platform data of the original FineDB database will be migrated to the new external database.

(3) Deselect Migrate Data to Database to Be Enabled when the new external database is empty.

After you click Enable New Database, the message "The target database is a new database and can be used after the current data is migrated to the target database." pops up.

After you click Migrate and Enable, the platform data of the original FineDB database will be migrated to the new external database.

(4) Deselect Migrate Data to Database to Be Enabled when the platform data already exists in the new external database.

After you click Enable New Database, the BI project will automatically detect whether the JAR package version corresponding to the data in the target database is consistent with that in the current project.

• If the versions are inconsistent, the message "Unable to enable it. Project version required to enable the new database: xxx Current project version: xxx Ensure that the project version is consistent." is displayed. In this case, you fail to configure external databases.

• If the JAR package versions are consistent and the original FineDB database is a built-in database, the new external database is enabled directly.

• If the JAR package versions are consistent and the original FineDB database is an external database, the configuration information of the original database is automatically backed up before migration (as result of which the db.properties.bak file is generated in webapps\webroot\WEB-INF\config), and the new external database is enabled directly.

Successful Migration

Wait until the migration is successful. Different pop-up prompts may appear depending on the migration situation.

(1) New external database using data of the original FineDB database

The message "The target database has been enabled successfully." pops up. Click OK to complete the migration.

(2) New external database using data of the original FineDB database (which is a built-in database)

The message "Successfully switched to the target database. If data is different between the old and new databases, system running may be affected. You are advised to restart the project for normal use." pops up.  

Click OK and restart the BI project to complete the migration.

(3) New external database using data of the original FineDB database (which is an external database)

The following two messages pop up:

• Successfully switched to the target database. If data is different between the old and new databases, system running may be affected. You are advised to restart the project for normal use.  

• The original database configuration has been backed up to the config folder. You can use the backup file to restore the database configuration if necessary.

Click OK and restart the BI project to complete the migration.

Deleting Built-in Database Data Connections

The built-in FineDB database is an HSQL database that creates an in-memory database when used, which will occupy a large amount of memory when the data volume is large.

Therefore, after configuring the external database, check whether a data connection exists for the built-in FineDB database in Data Connection.

If so, delete the data connection in time. Otherwise, the connection will continue to occupy server memory.

Sort Rule Modification

If the database sort rule is not set to utf8_bin at the beginning but data has already been migrated, the settings need to be modified to utf8_bin in the fine_conf_entity, fine_conf_classname, and fine_authority tables. The following uses fine_conf_entity as an example:

(1) Query the current character set sort rule.

show full columns from fine_conf_entity

(2) Set the character set sort rule to utf8_bin.

• Set the default validation rule for columns.

ALTER TABLE fine_conf_entity MODIFY id VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin;

• Set the validation rule for the table.

ALTER TABLE fine_conf_entity DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

(3) Check whether the modification is successful.

show full columns from fine_conf_entity

(4) Use the same procedures as in steps (1), (2), and (3) to modify the sort rule to utf8_bin in fine_conf_classname and fine_authority, as shown in the following figure.

Data Connection Permission Exception

Problem:

After configuring the external database for the FineBI system, you have assigned the management permissions on the SQL dataset and corresponding data connections (with names in lowercase English, such as test) to members.

But when members log in to view the SQL dataset, the Modify SQL button does not appear, indicating that the management permissions assigned on data connections do not take effect, as shown in the following figure.

Cause:

The management permissions assigned on data connections are stored in the fine_authority table. The sort rule in this table is non-UTF8 encoding (case-insensitive). As a result, the data connection name test is recorded as TEST.

Therefore, the FineBI system fails to query the permission configuration records of the data connection (test) based on the fine_authority table, resulting in data connection configuration failure.

Solution:

Refer to section "Sort Rule Modification" to set the character set sort rule to utf8_bin (case-sensitive) in the fine_conf_entity, fine_conf_classname, and fine_authority tables. Then the data connection management permission assignment can take effect normally.