no messages received

[DerDoubleD]

Please help me again.

The script appears to start normally and continues to run until I stop it.
But it seems that no messages are being received from MQTT.
I checked it with MQTTfx, and messages are being sent to the correct topic.

2025-10-10 18:52:31: mqtt2sql.py[34852] v3.0.4 start
2025-10-10 18:52:31:   MQTT server: 127.0.0.1:1883  keepalive 60
2025-10-10 18:52:31:     user:    None
2025-10-10 18:52:31:     topics:  wetterstation/#
2025-10-10 18:52:31:     exclude: None
2025-10-10 18:52:31:   SQL type: MySQL
2025-10-10 18:52:31:     server:   127.0.0.1:3306 [max 50 connections]
2025-10-10 18:52:31:     db:       mbcbwetter
2025-10-10 18:52:31:     table:    mqtt
2025-10-10 18:52:31:     user:     mbcbwetter
2025-10-10 18:52:31:     timezone: UTC
2025-10-10 18:52:31:   Log file: /opt/testmqtt2sql/mqtt2sql/log_mqtt2sql.log
2025-10-10 18:52:31:   Debug level: 1
2025-10-10 18:52:31:   Verbose level: 1

also a paho_mqtt script on the same server delivers results

import paho.mqtt.client as paho
def on_subscribe(client, userdata, mid, granted_qos):
    print("Subscribed: "+str(mid)+" "+str(granted_qos))
def on_message(client, userdata, msg):
    print(msg.topic+" "+str(msg.qos)+" "+str(msg.payload))
client = paho.Client()
client.on_subscribe = on_subscribe
client.on_message = on_message
client.connect('localhost', 1883)
client.subscribe('wetterstation/#', qos=1)
client.loop_forever()

 client = paho.Client()
Subscribed: 1 (1,)
wetterstation 0 b'60\n\n\n\n'
wetterstation 0 b'60\n\n\n\n'
wetterstation 0 b'60\n\n\n\n'
wetterstation 0 b'60\n\n\n\n'

How could I debug better to further narrow down my error?

[DerDoubleD]

MQTT messages arrive, but nothing is written to the database.
I have added more print outputs to test it

    def on_message(self, client, userdata, message):
        **print(message.topic+" "+str(message.qos)+" "+str(message.payload))**

wetterstation 0 b'12345'

**print("client:" + str(client) + " userdata: " + str(userdata) + " message: " +str(message))**
client:<paho.mqtt.client.Client object at 0x7aac93b34c20> userdata: {'haveresponse': False, 'starttime': 1760118559.2190216} message: <paho.mqtt.client.MQTTMessage object at 0x7aac93adf0d0>

The database remains empty

[curzon01]

General: For more debug output simply use debug=2 or debug=3 within your mqtt2sql.conf, you don't necessarily have to change the program with the specified ones (which sometimes leads to side effects depending on the change).

Do you have access to mysql with the given credentials, e. g.

mysql -u mbcbwetter -p -h localhost mqtt -e 'INSERT INTO mqtt (topic, value) VALUES ("wetterstation", "0");'

?

[DerDoubleD]

Thanks for the tip about debug=2.

The following error message is displayed...

2025-10-11 23:20:22: subscribe to topic ws/#
2025-10-11 23:20:28: [133518132627136]: MySQL transaction ERROR: [1366]: Incorrect integer value: 'False' for column 'retain' at row 1, retry=10, delay=0.6049242858413291
2025-10-11 23:20:47: [133518132627136]: MySQL transaction ERROR: [1366]: Incorrect integer value: 'False' for column 'retain' at row 1, retry=10, delay=1.4757525702814391
2025-10-11 23:21:03: [133518132627136]: MySQL transaction ERROR: [1366]: Incorrect integer value: 'False' for column 'retain' at row 1, retry=10, delay=1.3555021590242604

[curzon01]

Just out of interest: which version of Paho are you using (use python -m pip show paho-mqtt)?

[DerDoubleD]

Just out of interest: which version of Paho are you using (use python -m pip show paho-mqtt)?

Name: paho-mqtt
Version: 2.1.0

Python 3.13.5

Debian 13 trixie

I've learned a lot so far and want to give something back.
Is it okay with you if I submit a pull request with additions to the README?

[curzon01]

Thx, PRs are always welcome

[curzon01]

Fixed, use current v3.0.5