Configure the External Database

HSQL is a built-in database for reports. It cannot be accessed by multiple threads, and will be unstable if there is a large amount of data in it. In case of cluster or a large amount of data, you need to configure an external database to ensure the stability of the system.

Note: When the JAR package version is lower than 2019-05-20, users can only migrate data when configuring the external database, and cannot directly enable the new database.

1. Supported databases

2. Database capacity

The user can configure the external database in "initialization interface" or configure the external database in the platform. Except for the different methods, the two configuration methods have the same specific configuration requirements and steps.

1. Configure the external database during initialization

1) You can configure an external database when you enter the Decision-making Platform for the first time.

2) Enter the External Database Configuration interface, where the following four kinds of database types are available: MySQL, SQL Server, Oracle, and DB2. The driver will automatically change according to the database type.

3) Take MySQL database as an example, you should fill in the blanks, and click Enable New Database.

The description of each setting item is shown in the following table:

4) After waiting for a while, it prompts that the new database has been successfully enabled. Then you can login to the platform.

2. Configure an external database in the Decision-making Platform

1) Enter the Decision-making Platform, select [Manage]>[System]>[General]. Then you can see that the status of External Database is [to be Configured]. 

2) You can click [to be Configured] icon to view or modify the detailed information in External Information Configuration, and then click Enable New Database. 

3) Wait for a moment.

4) A pop-up window prompts: the target database has been successfully activated. Just click "OK", as shown below:

3. Migrate data to database

1) When configuring an external database in the Decision-making Platform, you can select whether to [Migrate data to the database you want to enable], which is checked by default. 

Note:If the external database is configured before initialization, then the [Migrate data to the database you want to enable] will not appear in the above figure. 

2) Check [Migrate data to the database you want to enable], and Click [Enable New Database], then the Decision-making Platform will migrate data to the newly configured database, and will not generate history data back file db.properties.bak. 

3) Uncheck [Migrate data to the database you want to enable]:

• If the external database is not empty, then you can directly click [Enable New Database] to migrate data and generate a backup file db.properties.bak to save the configuration information of the previous database.

• If the external database is empty, then a pop-up window will appear to prompt you to migrate data and enable the database. 

4. Important Notes

• To prepare the database to which the platform data will be migrated, it is recommended that users create an empty database in the local database (please refer to section 2.2 of this article for database capacity). The database name during subsequent external database configuration needs to be filled in with the database name corresponding to the migration storage.
  

• For different versions of FineReport projects, their external databases cannot be the same database, otherwise the data may be disordered.

• If there is any platform data in the external database, then the system will pop up a window to inform you of the existence of the data. Then you can click OK in the pop-up window to clear the original platform data, and import data. If there is no platform data in the external database, then import data directly.

• Do not refresh the page during the migration process.

• Users of MySQL, SQL Server, Oracle, and DB2 need basic permissions: create, delete, alter, update, select, insert; it is recommended to have the highest root permissions.

1. MySQL

1.1 Steps

1) Create a new MySQL database

The default character set for MySQL databases should be utf8, with the default sorting rule being utf8_bin.

Note the following points for MySQL database:

• When connecting to a MySQL database, the encoding only supports utf8-UTF-8 Unicode, not utf8mb4-UTF-8 Unicode

• When the platform is connected to the MySQL database, it is necessary to set the character set collation (case sensitive).

There are a variety of utf8 encoding collations in MySQL, the most common two are shown in the following table:

2) Configure the external database

Refer to section III of this article, enter the platform, enter the actual database information, and configure the external database.

1.2 Important Notes

If utf8_bin is not set in the database collation at the beginning, but it has been migrated, then there are two tables fine_conf_entity and fine_conf_classname that need to be modified to utf8_bin. The following is an example of fine_conf_entity:

1) Check the current charset collation

show full columns from fine_conf_entity

2) Set the charset collation to utf8_bin

• Set column validation rule

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

• Set table validation rule:

ALTER TABLE fine_conf_entity DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

3) Check if configuration is successful:

show full columns from fine_conf_entity

4) Apply above steps to fine_conf_classname.

2. Oracle 

2.1 Steps

1) New account

For the Oracle database, it is best to create a separate account (create a new tablespace and specify it as the user's default tablespace) before configuration.

For example, log in to Oracle as user system and add user finedb:

create tablespace finedb datafile '/u01/oraDatafile/finedb01.dbf' size 50M autoextend on next 64M maxsize unlimited extent management local;
create user finedb identified by fr123 default tablespace finedb;

And assign permissions to the user:

grant connect,resource to finedb; 
grant create sequence to finedb;
grant create  table to finedb;
grant unlimited tablespace to finedb;
grant create  procedure to finedb;
grant create  view to finedb;
grant create  Trigger to finedb

2) New database

Create a new FineDB database in Oracle to store the migrated data. Here, if you do not create a new one, the required table space should preferably be empty, and the database name in the subsequent external database configuration needs to be filled in with the database name corresponding to the migration storage.

3) Refer to section III of this article, enter the platform, enter the actual database information (jdbc:oracle:thin:@<host>:<port>:<SID>), and configure the external database.

Oracle will have a mode option. The name of the schema is the same as the name of the database user (Oracle's user name and schema are recommended to be capitalized).

When using Oracle clusters, we need to use jdbc:oracle:thin:@<host>:<port>/<service_name> format, as shown in the figure below:

For clusters version after 10.0.11, this format is supported:

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.1)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.2)(PORT=1521)))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxrac)))

3. SQL Server 

Refer to section III of this article, enter the platform, enter the actual database information, and configure the external database.

When the external database is SQL Server, you need to pay attention to the "Mode Settings" and "Sort Rules". The details are as follows.

3.1 Mode Setting

1) Jar version: later than 2018-12-27

When connecting to an external SQL Server database, there will be a schema option. Select dbo by default.

You can also use SELECT * FROM sys.database_principals to find the existing schema, or use Navicat to connect to view the schema of each database is more intuitive.

2) Jar version: before 2018-12-27

When upgrading the JAR after 2018-12-27, you need to manually add the schema.

Adding method: Modify the %FR_HOME%\webapps\webroot\WEB-INF\config\db.properties file, and manually add the schema.

Set it to dbo mode, hibernate.default_schema=dbo, as shown in the figure below:

3.2 Sort Rules

The collation of the SQL Server database can choose Chinese_PRC_CS_AS (case sensitive) or Chinese_PRC_CI_AS (case insensitive), and it is recommended to use Chinese_PRC_CS_AS.

• Select Chinese_PRC_CS_AS to migrate all the data intact.

• Select Chinese_PRC_CI_AS. If the fine_conf_entity table violates the PRIMARY KEY constraint and other words during the migration, it is usually the project has dirty data. You can execute the following SQL statement to view the dirty data:
  

select lower(ID),COUNT(1) FROM FINE_CONF_ENTITY GROUP BY LOWER (ID) HAVING COUNT(1) >1

4. DB2 

Refer to section III of this article, enter the platform, enter the actual database information, and configure the external database.

When the external database is a DB2 database, you need to pay attention to the "Mode Settings" and "Log Space Configuration". The details are as follows.

4.1 Mode Settings

When connecting to a DB2 database, there will be a mode option. The mode is generally the same as the user name.

Note: The user name is highly authorized when DB2 is migrated. There will be a pattern with the same name as the user name, and you can also create a new pattern.

4.2 Log Space Configuration

If the amount of FineDB data is too large and the log space is insufficient, there may be an error in the configuration process (SQLCODE=-964, SQLSTATE=57011), so you need to ensure that the log space is sufficient.

Check log fonfiguration:

get db cfg for DATABASENAME

• Modify log size:

db2 update db cfg for <DATABASENAME> using LOGFILSIZ 8192

• Change the number of primary logs:

db2 update db cfg for <DATABASENAME> using LOGPRIMARY 50

• Change the number of secondary logs:

db2 update db cfg for <DATABASENAME> using LOGSECOND 20

5. RDS MySQL 

Refer to section III of this article, enter the platform, enter the actual database information, and configure the external database.

Pay attention to the following points when configuring an external database:

• When using the Alibaba Cloud RDS MySQL database, the connection is successful but the import error is reported during the migration. This is because GTID is enabled after Alibaba Cloud RDS 5.6 to increase security, and the default is enabled and cannot be disabled. Alibaba Cloud RDS 5.5 does not have this problem .

• Same as MySQL database, RDS MySQL database also needs to set character set collation (case sensitive), and the setting method is the same as MySQL.

• When GTID is turned on, the local test migration is successful. Confirm that it supports rdsmysql5.7 and 8.0. 8.0 requires a higher version of the mysql driver, such as 5.1.44. A lower version of the driver will cause the connection to fail.

If the external database is configured before initialization, then the status of External Database will be [Configured].

After the configuration of external database and the migration of data, the backup file of the original database db.properties will be stored at %FR_HOME%\webapps\webroot\WEB-INF\config.

If the file already exists when saving, it will be overwritten. If an accident occurs or otherwise when using the an external database, then you can restore the original database through the backup file.