mysql

The MySQL plugin is one of the most complicated to set up. It requires the following configuration:

[config:mysql]
host  =  'localhost'
port  =  3306
user  =  'jane'
pass  =  'secret'
dbname  =  'test'
targets = {
          # tablename  #fallbackcolumn
 'm2'   : [ 'names',   'full'            ]
  }

Suppose we create the following table for the target specified above:

CREATE TABLE names (id INTEGER, name VARCHAR(25));

and publish this JSON payload:

mosquitto_pub -t my/2 -m '{ "name" : "Jane Jolie", "id" : 90, "number" : 17 }'

This will result in the two columns id and name being populated:

+------+------------+
| id   | name       |
+------+------------+
|   90 | Jane Jolie |
+------+------------+

The target contains a so-called fallback column into which mqttwarn adds the "rest of" the payload for all columns not targetted with JSON data. I'll now add our fallback column to the schema:

ALTER TABLE names ADD full TEXT;

Publishing the same payload again, will insert this row into the table:

+------+------------+-----------------------------------------------------+
| id   | name       | full                                                |
+------+------------+-----------------------------------------------------+
|   90 | Jane Jolie | NULL                                                |
|   90 | Jane Jolie | { "name" : "Jane Jolie", "id" : 90, "number" : 17 } |
+------+------------+-----------------------------------------------------+

As you can imagine, if we add a number column to the table, it too will be correctly populated with the value 17.

The payload of messages which do not contain valid JSON will be coped verbatim to the fallback column:

+------+------+-------------+--------+
| id   | name | full        | number |
+------+------+-------------+--------+
| NULL | NULL | I love MQTT |   NULL |
+------+------+-------------+--------+

You can add columns with the names of the built-in transformation types (e.g. _dthhmmss, see below) to have those values stored automatically.