MySQL(3.0)

Owned by Former user (Deleted)
Last updated: Apr 29, 2023 by Yeok Hong Ng
2 min read

This section contains information that is specific to the database type MySQL.

Supported Functions

 The MySQL database can be used with:

  •  Database Bulk Lookup Functions (APL)
  •  Database Table Related Functions (APL)
  •  Event Notifications
  •  Prepared Statements (APL)
  •  SQL Collection/Forwarding Agents
  •  SQL Loader Agent
  •  Task Workflows Agents (SQL)

Properties

When selecting the MySQL database type, you can configure the following property using the Properties tab in the Database profile:

mysql.connectionpool.maxlimit <int_value> - maximum number of connections in a connection pool


Preparations

The MySQL driver has to be downloaded to the Platform in order to connect to a MySQL database from .

You must proceed as follows:

  1. Go to the MySQL web page and download Connector/J from MySQL Connectors:
    https://dev.mysql.com/downloads/connector/j/

    Note!

    For supported versions, see Database Types.

    You may have to disable any popup blockers you have activated in order to find it.

  2. Place the downloaded jar file in the $MZ_HOME/3pp directory.

  3. Restart the Platform and EC/ECSAs for the change to take effect.

The following applies from version 8.0.28 of the MySQL Connector (mysql_connector_8.0.28):
If you want to run LOAD DATA LOCAL in your sql for a database agent, you must proceed as follows:
You must add the following to my.cnf for the server:

[server]
local_infile=1


Alternatively, you must log in to the server and change local_infile to:

SET GLOBAL local_infile=1;


Furthermore, you must use "Advanced Connection Setup" in the database profile with connection string:

jdbc:mysql://<host>:<port>/<database>?allowLoadLocalInfile=true


For example:

jdbc:mysql://172.17.0.2:3306/guran?allowLoadLocalInfile=true



In addition, you must carry out the following preparations before attempting to connect to a MySQL database.

When performing table lookups to a MySQL database, the result may not be updated unless the Execution Context is restarted. Use the following MySQL statement to avoid this issue:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

To decrease re-connection overhead, database connections are saved in a connection pool. To set the connection pool size, set the Execution Context property mysql.connectionpool.maxlimit in the relevant executioncontext.xml file:

<property name="mysql.connectionpool.maxlimit" value="6"/>

Note!

The value set in a profile takes precedence over the execution context property.