search

emqx / MQTTX

A Powerful and All-in-One MQTT 5.0 client toolbox for Desktop, CLI and WebSocket.
https://mqttx.app
Apache License 2.0
3.85k stars 444 forks source link

Messages do not appear in the UI due to "SQLITE_ERROR: no such column: c.meta" #1405

Open kerryland opened 1 year ago

kerryland commented 1 year ago

What did I do

I upgraded to 1.9.5

What happened

mqtt messages no longer appear in the UI -- only in the logs, along with:

[2023-09-03 13:05:20] [ERROR] QueryFailedError: SQLITE_ERROR: no such column: c.meta

Expected

mqtt messages no longer appear in the UI

Environment

More detail

Logs:

[2023-09-03 13:03:50] [INFO] APP init
[2023-09-03 13:03:56] [INFO] MQTTX client with ID b0942945-dfc5-4e0d-8d4f-dc2fd34caecf assigned
[2023-09-03 13:03:56] [INFO] Connect client blackbox, MQTT/TCP connection: mqtt://192.168.1.25:1883 with Properties: {"sessionExpiryInterval":0,"receiveMaximum":null,"maximumPacketSize":null,"topicAliasMaximum":null,"requestResponseInformation":null,"requestProblemInformation":null,"authenticationMethod":null}
[2023-09-03 13:03:56] [INFO] blackbox connect success, MQTT.js onConnect trigger
[2023-09-03 13:03:56] [INFO] Topic: zabbix/problems/# is resubscribing
[2023-09-03 13:03:56] [INFO] Saved topic: zabbix/problems/#
[2023-09-03 13:03:56] [INFO] Topic: zabbix/problems/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:03:56] [INFO] Topic: homestatus/display/# is resubscribing
[2023-09-03 13:03:56] [INFO] Saved topic: homestatus/display/#
[2023-09-03 13:03:56] [INFO] Topic: homestatus/display/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:03:56] [INFO] Topic: RFM/101/03/up/1 is resubscribing
[2023-09-03 13:03:56] [INFO] Saved topic: RFM/101/03/up/1
[2023-09-03 13:03:57] [INFO] Topic: RFM/101/03/up/1 successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:03:57] [INFO] Topic: esp32/human_sensor/bedroom/# is resubscribing
[2023-09-03 13:03:57] [INFO] Saved topic: esp32/human_sensor/bedroom/#
[2023-09-03 13:03:57] [INFO] Topic: esp32/human_sensor/bedroom/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:03:57] [INFO] Topic: /topic/qos0/# is resubscribing
[2023-09-03 13:03:57] [INFO] Saved topic: /topic/qos0/#
[2023-09-03 13:03:57] [INFO] Topic: /topic/qos0/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:03:57] [INFO] Topic: stairwell/stat/motion is resubscribing
[2023-09-03 13:03:57] [INFO] Saved topic: stairwell/stat/motion
[2023-09-03 13:03:57] [INFO] Topic: stairwell/stat/motion successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:03:57] [INFO] Message Arrived with topic: stairwell/stat/motion
[2023-09-03 13:03:57] [INFO] blackbox message arrived: message added "message_cc7f4696-e896-4823-939b-fffd725a1a79" and added to topic: "stairwell/stat/motion", payload: "UP" MQTT.js onMessageArrived trigger with Properties: undefined, Retain Message
[2023-09-03 13:03:57] [ERROR] QueryFailedError: SQLITE_ERROR: no such column: c.meta
[2023-09-03 13:04:14] [INFO] Clear history successfully
[2023-09-03 13:04:34] [INFO] APP init
[2023-09-03 13:05:19] [INFO] MQTTX client with ID b0942945-dfc5-4e0d-8d4f-dc2fd34caecf assigned
[2023-09-03 13:05:19] [INFO] Connect client blackbox, MQTT/TCP connection: mqtt://192.168.1.25:1883 with Properties: {"sessionExpiryInterval":0,"receiveMaximum":null,"maximumPacketSize":null,"topicAliasMaximum":null,"requestResponseInformation":null,"requestProblemInformation":null,"authenticationMethod":null}
[2023-09-03 13:05:19] [INFO] blackbox connect success, MQTT.js onConnect trigger
[2023-09-03 13:05:19] [INFO] Topic: zabbix/problems/# is resubscribing
[2023-09-03 13:05:19] [INFO] Saved topic: zabbix/problems/#
[2023-09-03 13:05:20] [INFO] Topic: zabbix/problems/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:05:20] [INFO] Topic: homestatus/display/# is resubscribing
[2023-09-03 13:05:20] [INFO] Saved topic: homestatus/display/#
[2023-09-03 13:05:20] [INFO] Topic: homestatus/display/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:05:20] [INFO] Topic: RFM/101/03/up/1 is resubscribing
[2023-09-03 13:05:20] [INFO] Saved topic: RFM/101/03/up/1
[2023-09-03 13:05:20] [INFO] Topic: RFM/101/03/up/1 successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:05:20] [INFO] Topic: esp32/human_sensor/bedroom/# is resubscribing
[2023-09-03 13:05:20] [INFO] Saved topic: esp32/human_sensor/bedroom/#
[2023-09-03 13:05:20] [INFO] Topic: esp32/human_sensor/bedroom/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:05:20] [INFO] Topic: /topic/qos0/# is resubscribing
[2023-09-03 13:05:20] [INFO] Saved topic: /topic/qos0/#
[2023-09-03 13:05:20] [INFO] Topic: /topic/qos0/# successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:05:20] [INFO] Topic: stairwell/stat/motion is resubscribing
[2023-09-03 13:05:20] [INFO] Saved topic: stairwell/stat/motion
[2023-09-03 13:05:20] [INFO] Topic: stairwell/stat/motion successfully subscribed, Subscription Identifier: null, No Local flag: false, Retain as Published flag: false, Retain Handling: 0
[2023-09-03 13:05:20] [INFO] Message Arrived with topic: stairwell/stat/motion
[2023-09-03 13:05:20] [INFO] blackbox message arrived: message added "message_d94824a5-9ddd-4a39-824d-f227ef3e1fa9" and added to topic: "stairwell/stat/motion", payload: "UP" MQTT.js onMessageArrived trigger with Properties: undefined, Retain Message
[2023-09-03 13:05:20] [ERROR] QueryFailedError: SQLITE_ERROR: no such column: c.meta
[2023-09-03 13:05:27] [INFO] Message Arrived with topic: stairwell/stat/motion
[2023-09-03 13:05:27] [INFO] blackbox message arrived: message added "message_a4665f59-6dca-4894-bf95-a86cedabce89" and added to topic: "stairwell/stat/motion", payload: "DOWN" MQTT.js onMessageArrived trigger with Properties: undefined
[2023-09-03 13:05:27] [ERROR] QueryFailedError: SQLITE_ERROR: no such column: c.meta

I also tried to uninstall/reinstall 1.9.5, but it won't uninstall either:

Taskmgr_YacaWSTELL Un_A_wQ3uCi8CFf

ysfscream commented 1 year ago

Hello, @kerryland

From the provided logs and the described issue, a problem might be related to the database migration process. The QueryFailedError: SQLITE_ERROR: no such column: c.meta error suggests a missing database column, possibly due to an incomplete or failed migration script run.

You could try the following steps to address the issue:

  1. Close the MQTTX application completely.
  2. Navigate to %USERPROFILE%\AppData\Roaming\MQTTX and delete the MQTTX.db file. This action will remove the local data, so please back up any critical configurations or information if needed.
  3. Forcefully uninstall the application.
  4. Download and reinstall the latest version.

We are aware of some issues with version 1.9.5. Our team is actively working on these, and we plan to release version 1.9.6 soon to address them. We apologize for the inconvenience and thank you for your patience.

kerryland commented 1 year ago

Alternative workaround: Download the sqlite database browser, open MQTTX.db, and add a column named meta of type varchar to the MessageEntity table.

Playing with fire, obviously, but "it works for me" :-)