Cannot connect to MYSQL database from agent when collecting stats

[scryan]

I am trying to get the mysql stats plugin to work with my 5.0 installation. I went into the HQ server configuration screen and updated the defaults to add in the root password. When I try to save the configuration I get an error message.

The configuration has not been set for this resource due to : Invalid configuration: Error retrieving value: Service mysqlstats:query=show /*!50002 global */ status,key=variable_name,value=value:Slow_queries not found: Could not create connection to database server. Attempted reconnect 3 times. Giving up.

When I set the agent logging to debug I get the same message but no further details. I have tried to connect with the command locally on the same box as the agent and it seems to work. Is there anything else I can do to debug or is there more information on setup configuaration.

I have the server on the same machine and it is connecting fine. I tried to enter the IP address instead of localhost but it just reset it to localhost. I did not change any of the defaults.

I am having the same issues on 4 other machines.

Thanks

[excowboy]

Hi,

has your account the correct permissions ? Log in with the same hostname, username and password you have configured in Hyperic and run:

show status where Variable_Name='Slow_queries';

Do you get an error message or a result ?

Cheers,
Mirko

[scryan]

The query runs fine and I get results

ysql> show status where Variable_Name='Slow_queries';
+

[excowboy]

Hi,

try to invoke the the plugin standalone on the commandline.
At first, try to autodiscover the MySQL instance and write the settings to a properties file:

java -jar hyperic-hq-agent-4.2.0/bundles/agent-4.2.0-1260/pdk/lib/hq-product.jar -Dplugins.include=mysql_stats -m discover -a properties

Now run the metric collection based on some given parameters and the settings in the properties file:
java -jar hyperic-hq-agent-4.2.0/bundles/agent-4.2.0-1260/pdk/lib/hq-product.jar -Dplugins.include=mysql_stats -m metrics -DjdbcUser=root -DjdbcPassword=changeme plugin-properties/mysql-stats-5.0.x/yourhost_MySQL_Stats_5.0.x.properties

What is the output ?

Mirko

[scryan]

From the first command I received the errors below and when I try to
telnet om5vm.hostname.local 3306 I get a connection refused and it
looks like it is trying to go to 127.0.0.1 which must be blocked by
the firewall or not open as a socket port since it is internal. The
second command gave me
errors as printed below the first output. I will have to research to
see how to open up the internal socket port 127.0.0.1 . The host is
reachable by its ip address but not internally to the box. Any idea
how to open that up either by mysql configuration or firewall and
such. The operating system is
Centos 5.3

FIRST COMMAND OUTPUT **************************************

[hyperic@om5vm ~]$ java -jar agent-4.2.0/bundles/agent-4.2.0-1260/pdk/
lib/hq-product.jar -Dplugins.include=mysql_stats -m discover -a
properties
1 servers detected

Server: om5vm.hostname.local MySQL Stats 5.0.x [/usr]
AIID...../usr
config...
product..{jdbcUser=root, process.query=State.Name.eq=mysqld,
jdbcUrl=jdbc:mysql://localhost:3306}
metric...{}
control..null
cprops...{version=5.0.x}
generating [plugin-properties/mysql-stats-5.0.x/
om5vm.hostname.local_MySQL_Stats_5.0.x.properties]
ERROR [main] [PluginDiscoverer] MySQL Stats 5.0.x Linux--
>org.hyperic.hq.product.PluginException:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet sent successfully to the server was 0 milliseconds
ago. The driver has not received any packets from the server.
org.hyperic.hq.product.PluginException:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet sent successfully to the server was 0 milliseconds
ago. The driver has not received any packets from the server.
at
org
.hyperic
.hq
.plugin
.mysql_stats
.MySqlServerDetector.discoverServices(MySqlServerDetector.java:125)
at
org
.hyperic
.hq.product.ServerDetector.discoverResources(ServerDetector.java:221)
at
org
.hyperic
.hq.product.util.PluginDiscoverer.runtimeScan(PluginDiscoverer.java:604)
at
org
.hyperic.hq.product.util.PluginDiscoverer.start(PluginDiscoverer.java:
321)
at
org
.hyperic.hq.product.util.PluginDumper.testDiscovery(PluginDumper.java:
1149)
at org.hyperic.hq.product.util.PluginDumper.invoke(PluginDumper.java:
454)
at org.hyperic.hq.product.util.PluginMain.main(PluginMain.java:311)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet sent successfully to the server was 0 milliseconds
ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun
.reflect
.NativeConstructorAccessorImpl
.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun
.reflect
.DelegatingConstructorAccessorImpl
.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:
1074)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2214)
at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:781)
at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun
.reflect
.NativeConstructorAccessorImpl
.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun
.reflect
.DelegatingConstructorAccessorImpl
.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:352)
at
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:
284)
at
org
.hyperic
.hq
.plugin
.mysql_stats
.MySqlServerDetector.getConnection(MySqlServerDetector.java:192)
at
org
.hyperic
.hq
.plugin
.mysql_stats
.MySqlServerDetector.discoverServices(MySqlServerDetector.java:120)
... 6 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
Communications link failure

The last packet sent successfully to the server was 0 milliseconds
ago. The driver has not received any packets from the server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
at
sun
.reflect
.NativeConstructorAccessorImpl
.newInstance(NativeConstructorAccessorImpl.java:39)
at
sun
.reflect
.DelegatingConstructorAccessorImpl
.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at
com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:
1074)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:343)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137)
... 17 more
Caused by: java.net.ConnectException: Connection refused
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)
at java.net.Socket.connect(Socket.java:519)
at java.net.Socket.connect(Socket.java:469)
at java.net.Socket.<init>(Socket.java:366)
at java.net.Socket.<init>(Socket.java:209)
at
com
.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:
253)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:292)
... 18 more

SECOND COMMAND OUTPUT

[hyperic@om5vm ~]$ java -jar agent-4.2.0/bundles/agent-4.2.0-1260/pdk/
lib/hq-product.jar -Dplugins.include=mysql_stats -m metrics -
DjdbcUser=root -DjdbcPassword=password plugin-properties/mysql-
stats-5.0.x/om5vm.hostname.local_MySQL_Stats_5.0.x.properties
Unknown method: metrics
[-p plugin] [-t type] [-m method] [-a action] [-D key=value]


Scott Ryan

[excowboy]

Hi Ryan,

the first command should give you a different error message once you have managed to open up the port.
Regarding the second command. There is a typo. Please replace
-m metrics
with
-m metric

Sorry, my bad. Of course you also have to change jdbcUser and jdbcPassword values accordingly to your environment.

Have a look at you mysql configuration in /etc/my.cnf and check for the parameter bind-address.
CentOS comes with a builtin firewall, but it should allow you access to every local service.
You could also try to run
netstat -an | grep 3306 | grep LISTEN

This is the output on my box, which indicates that the listener is up:

mpluhar@mothra:/etc$ netstat -an | grep 3306 | grep LISTEN
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN


Mirko

[scryan]

YEA,
You led me down the correct path. The bind-address was set to the ip
so we could service outside clients. I had to set the bind-address to
0.0.0.0 so it would service both internal locahost(127.0.0.1) clients
which the hyperic agents needs and the external ips which all of our
clients need. There is no way to include both addresses so we use
0.0.0.0 and use the firewall and user permissions to control access.
Now all is working and I just need to do some follow up to make sure
this is the correct security solution.

Thanks so much for you help.


Scott Ryan

[scryan]

So when i run the statics metric command I get a lot of postive
feedback but I do get one error

MySQL Stats 5.0.x Process Start Time:
sigar:Type=ProcTime,Arg=State.Name.eq
%3Dmysqld:StartTime:jdbcUrl=jdbc%3Amysql%3A//localhost
%3A3306,jdbcUser=root,jdbcPassword=mysql
=>3/25/10 11:54:53 AM<=
MySQL Stats 5.0.x Process Open File Descriptors:
sigar:Type=ProcFd,Arg=State.Name.eq%3Dmysqld:Total:jdbcUrl=jdbc
%3Amysql%3A//localhost%3A3306,jdbcUser=root,jdbcPassword=mysql
getValue failed for metric: MySQL Stats
5.0.x:sigar:Type=ProcFd,Arg=State.Name.eq%3Dmysqld:Total:jdbcUrl=jdbc
%3Amysql%3A//localhost%3A3306,jdbcUser=root,jdbcPassword=xxxxx
MetricNotFoundException: Permission denied: State.Name.eq=mysqld


What do I need to change or check to make sure that one runs?


Scott Ryan
President/CTO
Soaring Eagle L.L.C.
scott@theryansplace.com
(303) 263-3044

[excowboy]

The MySQL plugin gathers metrics by JDBC from the MySQL system tables and by SIGAR from the OS. Please check if you get any error messages for other SIGAR metrics.
The reason for getting an error for that metric could be a permission problem of the user you use to run the plugin on the commandline.

Cheers,
Mirko

[scryan]

Thanks for the answer and helping me to resolve this issue. The detailed process to resolve the issue is much appreciated. I appreciate the ongoing dialog to resolve the real issue.