[HELP] Unreliable data propagation to thingsbaord server using ODBC connector

[murp-C2D2]

Description I am running a thingsboard-iot-gateway instance running on an docker container in Windows. I have configured the ODBC connector and is able to query data from a PostgreSQL database and push them to the thingsboard server. I have a test table with 32 records containing only 3 columns.

There is however a problem here with the transmission/reliability of the gateway sending all the records. When I see the logs, The connector reports that all 32 records are processed. However, when I see the telemetry entries on the thingsboard server, several records (>50%) are missing. I don't see any error or warning in the logs on either on the server or the gateway.

What I tried and didn't work:

1. Tested it by connecting to the thingsboard demo server and the number of missing records is even higher.
2. Sorage type memory and file: no change
3. iterator persistance: true or false: no change

My tb_gateway.yml config

thingsboard:
  host: demo.thingsboard.io
  port: 1883
  remoteShell: false
  remoteConfiguration: false
  security:
    accessToken: testtesttes
  qos: 1
storage:
  type: memory
  read_records_count: 100
  max_records_count: 100000

connectors:
   name: ODBC Connector
   type: odbc
   configuration: odbc.json

My odbc.json

{
  "connection": {
    "str": "Driver={PostgreSQL ANSI};Server=192.168.1.104;Port=5432;Database=thingboard_test;Uid=cube_tester;Pwd=test;",
    "attributes": {
      "autocommit": true,
      "timeout": 0
    },
    "encoding": "utf-8",
    "decoding": {
      "char": "utf-8",
      "wchar": "utf-8",
      "metadata": "utf-16le"
    },
    "reconnect": true,
    "reconnectPeriod": 60
  },
  "pyodbc": {
    "pooling": false
  },
  "polling": {
    "query": "SELECT * FROM public.test WHERE record_id > ? ORDER BY record_id ASC LIMIT 10",
    "period": 60,
    "iterator": {
      "column": "record_id",
      "query": "SELECT MIN(record_id) - 1 FROM public.test",
      "persistent": true
    }
  },
  "mapping": {
    "device": {
      "type": "postgres",
      "name": "'ODBC_test_remote'"
    },
    "sendDataOnlyOnChange": false,
    "attributes": ["record_id"],
    "timeseries": ["record_id","temperature" ]
  },
  "serverSideRpc": {
    "enableUnknownRpc": false,
    "overrideRpcConfig": true,
    "methods": [
      "procedureOne",
      {
        "name": "procedureTwo",
        "args": [ "One", 2, 3.0 ]
      }
    ]
  }
}

Connector name: [ODBC Connector]

Error traceback: Extract of connector log file

""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 411 - [ODBC Connector] Set pyodbc attributes: {'pooling': False}"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 420 - [ODBC Connector] Processing unknown RPC disabled"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 426 - [ODBC Connector] Overriding RPC config enabled"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 289 - [ODBC Connector] Setting encoding to utf-8"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 296 - [ODBC Connector] Setting SQL_CHAR decoding to utf-8"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 299 - [ODBC Connector] Setting SQL_WCHAR decoding to utf-8"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 303 - [ODBC Connector] Setting SQL_WMETADATA decoding to utf-16le"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 311 - [ODBC Connector] Connection to database opened, attributes {'autocommit': True, 'timeout': 0}"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 342 - [ODBC Connector] Iterator saving enabled"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 362 - [ODBC Connector] Init iterator from database: column=record_id, start_value=0"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 217 - [ODBC Connector] Fetch column names: ['record_id', 'ts', 'temperature']"
""2021-01-08 18:29:19" - INFO - [odbc_connector.py] - odbc_connector - 228 - [ODBC Connector] Polling iteration finished. Processed rows: current 10, total 10"
""2021-01-08 18:30:19" - INFO - [odbc_connector.py] - odbc_connector - 228 - [ODBC Connector] Polling iteration finished. Processed rows: current 10, total 20"
""2021-01-08 18:31:19" - INFO - [odbc_connector.py] - odbc_connector - 228 - [ODBC Connector] Polling iteration finished. Processed rows: current 10, total 30"
""2021-01-08 18:32:19" - INFO - [odbc_connector.py] - odbc_connector - 228 - [ODBC Connector] Polling iteration finished. Processed rows: current 2, total 32"
""2021-01-08 18:33:19" - INFO - [odbc_connector.py] - odbc_connector - 228 - [ODBC Connector] Polling iteration finished. Processed rows: current 0, total 32"

Versions:

• OS: [WIndows 10 with Docker]
• Thingsboard IoT Gateway version [2.5.5.2]
• Python version[3.7.9]

[imbeacon]

Hi @murp-C2D2 ,

Thank you for your message, As we may see from your logs - you used LIMIT in your query. Please make sure that this query doesn't return the same value after every execution.

[murp-C2D2]

Thank you for your reply. I use the LIMIT clause in the SQL to limit the number of records to be processed (as suggested in the documentation). The record_id column I used in the SQL query is a auto incremented primary-key column in the database. So, it is not possible that the query would return duplicate records unless the gateway is not incrementing the iterator correctly. I have also checked on the server side to see if there were any duplicate records but this is not the case.

[samson0v]

Hi @murp-C2D2, your bug was fixed, please, update your Gateway to the newest version via the master branch. Thanks for your interest in ThingsBoard IoT Gateway.