MySQL

This topic explains how to configure connectivity to a MySQL target using the MySQL target connector. MySQL can be used as a target database in a replication task only. Before you can connect to a MySQL target you need to configure the Required permissions on the database. If you are connecting to MySQL via Data Movement gateway, you also need to install the driver as described in Driver setup.

For information about the limitations and considerations when using the MySQL connector, see Limitations and considerations.

Setting connection properties

To configure the connector, do the following:

1. In Connections, click Create connection.

2. Select the MySQL target connector and then provide the following settings:

Data target

• Data gateway: When working with Data Movement gateway, select the data gateway that will be used to test the connection to the MySQL target. This should be the same gateway that was used to access the data source.

  Information note

  • This field is not available with Qlik Talend Cloud Starter subscription as Data Movement gateway is not supported with this subscription tier.

  • You also need to install the appropriate driver on the Data Movement gateway machine. For details, see Driver setup below.

• Cloud provider: Choose one of the following as appropriate:

  • None

    • For MySQL on-premises, MariaDB, Amazon Aurora, or Microsoft Azure Database for MySQL - Flexible Server.
    • When working without Data Movement gateway

  • Amazon RDS

    For Amazon RDS for MariaDB or Amazon RDS for MySQL.

  • Google Cloud

    For Google Cloud SQL for MySQL.

  • Microsoft Azure

    For Microsoft Azure Database for MySQL.

• Server: The host name or IP address of the computer on which the MySQL database is installed.

• Port: The port to use when connecting to the database. The default is 3306.

Account properties

User Name and Password: The user name and password of a user authorized to access the MySQL Server database.

Database properties

• Target Database Type: Choose one of the following:
  • Specific database: When this option is selected, all source schemas will be loaded into the specified Database.
  • Multiple databases: When this option is selected, each of the source schemas will be loaded into its corresponding database. Additionally, by default, all Control Tables will be replicated to a new schema called attrep_control. For more information about Control Tables, see Control tables.
• Max file size (KB): Select or type the maximum size (in KB) of a CSV file before it is loaded into the MySQL target database. The default value is 32000 KB.
• Parallel load threads: Increasing the number of threads might improve performance when loading data into the MySQL target database. Note that setting a large number of threads may have an adverse effect on database performance since a separate connection is required for each thread.

Internal properties

Internal properties are for special use cases and are therefore not exposed in the dialog. You should only use them if instructed by Qlik Support.

Use the and buttons to the right of the fields to add or remove properties as needed.

Name

The display name for the connection.

Prerequisites

Required permissions

The user specified in the connector settings must be granted:

• MySQL account access
• Read/write privileges in the MySQL database
• The following permissions for each target schema involved in the replication:
  • ALTER
  • CREATE
  • CREATE TEMPORARY TABLES
  • DELETE
  • DROP
  • INSERT
  • SELECT
  • UPDATE

Driver setup

This section describes how to install the required driver. The process involves running a script that will automatically download, install and configure the required driver. You can also run scripts to update and uninstall the driver as needed.

Preparing the installation

• Make sure that Python 3.6 or later is installed on the Data Movement gateway server.

  Python comes preinstalled on most Linux distributions. You can check which Python version is installed on your system, by running the following command:

  python3 --version

Installing the driver

To download and install the driver:

1. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

2. Run the following command:

   Syntax:

   ./install mysql

   If the driver cannot be downloaded (due to access restrictions or technical issues), a message will be displayed instructing you where to download the driver and where to copy it on the Data Movement gateway machine. Once you have done that, run the install mysql command again.

   Otherwise, the EULA for the driver will be displayed.

3. Do one of the following:

   • Press [Enter] repeatedly to slowly scroll through the EULA.
   • Press the Spacebar repeatedly to quickly scroll through the EULA.
   • Press q to quit the license text and be presented with the EULA acceptance options.

4. Do one of the following:

   • Type "y" and press [Enter] to accept the EULA and begin the installation.
   • Type "n" and press [Enter] to reject the EULA and exit the installation.

   • Type "v" and press [Enter] to view the EULA again.

The driver will be installed.

Updating the driver

Run the update command if you want to uninstall previous versions of the driver before installing the provided driver.

To download and update the driver:

1. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

2. Run the following command:

   Syntax:

   ./update mysql

   If the driver cannot be downloaded (due to access restrictions or technical issues), a message will displayed instructing you where to download the driver and where to copy it on the Data Movement gateway machine. Once you have done that, run the update mysql command again.

   Otherwise, the EULA for the driver will be displayed.

3. Do one of the following:

   • Press [Enter] repeatedly to slowly scroll through the EULA .
   • Press the Spacebar repeatedly to quickly scroll through the EULA.
   • Press q to quit the license text and be presented with the EULA acceptance options.

4. Do one of the following:

   • Type "y" and press [Enter] to accept the EULA and begin the installation.
   • Type "n" and press [Enter] to reject the EULA and exit the installation.
   • Type "v" and press [Enter] to review the EULA from the beginning.

The old driver will be uninstalled and the new driver will be installed.

Uninstalling the driver

Run the uninstall command if you want to uninstall the driver.

To uninstall the driver:

1. Stop all tasks configured to use this connector.

2. On the Data Movement gateway machine, change the working directory to:

   opt/qlik/gateway/movement/drivers/bin

3. Run the following command:

   Syntax:

   ./uninstall mysql

   The driver will be uninstalled.

Installing the driver manually

You should only attempt to install the driver manually if the automated driver installation did not complete successfully.

After Data Movement gateway is installed, download the mysql-connector-odbc-<version>.x86_64.rpm driver. You can find a direct download link to the supported version under binary-artifacts in /opt/qlik/gateway/movement/drivers/manifests/mysql.yaml. Once the download completes, copy the RPM to the Data Movement gateway machine.

On the Data Movement gateway server, open a shell prompt and do the following:

1. Stop the Data Movement gateway service:

   sudo systemctl stop repagent

2. Optionally, confirm that the service has stopped:

   sudo systemctl status repagent

   The status should be as follows:

   Active: inactive (dead) since <timestamp> ago

3. Install the driver on the Data Movement gateway machine.

4. Change the working directory to <Data Movement gateway-Install-Dir>/bin.

5. Copy the driver location to the site_arep_login.sh file:

   echo "export LD_LIBRARY_PATH=\$LD_LIBRARY_PATH:/usr/lib64" >> site_arep_login.sh

   This will add the driver to "LD_LIBRARY_PATH" and update the driver location in the site_arep_login.sh file.

6. Optionally, confirm that the driver location was copied:

   cat site_arep_login.sh

7. Make sure that the /etc/odbcinst.ini file contains an entry for MySQL, as in the following example:

   [MySQL ODBC 8.0 Unicode Driver]

   Driver = /usr/lib64/libmyodbc8w.so

   UsageCount = 1

8. Start the Data Movement gateway service:

   sudo systemctl start repagent

9. Optionally confirm that the service has started:

   sudo systemctl status repagent

   The status should be as follows:

   Active: active (running) since <timestamp> ago

Limitations and considerations

The following limitations apply when using MySQL as a replication target:

• When replicating to the MariaDB target database, if the DATETIME value is set to zero, you need to define a transformation that replaces the DATETIME with valid values. For example:

  replace($datetime_column,'9999-12-31 23:59:59','2000-01-01 22:00:00')

  Information noteThis limitation does not apply to Microsoft Azure Database for MySQL.
• Due to the way MySQL operates, when loading data to a MySQL target during a Full Load task, duplicate key errors will not be reported to the logs.

• When updating a column's value to its existing value, a zero rows affected is returned from MySQL (unlike Oracle and Microsoft SQL Server that perform an update of one row).

  This generates an entry in the attrep_apply_exceptions Control Table and the following warning:

  Some changes from the source database had no impact when applied to the target database. See attrep_apply_exceptions table for details.

• Due to an issue with MySQL 5.7 (which was resolved in version 8.0), multibyte characters cannot be inserted into TEXT columns when the Full Load uses CSV files. Consequently, if the target endpoint is MySQL 5.7 and a replicated table contains TEXT columns with UTF-8 multibyte characters, the target table might be empty.

   

  Workaround:

  In the connector settings, set the loadUsingCSV internal parameter to FALSE. Note that this might impact performance.

• If you need to replicate 4-byte emoji characters, the target schema character set must be set to utf8mb4.

Data types

Native data type information is preserved, and is displayed in the Native data type column in dataset views. If the column is not visible, you need to open the column picker of the data set view and select the Native data type column.