Getting an error while elasticsearch variable in my mysql query

Grafana
1

I have an elasticsearch variable which I have included in MySQL query. But the MySQL query is failing in case the variable is empty. I am getting the following error

You have an error in your SQL syntax; check the manual that corresponds to your Mysql server version for the right syntax to use near ‘))’ at line 1

How can we fix this issue? Any suggestions should be helpful

2

please post what you see when you click Query Inspector?

And the query you are using

3

select count(name) from users where (name IN (‘Mathew’,‘Alysa’) AND name NOT IN ()) is what coming in Query inspector.

My actual query is

select count(name) from users where (name IN ($users) AND name NOT IN ($unused_users))

1 Like
4

Try a length check or null check specific to the sql language you use before using that variable like

(Isnull($unused_users) or len($unused_users) = 0)
 or name NOT IN ($unused_users))
5

@yosiasz Now I am getting the following in the query inspector.

NOT IN (Isnull() or len() = 0) or name NOT IN ()))

1 Like
6

yuck, then try this

if Isnull($unused_users) or len($unused_users) = 0
  begin
     select count(name) 
       from users 
     where (name IN ($users) )
  end
else
  begin
     select count(name) 
       from users 
     where (name IN ($users) AND name NOT IN ($unused_users))
  end

or whatever it is for your mysql sql query language.
your question is mysql and not grafana, so for better expertise post ina mysql forum.

7

@yosiasz I posted in grafana since the variable does not return a null or empty string when nothing matches. It just returns an empty which will cause an issue in MySQL or any data storage we are using.

1 Like
8

what does mean it just returns an empty? empty what?

9

@yosiasz It doesn’t return anything. If we check the query. ISNULL itself will give syntax error as nothing in that.

NOT IN (Isnull() or len() = 0) or name NOT IN ()))

10

So you need to have it return something when there is nothing cause grafana cant do anything when there is nothing :laughing: