Knowledge Base/Engine Yard Cloud Documentation/Troubleshooting

Troubleshoot Your Database

Engine Yard
posted this on February 16, 2012 10:02 AM

Updated: March 7th, 2013

This page describes some things to check when troubleshooting your database.

Topics on this page:

MySQL: Restart the database

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

To restart the MySQL database

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

  2. Type:

    sudo /etc/init.d/mysql restart 

    or

     sudo /etc/init.d/mysql stop 
    sudo /etc/init.d/mysql start

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

Symptom

After deploying, you see 500 errors and the deployment log shows “Please install the mysql2 adapter” error.

Solution

See 500 errors after deploying / Gemfile missing database adapter.

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

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

  2. 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.

For more information, see http://dev.mysql.com/doc/refman/5.0/en/flush.html.

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

Symptom

The application log (in /data/app_name/current/log) shows an error like this:
Mysql::Error: MySQL server has gone away:.

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.

This error can also be seen if the MySQL server is not running.

For more information, see http://dev.mysql.com/doc/refman/5.0/en/gone-away.html.

Solution

Verify MySQL is running on the appropriate database instance. Type: ps -ef |grep '[m]ysqld'

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

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

  2. Type:

    sudo /etc/init.d/postgresql-x.x restart

    where  x.x is the version of PostgreSQL, for example

     sudo /etc/init.d/postgresql–9.0 restart

    or

     sudo /etc/init.d/postgresql–9.1 restart 

More information

For more information about...See...
SSHing into an instance Connect to your instance via SSH.
Finding the password for your database                                                         Find key information about your database.                                                               

If you have feedback or questions about this page, add a comment below. If you need help, submit a ticket with Engine Yard Support.