MySQL Error: (2003, “Can’t connect to MySQL server on ‘2001:db8:81:2c::2’ (-9)”)

ipv6MySQLzenoss

I'm trying to set up Zenoss 4.2.0 on CentOS 6.3 to monitor a remote MySQL 5.5.25a server via IPv6. The firewall is open for the monitoring server and I can connect fine from the command line:

[root@zenoss ~]# mysql -u zenoss -p -h 2001:db8:81:2c::2
...
mysql> SELECT USER(),CURRENT_USER();
+-----------------------------------------+-----------------------------------------+
| USER()                                  | CURRENT_USER()                          |
+-----------------------------------------+-----------------------------------------+
| zenoss@2001:db8:16:bf:5054:ff:fec0:f7a5 | zenoss@2001:db8:16:bf:5054:ff:fec0:f7a5 |
+-----------------------------------------+-----------------------------------------+
1 row in set (0.09 sec)

Zenoss, however, generates an event "No performance data from plugin" whose details complain that it can't connect to the server:

MySQL Error: (2003, "Can't connect to MySQL server on '2001:db8:81:2c::2' (-9)")

As far as I know, -9 isn't even a valid errno. And of course it's impossible to Google a negative number.

enter image description here

I have checked zMySqlUsername and zMySqlPassword – more than once – and they have the correct values.

I have also tried entering the IPv6 address with brackets, but MySQL doesn't like that at all, either within Zenoss or on the command line.

What is the cause of this problem?

Best Answer

I finally gave up and went to debug this one myself.

Based on @SelivanovPavel's answer I turned up debugging on zencommand and waited, and sure enough, the ZenPack was failing.

2012-08-16 18:16:14,092 INFO zen.zencommand: Datasource MySQL/mysql command: /opt/zenoss/ZenPacks/ZenPacks.zenoss.MySqlMonitor-2.2.0-py2.7.egg/ZenPacks/zenoss/MySqlMonitor/libexec/check_mysql_stats.py -H 2001:db8:81:2c::2 -p 3306 -u zenoss -w 'password' -g
2012-08-16 18:16:14,100 DEBUG zen.zencommand: Running /opt/zenoss/ZenPacks/ZenPacks.zenoss.MySqlMonitor-2.2.0-py2.7.egg/ZenPacks/zenoss/MySqlMonitor/libexec/check_mysql_stats.py
2012-08-16 18:16:14,544 DEBUG zen.zencommand: Datasource: mysql Received exit code: 1 Output: 'MySQL Error: (2003, "Can\'t connect to MySQL server on \'2001:db8:81:2c::2\' (-9)")\n'
2012-08-16 18:16:14,545 DEBUG zen.zencommand: Process MySQL/mysql stopped (1), 0.43 seconds elapsed

So I dug into the ZenPack and found out it was importing (an apparently old version of) pymysql from /opt/zenoss/lib/python.

On testing from the python command line I discovered where the exception was being thrown from:

>>> sys.path.insert(0, "/opt/zenoss/lib/python");
>>> import pymysql
>>> pymysql.install_as_MySQLdb()
>>> import MySQLdb
>>> self.conn = MySQLdb.connect(host="2001:db8:81:2c::2", port=3306, db='', user='zenoss', passwd='password')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/opt/zenoss/lib/python/pymysql/__init__.py", line 93, in Connect
    return Connection(*args, **kwargs)
  File "/opt/zenoss/lib/python/pymysql/connections.py", line 504, in __init__
    self._connect()
  File "/opt/zenoss/lib/python/pymysql/connections.py", line 673, in _connect
    raise OperationalError(2003, "Can't connect to MySQL server on %r (%s)" % (self.host, e.args[0]))
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '2001:db8:81:2c::2' (-9)")

And on inspecting connections.py in that general vicinity I discovered to my horror that it was attempting to open an AF_INET socket, and there was no code anywhere to open an AF_INET6 socket. Boom, instant fail.

The current version of pymysql also seems to contain this deficiency; no IPv6 support whatsoever.

So the "answer" is I'm going to have to fix pymysql. Not how I wanted to spend my afternoon.

This bit of nasty hackery gets things working (though you need Python 2.6). Open up /opt/zenoss/lib/python/pymysql/connections.py and search for AF_INET around line 660. Then make the following change:

                 if DEBUG: print 'connected using unix_socket'
             else:
-                sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
-                t = sock.gettimeout()
-                sock.settimeout(self.connect_timeout)
-                sock.connect((self.host, self.port))
-                sock.settimeout(t)
+                sock = socket.create_connection((self.host, self.port), self.connect_timeout)
                 self.host_info = "socket %s:%d" % (self.host, self.port)
                 if DEBUG: print 'connected using socket'

This has since been fixed in upstream pymysql and should be available in a future release.

Related Topic