The MySQL Connector connects SIA Connect to a MySQL database. Use it to run read queries on a schedule and execute SQL statements generated by mappings.

This guide explains how to create a MySQL instance, configure TLS, create read and write items, understand returned data, and resolve common connection and query errors.

Before you begin

Prepare the following information:

• The hostname or IP address of the MySQL server.
• The TCP port. MySQL normally uses port 3306.
• The database name.
• A database username and password.
• The SQL queries that SIA Connect must run.
• A CA certificate if the server requires TLS.
• A client certificate and matching private key if the server requires certificate authentication.

Make sure SIA Connect can reach the database server and that the database accepts remote TCP connections.

Create a dedicated database account with only the permissions required by the configured queries. Read items should normally use an account with SELECT permission. Grant write permissions only when the application needs them.

Create a MySQL instance

1. Sign in to the Edge Portal.
2. Open Workspace > Instances.
3. Select Add instance.
4. Select the MySQL Connector.
5. Enter a descriptive instance name.
6. Enter the database server hostname or IP address in Address.
7. Complete the Connector-specific parameters described below.
8. Select Save.

Instance parameters

Configure a TLS connection

Upload the required certificates to the MySQL Connector before configuring the instance. Assign each uploaded file to the correct category:

• CA certificate: Verifies the certificate presented by the MySQL server.
• Client certificate: Identifies SIA Connect when the database account requires a client certificate.
• Client key: Private key that matches the client certificate.

To enable TLS:

1. Open the MySQL instance editor.
2. Set SSL enable to Enabled.
3. Select the server's CA certificate under SSL CA.
4. If the server requires certificate authentication, select the matching SSL cert and SSL key.
5. Keep SSL cipher set to Default (auto) unless the server administrator requires a specific cipher.
6. Select Save.

The available explicit cipher selections are:

• ECDHE-RSA-AES128-GCM-SHA256
• ECDHE-RSA-AES256-GCM-SHA384
• AES256-SHA

Understand MySQL items

Each item contains a Query default field. How the Connector uses the item depends on its read and write direction.

• Read: Runs the SQL statement entered in Query default at the item's configured polling interval.
• Write: Executes the incoming item value as a complete SQL statement. The statement normally comes from a mapping.
• Read/Write: Can run the configured read query and execute incoming SQL statements.

The Connector automatically creates a Write item named Query write for each MySQL instance. Use this item as a mapping receiver when the mapping produces the complete SQL statement.

Create a read item

1. Open the MySQL instance.
2. Open its Items section.
3. Select Add item.
4. Enter a descriptive item name.
5. Select Read as the item direction.
6. Enter a read query in Query default.
7. Set the required polling interval and standard item settings.
8. Select Save.

For example:

SELECT temperature, status
FROM machine_state
WHERE machine_id = 1
LIMIT 1

A matching row can produce a value such as:

[{"temperature":21.5,"status":"running"}]

The result is always returned as a JSON array. Each row becomes one JSON object and each selected column becomes a property.

Use a WHERE clause and LIMIT where appropriate. Returning an entire large table can consume significant memory and increase database and network load.

Statements blocked on read items

Read items are intended for queries that retrieve data. The Connector rejects the following modifying statements when it detects them in a read query:

• UPDATE
• DELETE
• INSERT INTO
• DROP
• TRUNCATE

Do not rely on this check as a database security control. Restrict the database account itself to the required permissions.

Execute a write query

A Write item does not execute the text stored in Query default. It executes the incoming item value as the SQL statement.

1. Open or create the mapping that will send data to MySQL.
2. Select the MySQL instance's Query write item as the receiver.
3. Configure the mapping output so it produces a complete SQL statement.
4. Save and enable the mapping.
5. Trigger the mapping with a controlled test value.
6. Confirm that the expected record was inserted or updated.

An incoming value can contain a statement such as:

INSERT INTO measurements (source, value)
VALUES ('line_1_temperature', 21.5)

The Connector sends this text directly to MySQL. It does not automatically add quotes, escape text values, or convert the statement into a parameterized query.

Understand returned data types

The Connector keeps compatible MySQL types when building the JSON result.

Verify the configuration

1. Create a Read item with a small query that returns one known row.
2. Confirm that the item receives a JSON array containing the expected column names and values.
3. Check that numbers, Boolean values, null values, and JSON columns have the expected JSON types.
4. If writing is required, send a controlled statement to the Query write item.
5. Verify the result using a separate database client or a Read item.
6. Confirm that the database account cannot access tables or operations outside its intended scope.

Troubleshoot the MySQL Connector

The Connector returns the error text supplied by the MySQL client library and server. The exact numeric code and wording can differ between MySQL versions.

If the problem continues, review the instance, item, and mapping errors together with the system logs. Query errors are assigned to the affected item or mapping. Connection-loss errors are also assigned to the instance and cause the Connector to reconnect on a later operation.

Related articles

• Install and manage Connectors
• Create and edit instances
• Create and edit items
• Create and edit mappings
• MySQL Query Examples
• Manage and configure Connector files and certificates
• Troubleshoot Connector, instance, item, and mapping errors
• Configure and review system logs