mysql max connections errors

[bwellsnc]

I have the proxy working now, but I have a new issue. I created a simple test proxy to an internal mysql server I have. It worked fine for the first couple of connections, then I received this error:

'hostname' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'

By default mysql is set to 10 max connection errors. In order to compensate, I increased the max_connection_errors variable in mysql to 5000. I really didn't want to make many changes on the MySQL side. Below is what I have in nginx:

check interval=3000 rise=2 fall=5 timeout=1000 type=mysql;

This is the default with the type of mysql added. I would like to know how I can fix this in nginx vs making changes in mysql configuration. If it can't be fixed in nginx, then I will just stick to the changes in mysql for now. Thanks!

[yaoweibin]

I think you want to limit the connection connect to the backend server. Is that right?

Maybe you can use the develop branch:

https://github.com/yaoweibin/nginx_tcp_proxy_module/tree/develop

And configure like this:

upstream backends { server 127.0.0.1:3306 max_busy=100; server 127.0.0.1:3307 max_busy=100; check interval=3000 rise=2 fall=5 timeout=1000 type=mysql; }

If the connections number with one backend server are greater than 100, It will try other server. If all the server are busy. It will close the socket.

On 2012-3-15 19:45, bwellsnc wrote:

I have the proxy working now, but I have a new issue. I created a simple test proxy to an internal mysql server I have. It worked fine for the first couple of connections, then I received this error:

'hostname' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'

By default mysql is set to 10 max connection errors. In order to compensate, I increased the max_connection_errors variable in mysql to 5000. I really didn't want to make many changes on the MySQL side. Below is what I have in nginx:

check interval=3000 rise=2 fall=5 timeout=1000 type=mysql;

This is the default with the type of mysql added. I would like to know how I can fix this in nginx vs making changes in mysql configuration. If it can't be fixed in nginx, then I will just stick to the changes in mysql for now. Thanks!

---

Reply to this email directly or view it on GitHub: https://github.com/yaoweibin/nginx_tcp_proxy_module/issues/29

[tak-amboss]

I encountered the same problem. After running well for hours suddenly I got:

 {Doctrine_Connection_Exception} PDO Connection Error: SQLSTATE[HY000] [1129] Host '10.0.0.2' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Here is my nginx conf:

tcp {
  upstream databases {
      server 10.0.0.2:3306;
      server 10.0.0.3:3306;
      server 10.0.0.1:3306 backup;
      check interval=3000 rise=2 fall=5 timeout=1000 type=mysql;
  }
  server {
      listen 3306;
      proxy_pass databases;
  }
}

Limiting with max_busy wouldn't help, because it isn't a “Too many connections” error. I suspect the tcp connections aren't closed well. Are there any options I could set to solve that?

Thanks in advance!