Using MySQL 5 as the External Database

Function Description

A FineDataLink system with an external database configured is stable in cluster environments or when dealing with high-volume data.

You can configure MySQL 5 as the external database during initial setup or in System Management.

Note:

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

If you need to modify them, see Changing the Username and Password of the External Database.

Database Version

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

Hardware Configuration

Notes:

1. Modify the occupied space according to the usage. You can set it to be one to two times larger than the size of the finedb folder in webapps/webroot/WEB-INF/embed (considering future use).

2. The recommended size of memory is intended for the project configuration database.

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

Creating a Database

Create a database to store platform-related data.

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

Note:

Different FineDataLink projects cannot share the same external database. Otherwise, data may become conflicted.

You are advised to set the default character set of the database to utf8 and the default collation to utf8_bin, as shown in the following figure.

Database name:

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

Character set:

1. When connecting the platform to a MySQL database, you need to set collation (case-sensitive).

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

3. You cannot use utf8mb4 encoding.

Collation:

Multiple formats of utf8 encoding collation are available for MySQL. The two most common ones are as follows.

1. utf8_bin (recommended)

Definition: Each character in a 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 collation for the utf8 character set.

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

Checking the Configuration

If you are using the Data Pipeline function, pause the running synchronization tasks before configuring the external database.

External Database Configuration Entries

The three external database configuration entries are as follows.

1. You (the super admin) can configure an external database for the system upon first login, as shown in the following figure.

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

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

Configuring the External Database

After you access the external database configuration page, select the database type and input the relevant database information, as shown in the following figure.

Setting Item

The following table describes each setting item.

Setting Item	Description
Database Type	Select mysql.
Driver	The MySQL 5 driver is built-in. This parameter is automatically configured, requiring no modification.
Database Name	For details, see the "Creating a Database" section. Notes: 1. Sharing a database with other projects is prohibited. You are advised to create a database. 2. The database name can contain only numbers, letters, underscores (_), and periods (.).
Username/Host/Password/Port	Set these parameters based on the actual database configuration. Notes: 1. The hostname can contain only numbers, letters, underscores (_), hyphens (-), and periods (.). 2. You need to have the create, delete, alter, update, select, insert, and index permissions.

Migrating Data to the Database to Be Enabled

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

1. If the new external database is empty, and Migrate Data to Database to Be Enabled is ticked:

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

2. If the platform data already exists in the new external database, and Migrate Data to Database to Be Enabled is ticked:

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. If the new external database is empty, and Migrate Data to Database to Be Enabled is unticked:

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 OK, the platform data of the original FineDB database will be migrated to the new external database.

4. If the platform data already exists in the new external database, and Migrate Data to Database to Be Enabled is unticked:

After you click Enable New Database, the project automatically checks 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 cannot proceed with the external database configuration.

• 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 system automatically backs up the original database configuration before migration, generates a db.properties.bak file in webapps/webroot/WEB-INF/config, and enables the new external database, as shown in the following figure.

Successful Migration

Wait for the migration to complete. Different pop-up prompts may be displayed depending on the migration situation.

1. If the new external database uses data of the original FineDB database:

The message "The target database has been enabled successfully." pops up. Click OK to complete the migration, as shown in the following figure.

2. If the new external database uses its own existing data and the original FineDB database 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 project to complete the migration, as shown in the following figure.

3. If the new external database using its own existing data and the original FineDB being 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 project to complete the migration, as shown in the following figure.

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.

If you did not set the database collation to utf8_bin initially but have already migrated the data, you need to modify it to utf8_bin in the fine_conf_entity and fine_conf_classname tables. The following uses fine_conf_entity as an example:

1. Query the current character set collation.

show full columns from fine_conf_entity

2. Set the character set collation 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 collation to utf8_bin in fine_conf_classname, as shown in the following figure.