New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


MySQL Query does not return correct values

Votes:

0

Your Vote:

Up

Down

Hi,

we try to gather the number of current connections of an MySQL database via the MySQL v2 sensor.

Unfortunatelly the received results are not matching with the expected results and therefore we assume the query do not work proper.

We have tried two different methods:

1. select count(*) from information_schema.PROCESSLIST

Under MySQL it returns the total number of connection in a single line and column. When we use the sensor to process the data it returns always 5.

2. select * from information_schema.PROCESSLIST where information_schema.PROCESSLIST.host not like "server%"

Under MySQL it returns the correct number of line (without the local server%) When we use the sensor to evaluate the rows of the query it returns always 1

What will be the database schema we need to trigger as a target? Is it information_schema or the one we created?

For the moment i did a workaround and process the query via comandline to an outfile and count the raws. Thats fine but may it can be easier to achieve with someones help.

Any ideas?

Thanks, Sascha

mysql mysql-v2-sensor query

Created on Jan 27, 2017 2:34:08 PM by  technikffh (0) 1



14 Replies

Votes:

0

Your Vote:

Up

Down

Dear Sascha

Please manually test the sensor and check the result of your query. The executable is "SQLv2.exe", usually located in C:\Program Files (x86)\PRTG Network Monitor\Sensor System. Please start the file, configure the options according to your MySQL server and perform the query. Are you getting the expected output table?

Created on Jan 27, 2017 3:39:56 PM by  Arne Seifert [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Dear Arne,

thanks a lot for your reply.

It is very helpful to have the option you have mentioned.

The system returns with a access denied error due to missing permissions as follows:

Authentication to host x.x.x.x for user " using method 'mysql_native_password' failed with the message: Access denied for user "@'PRTGASSET' (using password: NO)

Unfortunately I can not find a switch to provide username and password and anonymous user on MySQL is enabled.

Additional information are much appreciated.

Thanks, Sascha

Created on Jan 27, 2017 4:16:13 PM by  technikffh (0) 1



Votes:

0

Your Vote:

Up

Down

Dear Sascha

Please check if you entered the credentials into the according fields to connect to the MySQL database, including the password. The SQLv2.exe form should have fields to provide the credentials.

Created on Jan 27, 2017 4:48:04 PM by  Arne Seifert [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Thanks Arne,

i found the option to enter username and password - seems to was blind.

However, i have tested several queries and they are returning the wrong values compared to the queries within mysql itself.

This seems to be related only to queries affecting the information_schema database itself. For example the query "select * from information_schema.processlist" returns the following:

IDUSERHOSTDBCOMMANDTIMESTATEINFO
8906rootPRTGASSET:55742information_schemaQuery0select * from information_schema.processlist

Looks like the result gets filterd for connections comming from the PRTGSERVER itself instead of displaying all hosts.

Thanks, Sascha

Created on Jan 30, 2017 7:50:29 AM by  technikffh (0) 1

Last change on Jan 30, 2017 7:55:36 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

You could try the following:

SET @servername = @prtg;
SHOW FULL PROCESSLIST WHERE Host NOT LIKE %@servername%;

using %server as input parameter. Does that work?

Created on Jan 30, 2017 2:08:18 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Unfortunately the query is not working at all, but when I change the query to SHOW FULL PROCESSLIST it still just Returns 1 row with the PRTG - Servername only. The results looks to be limited to the query initiator source....

It is strange, isnt it?

Created on Jan 30, 2017 2:45:09 PM by  technikffh (0) 1



Votes:

0

Your Vote:

Up

Down

When I'm opening two SQL clients (i.e. two connections), I do get indeed two lines...have you tried that yet?

Created on Jan 30, 2017 2:52:02 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

We have about 45 Clients that use the MySQL permanent. Usually it is between 15 and 40 concurrent connections. Therefore, when I querythe processlist from MySQL Workbench it shows all connections, but only not from SQLv2. I don´t understand why SQLv2 just considers the PRTG server in the query...

Created on Jan 31, 2017 4:30:31 PM by  technikffh (0) 1



Votes:

0

Your Vote:

Up

Down

Could you try SHOW FULL PROCESSLIST; as query? Does that return a number other than 1?

Created on Feb 1, 2017 9:16:15 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

I am afraid, I already tried this but sqlv2.exe still returns just one row...

...I have tried to start the application from another device but it cant run without prtg installation.

BTW, the version of sqlv2.exe is 0.1.0.27652. Any update available?

Created on Feb 1, 2017 10:01:00 AM by  technikffh (0) 1



Votes:

0

Your Vote:

Up

Down

Nope, that's the latest. But the problem seems to be related to the privileges of the user you're using. Make sure that the same has the PROCESS privilege globally. Can you check that? If you need to set it, please remember to flush the privileges afterwards in order for them to be actually updated.

Created on Feb 1, 2017 10:16:18 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

Perfect, it was the right direction... somehow this user is not able to process the query properly, even with right permissions. I changed to a different user and all looks fine from the sqlv2.exe now. I will give it a shot in PRTG now and return in case of ongoing trouble...

...many thanks so far!

Created on Feb 1, 2017 10:56:23 AM by  technikffh (0) 1



Votes:

0

Your Vote:

Up

Down

Nice :) Glad to be of help!

Created on Feb 1, 2017 11:05:29 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

something similar happened to me, the solution was to eliminate the character of the password since it is a reserved symbol

Created on Nov 6, 2018 2:25:18 PM by  ftorres (0) 1



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.