Subscription of multiple topics

[alberto-y]

Hi,
first of all, thanks to your script I've been able, with some modifications, to start the script like a service, subscribe a topic, and write in MySQL the payload.
The only that I could improve, it's that I read in DB that the topicID only "1" and not the real name of the topic, but in this moment it's not a big issue

The biggest problem it's that I can't subscribe to multple topic, like I read in the mqtt2sql.conf file :

MQTT topics subscribe to [string] or [array]
mqtt-topic = [topic1/#, topic2/+/sub/#, topic3/#]

It works only with a single topic, it ignore if a put a second one, with [ ] or not, and also with , or ' '
Do you experienced something similar?
How I can solve it?
Thank you

[curzon01]

regardless of your modifications and their possible effects:

first enable

debug
verbose

within your conf or better add -v -dd to program parameter and add the program info here when you start it.

I assume mqtt-topic = [topic1/#, topic2/+/sub/#, topic3/#] is a conf line, right? or did you specify that as a program parameter?

[alberto-y]

Yes, I used the configuration file, according the documentation. I changed the parameters with my topics, but even if I wrote 2 or 3 topics, the script subscribe only the first.
I don't try to specify more than 1 topic with the parameters in the command line like parameter, in thi way I tested only with one topic.
Which is the right sintax to specify more than one topic like parameter ?
I'll make a try with the verbose and debug parameter
Thank you

[curzon01]

As long as I don't have any further info (your verbose/debug output, your conf file, your modifications etc.) I can't say anything about your issue

The right syntax for multiple topics in conf file is [topic1,topic2,topic3] like in the example file e.g.
mqtt-topic = [topic1/data/#, topic2/data/#]

The right syntax for multiple topics on command line is --mqtt-topic topic1 topic2 topic3 e.g.
--mqtt-topic topic1/data/# topic2/data/#

Then using add. -v -dd it starts like that:

mqtt2sql.py[27735] v2.5.5 start
  MQTT server: localhost:8883 SSL keepalive 60
       user:   mqttuser
       topics: ['topic1/data/#', 'topic2/data/#']
       exclude: None
  SQL  type:   MySQl
       server: localhost:3306 [max 100 connections]
       db:     mqtt
       table:  mqtt
       user:   mysqluser
  Log file:    mqtt2sql.log
  Debug level: 2
  Verbose level: 1
mqttc.connect(localhost, 8883, 60)
mqttc.connect() returns 0
MQTT on_connect(<paho.mqtt.client.Client object at 0x7f0525d93340>,{'haveresponse': False, 'starttime': 1676715164.9931443},{'session present': 0},0): No error.
subscribe to topic topic1/data/#
subscribe to topic topic2/data/#

[alberto-y]

I was able make a test, and subscribe multiple topics with this string (adapted from your documentation, with the latest info regarding the sintax to pass the arguments for the --mqtt-topic) :
./mqtt2sql.py --mqtt mqtt://username:password@localhost/ --mqtt-topic topic1/# topic2/#
--sql-type mysql --sql-host localhost --sql-username user --sql-password 'password' --sql-db mqtt -v -dd

2023-02-19 20:17:32: mqtt2sql.py[14905] v2.5.5 start
2023-02-19 20:17:32: MQTT server: localhost:1883 keepalive 60
2023-02-19 20:17:32: user: user
2023-02-19 20:17:32: topics: ['topic1/#', 'topic2/#']
2023-02-19 20:17:32: exclude: None
2023-02-19 20:17:32: SQL type: MySQl
2023-02-19 20:17:32: server: localhost:3306 [max 50 connections]
2023-02-19 20:17:32: db: mqtt
2023-02-19 20:17:32: table: mqtt
2023-02-19 20:17:32: user: user
2023-02-19 20:17:32: Debug level: 2
2023-02-19 20:17:32: Verbose level: 1
2023-02-19 20:17:32: mqttc.connect(localhost, 1883, 60)
2023-02-19 20:17:32: mqttc.connect() returns 0
2023-02-19 20:17:32: MQTT on_connect(<paho.mqtt.client.Client object at 0x7f1c0a0f61f0>,{'haveresponse': False, 'starttime': 1676837852.639129},{'session present': 0},0): No error.
2023-02-19 20:17:32: subscribe to topic topic1/#
2023-02-19 20:17:32: subscribe to topic topic2/#
2023-02-19 20:17:53: [139758391682816]: SQL success: table='mqtt', topic='topic2', value='b'Topic 2'', qos='0', retain='0'
2023-02-19 20:18:07: [139758391682816]: SQL success: table='mqtt', topic='topic1', value='b'Topic 1'', qos='0', retain='0'

I could not achieve the same result with the conf file uncommenting "verbose" and "debug" in the conf file, but it works passing it with the parameters :
./mqtt2sql.py -c /mqtt2sql/mqtt2sql.conf -v -dd
But the results are the same, finally all seems to work right, maybe there was some problem with the sintax, or some option uncommented I think.
With -v and -dd I can double check the output and compare it with the parameters and using the configuration file.

I see that the topic names are in mqtt table with its ID, right?
We can't have the name directly in the mqtt_history table?

If you want, I suggest to insert in the "Usage" section the --mqtt-topic parameter, and in the part "Create mqtt2sql.service" add
[Unit] on top of
Description=MQTT2SQL
After=local-fs.target network.target mysql.service

[Service]
Type=simple
Restart=always
RestartSec=10
ExecStart=/usr/local/bin/mqtt2sql.py --configfile /usr/local/etc/mqtt2sql.conf

[Install]
WantedBy=multi-user.target

Thank you so much for the script and the precious help and support

BR

[curzon01]

Regarding conf file, help is only possible if I can take a look into the file (hide private info like usernames, pw hostnames by *** if needed)

History table is normalized and storing the topic also in a second table contradicts the idea of relational databases. It also unnecessarily increases the amount of space needed, since all the information is already atomic and exists.

To get the topics also with the history data, JOIN the tables e. g.

SELECT 
    h.id,
    h.ts,
    m.topic, 
    h.value
FROM mqtt_history h
INNER JOIN mqtt m ON m.id = h.topicid;

Create a view if you want it using as a table:

CREATE VIEW v_mqtt_history AS
SELECT 
    h.id,
    h.ts,
    m.topic, 
    h.value
FROM mqtt_history h
INNER JOIN mqtt m ON m.id = h.topicid;

than you can use it like that:

SELECT  *
FROM v_mqtt_history
WHERE topic LIKE 'topic1/%'

[alberto-y]

I already solved with a similar query, it was only a question.
In this moment all works fine, so I think we can close the issue.
The only thing it's that I can't activate debug and verbose with the conf file, but it works right passing "-v -dd" parameters, so I don't need more help
Thank you so much for the kind and precious support
;)

[alberto-y]

[EDIT]
Sorry boring you, I understood that some problems are related to user rights and environment, due I'm using ubuntu without root user enabled, so the requirements are not installed right for root.

Making a fresh new installationin a VM, I need, more than what you wrote in the requirements : python3-dev python-is-python3 libmysqlclient-dev
And after check pip3 install paho-mqtt and mysqlclient

Then all works fine

[curzon01]

Making a fresh new installationin a VM, I need, more than what you wrote in the requirements : python3-dev python-is-python3 libmysqlclient-dev

Ubuntu 22.04.1 LTS Minimal

sudo apt install libmysqlclient-dev is enough that requirements works - it's a broken pypi package for mysqlclient installation (see #575 and #576)