尝试更新mySQL中的640k行将获得

尝试更新mySQL中的640k行将获得

本文介绍了尝试更新mySQL中的640k行将获得“查询期间与MySQL服务器的连接中断".的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试从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代码库中使用了一段时间,直到更改被还原,因为它引起了各种问题,包括杀死长期运行的查询,但仍然.

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服务器的连接中断".的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-10 21:55