Ruby On Rails连接MySQL 5.7自动断开问题排查求助

First off, looking at your MySQL config: wait_timeout=28800 and interactive_timeout=28800 are both set to 8 hours—MySQL won’t drop idle connections after just a few seconds on its own. So the problem is definitely on the Ruby on Rails side of things, not your MySQL server. Let’s break down the common causes and fixes:

1. Stale connections in the Rails connection pool #

Rails uses a connection pool to reuse database connections for efficiency, but it doesn’t automatically check if a pooled connection is still valid. If a connection gets dropped (say, by a firewall blip, network glitch, or even an intermediate device timing out), Rails will try to use that dead connection when the app wakes up, triggering the error.

Fix: Enable automatic reconnection in your database config #

Add reconnect: true to your database.yml for the affected environment. This tells the mysql2 driver to automatically detect dead connections and re-establish them when needed. Here’s an example:

production:
  adapter: mysql2
  database: your_app_db
  username: db_user
  password: db_pass
  host: your_mysql_server
  reconnect: true

2. Network device timeouts (firewall, load balancer) #

Even if MySQL is set to 8 hours, if there’s a firewall, load balancer, or other network gear between your Rails server and MySQL, that device might have a much shorter idle timeout (like a few seconds or minutes) that’s killing the TCP connection.

How to verify: #

• Use telnet your_mysql_host 3306 from your Rails server, leave it idle for the length of time the error takes to appear, and see if the connection drops.
• Check the config of your network devices for idle connection timeout settings.

Fixes: #

• Adjust the network device’s timeout to match or exceed MySQL’s wait_timeout (28800 seconds).
• Keep the reconnect: true setting in Rails to auto-recover from these drops.
• For critical apps, you can add a periodic heartbeat (e.g., a background job that runs ActiveRecord::Base.connection.execute('SELECT 1') every few minutes) to keep connections alive.

3. Connection leaks in your Rails code (less likely here) #

If your app isn’t properly releasing database connections (especially in multi-threaded code like background jobs), you might end up with stale connections sitting in the pool. This is less likely if you only get the error after idle time and retrying works, but it’s worth checking.

Fix: #

• Audit code where you manually grab connections (e.g., ActiveRecord::Base.connection)—make sure you call ActiveRecord::Base.clear_active_connections! or use blocks like ActiveRecord::Base.connection_pool.with_connection { ... } to ensure connections are returned to the pool.
• For background processors like Sidekiq, ensure you’re using connection middleware that manages connections correctly.

Final Takeaway #

Based on your scenario (error after a few seconds of idle, retry works), the root cause is almost certainly stale connections in Rails’ connection pool that aren’t being automatically reconnected. Adding reconnect: true to your database.yml should resolve this immediately.

内容的提问来源于stack exchange，提问作者Noam B.