尝试更新 mySQL 中的 640k 行会出现“查询期间与 MySQL 服务器的连接丢失" [英] Trying to update 640k rows in mySQL gets "Lost connection to MySQL server during query"

问题描述

我正在尝试从我的 seed.rb 文件中执行这一行:

I'm trying to execute this line from my seeds.rb file:

ActiveRecord::Base.connection.execute("UPDATE bairros SET created_at = (SELECT NOW());")

我的名为bairros"的表有 643k 行，一旦它通过 rake db:seed 命令到达这个文件，它就会抛出这个错误

My table called "bairros" has 643k lines and as soon as it reaches this file on the rake db:seed command, it throws this error

    Mysql2::Error: Lost connection to MySQL server during query: UPDATE bairros SET created_at = (SELECT NOW());
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:286:in `query'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:286:in `block in execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activesupport-4.0.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:286:in `execute'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/connection_adapters/mysql2_adapter.rb:220:in `execute'
/home/ubuntu/projetos/AnuncieImoveis/releases/20131015210221/db/seeds.rb:48:in `<top (required)>'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activesupport-4.0.0/lib/active_support/dependencies.rb:222:in `load'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activesupport-4.0.0/lib/active_support/dependencies.rb:222:in `block in load'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activesupport-4.0.0/lib/active_support/dependencies.rb:213:in `load_dependency'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activesupport-4.0.0/lib/active_support/dependencies.rb:222:in `load'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/railties-4.0.0/lib/rails/engine.rb:540:in `load_seed'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/tasks/database_tasks.rb:153:in `load_seed'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/gems/activerecord-4.0.0/lib/active_record/railties/databases.rake:181:in `block (2 levels) in <top (required)>'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/bin/ruby_noexec_wrapper:14:in `eval'
/home/ubuntu/.rvm/gems/ruby-2.0.0-p195@thedoors/bin/ruby_noexec_wrapper:14:in `<main>'
Tasks: TOP => db:seed
(See full trace by running task with --trace)

我真的不知道如何解决这个问题.有人可以帮我吗?

I really have no idea how to fix this. Could anyone please help me?

推荐答案

这个错误还有一个潜在的原因，那就是 ActiveRecord 连接池 收割者.启用后，收割者会扫描连接池中的死"连接并关闭它们.在我的测试中，它似乎过于热情并且关闭了完全有效的连接(通常是运行稍大的查询的连接).

There is another potential reason for this error, and that is the ActiveRecord connection pool reaper. When enabled, the reaper scans the connection pool for "dead" connections and closes them. In my testing, it appears to be overzealous and closes perfectly alive connections too (generally ones running slightly bigger queries).

尝试从您的数据库配置中清除 reaping_frequency(将其关闭)，看看是否有帮助.扫描该字符串的代码库，并确保它未设置(或简单地删除！).如果你看到像 config['reaping_frequency'] = ENV['DB_REAP_FREQ'] || 这样的行10，知道||10 实际上是设置一个较低的默认值 10s.此模式在 rails 代码库中存在一段时间，直到更改被恢复，因为它导致了各种问题，包括终止长时间运行的查询，但仍然是Heroku 推荐.

Try clearing reaping_frequency from your DB config (which turns it off) and see if that helps. Scan your codebase for that string, and make sure it's unset (or simply removed!). If you see a line like config['reaping_frequency'] = ENV['DB_REAP_FREQ'] || 10, know that the || 10 is actually setting a low default of 10s. This pattern was in the rails codebase for a time, until the change was reverted due to it causing various issues, including killing long running queries, but is still recommended by Heroku.

如果禁用收割者可以解决问题，我建议将其禁用.Rails 不再默认设置它，而且它带来的问题似乎比它解决的要多.

If disabling the reaper fixes things, I would recommend leaving it disabled. Rails no longer sets it by default, and it seems to cause more problems than it solves.

我遇到了和你一样的错误，我就是这样解决的.就个人而言，我将其禁用.我在我的博客上更详细地描述了我的具体问题.

I had an error like yours, and that was how I fixed it. Personally, I am keeping it disabled. I wrote about my specific problem in more detail on my blog.

这篇关于尝试更新 mySQL 中的 640k 行会出现“查询期间与 MySQL 服务器的连接丢失"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！