How to find where a query came from

pascal.fragnoud · March 23, 2021, 10:49am

Hello folks,

I’m trying to figure out where a query comes from, but to no avail.
Using the general log to monitor what’s going on:

mysql -e "set global general_log = ON"
tail -f /var/lib/mysql/logfile.log

I get this :

210323  9:13:27     146 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
                    146 Query    SELECT 1
                    957 Query    SELECT 1
                    957 Query    SELECT zone_num FROM param_zone
210323  9:13:28     991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
                    957 Query    SELECT 1
                    957 Query    SELECT zone_num FROM param_zone
210323  9:13:29     991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
                    957 Query    SELECT 1
                    957 Query    SELECT zone_num FROM param_zone
210323  9:13:30     991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
                    957 Query    SELECT 1
                    957 Query    SELECT zone_num FROM param_zone
210323  9:13:31     991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
                    957 Query    SELECT 1
210323  9:13:32     957 Query    SELECT zone_num FROM param_zone
                    991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
210323  9:13:33     957 Query    SELECT 1
                    957 Query    SELECT zone_num FROM param_zone
                    991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
210323  9:13:34     957 Query    SELECT 1
                    991 Query    SELECT 1
                    991 Query    SELECT zone_num FROM param_zone
                    957 Query    SELECT zone_num FROM param_zone

So, there’s a “SELECT 1” every 10 seconds, then another one along with a “SELECT zone_num FROM param_zone” every second.
This happens with no client opened, and only with one of several basically identical projects. We’re using MariaDB.
I have NO IDEA where those come from.
I tried searching for those terms, but found nothing.

Might anyone have a few tips on how to go about hunting queries ?

zxcslo · March 23, 2021, 10:57am

Edit your database connection on the Ignition server web page and check the ‘Show advanced properties’ checkmark at the bottom.
In the ‘Connection Testing’ section, you’ll see…

pascal.fragnoud · March 23, 2021, 1:11pm

Thanks,

I figured the 10 seconds one might be some sort of ping (thought should it really be trying to validate a connection when we’re not even attempting to use it ? The “Test while idle?” field is disabled),
but i’m more concerned about the other query (SELECT zone_num FROM param_zone).
This one makes no sense to me !

MMaynard · March 23, 2021, 3:06pm

If a system wide find doesn’t find it, I would bet it is a tag change script or a sql query tag.
Export the tag database, then open it with a text editor like Notepad++ and search for the query string.

pascal.fragnoud · March 24, 2021, 8:05am

Assuming I did things right, it’s not there either.
Just to make sure I didn’t miss something: I exported my project’s tags, which gave me a json. I then tried to grep “SELECT” in that json, which yielded nothing.

MMaynard · March 24, 2021, 11:36am

Anything in client tags?

bkarabinchak.psi · March 24, 2021, 12:50pm

Do you have any dynamic query generating functions? Something like "SELECT % FROM %s" and perhaps its getting filled in with your two values? Query building functions can be helpful but can also make stuff like this hard to track down.

lrose · March 24, 2021, 12:50pm

Any Gateway scripts running?

pascal.fragnoud · March 29, 2021, 6:58am

That’s a very good idea, I didn’t think of that.
I’ll try and find out.

@MMaynard:
I’m afraid not…

@lrose:
Yes, but no query involved that looks anything like this.

edit:
Any way I could isolate things that get called every second or so ?

pascal.fragnoud · March 29, 2021, 7:13am

Another thing:
I have queries that are just like this one, but with WHERE clauses.
Any chance the clause might be removed somehow ?

I should add that pretty much none of the code is mine, I’m actually in the process of cleaning and fixing it…
It makes it hard to know what’s going on.

lrose · March 29, 2021, 11:34am

There is no way to isolate things based on their polling rate that I am aware of.

The fact that it happens with no client open and only on a particular project, indicates to me that you should be looking in a Gateway Event Script.

Gateway Event Scripts are considered a project resource and so would be isolated to a single project
They run on the gateway and so do not require any sessions or clients to be open
If these were the result of a Tag Change Script, or Query Tag Polling then it would be across all projects (unless the projects are on separate gateways).
Any script on a window would require a client to be running
Client Event Scripts require a client to be running

The fact that this is so cyclical would lead me to the Timer Events, particularly those with a 10,000ms delay/rate.

pascal.fragnoud · March 29, 2021, 1:01pm

There are only two gateway scripts, one that is running on a 5.000 ms timer, and another one on tag change.
Neither of those use queries.

MMaynard · March 29, 2021, 1:34pm

Any named queries?

pascal.fragnoud · March 30, 2021, 8:38am

Quite a few indeed, but none of them matches this query.
I have a couple that look like it, but with extra conditions or columns, things like that.
besides, I don’t think any of them is called on such a timer, they’re supposed to be called on user interaction…

edit:
I want to thank you guys for the help, it’s very nice of you.

bkarabinchak.psi · March 30, 2021, 12:13pm

Perhaps a dumb question but does any other application use this database?

I believe too MySQL can have events that run queries - have you checked this?

pascal.fragnoud · April 6, 2021, 11:53am

No, this db is only used for ignition.

CB123 · June 10, 2025, 5:35pm

I hate to pull up a fairly old thread, but Pascal did you ever figure out why the query you were seeing in the activity wasn't matching up exactly (but pretty close) to your known queries? I have the same concerns with a query I am seeing running very often, it looks fairly generic to me but it could be just what my normal queries look like running. IE it looks like it wants to pull all fields from the sqlth_1_data table, but my known queries (exported all tags and NQ to search them) only pull mostly one of the fields. I see it pulls DESC LIMIT 1, which I definitely don't have but maybe that's what a scaler return format really looks like in the backend. I am working on bringing my setup more inline with what I am learning is best practice (IE don't use query tags to start with, if that's the case not sure why they exist). Thanks for any knowledge you can dredge up from this long ago.

pturmel · June 10, 2025, 6:36pm

That the historian itself. The DESC LIMIT 1 is used to determine the last data point in a time range, typically for a chart.