MySQL: 500 errors and "Please install the mysql2 adapter" error

Symptom

Solution

MySQL: Host '...' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Symptom

This error Host '...' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts' appears, for example, in the application log, /data/app_name/current/log.

This is a security feature of MySQL to prevent unauthorized users from gaining access to your database instance. After 10 consecutive failed authentications from a specific host that host is barred from further attempts to log in until the administrator flushes the hosts or the database instance is restarted. Frequent occurrences of this error may indicate an attack or possibly an otherwise undetectable network issue.

Solution

Run the FLUSH HOSTS command, either through the mysql client or through mysqladmin.

To run the FLUSH HOSTS command

Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment).

Type:

mysql -u root -p -e “FLUSH HOSTS;”

or

mysqladmin -u root -p flush-hosts

The password is the same for the deploy user and the root user on the MySQL database.

Mysql::Error: MySQL server has gone away [query that was trying to run]

Symptom

Rails relies upon connection pooling to eliminate the time overhead of spawning a database connection when a request comes in; the default connection pool size is 5. MySQL operates with a default wait_timeout setting of 28,800 seconds (8 hours). If a connection in the pool is inactive for more than 8 hours MySQL closes it to avoid the memory overhead of unused connections. The next attempt by Rails to use this connection results in the error that the connection has gone away.

Solution

Rails 2.3 and higher offers the ability to specify reconnect: true in the database.yml. Using this functionality results in rails automatically reconnecting to the database if the connection has been closed.

Note:reconnect: true is now added to database.yml by default.

PostgreSQL: Restart the database

If you change database configuration, you might need to restart the database.

To restart the PostgreSQL database

Via SSH, connect to the application and database instance (for single server environment) or the master database instance (for a clustered environment).