MySQL – Connection Blocked After Spamming Queries, Duration Explained

access-controlblockingMySQL

I am creating a web application that depends on querying a legacy DB without an API.

It's MySql.

Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app fired a bunch of (Probably a few hundred) get requests towards the database, and now i can't connect to it anymore, it simply times out.

I horrified tried to log into to it via the browser and PHPmyAdmin, and to my relief nothing was wrong, but in Status->Processes i can see this process:

Kill    25289014    myusername  localhost   None    Query   0   ---

I reckon the DB killed all incoming connections from my user when i began to spam it from my application.

Do any of you guys know if the "block" will be lifted after a certain amount of hours. It's about 12 hours ago now.

Or if i have to contact the administrator to lift the block, or kill the kill process? My user doesn't seem to have the privilege to kill the process as there is no buttons to do so.

Thanks in Advance.

Best Answer

SUGGESTION

If you have the RELOAD privilege, you can run this

mysql> FLUSH HOSTS;

According to MySQL Documentation on running FLUSH HOSTS:

Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

You should run this command

SELECT host,Reload_priv FROM mysql.user
WHERE user = 'mysqluser';

If nothing appears, then you do not have the RELOAD privilege. Consequently, you have no choice but to contact the system administrators to run FLUSH HOSTS; for you.

ALTERNATIVE SUGGESTION

Ask the system administrators to configure max_connect_errors in my.cnf

[mysqld]
max_connect_errors = 18446744073709551615;

To avoid restarting mysql, ask the system administrators to run this

mysql> set global max_connect_errors = 18446744073709551615;

Give it a Try !!!

Related Solutions

Mysql – How long is “too long” for MySQL Connections to sleep

mysqld will timeout database connections based on two server options:

Both are 28,800 seconds (8 hours) by default.

You can set these options in /etc/my.cnf

If your connections are persistent (opened via mysql_pconnect) you could lower these numbers to something reasonable like 600 (10 minutes) or even 60 (1 minute). Or, if your app works just fine, you can leave the default. This is up to you.

You must set these as follows in my.cnf (takes effect after mysqld is restarted):

[mysqld]
interactive_timeout=180
wait_timeout=180

If you do not want to restart mysql, then run these two commands:

SET GLOBAL interactive_timeout = 180;
SET GLOBAL wait_timeout = 180;

This will not close the connections already open. This will cause new connections to close in 180 seconds.

MySQL – Connection blocked because of many connection errors – How to find root cause

The advice you've seen about max_connections is a distraction from the issue you're seeing, because it has nothing to do with connection errors.

You can log the failed connections to the mysql error log by issuing this:

SET GLOBAL log_warnings = 2;

You can also make this a permanent setting in my.cnf so that it persists after a restart.

http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_log-warnings