SQL error: md_ets_metadata when trying tutorial docker setup

[derteufelqwe]

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]

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]

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]

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]

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]

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]

@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:

• https://github.com/orchestracities/ngsi-timeseries-api/releases/tag/1.0.0
• https://hub.docker.com/layers/orchestracities/quantumleap/1.0.0/images/sha256-d86bae97bea13b06830039979af979b1c9b24beddbc70df7a6ab4748ff618897?context=explore