Logging every published message

[alberto-y]

Hi,
I have small question.
There's an easy way to LOG every message/received, also for the the same topic with the same payload, changing only the timestamp?
I enabled history_enable and history_diffonly, and I achieve to log every message, but only if the payload changes from the last publish of the same topic.
For testing purposes I need to log every publish, to check how many messages sent from the remote application lands right to the broker, and how many are lost.
For example, I send 5 times the same payload to the same topic, with some minutes of delay, but actually I can't see if the broker received it for 5 times, or maybe 1 or 2.
Sometimes we have remote connection stability problem, so could be useful if we can check remotely how many time we sent the message, and locally how many messages we received, without manipulate the payload to change something.
When I'll complete this testing period, and analyzed all the records, we can restore the normal behaviour.
Thank you so much

[curzon01]

Disable history_diffonly for all desired topic(s) in table mqtt.

This logs all desired topics in table mqtt_history with timestamp of occurrence, regardless of whether the payload changes or not.

see also History control history_diffonly

[alberto-y]

Hi already disabled history_diffonly , as I wrote, but the script logs only one message, if the payload don't change.
For example if I publish 5 times "1" in the topic "test" the script logs only 1 message (the first).
Instead if I publish "1" and then "2", then newly "1" and "2" again it works right, and logs every message, but I need to change the payload every publish.
The script don't log the same payload, if I send it more times consecutively

[curzon01]

Sorry, you wrote

I enabled history_enable and history_diffonly

is history_diffonly enabled (=1) or not (=0)?

[alberto-y]

Sorry, I think I wasn't clear in my previous message, when I wrote "enabled", I mean that I set this parameters to record everything.
In this moment I right activated recording every message, with history_enable at 1 (history_diffonly it's ignored with this at '0') and history_diffonly at '0', disabled, to record everything (with this at '1' the script record only one message if the payload it's the same, even if I send it more times).
The "problem" it's that I need to record every message, also if the payload it's the same, but in this moment, with this settings, the only way it's to change the payload, and alternate it.
For example :
test/1
test/1
test/1
test/1
test/1
I record only the first
test/1
test/2
test/1
test/2
test/1
test/2
I record in the DB all the messages.
But I'm in the first case

[curzon01]

I fully understand your request but I don't understand what you are doing.

Given the following table contents:

> select * from mqtt;
+----+---------------------+----------+-------+-----+--------+----------------+------------------+
| id | ts                  | topic    | value | qos | retain | history_enable | history_diffonly |
+----+---------------------+----------+-------+-----+--------+----------------+------------------+
|  1 | 2023-03-22 10:59:37 | dev/test | 1     |   0 |      0 |              1 |                0 |
+----+---------------------+----------+-------+-----+--------+----------------+------------------+

> select * from mqtt_history;
+----+---------------------+---------+-------+
| id | ts                  | topicid | value |
+----+---------------------+---------+-------+
|  1 | 2023-03-22 10:59:37 |       1 | 1     |
+----+---------------------+---------+-------+

another publish to topic dev/test using payload 1 at 2023-03-22 11:06:46 changes the tables to

> select * from mqtt;
+----+---------------------+----------+-------+-----+--------+----------------+------------------+
| id | ts                  | topic    | value | qos | retain | history_enable | history_diffonly |
+----+---------------------+----------+-------+-----+--------+----------------+------------------+
|  1 | 2023-03-22 11:06:46 | dev/test | 1     |   0 |      0 |              1 |                0 |
+----+---------------------+----------+-------+-----+--------+----------------+------------------+

> select * from mqtt_history;
+----+---------------------+---------+-------+
| id | ts                  | topicid | value |
+----+---------------------+---------+-------+
|  1 | 2023-03-22 10:59:37 |       1 | 1     |
|  2 | 2023-03-22 11:06:46 |       1 | 1     |
+----+---------------------+---------+-------+

and this is extact what you want.

If this does not work, check the functionaly related trigger mqtt_after_update, it must contain (from init script mysql.sql)

BEGIN
    IF NEW.history_enable = 1 AND (NEW.history_diffonly = 0 OR (NEW.history_diffonly = 1 AND OLD.value != NEW.value)) THEN
        INSERT INTO mqtt_history SET ts=NEW.ts, topicid=NEW.id, value=NEW.value;
    END IF;
END

[alberto-y]

I'm really sorry, was my fault!
Using a couple of already logged Topid ID in 'mqtt' table, and double checking the records, I see that I updated the right fields in the table properties, but not in the already existent records.
Creating a new topic, and with a simple update of the existent records in 'mqtt' table, all works flawless
Thank you so much for the kind support and help
Sorry...