Rails 3.1/mysql2 错误:“MySQL 服务器已消失" [英] Rails 3.1 / mysql2 error : "MySQL server has gone away"

问题描述

我在将 Rails 2.3.14/ruby​​ 1.8.7 应用程序升级到 3.1.1/1.9.2 时遇到问题:我有一些

I'm experiencing trouble upgrading my rails 2.3.14 / ruby 1.8.7 app to 3.1.1/1.9.2 : I have some

(ActiveRecord::StatementInvalid) "Mysql2::Error: MySQL server has gone away"

偶尔会发生错误.重要的是要确切地说，我在 2.3.14 上的 'mysql' gem 和完全相同的 db 上从来没有遇到过这样的问题(所以错误不应该来自 mysql (v5.5.10)).

errors happening sporadically. It's important to precise that I never had such issues with the 'mysql' gem on 2.3.14 and the exactly same db (so the bug shouldn't come from mysql (v5.5.10)).

示例:

$ rails c production
Loading production environment (Rails 3.1.1)
ruby-1.9.2-p290 :001 > ActiveRecord::Base.connection.active?
 => false
ruby-1.9.2-p290 :002 > exit
$ rails c production
Loading production environment (Rails 3.1.1)
ruby-1.9.2-p290 :001 > ActiveRecord::Base.connection.active?
 => true 

这只发生在我的(远程)生产数据库中，我的本地开发数据库没有问题.我试图在我的 database.yml 中设置reconnect: true"，但它导致了

This happens only with my (remote) production database, no problem with my local development db. I've tried to set "reconnect: true" in my database.yml but it led to a

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

我试图用一个只加载 mysql2 和 activerecord 的小 rb 脚本来隔离问题，但我没有设法以这种方式重现错误(因此它可能链接到 rails 堆栈).

I've tried to isolate the problem with a little rb script only loading mysql2 and activerecord but I didn't manage to reproduce the bug that way (so it may be linked to the rails stack).

由于编码问题，我无法从 'mysql2' 返回到 'mysql' gem ( http://www.rorra.com.ar/2010/07/30/rails-3-mysql-and-utf-8/ ).结果，我不得不将我的生产回滚到我的 rails 2.3.14 应用程序，这让我非常难过......

I can't go back from the 'mysql2' to the 'mysql' gem because of encoding issues ( http://www.rorra.com.ar/2010/07/30/rails-3-mysql-and-utf-8/ ). As a consequence, I had to rollback my production to my rails 2.3.14 app, which saddens me very much...

你知道我可以做些什么来调试这个吗?我什至找不到重现错误的可靠方法...有没有人遇到过同样的错误?

Do you see what I can do to debug this ? I can't even find a sure way to reproduce the error... Have anyone met the same bug ?

我发现很少有人提到这个错误(例如:https://github.com/brianmario/mysql2/issues/213) 但不是解决方案.

I just found few people mentionning this bug (ex : https://github.com/brianmario/mysql2/issues/213) but not a solution.

感谢您的帮助.

推荐答案

好的，我想我解决了我的问题.我在发布问题时没有注意到，但错误似乎与超时有关:大约 20 秒后，activerecord 失去了连接.

Ok, I think I solved my problem. I didn't notice it when I posted my question, but it seemed that the error was timeout related : after about 20s, activerecord losts its connection.

$ rails runner "sleep 23; puts ActiveRecord::Base.connection.active?"
=> true
$ rails runner "sleep 25; puts ActiveRecord::Base.connection.active?"
=> false

所以我进一步挖掘，我意识到 mysql 和 mysql2 gems 没有以同样的方式处理 MySQL 'wait_timeout' 参数:mysql gem 没有设置它因此使用 MySQL 默认值 28800，而 mysql2 gem 将其设置为 2592000 如果未在 database.yml 中定义.但我的印象是值 2592000 超过了这个参数的最大值:2147483 ！这可能会导致我描述的意外行为......

So I dug further and I realized that mysql and mysql2 gems didn't deal with the MySQL 'wait_timeout' param the same way : mysql gem doesn't set it thus uses the MySQL default value 28800, whereas mysql2 gem sets it at 2592000 if not defined in the database.yml. But I have the impression that the value 2592000 is over the max value for this param : 2147483 ! Which could lead to the unexpected behavior I described...

我构建了一个显示错误的脚本测试:https://gist.github.com/1514154

I build a script test showing the bug : https://gist.github.com/1514154

如果我在加载 rails 控制台时出现明显的随机断开连接(参见我的问题)，我认为这是因为我的应用加载时间很长，而且我有时会在输入命令之前等待几秒钟.

And if I had some apparently random disconnect while loading rails console (cf my question), I think it's because of my app taking a long time to load and me sometimes waiting a few seconds before typing my command.

我无法解释为什么我们很少遇到这个问题.也许它特定于我的 conf(远程数据库，MySQL 版本?).我已经尝试使用另一个远程登台数据库:该错误没有重现...

I can't explain why we are so few to encounter this problem. Perhaps it's specific to my conf (remote database, MySQL version ?). I've tried with another remote staging database : the bug didn't reproduce...

作为结论，我将在我的 database.yml 中设置 wait_timeout: 2147483.也许拉请求导轨...

So as a conclusion, I will set wait_timeout: 2147483 in my database.yml. And maybe pull request rails...

这篇关于Rails 3.1/mysql2 错误:“MySQL 服务器已消失"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！