MariaDB Flux Asset
Introduction
MariaDB is a popular SQL database management system that derives from MySQL. It offers a rich set of features and is widely recognized for its performance, reliability, and ease of use. MariaDB serves as a robust backend for a wide array of applications, from simple websites to complex transactional systems for large enterprises.
MariaDBMQTT facilitates seamless integration between MariaDB and various MQTT-enabled devices and services. It supports efficient data transfer from MariaDB to an MQTT broker and vice versa. This asset enables users to leverage MQTT for interacting with the database, allowing for both data extraction and insertion via MQTT messages. This interaction model supports real-time data processing and enhances the capabilities of IoT applications by linking them directly with MariaDB.
Features and Benefits
- Seamless Communication: Enables seamless communication between MariaDB databases and MQTT clients, streamlining the exchange of data across platforms.
- Real-time Query Execution: Facilitates real-time querying of the MariaDB database through MQTT messages, enabling dynamic data retrieval and manipulation.
- Easy Integration: Simplifies the integration of MariaDB with your IoT projects, making it straightforward to connect database services with various IoT devices.
- Dynamic Query Capability: Allows users to execute SQL queries directly through MQTT, enhancing flexibility and responsiveness.
Prerequisites
Before you install and configure the asset, make sure you have the following prerequisites:
- Basic understanding of the MQTT protocol.
- Basic understanding of SQL and MariaDB operations.
- Access to an MQTT broker compatible with your setup.
- MQTT Client software (such as MQTT Explorer or similar).
- Access to a MariaDB database instance.
Asset installation
Using Coreflux HUB
Please refer the to general docs for asset installation through the Coreflux HUB.
Using Coreflux HUBLESS
Asset management and control are conducted using the MQTT protocol. Commands (payload) are sent to the $SYS/Coreflux/Command topic. The results of these commands are published to $SYS/Coreflux/Command/Output. The following steps will focus solely on the payload that needs to be sent.
- Connect to the Coreflux MQTT Broker using your preferred client.
- Login using your coreflux account:
- Check your available assets:
- Install asset:
- If there were no issues during the installation you should receive a message on the $SYS/Coreflux/Command/Output:
Asset configuration
⚠ : If a parameter is not included in your asset's configuration and it is not required, a default value will be applied. Incorrect configuration may cause the asset to not work as intended.
ℹ : This configuration is presented using a hubless setup as an example. The key takeaway is the understanding of the parameters and their significance. If you are configuring the asset via Coreflux Hub, the same parameters and configurations apply.
MQTT Parameters
The MQTTClient in the JSON configuration define how to connect to an MQTT broker, specifying the communication details. The Address and Port indicate the broker's network location (in this case, 127.0.0.1 on port 1883), which is where the data will be sent to or received from. The parameters also detail authentication methods and the use of TLS for secure communication. This setup determines the pathway for data exchange between the device and the MQTT broker, facilitating the monitoring or control of device operations.
Parameter | Description | Required | Example | Default Value |
---|---|---|---|---|
Port | Port number on which the MQTT broker is running. | Yes | 1883 |
1883 |
Address | IP address or hostname of the MQTT broker. | Yes | "127.0.0.1" |
"127.0.0.1" |
IsAnonymous | Indicates if the connection is anonymous (no username/password required). | Yes | true |
true |
Username | Username for authentication, if not anonymous. | No | "" (empty string) |
"" (empty string) |
Password | Password for authentication, if not anonymous. | No | "" (empty string) |
"" (empty string) |
EnableTLS | Specifies whether TLS encryption is enabled for secure communication. | Yes | true |
false |
Example:
{
"MQTTClient": {
"Port": 1883,
"Address": "127.0.0.1",
"IsAnonymous": true,
"Username": "",
"Password": "",
"EnableTLS": false
}
}
SQL Parameters
The SQL in the JSON configuration outline the setup for SQL communication between the asset and a MariaDB database. These parameters are essential for establishing a connection to the database, enabling the asset to execute queries and manage data effectively. They include specifying the server address, port, database name, and credentials for authentication. This configuration ensures the asset can interact with the database securely and efficiently. Additionally, the query timeout setting helps manage the performance of data interactions, preventing long waits in case of database response delays.
Parameter | Description | Required | Example | Default Value |
---|---|---|---|---|
ServerAddress | IP address or hostname of the SQL server. | Yes | "127.0.0.1" |
N/A |
ServerPort | Port number on which the SQL server is running. | Yes | 3306 |
N/A |
Databasename | Name of the database to connect to. | Yes | "example-database" |
N/A |
Username | Username for authentication with the SQL server. | Yes | "user" |
N/A |
Password | Password for authentication with the SQL server. | Yes | "pass" |
N/A |
QueryTimeoutInSeconds | Maximum duration in seconds before a query is terminated if not done. | No | 10 |
N/A |
Example:
{
"MQTTClient": {
"Port": 1883,
"Address": "127.0.0.1",
"IsAnonymous": true,
"Username": "",
"Password": "",
"EnableTLS": false
},
"SQL": {
"ServerAddress": "test.address",
"ServerPort": 3306,
"Databasename": "example-database",
"Username": "user",
"Password": "pass",
"QueryTimeoutInSeconds": "10"
}
}
Tags
Tags are crucial components that facilitate both querying from and writing to your MariaDB database via MQTT interactions. Each Tag establishes a direct link between a database operation and an MQTT topic, enabling not only the execution of SQL queries but also the transmission of responses back to MQTT clients. These tags configure the specifics of the SQL operations and the MQTT communication, including the SQL query to execute, the MQTT topics for initiating requests and receiving responses, and the data formatting preferences.
Parameter | Description | Required | Example | Default Value |
---|---|---|---|---|
Name | A unique identifier for the tag, up to 50 characters. Used to reference the tag within the system. | Yes | "TemperatureData" |
N/A |
WriteDirection | Direction of data flow (ToAll (0), ToMQTT (1), ToSQL (2)), indicating how data is communicated. | Yes | 2 |
N/A |
mqttTriggerTopic | MQTT topic for triggering the query associated with the Tag. | Yes | "request/temperature" |
N/A |
mqttFeedbackTopic | MQTT topic for publishing the results of the query. | Yes | "response/temperature" |
N/A |
mqttFeedbackQos | Quality of Service level for MQTT communications (0 to 2), balancing efficiency and reliability. | Yes | 1 |
0 (At most once) |
mqttFeedbackRetain | Indicates if the last message published to the MQTT topic should be retained by the broker. | Yes | true |
false |
Query | The SQL query to be executed when this Tag is triggered. | Yes | "SELECT temperature FROM data" |
N/A |
OutputType | The format of the output data (Raw (0), Csv (1), Json (2)), affecting how the results are formatted. | Yes | 2 |
N/A |
Example:
{
"MQTTParameters": {
"Port": 1883,
"Address": "127.0.0.1",
"IsAnonymous": true,
"Username": "",
"Password": "",
"EnableTLS": false
},
"SQL": {
"ServerAddress": "test.address",
"ServerPort": 3306,
"Databasename": "example-database",
"Username": "user",
"Password": "pass",
"QueryTimeoutInSeconds": "10"
},
"Tags": [
{
"Name": "TemperatureData",
"WriteDirection": "ToMQTT",
"mqttTriggerTopic": "request/temperature",
"mqttFeedbackTopic": "response/temperature",
"mqttFeedbackQos": 1,
"mqttFeedbackRetain": true,
"Query": "SELECT temperature FROM data",
"OutputType": "Json"
},
{
"Name": "TemperatureData2",
"WriteDirection": "ToMQTT",
"mqttTriggerTopic": "request/temperature2",
"mqttFeedbackTopic": "response/temperature2",
"mqttFeedbackQos": 1,
"mqttFeedbackRetain": true,
"Query": "SELECT temperature FROM data2",
"OutputType": "Json"
}
]
}
Saving the configuration
Asset management and control are conducted using the MQTT protocol. Commands (MQTT payload) are sent to the $SYS/Coreflux/Command topic. The results of these commands are published to $SYS/Coreflux/Command/Output. The following steps will focus solely on the payload that needs to be sent.
ℹ : Configuration requires the user to be logged in and have an asset installed. If these requirements are not met, please refer to the installation section.
- Save configuration:
ℹ : The asset asset_guid can be obtained by consulting the $SYS/Coreflux/Assets topic.
Example:
{"MQTTParameters":{"Port":1883,"Address":"127.0.0.1","IsAnonymous":true,"Username":"","Password":"","EnableTLS":false},"SQL":{"ServerAddress":"test.address","ServerPort":3306,"Databasename":"example-database","Username":"user","Password":"pass","QueryTimeoutInSeconds":"10"},"Tags":[{"Name":"TemperatureData","WriteDirection":"ToMQTT","mqttTriggerTopic":"request/temperature","mqttFeedbackTopic":"response/temperature","mqttFeedbackQos":1,"mqttFeedbackRetain":true,"Query":"SELECT temperature FROM data","OutputType":"Json"},{"Name":"TemperatureData2","WriteDirection":"ToMQTT","mqttTriggerTopic":"request/temperature2","mqttFeedbackTopic":"response/temperature2","mqttFeedbackQos":1,"mqttFeedbackRetain":true,"Query":"SELECT temperature FROM data2","OutputType":"Json"}]}
- Check if the configuration was saved (optional) :
Using the asset
⚠ : It is important to understand that the flow of data between the broker and the device highly depends on the configuration of both the asset and the device. In case of unexpected results, please verify the configuration and/or check the logs for any possible errors.
With Coreflux HUB
Please refer the to general docs for asset installation through the Coreflux HUB.
With Coreflux HUBLESS
Asset management and control are conducted using the MQTT protocol. Commands (MQTT payload) are sent to the $SYS/Coreflux/Command topic. The results of these commands are published to $SYS/Coreflux/Command/Output. The following steps will focus solely on the payload that needs to be sent.
ℹ : The asset guid can be obtained by consulting the $SYS/Coreflux/Assets topic.
Run asset
- Run asset:
Stop asset
- Stop asset:
Logs
Logs are essential for monitoring and debugging your system. They provide insight into the operation of your assets and can help identify and resolve issues promptly.
With Coreflux HUB
On Coreflux HUB, the logs are displayed in the "Log" section of the asset configuration.
With MQTT Explorer
To display logs in MQTT Explorer, follow these steps:
- Publish the following command to the $SYS/Coreflux/Command topic:
Example1:
To display informational
logs, use:
Note : This will display the Logs "Information" of
all the assets
Example2:
To display error logs, from a specific asset, use:
Note : This will display the Logs "Error" of a
specific asset
Note: To display all three types of logs (Error, Information, and Warning), you will need to repeat this process three times, one for each log level. three times, one for each log level.