MySQL Data Connection

Version

Application Scenario

This document describes how to connect to a MySQL database in FineBI.

Version and Driver

Download the driver package and upload it to FineBI. For details, see section "Creating a Data Connection" in Driver Management.

Collecting Connection Information

Before connecting the database, you need to collect the following information.

1. IP address and port number of the database server

2. Database name

3. The username and password of the database

1. Log in to FineBI as the admin, choose System Management > Data Connection > Data Connection Management, and click New Data Connection, as shown in the following figure.

Note:

If you are not the admin, you can configure data connections only after the admin assigns the permission on data connection management to you. For details, see Data Connection Permission.

2. Find the MySQL icon.

3.Set Driver to Default (or Custom and select the driver you re-upload), and enter the connection information collected in section "Collecting Connection Information."

Note:

If incorrect dates exist in the value of Data Connection URL, you can suffix the value of Data Connection URL to form jdbc:mysql://hostname:port/database?generateSimpleParameterMetadata=true&useUnicode=true&useSSL=false&serverTimezone=America/New_York.

The suffix serverTimezone=America/New_York means that New York time zone is used.

4. Click Test Connection. If the connection is successful, click Save to save the configuration, as shown in the following figure.

Adding Database Tables to FineBI

You can add database tables to FineBI directly or by adding SQL datasets.

Database Encoding Problem

If the MySQL database uses the UTF-8 format, you need to suffix the data connection URL with ?useUnicode=true&characterEncoding=UTF-8.

If the garbled code remains after you add the suffix, you need to use the default encoding format.

Data Table Adding Problem

SQL Statement

1. When you add a SQL dataset, comments cannot be added to the entered SQL statement.

2. When you add a SQL dataset, the entered SQL statement does not support the top-N queries.

3.When you add a SQL dataset with the SQL statement that contains the CONCAT function, for example, select concat( count(*) ,"Quantity")from table GROUP  BY table1, garbled code will occur if the fields to be concatenated are of the different types.

In this case, you can format the field as char with the CONVERT function in MySQL, for example, select concat( CONVERT(count(*),char),"Quantity")from table GROUP  BY table1.

Field Type

1. If the database is of the V5.6 version when you add a data table through the MySQL data connection, ensure that the fields of the varchar text type cannot be empty. Otherwise, the  garbled code will occur after the table is added to FineBI.

2. When you add a data table through the MySQL data connection, fields of the year type (if any) in the database will be identified as text fields and displayed in the format of Year-Month-Day. For example, the year field 2015 in the database will be displayed as 2015-01-01 after you add the data table that contains the field to FineBI.

Date Error

The time field of the database displayed in the dashboard or on the data preparation page is one day less than the actual time data recorded in the database.

This is because the MySQL database uses CST as the default time zone, which differs from the current time zone.

You can solve this problem by suffix the data connection URL. For example, if you are in Shanghai, suffix the URL with ?serverTimezone=Asia/Shanghai.

No Data Displayed After Database Table Added

1. You have created a MySQL data connection and added a database table. However, data in the database is displayed as empty during preview in FineBI.

2. To solve the problem, suffix the data connection URL with ?zeroDateTimeBehavior=convertToNull.