orchestracities/ngsi-timeseries-api

SQL error: md_ets_metadata when trying tutorial docker setup

derteufelqwe opened this issue · 6 comments

derteufelqwe commented

Describe the bug
When I try the docker-compose file from the tutorial and try to query quantumleap, I get an exception in quantumleap and no data returned.
Quantumleap reports the following error:

Logs

time=2023-12-03 12:45:28.089 | level=ERROR | corr=None | from=172.22.0.1 | srv=openiot | subserv=None | op=_get_entity_type | comp=translators.crate | msg=RelationUnknown[Relation 'md_ets_metadata' unknown] | payload=None | thread=139669828012832 | process=9
2023-12-03T12:45:28.094796692Z Traceback (most recent call last):
2023-12-03T12:45:28.094806282Z File "/src/ngsi-timeseries-api/src/translators/sql_translator.py", line 1660, in _get_entity_type
2023-12-03T12:45:28.094840812Z self.cursor.execute(stmt)
2023-12-03T12:45:28.094846902Z File "/usr/local/lib/python3.8/site-packages/crate/client/cursor.py", line 53, in execute
2023-12-03T12:45:28.094850262Z self._result = self.connection.client.sql(sql, parameters,
2023-12-03T12:45:28.094853182Z File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 396, in sql
2023-12-03T12:45:28.094856342Z content = self._json_request('POST', self.path, data=data)
2023-12-03T12:45:28.094859272Z File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 523, in _json_request
2023-12-03T12:45:28.094863482Z _raise_for_status(response)
2023-12-03T12:45:28.094870502Z File "/usr/local/lib/python3.8/site-packages/crate/client/http.py", line 206, in _raise_for_status
2023-12-03T12:45:28.094873741Z raise ProgrammingError(error.get('message', ''),
2023-12-03T12:45:28.094876641Z crate.client.exceptions.ProgrammingError: RelationUnknown[Relation 'md_ets_metadata' unknown]

To Reproduce

  1. Download this dockerfile
  2. Replace the orion image with fiware/orion-ld (or just take the docker-compose file below)
  3. Try to run the query below
  4. Take a look at the quantumleap container
my docker-compose.yaml file 

version: '3'

services:

  orion:
    image: fiware/orion-ld
    ports:
      - "1026:1026"
    command: -logLevel DEBUG -noCache -dbhost mongo
    depends_on:
      - mongo
    healthcheck:
      test: ["CMD", "curl", "-f", "http://0.0.0.0:1026/version"]
      interval: 1m
      timeout: 10s
      retries: 3

  quantumleap:
    image: orchestracities/quantumleap:${QL_VERSION:-latest}
    ports:
      - "8668:8668"
    depends_on:
      - mongo
      - orion
      - crate
    environment:
      - CRATE_HOST=${CRATE_HOST:-crate}
      - USE_GEOCODING=True
      - REDIS_HOST=redis
      - REDIS_PORT=6379
      - LOGLEVEL=DEBUG

  mongo:
    image: mongo:${MONGO_VERSION:-4.4}
    ports:
      - "27017:27017"
    volumes:
      - mongodata:/data/db

  crate:
    image: crate:${CRATE_VERSION:-4.6.7}
    command: crate -Cauth.host_based.enabled=false
      -Ccluster.name=democluster -Chttp.cors.enabled=true -Chttp.cors.allow-origin="*"
    environment:
      - LOG4J_FORMAT_MSG_NO_LOOKUPS=true
    ports:
      # Admin UI
      - "4200:4200"
      # Transport protocol
      - "4300:4300"
    volumes:
      - cratedata:/data

  timescale:
    image: timescale/timescaledb-postgis:${TIMESCALE_VERSION:-2.3.0-pg13}
    ports:
      - "5432:5432"
      # Don't expose container port 5432 with the same number outside of the
      # swarm. In the Travis test env, there's already a PG instance running
      # on port 5432!
    environment:
      - POSTGRES_PASSWORD=*

  grafana:
    image: grafana/grafana
    ports:
      - "3000:3000"
    depends_on:
      - crate

  redis:
    image: redis:${REDIS_VERSION:-6.2.3}
    ports:
      - "6379:6379"
    volumes:
      - redisdata:/data

volumes:
  mongodata:
  cratedata:
  redisdata:

networks:
  default:

GET localhost:8668/v2/entities/urn:ngsi-ld:Sensor:1/attrs/unixtime
Accept: application/json
Fiware-Service: openiot

Expected behavior
No error happens and the md_ets_metadata table exists

Environment (please complete the following information):

  • OS: Ubuntu 22.04.3 LTS
  • Version Latest docker container
SBlechmann commented

Hi @derteufelqwe,

I suggest to use the "orchestracities/quantumleap:edge" according to #742.

Edit. Since the new version 1.0.0 was released 3 days ago, I guess you pulled that image?

SBlechmann commented

Besides: Did you even push any data to QL? According to your described steps, you do not push any data to Orion so that a notification could be triggered.

derteufelqwe commented

Thanks @SBlechmann,
but the edge version of the image didn't solve the issue.

You are correct, I never pushed any data.
Can you give me a hint where to push my data to, so I receive it in quantumleap? I tried to follow the official documentation but the actual updating of sensor values is hidden in a docker container.

Where would I send sensor values when I have the following subscription?

POST localhost:1026/v2/subscriptions/
Content-Type: application/json
fiware-service: openiot
fiware-servicepath: /

{
  "description": "Notify QuantumLeap of count changes of any Motion Sensor",
  "subject": {
    "entities": [
      {
        "idPattern": "Motion.*"
      }
    ],
    "condition": {
      "attrs": [
        "xxValue"
      ]
    }
  },
  "notification": {
    "http": {
      "url": "http://quantumleap:8668/v2/notify"
    },
    "attrs": [
      "xxValue"
    ],
    "metadata": ["dateCreated", "dateModified"]
  },
  "throttling": 1
}
SBlechmann commented

Hi @derteufelqwe,

I guess, you have found these tutorials? https://fiware-tutorials.readthedocs.io/en/latest/iot-sensors.html

If the subscription is saved in the context broker, you just need to patch an attribute, see https://github.com/telefonicaid/fiware-orion/blob/master/doc/manuals/user/walkthrough_apiv2.md#update-entity

derteufelqwe commented

Hi @SBlechmann
After a whole lot of trial and error I managed to get this to work. As you said I need to push data via the subscription first, so the necessary tables are created.
I still feel like quantumleap shouldn't throw any errors as tha's quite confusing for the user.

c0c0n3 commented

@SBlechmann thanks so much for replying to @derteufelqwe, much appreciated!

@derteufelqwe thanks for your feedback.

I still feel like quantumleap shouldn't throw any errors as tha's quite confusing for the user.

It is, I agree. This is an unfortunate side-effect of creating tables on the fly. The metadata table only gets created when QL receive the first notification ever. So if you try querying data before the first notification you get that error. At the moment fixing this isn't a priority for us, since it's a corner case. But you're welcome to open an issue about it, so we can track it.

@SBlechmann @derteufelqwe we released version 1.0.0, rather use that if you can: