Trying to update 640k rows in mySQL gets "Lost connection to MySQL server during query"

Question

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

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

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?

Answer 1

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).

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.

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.

Answer 2

"Lost connection to MySQL server during query" means that either:
1. The database crashed while running the query (can be due to restart, table corruption, etc).
2. The session disconnected due to query timeout (check variable wait_timeout and interactive_timeout depending on your connection type), network problem, someone\something killing your query.