Versions Compared

Old Version 1

changes.mady.by.user Former user

Saved on

compared with

New Version 2

changes.mady.by.user Jenni Wallin

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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
    titleNote!

    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.
Note

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:

Code Block
languagetext
themeEclipse
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:

Code Block
languagetext
themeEclipse
<property name="mysql.connectionpool.maxlimit" value="6"/>


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