[BUG] ODBC Connector/Gateway service failing with timestamp(ts) column

[murp-C2D2]

ODBC connector and the gateway service fails to process the records when the records consists of timestamp column with columns name "ts". This error occurs both when blobbing (telemetry: "*") or column lists (telemetry: ["ts", "temperature"]) are specified in odbc connector configs.

I have debugged the code and have narrowed the issue to the code in tb_gateway_service.py. I have attached a patch that highlights these changes and fixes this issue. The issue being, when "ts" item is present the json telemetry object is incorrectly built.

I have only tested to check if the ODBC connector works but I haven't tested if this fix breaks other connectors.

Connector name (If bug in the some connector): [ODBC Connector]

Versions (please complete the following information):

• OS: Windows 10 with docker
• Thingsboard IoT Gateway version: 2.5.5/ master
• Python version:3.7

**Patch

diff --git a/thingsboard_gateway/gateway/tb_gateway_service.py b/thingsboard_gateway/gateway/tb_gateway_service.py
index 7e990d7..2bd011f 100644
--- a/thingsboard_gateway/gateway/tb_gateway_service.py
+++ b/thingsboard_gateway/gateway/tb_gateway_service.py
@@ -337,7 +337,7 @@ class TBGatewayService:
             if item.get("ts") is None:
                 telemetry = {**telemetry, **item}
             else:
-                telemetry_with_ts.append({"ts": item["ts"], "values": {**item["values"]}})
+                telemetry_with_ts.append({"ts": item["ts"], "values": {**telemetry}})
         if telemetry_with_ts:
             data["telemetry"] = telemetry_with_ts
         else:

[SamueleCarboni]

Hello @murp-C2D2 . I had the same problem with the MQTT connector. I tried your patch, but I noticed that in that case the converted "ts" key must be put as last in the telemetry part of the mqtt.json config file.

I fixed it just by using this:

telemetry = {}
telemetry_with_ts = []
my_ts=int(time() * 1000)
for item in data["telemetry"]:
    if item.get("ts") is None:
        telemetry = {**telemetry, **item}
    else:
        my_ts=item["ts"]

data["telemetry"] = {"ts": my_ts, "values": telemetry}

Thank you for the assist!

[murp-C2D2]

Hi @zbeacon, are there any updates in regards to this bug? The above provided patch/solution seems to be not yet integrated in the current release version. It would be really nice if this can be integrated/ fixed. Thank you for your efforts!

[samson0v]

Hi @SamueleCarboni, please, update your Gateway to the newest version via the master branch and retest your bug. If it doesn't help, let us know.

Thanks for your interest in ThingsBoard IoT Gateway.