SQL error: md_ets_metadata when trying tutorial docker setup

derteufelqwe commented 7 months ago

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

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

my docker-compose.yaml file

```yaml 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 7 months ago

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 7 months ago

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 7 months ago

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 7 months ago

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 7 months ago

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 6 months ago

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

orchestracities / ngsi-timeseries-api

SQL error: md_ets_metadata when trying tutorial docker setup #750