search

OpenBMP / obmp-psql

New OpenBMP PostgreSQL consumer and database
BSD 3-Clause "New" or "Revised" License
6 stars 9 forks source link

Has Anyone Ran Into db query error: pq: syntax error at or near ")" Errors? #11

Open kbarton1212 opened 1 year ago

kbarton1212 commented 1 year ago

Hi!

I do not consider myself knowledgeable when it comes to DB's, and a google search for this exact error did not yield anything useful.

All of my Grafana dashboards show the same error which is: db query error: pq: syntax error at or near ")"

Looking at the logs from within obmp-sql, I see more detail:

023-04-07 19:17:08 2023-04-07 23:17:08.869 UTC [176] ERROR:  syntax error at or near ")" at character 90
2023-04-07 19:17:08 2023-04-07 23:17:08.869 UTC [176] STATEMENT:  select peername as __text, peer_hash_id as __value from v_peers where router_hash_id in ()
2023-04-07 19:17:09 2023-04-07 23:17:09.122 UTC [176] ERROR:  syntax error at or near ")" at character 302
2023-04-07 19:17:09 2023-04-07 23:17:09.122 UTC [176] STATEMENT:  SELECT
2023-04-07 19:17:09         time_bucket('60.000s',interval_time) as time,
2023-04-07 19:17:09        sum(updates) as updates,sum(withdraws) as withdraws
2023-04-07 19:17:09     FROM stats_chg_bypeer s
2023-04-07 19:17:09       JOIN v_peers p ON (s.peer_hash_id = p.peer_hash_id)
2023-04-07 19:17:09     WHERE interval_time BETWEEN '2023-04-06T23:17:09.536Z' AND '2023-04-07T23:17:09.536Z'
2023-04-07 19:17:09       AND s.peer_hash_id in ()
2023-04-07 19:17:09     GROUP BY time
2023-04-07 19:17:09     ORDER BY time ASC
2023-04-07 19:17:09 
2023-04-07 19:17:09 
2023-04-07 19:17:09 2023-04-07 23:17:09.136 UTC [176] ERROR:  syntax error at or near ")" at character 302
2023-04-07 19:17:09 2023-04-07 23:17:09.136 UTC [176] STATEMENT:  SELECT
2023-04-07 19:17:09         time_bucket('120.000s',interval_time) as time,
2023-04-07 19:17:09         sum(withdraws),
2023-04-07 19:17:09         p.routername as metric
2023-04-07 19:17:09       FROM stats_chg_bypeer l
2023-04-07 19:17:09       JOIN v_peers p ON (p.peer_hash_id = l.peer_hash_id)
2023-04-07 19:17:09       WHERE interval_time BETWEEN '2023-04-06T23:17:09.572Z' AND '2023-04-07T23:17:09.572Z'
2023-04-07 19:17:09          AND l.peer_hash_id in ()
2023-04-07 19:17:09       group by routername,time
2023-04-07 19:17:09       order by time
2023-04-07 19:17:09 2023-04-07 23:17:09.181 UTC [176] ERROR:  syntax error at or near ")" at character 298
2023-04-07 19:17:09 2023-04-07 23:17:09.181 UTC [176] STATEMENT:  SELECT
2023-04-07 19:17:09         time_bucket('900.000s',interval_time) as time,
2023-04-07 19:17:09         sum(updates),
2023-04-07 19:17:09         p.PeerName as metric
2023-04-07 19:17:09       FROM stats_chg_bypeer l
2023-04-07 19:17:09       JOIN v_peers p ON (p.peer_hash_id = l.peer_hash_id)
2023-04-07 19:17:09       WHERE interval_time BETWEEN '2023-04-06T23:17:09.584Z' AND '2023-04-07T23:17:09.584Z'
2023-04-07 19:17:09          AND l.peer_hash_id in ()
2023-04-07 19:17:09       GROUP BY peername,time
2023-04-07 19:17:09       order by time asc
2023-04-07 19:17:09 
2023-04-07 19:17:09 2023-04-07 23:17:09.208 UTC [176] ERROR:  syntax error at or near ")" at character 251
2023-04-07 19:17:09 2023-04-07 23:17:09.208 UTC [176] STATEMENT:  SELECT as_name as "AS Name",d.* from (
2023-04-07 19:17:09       SELECT
2023-04-07 19:17:09           origin_as as asn,
2023-04-07 19:17:09           sum(updates) as "Updates"
2023-04-07 19:17:09         FROM stats_chg_byasn l
2023-04-07 19:17:09         WHERE interval_time BETWEEN '2023-04-06T23:17:09.603Z' AND '2023-04-07T23:17:09.603Z'
2023-04-07 19:17:09             AND peer_hash_id in ()
2023-04-07 19:17:09         GROUP BY asn
2023-04-07 19:17:09         order by "Updates" desc
2023-04-07 19:17:09       LIMIT 20
2023-04-07 19:17:09     ) d
2023-04-07 19:17:09     LEFT JOIN info_asn i ON (i.asn = d.asn)
2023-04-07 19:17:09 
2023-04-07 19:17:09 2023-04-07 23:17:09.228 UTC [521] ERROR:  syntax error at or near ")" at character 300
2023-04-07 19:17:09 2023-04-07 23:17:09.228 UTC [521] STATEMENT:  SELECT
2023-04-07 19:17:09         time_bucket('900.000s',interval_time) as time,
2023-04-07 19:17:09         sum(updates),
2023-04-07 19:17:09         p.routername as metric
2023-04-07 19:17:09       FROM stats_chg_bypeer l
2023-04-07 19:17:09       JOIN v_peers p ON (p.peer_hash_id = l.peer_hash_id)
2023-04-07 19:17:09       WHERE interval_time BETWEEN '2023-04-06T23:17:09.556Z' AND '2023-04-07T23:17:09.556Z'
2023-04-07 19:17:09          AND l.peer_hash_id in ()
2023-04-07 19:17:09       GROUP BY routername,time
2023-04-07 19:17:09       order by time asc
2023-04-07 19:17:09 
2023-04-07 19:17:09 2023-04-07 23:17:09.275 UTC [176] ERROR:  syntax error at or near ")" at character 240
2023-04-07 19:17:09 2023-04-07 23:17:09.275 UTC [176] STATEMENT:  SELECT as_name,d.* from (
2023-04-07 19:17:09       SELECT
2023-04-07 19:17:09           origin_as as asn,
2023-04-07 19:17:09           sum(withdraws) as "Withdraws"
2023-04-07 19:17:09         FROM stats_chg_byasn l
2023-04-07 19:17:09         WHERE interval_time BETWEEN '2023-04-06T23:17:09.61Z' AND '2023-04-07T23:17:09.61Z'
2023-04-07 19:17:09             AND peer_hash_id in ()
2023-04-07 19:17:09         GROUP BY asn
2023-04-07 19:17:09         order by "Withdraws" desc
2023-04-07 19:17:09       LIMIT 20
2023-04-07 19:17:09     ) d
2023-04-07 19:17:09     LEFT JOIN info_asn i ON (i.asn = d.asn)
2023-04-07 19:17:09 
2023-04-07 19:17:09 2023-04-07 23:17:09.297 UTC [522] ERROR:  syntax error at or near ")" at character 300
2023-04-07 19:17:09 2023-04-07 23:17:09.297 UTC [522] STATEMENT:  SELECT
2023-04-07 19:17:09         time_bucket('120.000s',interval_time) as time,
2023-04-07 19:17:09         sum(withdraws),
2023-04-07 19:17:09         p.PeerName as metric
2023-04-07 19:17:09       FROM stats_chg_bypeer l
2023-04-07 19:17:09       JOIN v_peers p ON (p.peer_hash_id = l.peer_hash_id)
2023-04-07 19:17:09       WHERE interval_time BETWEEN '2023-04-06T23:17:09.593Z' AND '2023-04-07T23:17:09.593Z'
2023-04-07 19:17:09          AND l.peer_hash_id in ()
2023-04-07 19:17:09       group by peername,time
2023-04-07 19:17:09       order by time

Has anyone ran into a similar issue before?

kbmanseau commented 1 year ago

Can you confirm if you have connected any BMP sources feeding your DB? I am not sure if this is the specific issue you are facing but I see that AND s.peer_hash_id in () shows an empty array/list. I think this would stem from the dashboard variables being empty.

You could look at the dashboard settings and see what queries grafana is using to build the variables