How to find where a query came from

Ignition
1

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 ?

2

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…

3

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 !

4

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.

1 Like
5

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.

6

Anything in client tags?

7

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.

8

Any Gateway scripts running?

9

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 ?

10

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.

11

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.

  1. Gateway Event Scripts are considered a project resource and so would be isolated to a single project
  2. They run on the gateway and so do not require any sessions or clients to be open
  3. 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).
  4. Any script on a window would require a client to be running
  5. 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.

2 Likes
12

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.

13

Any named queries?

14

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.

15

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?

1 Like
16

No, this db is only used for ignition.