How save name MQTT topic to mysql

[company-ms]

Hello all!
I want save body MQTT topic to mysql.

How to implement this in mqttwarn.ini ?

My mqttwarn.ini:

[defaults]
hostname  = 'localhost'
port      = 1883

; name the service providers you will be using.
launch	 = mysql


[config:mysql]
host  =  'mysql.******.myjino.ru'
port  =  3306
user  =  '046626376_mqtt'
pass  =  '123456'
dbname  =  '9*******_mqtt'
targets = {
          # tablename  #fallbackcolumn ('NOP' to disable)
 'm2'   : [ 'namess',   'full'            ]
  }
  
[#]
targets = mysql:m2

[rgitzel]

@company-ms Ignore my comment, I'd not woken up. I'm trying your example now.

[jpmens]

Shall we assume that "body MQTT topic" means the MQTT payload? Question is also in which format is that payload, or should it just be put raw into the database? I'm not sure we can do that

[rgitzel]

@company-ms While setting up the mysql target is not easy, add the topic to your table is.

Just add a column called topic!

I created my table with that column: mysql> CREATE TABLE names (id INTEGER, name VARCHAR(25), topic VARCHAR(25));

I sent this message: '{"id": 1, "name": "rodney"}'

And got:

mysql> select * from names;
+------+--------+-------+
| id   | name   | topic |
+------+--------+-------+
|    1 | rodney | db    |
+------+--------+-------+
1 row in set (0.00 sec)

To test my theory, I added a payload column: mysql> alter table names add column payload VARCHAR(1024) AFTER name;

I did not change mqttwarn's config, or restart it. I simply sent the same message, and this was my table:

mysql> select * from names;
+------+--------+-----------------------------+-------+
| id   | name   | payload                     | topic |
+------+--------+-----------------------------+-------+
|    1 | rodney | NULL                        | db    |
|    1 | rodney | {"id": 1, "name": "rodney"} | db    |
+------+--------+-----------------------------+-------+
2 rows in set (0.00 sec)

[company-ms]

Example my topic which telemetry ( "/79037***/dht/ 23.5 ) - save all to mysql . Now only save 23.5

[rgitzel]

One more test:

mysql> alter table names add column _dtiso VARCHAR(1024) AFTER payload;
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from names;
+------+--------+-----------------------------+-----------------------------+-------+
| id   | name   | payload                     | _dtiso                      | topic |
+------+--------+-----------------------------+-----------------------------+-------+
|    1 | rodney | NULL                        | NULL                        | db    |
|    1 | rodney | {"id": 1, "name": "rodney"} | NULL                        | db    |
|    1 | rodney | {"id": 1, "name": "rodney"} | 2018-09-17T18:48:48.399504Z | db    |
+------+--------+-----------------------------+-----------------------------+-------+
3 rows in set (0.00 sec)

So it would seem a column with one of the names of the 'transformation data' fields will cause them to be stored automatically as strings. (I tried making '_dtsio' a timestamp column, but then the value is not set.)

[jpmens]

@rgitzel I think MySQL TIMESTAMP should work if our _dtiso timestamp format is supported by MySQL. Is it?

[rgitzel]

@jpmens I added some logging:

2018-09-17 19:10:25,008 DEBUG [mysql] adding row with sql 'insert into names (topic, payload, _dtiso, name, id) values (%s, %s, %s, %s, %s)' and values: ('db', u'{"id": 1, "name": "rodney"}', '2018-09-17T19:10:24.961154Z', 'rodney', 1)
2018-09-17 19:10:25,010 WARNING [mysql] Cannot add mysql row: (1292, "Incorrect datetime value: '2018-09-17T19:10:24.961154Z' for column '_dtiso' at row 1")

So, no. Some quick googling shows lots of people complaining that they can't insert an ISO-8601 timestamp directly.

[jpmens]

So we leave it as is, or we create a _dtmysqlnotisotimestamp or some sexy sounding equivalent in order to do so? :-) If you think it's worthwhile, please add a new issue, and we can probably easily do that.

[jpmens]

Dangerous territory though if we have to start mucking about with timezones. That is one thing we won't do. Zulu (UTC) or bust.

[rgitzel]

@jpmens I don't see a need for it at the moment. That said, I'm not using MySQL so I'm biased. ;-)

If someone needs it, they can ask. In meantime, I'll update the docs so it's more obvious. Then someone might pipe up.

[jpmens]

The Z is bothering it:

mysql> insert into dt values ("2018-09-17T19:10:24Z");
ERROR 1292 (22007): Incorrect datetime value: '2018-09-17T19:10:24Z' for column 'dt' at row 1
mysql> insert into dt values ("2018-09-17T19:10:24");
Query OK, 1 row affected (0.00 sec)

mysql> select * from dt;
+---------------------+
| dt                  |
+---------------------+
| 2018-09-17 19:10:24 |
+---------------------+
1 row in set (0.01 sec)

mysql>

[jpmens]

@rgitzel perfect, thank you.

[rgitzel]

@jpmens @company-ms how does this look?

https://github.com/rgitzel/mqttwarn/tree/issue-334-clarify-mysql-target#mysql

[company-ms]

@rgitzel Oh ,Thanks so much! I didn't know it was that easy.

how does this look?

Yeah, but it's hard for me(

@jpmens best product. thank you for it!

[jpmens]

@rgitzel that looks good; I am assuming #335 superceeds this, so I'll continue there.