MySQL

7 min

The MySQL connector writes Litmus Edge (LE) DeviceHub messages into a table in a MySQL database. The connector writes rows in batches, and uses one transaction for each batch.

The connector also supports Execute Command for ad-hoc SQL queries.

Supported communication

Outbound (write data from LE to the database): Yes

Inbound (read data from the database to LE): No

Execute Command (ad-hoc SQL query): Yes

Compatibility

The connector supports the following products. Other products and versions might work.

Product	Version
MySQL	5.7 and later. Validated on 8
MariaDB	-
Percona Server	-
Google Cloud SQL	-

Connector configuration

Field	Type	Description	Required	Default
Name	String	Name of this connector instance.	Yes	-
Hostname	String	DNS name or IP address of the MySQL host. The host must be reachable from the LE system. Length is 3 to 255 characters.	Yes	-
Port	Integer	Port of the MySQL host. Range is 1 to 65535.	Yes	3306
SSL Mode	Dropdown	disable connects without TLS. require encrypts the connection but doesn't verify the server certificate.	No	disable
CA Certificate	String (Secret)	CA certificate for TLS. You can also add it to the system-wide certificate list. Applies only when SSL Mode is require.	No	-
Certificate	String (Secret)	PEM client certificate for mutual TLS authentication. Applies only when SSL Mode is require.	No	-
Private key	String (Secret)	Private key that matches the client certificate. Applies only when SSL Mode is require.	No	-
Username	String	Database user that the connector authenticates as.	Yes	-
Password	String (Secret)	Password for the database user.	No	-
Database	String	Database that holds the target table. In MySQL, a database is the same as a schema.	Yes	-
Table	String	Target table that the connector writes into. The table must exist in the database unless you enable Create table.	Yes	-
Mapping	Object	Key-value pairs that map payload data to the columns of your table. The key is a column in your table. The value is a payload field name such as deviceId, the matching default-schema column name such as device_id, or static text with an @ prefix, such as @plant-a. Leave it empty to use the default table schema.	No	-
Enable JSONPath	Boolean	Read Mapping values that start with $ as JSONPath expressions, such as $.factory.line.machine.sensors.temperature. Values that don't start with $ keep their usual meaning, and an expression that matches nothing writes NULL.	No	Disabled
Create table	Boolean	Create the target table with the default table schema if it doesn't exist. The database user needs permission to create a table.	Yes	Disabled
Commit timeout	Integer	Maximum time in milliseconds that one batch insert transaction can take before the connector treats it as failed and retries it. Range is 10 to 65535.	No	30000
Batch timeout	Integer	Maximum time in milliseconds after the previous commit attempt before the connector writes a batch that isn't full. Range is 10 to 65535.	No	500
Max transaction size	Integer	Maximum number of messages in one batch insert. The connector writes a full batch without waiting for Batch timeout. Range is 1 to 65535.	No	500
Bulk insert count	Integer	Number of messages the connector buffers before it writes a batch. A value of 0 uses Max transaction size. Range is 0 to 65535.	No	0
Write mode	Dropdown	When a message counts as delivered: sync or async.	No	sync
Parallel transactions	Integer	Maximum number of batch inserts that run at the same time, each on its own database connection. Values above 1 increase throughput, but the connector can then write rows out of publish order. Range is 1 to 64.	No	1
Parallel Publish Count	Integer	Number of messages the connector publishes at the same time. In sync write mode, this value limits how many rows a batch can collect. Range is 1 to 4000.	No	1000

Additional attributes

Field	Type	Description	Required	Default
Throttling limit	Integer	Maximum outbound messages per second. A value of 0 disables throttling. Minimum is 0.	No	0
Persistent storage	Boolean	Store outbound queue messages on disk. If the connection drops or the system loses power, the messages are retained and sent once the connection is restored.	No	Enabled
Persistent storage size limit	Integer	Maximum disk space in GB used for persisted data. Applies only when Persistent storage is enabled. Minimum is 1.	Conditional	1
Queue Mode	Dropdown	Order in which the connector sends buffered messages. fifo sends the earliest message first. lifo sends the latest message first. Applies only when Persistent storage is enabled.	No	lifo
Enable Logging	Boolean	Write connection and publish error messages to the connector log. Logging can increase CPU usage, so enable it only for debugging.	No	Disabled

Default table schema

When you enable Create table and the target table doesn't exist, the connector creates it with the following schema.

CREATE TABLE IF NOT EXISTS `TABLE_NAME`
(
    id          BIGINT AUTO_INCREMENT NOT NULL,
    record_uuid CHAR(36)              NOT NULL,
    arrived_at  DATETIME(3)           NOT NULL,
    device_id   VARCHAR(64)           NULL,
    register_id VARCHAR(64)           NULL,
    tag_name    VARCHAR(64)           NULL,
    datatype    VARCHAR(32)           NULL,
    value       TEXT                  NULL,
    success     TINYINT               NULL,
    PRIMARY KEY (id)
);

To write into a table with different columns, create the table yourself and use Mapping. For mapping and JSONPath examples, see the integration guide.

Write modes

Write mode	Behavior
sync	The connector acknowledges a message only after it commits the batch that holds the message. If the database is unreachable, the message stays in persistent storage, and the connector writes it after it reconnects.
async	The connector acknowledges a message once it buffers it. If a batch fails to write, the connector drops it and counts the messages as dropped.

Caution: In async write mode, or with Persistent storage disabled, you can lose data during a database outage. Use async only when throughput matters more than delivery.

IT Hub

Use the IT Hub ProcessorIT Hub Processor to run an ad-hoc SQL query through the connector. The connector streams the result back one message per row, on the response topic supplied in the request.

Add a ConnectorAdd a Connector

Manage ConnectorsManage Connectors

IT Hub ProcessorIT Hub Processor