Ruby on Rails 的“MySQL 服务器已经消失”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/100631/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"MySQL server has gone away" with Ruby on Rails
提问by Gwyn Morfey
After our Ruby on Rails application has run for a while, it starts throwing 500s with "MySQL server has gone away". Often this happens overnight. It's started doing this recently, with no obvious change in our server configuration.
在我们的 Ruby on Rails 应用程序运行一段时间后，它开始抛出 500 次“MySQL 服务器已经消失”。这通常会在一夜之间发生。它最近开始这样做，我们的服务器配置没有明显变化。
Mysql::Error: MySQL server has gone away: SELECT * FROM `widgets`
Restarting the mongrels (not the MySQL server) fixes it.
重新启动杂种（不是 MySQL 服务器）修复它。
How can we fix this?
我们怎样才能解决这个问题？
采纳答案by Laurie Young
This is probably caused by the persistent connections to MySQL going away (time out is likely if it's happening over night) and Ruby on Rails is failing to restore the connection, which it should be doing by default:
这可能是由于到 MySQL 的持久连接消失（如果它发生在一夜之间可能会超时）并且 Ruby on Rails 无法恢复连接，默认情况下它应该这样做：
In the file vendor/rails/actionpack/lib/action_controller/dispatcher.rb is the code:
在文件 vendor/rails/actionpack/lib/action_controller/dispatcher.rb 中是代码：
if defined?(ActiveRecord)
before_dispatch { ActiveRecord::Base.verify_active_connections! }
to_prepare(:activerecord_instantiate_observers) {ActiveRecord::Base.instantiate_observers }
end
The method verify_active_connections!performs several actions, one of which is to recreate any expired connections.
该方法verify_active_connections!执行多项操作，其中之一是重新创建任何过期的连接。
The most likely cause of this error is that this is because a monkey patchhas redefined the dispatcher to not call verify_active_connections!, or verify_active_connections!has been changed, etc.
此错误最可能的原因是，这是因为猴子补丁已将调度程序重新定义为不调用verify_active_connections!，或verify_active_connections!已更改等。
回答by mixonic
Ruby on Rails 2.3 has a reconnect option for your database connection:
Ruby on Rails 2.3 为您的数据库连接提供了重新连接选项：
production:
# Your settings
reconnect: true
See:
看：
Good luck!
祝你好运！
回答by Matt Connolly
Try ActiveRecord::Base.connection.verify!in Ruby on Rails 4. Verify pings the server and reconnects if it is not connected.
ActiveRecord::Base.connection.verify!在 Ruby on Rails 4 中尝试。验证 ping 服务器并在未连接时重新连接。
回答by Dave Cheney
As the other contributors to this thread have said, it is most likely that MySQL server has closed the connection to your Ruby on Rails application because of inactivity. The default timeout is 28800 seconds, or 8 hours.
正如该线程的其他贡献者所说，很可能 MySQL 服务器由于不活动而关闭了与 Ruby on Rails 应用程序的连接。默认超时为 28800 秒或 8 小时。
set-variable = wait_timeout=86400
Adding this line to your /etc/my.cnfwill raise the timeout to 24 hours
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout.
将此行添加到您的/etc/my.cnf将超时提高到 24 小时
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_wait_timeout。
Although the documentation doesn't indicate it, a value of 0 maydisable the timeout completely, but you would need to experiment as this is just speculation.
虽然文档没有说明，但值为 0可能会完全禁用超时，但您需要进行试验，因为这只是推测。
There are however three other situations that I know of that can generate that error. The first is the MySQL server being restarted. This will obviously drop all the connections, but as the MySQL client is passive, and this won't be noticed till you do the next query.
然而，我知道还有其他三种情况会产生该错误。第一个是正在重新启动的 MySQL 服务器。这显然会删除所有连接，但由于 MySQL 客户端是被动的，并且在您执行下一个查询之前不会注意到这一点。
The second condition is if someone kills your query from the MySQL command line, and this also drops the connection, because it could leave the client in an undefined state.
第二个条件是，如果有人从 MySQL 命令行终止了您的查询，这也会断开连接，因为这可能会使客户端处于未定义状态。
The last is if your MySQL server restarts itself due to a fatal internal error. That is, if you are doing a simple query against a table and instantly see 'MySQL has gone away', I'd take a close look at your server's logs to check for hardware error, or database corruption.
最后一个是如果您的 MySQL 服务器由于致命的内部错误而自行重启。也就是说，如果您正在对表进行简单的查询并立即看到“MySQL 已消失”，我会仔细查看您的服务器日志以检查硬件错误或数据库损坏。
回答by Isaac Betesh
I had this problem when sending really large statements to MySQL. MySQL limits the size of statements and will close the connection if you go over the limit.
我在向 MySQL 发送非常大的语句时遇到了这个问题。MySQL 限制语句的大小，如果超过限制，将关闭连接。
set global max_allowed_packet = 1048576; # 2^20 bytes (1 MB) was enough in my case
回答by Abdo
First, determine the max_connections in MySQL:
首先，确定MySQL中的max_connections：
show variables like "max_connections";
You need to make sure that the number of connections you're making in your Ruby on Rails application is less than the maximum allowed number of connections. Note that extra connections can be coming from your cronjobs, delayed_job processes (each would have the same pool size in your database.yml), etc.
您需要确保在 Ruby on Rails 应用程序中建立的连接数小于允许的最大连接数。请注意，额外的连接可能来自您的cron作业、delayed_job 进程（每个进程在您的database.yml.
Monitor the SQL connections as you go through your application, run processes, etc. by doing the following in MySQL:
通过在 MySQL 中执行以下操作，在您通过应用程序、运行进程等时监控 SQL 连接：
show status where variable_name = 'Threads_connected';
You might want to consider closing connections after a Threadfinishes execution as database connections do not get closed automatically (I think this is less of an issue with Ruby on Rails 4 applications Reaper):
您可能需要考虑在Thread完成执行后关闭连接，因为数据库连接不会自动关闭（我认为这对 Ruby on Rails 4 应用程序Reaper来说不是问题）：
Thread.new do
begin
# Thread work here
ensure
begin
if (ActiveRecord::Base.connection && ActiveRecord::Base.connection.active?)
ActiveRecord::Base.connection.close
end
rescue
end
end
end
回答by David Precious
The connection to the MySQL server is probably timing out.
与 MySQL 服务器的连接可能超时。
You should be able to increase the timeout in MySQL, but for a proper fix, have your code check that the database connection is still alive, and re-connect if it's not.
您应该能够增加 MySQL 中的超时时间，但要进行适当的修复，请让您的代码检查数据库连接是否仍然有效，如果不是，则重新连接。
回答by Z99
Do you monitor the number of open MySQL connections or threads? What is your mysql.ini settings for max_connections?
您是否监控打开的 MySQL 连接或线程的数量？max_connections 的 mysql.ini 设置是什么？
mysql> show status;
Look at Connections, Max_used_connections, Threads_connected, and Threads_created.
查看 Connections、Max_used_connections、Threads_connected 和 Threads_created。
You may need to increase the limits in your MySQL configuration, or perhaps rails is not closing the connection properly*.
您可能需要增加 MySQL 配置中的限制，或者 rails 可能没有正确关闭连接*。
Note: I've only used Ruby on Rails briefly...
注意：我只是简单地使用过 Ruby on Rails...
The MySQL documentation for server status is in http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html.
服务器状态的 MySQL 文档位于http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html。
回答by Graeme Irwin
Using reconnect: true in the database.yml will cause the database connection to be re-established AFTER the ActiveRecord::StatementInvalid error is raised (As Dave Cheney mentioned).
在 database.yml 中使用 reconnect: true 将导致在引发 ActiveRecord::StatementInvalid 错误后重新建立数据库连接（正如 Dave Cheney 提到的）。
Unfortunately adding a retry on the database operation seemed necessary to guard against the connection timeout:
不幸的是，在数据库操作上添加重试似乎有必要防止连接超时：
begin
do_some_active_record_operation
rescue ActiveRecord::StatementInvalid => e
Rails.logger.debug("Got statement invalid #{e.message} ... trying again")
# Second attempt, now that db connection is re-established
do_some_active_record_operation
end
回答by Ryan Allen
I had this problem in a Ruby on Rails 3 application, using the mysql2gem. I copied out the offending query and tried running it in MySQL directly, and I got the same error, "MySQL server has gone away.".
我在使用mysql2gem的 Ruby on Rails 3 应用程序中遇到了这个问题。我复制了有问题的查询并尝试直接在 MySQL 中运行它，但我得到了同样的错误，“MySQL 服务器已经消失。”。
The query in question was very, very large. A very large insert (+1 MB). The field I was trying to insert into was a TEXT column and their max size is 64 KB. Rather than throwing an errorm, the connection went away.
有问题的查询非常非常大。一个非常大的插入 (+1 MB)。我试图插入的字段是一个 TEXT 列，它们的最大大小为 64 KB。连接没有抛出错误，而是消失了。
I increased the size of the field and got the same thing, so I'm still not sure what the exact issue was. The point is that it was in the database due to some strange query. Anyway!
我增加了字段的大小并得到了相同的结果，所以我仍然不确定确切的问题是什么。关键是由于一些奇怪的查询，它在数据库中。反正！
