The MySQL Adapter is used to import (insert), export (select) and amend (update) MySQL / MariaDB database tables and views. The Adapter supports MySQL databases from version 3.x to 8.x. There is also support for the offshoot MariaDB, which was created in 2009 from a fork of MySQL. MariaDB versions from 5.x to 10.x are presently supported.
General tab
Connection and authentication
MySQL Adapter, database properties
Server name or IP: This defines the host MySQL Server that FlowHeater should connect to. As well as a URL specifying the host via DNS, a direct IP address can be used.
Database: Here you enter the name of the MySQL database or the catalog name that the data for the import/export shall be assigned to .
Port: The address of the port that the MySQL server accepts requests from. Default = 3306.
User / Password: Enter into these fields the database User and Password to enable FlowHeater to authenticate its connection to the MySQL database. Important: The password is only stored if you check the "Save Password" option. If a password is saved it is stored in an encrypted form in the Definition data.
SSH configuration
MySQL Adapter - SSH configurationIf your MySQL database is located on a Linux server that is connected over the Internet or if your MySQL server database is hosted by an Internet provider, a direct connection is often not possible or is blocked by your provider by firewall rules. In such cases, an SSH tunnel is usually offered for establishing the connection. Under the SSH tab you can configure the SSH tunnel settings for your MySQL database.
Important: When you configure an SSH tunnel, in most cases you have to use localhost" or "127.0.0.1" for the MySQL connection parameters (see illustration). Ask your systems administrator or internet provider for the exact connection parameters.
SSH Host (IP/DNS): Enter the official IP address or the DNS name of the SSH server to access to establish the MySQL connection. Note: Do not use "localhost" or "127.0.0.1" for this.
Port: Enter the port number to establish the SSH tunnel. The default port is 22.
User: The username to use for the SSH login.
Password: The password for the SSH login. If an alternative security arrangement using a private SSH key is used (see below), entering a password here is unnecessary and it will be ignored.
Private SSH key file: The MySQL Adapter supports SSH logins using a public/private SSH key procedure. If your SSH server is configured to permit this and your public key has been imported to the server, you can establish a connection without needing a password. Enter the path to your private SSH key file here.
SSL/TLS configuration
MySQL Adapter - SSL/TLS configurationThe MySQL Adapter supports encrypted communications and login to the MySQL database using SSL/TLS certificates. The following options are available:
Use SSL/TLS: Determines which SSL/TLS protocol to use. Currently TLS 1.0, 1.1 and 1.2 are supported.
SSL certificate file: Specifies the SSL client certificate data. The certificate must be in PEM format.
SSL private key file: Specifies the private key data. The private key must be in PEM format.
SSL CA file: The certificate issued by a certificate authority (CA). The certificate must also be in PEM format.
SSL cipher: Here you can specify which encryption method(s) you want to use. What you enter here depends on the configuration of your MySQL server. By default, the MySQL Adapter supports all the cryptographic methods listed below. If nothing is specified, the encryption method that currently offers the highest security "AES(256)" is tried first.
Supported cipher suites
DES, DES(40), DES(56), DES(168) or 3DES
AES, AES(128) or AES(256)
RC2 or RC2(40)
RC4, RC4(40) or RC4(128)
Common
Insert data: When this option is checked, SQL Insert statements are generated.
Ignore existing records: During an import and when this option is checked, records that already exist in the table are ignored.
Empty the table before importing: When this is checked you tell the MySQL Adapter to empty the contents of the table prior to running the import, effectively deleting all existing rows.
Update data: When this option is checked, SQL Update statements are generated. Note: If both the Insert and Update options are checked, the MySQL Adapter checks whether an SQL Update or Insert should be generated in each instance, by reference to the PrimaryKey. Tip: If you are certain there is only data to insert then avoid checking the Update option, as this will make the process significantly faster.
Remove records (Delete): This will attempt to delete existing records by reference to the fields of the index “Primary Key”. Note: This option cannot be used together with the INSERT or UPDATE options.
No updates or inserts to data, only generate an SQL script: When this option is checked, it signals the MySQL Adapter to make no inline changes to the database, but instead to store an SQL script with Insert and/or Update statements. This is useful for testing during development and for subsequent application to the database. If this option is checked you should also check the option below and enter the filename that the SQL statements are to be stored in.
Generate SQL script: This option instructs the MySQL Adapter to store the change statements (Insert, Update) as an SQL script file with the specified name and path.
Fields / Data types tab
SQL: 
MySQL Adapter, fields and data typesdata available varies according to the side of the Adapter in use:
On the READ side: here you can choose from Tables and Views.
On the WRITE side: only Tables are available.
On the READ side you also have the possibility to enter complex SQL statements in the text field. Table joins must be defined by hand. In the second combo box the tables and views are listed that are available in the specified MySQL (catalog) database.
Fields:: When you click the Load Schema button, information is retrieved from the database schema (field names, field sizes, data types, primary key, etc.) for the SQL statement above. The information about the fields is then loaded into the field list to the left of this button.
Note: The fields in the field list can be ordered in any sequence required. Fields that are not required can either be temporarily disabled here (tick removed) or simply deleted.
Field properties: How the properties of Primary Key and Auto Increment for the currently highlighted field are to be interpreted are adjusted. This information is only required on the WRITE side. No changes are needed here generally, since the correct information is usually obtained directly from the schema. Note: In versions of MySQL Server prior to version 5.0, Auto Increment fields are not automatically detected.
A PrimaryKey field is used in an Update to identify record that possibly exists.
Auto Increment fields are neither assigned nor amended in Insert/Update statements.
Warning: If you make changes here it can result in more than one record being updated with an Update!
Advanced tab
General
MySQL Adpater extented propertiesAutomatically strip spaces from the start and end of content: If you check this option an auto trim of whitespace characters will be carried out on STRING data types. This means that any spaces, tabs and line break characters are automatically removed from the leading and trailing parts of strings.
Use TRUNCATE TABLE (=quicker) instead of DELETE FROM: When the option “Empty the table before importing” under the General tab is checked “TRUNCATE TABLE” instead of “DELETE FROM” (=default) is used to empty the table content.
Connection timeout: Timeout in seconds while waiting for connection to be established. If no connection has been made after this period, then the import/export run is aborted.
Command timeout: Timeout in seconds while waiting for an SQL command to complete. By entering a zero value here, effectively disables the option altogether. In that case SQL commands will never timeout and are awaited endlessly. This option makes sense when you select massive data volumes from a database on the READ side and the MySQL Server takes a long time to prepare its result.
Use database transactions: This allows you to control how data is imported. According to the default settings MySQL Adapter uses a single “large” transaction to secure the entire import process. When importing extremely large volumes you may have to adapt transactional behavior to your needs using the “Run AutoCommit after writing every X records”.
Use UNICODE strings: When checked this option will force output of a prefix to the initial apostrophe. e.g. N’import value’ instead of ‘import value’.
Continue running in the face of SQL errors: This option allows you to instruct FlowHeater not to abort a run when it encounters an MySQL database error. Warning: This option should only be used in exceptional circumstances, because database inconsistencies could result.
Connection initialization
User-defined SQL instructions for initializing the connection: You can place any user-defined SQL commands here, which will be used for initializing the connection after a connection to MySQL Server has been established.
Adapter settings in the Format tab
Examples
MySQL® is a registered trademark of MySQL AB
