dbi plugin: Can't connect to MySQL server on 'localhost'

zdenekpizl commented 7 years ago

Version of collectd: 5.7.1
Operating system / distribution: CentOS-7 x86_64

Expected behavior

dbi plugin is able to load and connect correctly to (local) MySQL every time after restart.

Actual behavior

collectd[23873]: dbi plugin: cdbi_connect_database (mysql): dbi_conn_connect failed: 2003: Can't connect to MySQL server on 'localhost' (111) (status 2003)

Sometimes it could be seen the error above when restarted collectd service with mysql and dbi plugin. Despite that issue the MySQL server is running there for sure and listening at localhost interface too. No iptables or other firewall is set. MySQL server is not overloaded at the time.

Steps to reproduce

restart collectd service (systemctl restart collectd) several times
randomly, not every time it is restarted, the error will be issued to logs and/or console

Questions:

is collectd going to reload the failed plugin during next minutes again or is it giving up forever?
is there any possibility how to debug it in more depth?

Thank you, .zp.

rpv-tomsk commented 6 years ago

I saw the same issue with mysql plugin.

octo commented 6 years ago

If both, the dbi plugin and the mysql plugin show this behavior, it sounds like the common denominator is the mysql server. I'm assuming these local connections go over MySQL's unix socket, right? That makes getting a network dump hard :\

mgrobelin commented 5 years ago

Same for me using the mysql plugin with MariaDB 10.1 and collectd 5.7.2 on Ubuntu 18.04, when using unix domain sockets via Socket.

As a workaround I have to use TCP/IP by Host "127.0.0.1" (or systems FQDN) with a fitting Port.

Can I help out with further information?

  <Plugin mysql>
      <Database a>
        Socket "/var/run/mysql/mysqld-a.sock"
        User "xxx"
        Password "xxx"
        InnodbStats true
        MasterStats false
        SlaveStats true
      </Database>
      <Database b>
    Socket "/var/run/mysql/mysqld-b.sock"
        User "xxx"
        Password "xxx"
        InnodbStats true
        MasterStats false
        SlaveStats true
      </Database>
      <Database c>
        Socket "/var/run/mysql/mysqld-c.sock"
        User "xxx"
        Password "xxx"
        InnodbStats true
        MasterStats false
        SlaveStats true
      </Database>
  </Plugin>

mysql plugin: Failed to connect to database <none> at server localhost: Can't connect to local MySQL server through socket '/var/run/mysql/mysqld-a.sock' (2)
mysql plugin: Failed to connect to database <none> at server localhost: Can't connect to local MySQL server through socket '/var/run/mysql/mysqld-b.sock' (2)
mysql plugin: Failed to connect to database <none> at server localhost: Can't connect to local MySQL server through socket '/var/run/mysql/mysqld-c.sock' (2)

When TCP/IP is used instead, it works

  <Plugin mysql>
      <Database a>
        Host "127.0.0.1"
        Port "3307"
        User "xxx"
        Password "xxx"
        InnodbStats true
        MasterStats false
        SlaveStats true
      </Database>
      <Database b>
        Host "127.0.0.1"
        Password "3308"
        User "xxx"
        Password "xxx"
        InnodbStats true
        MasterStats false
        SlaveStats true
      </Database>
      <Database c>
        Host "127.0.0.1"
        Port "3309"
        User "xxx"
        Password "xxx"
        InnodbStats true
        MasterStats false
        SlaveStats true
      </Database>
  </Plugin>

mysql plugin: Successfully connected to database <none> at server 127.0.0.1 via TCP/IP with cipher <none> (server version: 5.5.5-10.1.38-MariaDB-1~bionic, protocol version: 10)
mysql plugin: Successfully connected to database <none> at server 127.0.0.1 via TCP/IP with cipher <none> (server version: 5.5.5-10.1.38-MariaDB-1~bionic, protocol version: 10)
mysql plugin: Successfully connected to database <none> at server 127.0.0.1 via TCP/IP with cipher <none> (server version: 5.5.5-10.1.38-MariaDB-1~bionic, protocol version: 10)

Sidenotes:

it would be nice to see the used Port in the fail/success line when TCP/IP is used
with above configuration I am monitoring three MariaDB servers, so for my purpose Database a actually should be seen (and named?) as Instance a

rpv-tomsk commented 5 years ago

I think there is wrong libmysql use, existing code does not respect multithreading carefully.

Further reading:

https://dev.mysql.com/doc/refman/8.0/en/c-api-threaded-clients.html https://stackoverflow.com/questions/26936481/multithreaded-programming-with-libmysql

rpv-tomsk commented 5 years ago

More precisely, I think there can be a race in mysql_init() -> mysql_library_init() call:

https://dev.mysql.com/doc/refman/8.0/en/mysql-library-init.html :

In a nonmultithreaded environment, the call to mysql_library_init() may be omitted, because mysql_init() will invoke it automatically as necessary. However, mysql_library_init() is not thread-safe in a multithreaded environment, and thus neither is mysql_init(), which calls mysql_library_init(). You must either call mysql_library_init() prior to spawning any threads, or else use a mutex to protect the call, whether you invoke mysql_library_init() or indirectly through mysql_init(). Do this prior to any other client library call.

I saw the same issue with mysql plugin.

In my case, I have server with multiple (4) MySQL instances running. I use Collectd to gather metrics from all of them. On Collectd restart I often see something like this:

mysql plugin: Successfully connected to  ... (server I)
mysql plugin: Successfully connected to  ... (server II)
mysql plugin: Successfully connected to  ... (server III)
mysql plugin: Failed to connect to database <none> at server localhost: Can't connect to MySQL server on 'localhost' (101)
read-function of plugin `mysql-server4' failed. Will suspend it for 20.000 seconds.
mysql plugin: Successfully connected to database <none> at server Localhost via UNIX socket with cipher <none> (server ver

There is no more problems, only this small delay on Collectd restart.

collectd / collectd