External Database Configuration FAQ

1. Overview

This article lists possible errors and solutions when configuring an external database .

2. After upgrading the JAR after database migration, the platform cannot be opened

Problem Description:

user has configured an external database. After upgrading the JAR, the platform cannot be opened, and an error is reported: TASKNAMECALCCULATEATEONCE identifier and USERALIAS identifier are invalid

Cause Analysis:

The user doing the migration does not have alter permission and cannot generate fields.

solution:

Grant alter permission to the migrated user.

3. User root login failed ClientConnectionId:XXX

Problem Description:

When configuring the external database, an error occurs: User root login failed, ClientConnectionId: e484a26e-8f2b-4e28-b9e4-32798ec032b7, and the external data cannot be connected to the configuration, etc.

Cause Analysis:

The error is due to the configuration error of the external database of FineReport . It is necessary to delete the configuration information of the external database of FineReport and reconfigure the external database.

solution:

Delete the suffix db.properties file under FineBI /webapps/ webroot /WEB-INF/config , and reconfigure the FineReport external database.

4. After migrating the database, the login to the platform of the fallback JAR package fails

Problem Description:

The report project has been migrated to an external database. After upgrading the JAR, it was migrated to another external database, and the JAR was rolled back. It was found that the platform login failed and the database connection was abnormal, as shown in the following figure:

Cause Analysis:

The JAR was rolled back, but the db.properties file that stores the external database configuration did not follow its changes.

solution:

Check the db.properties file in the % FineBI_HOME %/webapps/ webroot /WEB-INF/config path , and manually modify the relevant configuration.

example: a project is migrated to MySQL8 database, after upgrading JAR, it is re-migrated to MySQL5.7.28 database, rollback the project, startup fails, the front desk shows that the database cannot be connected, change the dialect 8 in db. properties to 5.

before fixing:

hibernate.dialect= com.fr.third.org.hibernate.dialect.MySQL8Dialect

After modification:

hibernate.dialect= com.fr.third.org.hibernate.dialect.MySQL5Dialect

5. FineDB migration fails in cluster environment

Problem Description:

FineDB database in the cluster environment , use the platform function to migrate, after the interface shown in the figure below appears, click login to refresh the interface repeatedly.

Cause Analysis:

An error occurred in the migration operation method in a cluster environment.

solution:

Cluster migration needs to shut down other nodes and start only one node for migration. After the migration is successful, copy the config file to the node that has not been migrated, and then start it again.

6. FineDB migration fails in CAS single-point environment

Problem Description:

1) When migrating an external database, it has been stuck in the data import stage.

2) There is an error in the background: database is locked.

3) The project has done CAS single point, and there are CAS related logs in the error report .

Cause Analysis:

During the migration, other users accessed the platform, resulting in some tables being locked and unable to be migrated.

solution:

First remove the CAS single point to ensure that no user accesses the platform.

After the external database is successfully migrated, CAS is added on a single point.

7. Scheduled tasks cause migration failure

Problem Description:

Migrating the external database failed, reporting the error:

Null value was assigned to a property [class com.fr.scheduler .quartz.entity.QuartzFiredTriggers.isNonconcurrent] of primitive type setter of com.fr.scheduler.quartz.entity.QuartzFiredTriggers.isNonconcurrent

Cause Analysis:

During the execution of timing scheduling , the QRTZ_FIRED_TRIGGERS table in FineDB will temporarily record timing scheduling data. After the task is successfully executed, the data will be automatically deleted.

When the scheduled scheduling task is half executed and the data is generated, the external database migration operation is performed, and the scheduled scheduling task fails to suspend, resulting in the migration failure.

solution:

Clear related tables according to the error message, for example, clear the QRTZ_FIRED_TRIGGERS table data.

8. MySQL could not execute statement

Problem Description:

Configure MySQL database, import data fails, and the front end of the page reports an error : could not execute statement ;

Check % FineBI %/logs/fanruan.log , the error is: Table ' finedb.QRTZ_PAUSED_TRIGGER_GRPS ' doesn't exist , as shown in the following figure:

Cause Analysis:

To view the MySQL data engine, log in to the MySQL database, and enter the show enginescommand at the mysql > prompt to view it. It displays the MyISAM data engine, which is the default engine of MySQL and does not support transactions or foreign keys. And FineBI does not support MyISAM , and compatibility is not considered in the future, so it is necessary to switch the data engine for MySQL to InnoDB ;

solution:

1) Add the sentence default-storage-engine=INNODB under [mysqld] in the MySQL database configuration file my.cnf and save it;

2) Restart the MySQL server: mysqladmin -u root -p shutdown or service mysqld restart .

3) Log in to the MySQL database and enter the show engines command at the mysql > prompt . If InnoDB |DEFAULT appears , it means that setting InnoDB as the default engine is successful.

9. MySQL could not execute batch

Problem Description:

Configure the external database MySQL to report an error. The error log is as follows:

16:59:48 Thread-45 ERROR [standard] could not execute batch
com.fr.third.org.hibernate .exception.GenericJDBCException : could not execute batch

......Caused by: java.sql.SQLException :Incorrect string value: 'xE6xA8xA1xE6x9DxBF...' for column 'id' at row 1

Cause Analysis:

Check to make sure you have permission

Incorrect string value: 'xE6xA8xA1xE6x9DxBF...' for column 'id' at row 1 

is the reason for the database encoding

solution:

View the statement of the customer to create the FineDB database: create database finedb

It is found that there are no constraints, and the statement is changed to: create database finedb DEFAULT CHARSET utf8 COLLATE utf8_bin

Import was successful.

10. MySQL migrate table com.fr.config.entity.Entity failed

Problem phenomenon:

Configure the MySQL database and report an error when importing data: java.lang .Exception : migrate table com.fr.config.entity.Entity failed

solution:

my.ini file of MySQL and change the character set to character-set-server=utf8 , as shown in the following figure:

11. MySQL The table ' fine_conf_entity ' is full

Problem phenomenon:

Configure the MySQL database and report an error when importing data: java.lang .Exception : migrate table com.fr.config.entity.Entity failed

Check % FineBI %/logs/fanruan.log , the error is reported: The table ' fine_conf_entity ' is full , as shown in the following figure:

solution:

Enter the MySQL configuration file /etc/my.cnf , add / modify two lines under [mysqld]:
tmp_table_size = 256M

max_heap_table_size = 256M
The system default is 16M, restart MySQL after modification.

12. Database connection failed wait millis 10000, active0, maxActive 50

Problem phenomenon:

When configuring databases with "schema" such as SQL Server and Oracle, an error occurs: database connection failed, wait millis 10000, active0, maxActive 50

As shown below:

Investigation ideas:

1) Confirm that the database name, host address, port username and password are correct.

2) Confirm whether the port of this server is open to other computers, and the database connection test can be performed in other places.

3) Change the schema to be the same as the database username, as shown in the image below:

13. Oracle could not execute statement

Problem phenomenon:

When configuring the external database Oracle, the error oracle could not execute statement or Oracle could not execute batch is reported .

Cause Analysis:

The database schema is wrong.

solution:

fill in thin in the pattern , an error will be reported, just change it to the username.

14. Oracle migrate table com.fr.config.entity.Entity failed

Problem phenomenon:

Configuring an external database Oracle reports an error java.lang.Exception : migrate table com.fr.config.entity.Entity failed

Log error: Caused by: java.sql.BatchUpdateException : ORA-01654: index BI_REPORT_RO.SYS_C0011297 cannot be extended by 128 (in tablespace BI_REPORT_RO)

data table was created successfully, but the data import failed.

Cause Analysis:

database table space is insufficient.

solution:

The default tablespace data file size is related to the size of DATA BLOCKS, and the default maximum is 32GB.

Users can add tablespace data files through the following SQL:
alter tablespace USERS add datafile ' D:appAdministratororadataorclUSERS02.DBF ' size 10240M;

15. Oracle19c data migration stuck

Problem phenomenon:

Oracle19c database, the data connection is successful, but it has been stuck in the connecting state when configuring the external database.

Cause Analysis:

PUT and DELETE requests are disabled in the user environment, resulting in some abnormal requests on the platform.

solution:

Users can convert PUT and DELETE requests into POST requests by installing the "PUT, DELETE request to POST" plugin.

For details, please refer to: PUT, DELETE request to POST plugin