Coalition DB ID not found in HOG

[ArneTR]

When submitting PowerHog data to the API this error occurs:

green-coding-gunicorn-container  |   File "/var/www/startup/venv/lib/python3.12/site-packages/fastapi/routing.py", line 212, in run_endpoint_function
green-coding-gunicorn-container  |     return await dependant.call(**values)
green-coding-gunicorn-container  |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
green-coding-gunicorn-container  |   File "/var/www/green-metrics-tool/api/main.py", line 806, in hog_add
green-coding-gunicorn-container  |     coaltion_db_id = DB().fetch_one(query=query, params=params)[0]
green-coding-gunicorn-container  |                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
green-coding-gunicorn-container  |   File "/var/www/green-metrics-tool/lib/db.py", line 66, in fetch_one
green-coding-gunicorn-container  |     return self.__query(query, params=params, return_type='one', fetch_mode=fetch_mode)
green-coding-gunicorn-container  |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
green-coding-gunicorn-container  |   File "/var/www/green-metrics-tool/lib/db.py", line 51, in __query
green-coding-gunicorn-container  |     cur.execute(query, params)
green-coding-gunicorn-container  |   File "/var/www/startup/venv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
green-coding-gunicorn-container  |     raise ex.with_traceback(None)
green-coding-gunicorn-container  | psycopg.errors.NumericValueOutOfRange: bigint out of range
green-coding-gunicorn-container  |
green-coding-gunicorn-container  |
green-coding-gunicorn-container  | <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< 0_o >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
green-coding-gunicorn-container  |
green-coding-gunicorn-container  | Error: Error in API call - catch_exceptions_middleware
green-coding-gunicorn-container  |
green-coding-gunicorn-container  | Url (<class 'starlette.datastructures.URL'>): http://api.green-coding.io/v1/hog/add
green-coding-gunicorn-container  | Query_params (<class 'starlette.datastructures.QueryParams'>):
green-coding-gunicorn-container  | Client (<class 'NoneType'>): None
green-coding-gunicorn-container  | Headers (<class 'starlette.datastructures.Headers'>): Headers({'host': 'api.green-coding.io', 'x-real-ip': '172.18.0.1', 'x-forwarded-for': '2003:fb:7f1a:f300:995d:69d6:9dec:a908, 172.18.0.1', 'x-forwarded-proto': 'http', 'connection': 'close', 'content-length': '436382', 'accept-encoding': 'gzip, br', 'cf-ray': '8c3ee62afd3c4369-EWR', 'cf-visitor': '{"scheme":"https"}', 'user-agent': 'Python-urllib/3.8', 'content-type': 'application/json', 'cf-connecting-ip': '2003:fb:7f1a:f300:995d:69d6:9dec:a908', 'cdn-loop': 'cloudflare; loops=1', 'cf-ipcountry': 'DE'})
green-coding-gunicorn-container  | Body (<class 'bytes'>): b'[{"time": 1722538613000, "data": "eJzsvWtvK0eWLfhXDvypL8Z ...

@ribalba What further data do you need to inspect the problem?

Currently I have turned the API endpoint off again. We need to chat if I shall reactivate some stuff.

[ArneTR]

This is some of the data that would be typically submitted as params:

green-coding-gunicorn-container  | Coalition ID params (163528, 'com.apple.mail', 636603618.022589, 12, 472, 3686400, 114688, 208, 7)
green-coding-gunicorn-container  | Coalition ID params (163529, 'io.green-coding.hog', 145610960.9103986, 2, 232, 0, 167936, 21, 2)
green-coding-gunicorn-container  | Coalition ID params (163530, 'io.green-coding.hog', 482475022.27459997, 9, 532, 377241600, 151552, 89, 0)
green-coding-gunicorn-container  | Coalition ID params (163531, 'io.green-coding.hog', 784321786.9305, 15, 1074, 747474944, 0, 128, 1)
green-coding-gunicorn-container  | Coalition ID params (163532, 'io.green-coding.hog', 415211342.6969937, 5, 489, 254672896, 335872, 42, 0)
green-coding-gunicorn-container  | Coalition ID params (163533, 'com.macpaw.CleanMyMac4.HealthMonitor', 852294756.4662961, 16, 2217, 28672, 0, 33, 0)
green-coding-gunicorn-container  | Coalition ID params (163534, 'io.green-coding.hog', 130205868.1005108, 2, 189, 8192, 151552, 21, 0)
green-coding-gunicorn-container  | Coalition ID params (163535, 'DEAD_TASKS_COALITION', 10898545771.384802, 212, 1091, 0, 0, 2124, 52)

[ribalba]

Which version of the GMT is this running. I can't find the line on current main. Can you reproduce this?
It could be that the DB is returning a bigint [0] here as the DB definition is id SERIAL PRIMARY KEY [1] but the foreign key is coalition integer REFERENCES hog_coalitions(id) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL, [2]

@ArneTR Could you please check in the DB what the id column in hog_coalitions is. If it is bigint we need to change the foreign key to also be bigint

[0]

green-metrics-tool/api/main.py

Line 740 in 2ea8877

RETURNING id

[1] https://github.com/green-coding-solutions/green-metrics-tool/blob/2ea8877216bc63c3490f7b19c7113cd9844d6e4a/docker/structure.sql#L254C5-L254C27
[2]

green-metrics-tool/docker/structure.sql

Line 279 in 2ea8877

coalition integer REFERENCES hog_coalitions(id) ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL,