MySQL data connection

This article will introduce how to connect to MySQL database in FineBI.

Note: If it is MySQL8.0, please refer to the document for connection method: MySQL8.0 Data Connection 

2.1 Configuration information

Note: If the MySQL database encoding is UTF-8, you need to add the parameter ?useUnicode=true&characterEncoding=UTF-8 after the URL of the data connection. If there are garbled queries, missing data, etc. in the Mysql database access, it can be solved by adding and changing parameters.

There are two ways to connect:

• Method 1: After installing the driver isolation plug-in, connect to the database.

  For details on downloading and installing the driver isolation plugin, please refer to: Driver isolation plugin

• Method 2: Do not use the driver isolation plug-in, directly connect to the database.

  Refer to section 2.2 of this article for the connection method. But in this way, there may be conflicts between JAR packages and cause connection failure. It is recommended that users use the first method.

2.2 Cennection steps

1) Prepare the driver package

• The MySQL version in the configuration information uses the driver package mysql-connector-java-5.1.44-bin.jar that comes with FineBI, and there is no need to download the driver package separately.

• For the MySQL version that is not in the configuration information, you need to download the connection driver package corresponding to the current MySQL minor version from the MySQL official website, and replace the mysql-connector-java-5.1.44 in the %FineBI%\webapps\webroot\WEB-INF\lib directory -bin.jar

2) Log in to the FineBI data decision system, select "Manage>Data Connection", click "New Data Connection" and select "MySQL", as shown in the figure below: 

3) According to the configuration information provided in chapter 2.1, enter the corresponding information of the database, and optionally fill in the connection pool attribute information (for the introduction of connection pool attributes, please refer to the configuration data connection), as shown in the following figure:

2.3 Test connection

Click Test Connection, if a pop-up box of test connection success as shown in the figure below appears, it means that the database is successfully connected. As shown below:

After the test connection is successful, click the save button in the upper right corner, and the data connection is added successfully. As shown below:

3.1 Database coding problem

If the MySQL database encoding is UTF-8, you need to add the parameter after the data connection URL?useUnicode=true&characterEncoding=UTF-8

If the garbled code shown  after adding parameters, you need to change the encoding to automatic.

3.2 Add data table problem

3.2.1 SQL statement

1) When adding a SQL data set, the input SQL statement does not support adding comments.

2) When adding a SQL data set, the input SQL statement does not support the top N statement.

3) Problem phenomenon: When adding a SQL data set, the input SQL statement has a concat() function, such as select concat( count(*) ,"A")from table GROUP BY table1. If the connected field types are different, it will Garbled characters appear.

Solution: Use MySQL's string conversion function CONVERT() to format the parameters as char type, for example: select concat( CONVERT(count(*),char),"A")from table GROUP BY table1.

3.2.2 Field Type

1) When using MYSQL data connection and adding a data table, if the database version is MYSQL 5.6, please make sure that the varchar text type field in the database is not empty, otherwise a small square 157499290783691.png garbled appears after adding the table to the BI.

2) When using MYSQL data connection and adding a data table, if the field type in the database is year (year), it will be recognized as a text field in BI and displayed in the format of year, month, and day. If there is a year type field 2015 in the database, adding the data table in BI will display it as 2015-01-01.

3.2.3 There is an error between the data field value of the added data set and the data of the database record

1) Phenomenon: The time field of the database displayed on the dashboard or data preparation page is one day smaller than the actual data.

2) Reason: The default time zone used by the mysql database is CST, which is different from the current time zone.

3) Solution: Add the parameter ?serverTimezone=Asia/Shanghai to the data connection URL.