Skip to content
This repository

Mysql2::Error: closed MySQL connection #209

Closed
ntalbott opened this Issue September 19, 2011 · 41 comments
Nathaniel Talbott

We have a long-running, fairly active daemon that dies intermittently with this error (max once a day, min every few weeks). We're on Rails 3.0.10 and mysql2 0.2.11. Here's the full error:

Mysql2::Error: closed MySQL connection: DELETE FROM `atable` WHERE (id IN (976613))

#<ActiveRecord::StatementInvalid: Mysql2::Error: closed MySQL connection: DELETE FROM `atable` WHERE (id IN (976613))>

Any ideas on what could be going on? Anything I can do to help debug?

Tom Hensel

same issue here. any hints?

Brian Lopez
Owner

Would you mind upgrading to 0.2.13? If that doesn't work maybe try the latest from master?

sidgopalan

I'm seeing the same issue. We're using ruby-1.8.7-p334, sequel (3.25.0), and mysql2 (0.3.7).
The app has two DB connections - a write connection to the master DB and a read connection to the slave DB.
The slave DB connection throws this error intermittently; unfortunately, its not easy to reproduce the error.

Duff OMelia

Just thought I'd report that we're on Rails 3.0.10 and we just upgraded to mysql2 0.2.13 and we're still seeing the issue ntalbott brought up. I agree with with sidgopalan that it's a pretty tough one to reproduce.

Brian Lopez
Owner

Do you guys happen to be using unicorn or any other kind of forked process worker model?

Nathaniel Talbott

We are, but not in the process that's failing. The failing process is a simple, long-running background daemon.

github0013

Rails 3.1.1
mysql2 (0.3.7)
resque (1.19.0)
redis 2.4.2
Darwin domain.local 10.8.0 Darwin Kernel Version 10.8.0: Tue Jun 7 16:33:36 PDT 2011; root:xnu-1504.15.3~1/RELEASE_I386 i386

I too encountered the same error under this environment.
exception message was
Mysql2::Error: closed MySQL connection: SELECT COUNT(*) FROM table_name WHERE table_name.key_id = 99999
at
gems/activerecord-3.1.1/lib/active_record/connection_adapters/mysql2_adapter.rb:687:in query
When this error happened, I was running a rails server, and 3 workers were running under resque.
The web server, very private, was not getting accesses much, I was mainly browsing resque web interface time to time. The workers were creating, updating, deleting rows.

any clues?

Mike Pack

+1 for me

0.3.11

Happens during a heroku db:pull

jrreed

Getting this intermittently as well, when I try to run rspec.

mysql2-0.3.11
rails-3.1.3
capybara-1.1.2
factory_girl_rails-1.4.0

FactoryGirl.create(:model)
ActiveRecord::StatementInvalid:
Mysql2::Error: closed MySQL connection: SHOW TABLES

Kenn Ejima

Recently upgraded and start to have this problem intermittently.

  1. First, ActiveRecord::StatementInvalid - Mysql2::Error: Lost connection to MySQL server during query
  2. then MySQL server has gone away
  3. followed by many Mysql2::Error - closed MySQL connection errors, until the server is restarted by monit

The original Gemfile that didn't cause problems:

mysql2 (0.2.13)
activerecord (3.0.10)
thin (1.2.11)
sinatra (1.2.7)

The upgraded Gemfile with which the problem started to appear:

mysql2 (0.3.11)
activerecord (3.1.3)
thin (1.3.1)
sinatra (1.3.2)

If one of you have any advice that I can do to narrow down the repro, let me know.

Joel Moss

I'm having this same problem when running resque. Any ideas guys?

Kenn Ejima

I think it's important to clarify what exactly happened. In my observation, every time it occurs, closed MySQL connection was the last (but massive) chunk of errors I find in the logs. But if I carefully trace back to the original point of failure, it's been always Lost connection to MySQL server during query. We should probably check if it is the case. (It's much easy to catch it if you use Airbrake)

Then I came across the official doc: http://dev.mysql.com/doc/refman/5.1/en/error-lost-connection.html

In my case, Rails and MySQL are connected via UNIX socket on the same machine, so it pretty much narrows down the cause. In fact, SHOW GLOBAL STATUS LIKE 'Aborted_connects' returns 0 and at this point I can only think of, somehow, net_read_timeout is reached.

Described as follows, http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_net_read_timeout this value is also applicable to UNIX socket starting MySQL 5.1.41 (mine is MySQL 5.1.55), and what is does is to timeout when the server is reading from the client. This is interesting, as in my case it's been always INSERT / UPDATE / DELETE that triggered the error.

It seems to me that, the mysql client library recognize the DML, but somehow it doesn't make it to the server, then dropping the connection.

This is the best guess I have so far. I'm still seeing this problem every 2-3 days.

TingChengLin

same here.

ruby 1.8.7
rails 3.0.7
mysql2 0.2.4
unicorn 4.2.0

any ideas?

Dmitry Vasilets

i fix by reconnect: true in database.yml

Alex Toulemonde

reconnect: true did not work for me. Happens during a heroku:pull as well. I suspect my table to have a weird encoding format and that it's causing the problems. Because I don't have this problems for every table

TingChengLin

update to mysql2 0.2.11 works for me!

Xavier Noria
fxn commented April 01, 2012

@brianmario I know of a Ruby on Rails application that has quite a lot of traffic and seems to have these reported intermittent exceptions every day. This application has some 400 processes with connections open to a server with a max_connections set to 5K. Approximately 7% of them cannot complete the process lifetime with the reconnect flag set to true. It is much worse if the reconnect flag is set to false.

I have done some instrumentation at the Rails level to monitor number of requests, and several metrics that suggest failures are kind of random. No apparent correlations or patterns.

Since we can reproduce this, would you like to instrument something at the connection level?

Xavier Noria
fxn commented April 06, 2012

It's fixed. In some points the application was forking to send emails asynchronously, and the subprocess closed the connection when finished. Short-term solution has been to use a thread, and long-term solution will probably be to use a local mail server that handles the queue between the application and the mail service provider.

jbuginas

Rails 3.2.2 and 3.2.3

  • mysql2 (0.3.11)
    • activemodel (3.2.3)
    • activerecord (3.2.3)
    • activeresource (3.2.3)
    • activesupport (3.2.3)

I had this same problem on rails in a simple rake program every time I ran it until I commented out the call to db:reset

This failed during the "make_entries"

namespace :db do
desc "Fill database with sample data"
task :populate => :environment do
Rake::Task['db:reset'].invoke
make_users
make_entries
end
end

While this one did not:

namespace :db do
task :populate => :environment do
make_users
make_entries
end
end

Alexander Zubkov
sekrett commented May 20, 2012

I didn't have this error on Mysql 5.0, but now on 5.5 it happens. I have a small background script. Is it related to execution time or something?

Rails 3.2.3, mysql2 0.3.11

Alexander Zubkov
sekrett commented May 20, 2012

I found the answer:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_net_read_timeout

Simple sleep(31) call reproduces this error and sleep(30) does not.

Brian Lopez
Owner

Can anyone else confirm this will reproduce and/or fix the issue?

Sean Hinton

So how do you fix this problem?

Damien Roche

Still nothing?

Hywel Carver

Real shame that this is still causing people problems. Is there any progress? Is there any debugging info I can give to help solve the bug?

Kelly Heikkila

We are having the same issue, as well. We get this error immediately on one specific query, (so it is not a timeout issue), and randomly on other simple/fast queries, where one attempt will die and re-running the query will work without error.

ree-1.8.7
mysql2 gem -v 0.2.18
mysql 5.5.18, for Linux (x86_64) using readline 5.1

Clement ROULLET

Same issue here, with ruby v1.9.3p327, rails 3.2.11, mysql2 0.3.11 on Mysql Server v5.5.29-1.
The problem came up when setting up the clockwork gem to work as a daemon with the daemons gem.

@pronix's tip seems to have worked for me:
reconnect: true in database.yml

Rob Taylor

Same issue...

running rake db:seed ... lots of seed data!

ruby 1.9.3-p194-perf
mysql2.0.3.11
rails-3.2.11

tried reconnect: true with no luck
tried bumping up connection pool size but no luck either

jjy

#209 (comment)
Same for me, and Model.connection.reconnect! can fix it.

Carlos Alexandro Becker

Same here with Ruby 1.9.3-p327.

It occurs randomly in production while querying a MySQL View.

Can't reproduce locally.

Lonny Eachus
lonny commented May 19, 2013

This issue should not be closed!

I am having the same issue, and so far it has been intractable. There is nothing really unusual about my application. I am using

Ruby: 1.9.3-p374
Rails: 3.2.13
mysql2: 0.3.11

I have tried reconnect: true and Model.connection.reconnect!, so far to no avail.

I have also tried mysql2 v. 0.3.10 and v. 0.3.12b, again no difference.

In my case this is NOT a timeout issue, as I did a reconnect just before trying to write to the database and the error occurrs within just a few seconds.

This is strictly reproducible with the data I am using, and happens both in dev mode and production.

It traces back to

Mysql2::Error: MySQL server has gone away: INSERT INTO...

which goes back to

active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'

which in turn is the "rescue" line in this block:

# Executes the SQL statement in the context of this connection.
def execute(sql, name = nil)
  if name == :skip_logging
    @connection.query(sql)
  else
      log(sql, name) { @connection.query(sql) }
  end
rescue ActiveRecord::StatementInvalid => exception
  if exception.message.split(":").first =~ /Packets out of order/
    raise ActiveRecord::StatementInvalid, "'Packets out of order' error was received from the database. Please update your mysql bindings (gem install mysql) and read http://dev.mysql.com/doc/mysql/en/password-hashing.html for more information. If you're on Windows, use the Instant Rails installer to get the updated mysql bindings."
  else
    raise
  end
end

The line being executed is simply an "insert into" which should not be causing this.

Champier Cyril

Same problem on a rake db:reset. The connexion is lost during the application of the migrations, so it cannot be my code.
But the strange thing is that I connect on an amazon RDS base, and the seeding is working when I execute from my local machine, but fails if I launch ot from an amazon EC2 vm...
Using mysql2 (0.3.11)

Rob Taylor

I have found that using db:setup rather than db:reset eliminated this issue on my Mac.

Champier Cyril

I confirm, same workaround on amazon ec2 with rake db:schema:load

Sangil.Jeong

Same problem on rake db:reset.
I did configure my.cnf like below..

[mysqld]
skip-name-resolve
interactive_timeout=180
wait_timeout=180

but error being executed yet. any solutions?

Carlos Alexandro Becker
Aaron Stone
Collaborator

Reading through this bug, I think the two problems were:

  1. loss of the client object while some result objects still needed it
  2. timeouts set too low.

At this time:

  1. is resolved by this month's mysql2 releases
  2. is a config issue.

Any objection to closing this issue now?

Lonny Eachus
lonny commented July 24, 2013

I ended up just downgrading to a different version, which solved the problem. I did not have the luxury of waiting this long for a fix.

I might agree with (1) there at the top but my problem was definitely NOT a timeout issue. It consistently occurred well before the timeout period. However it has now been so long that I no longer have any documentation.

agilastic

Same here while running long running rake task with a huge chunk of database entries...
mysql2 (0.3.11)
rails/activerecord etc... (3.2.13)

agilastic

ok seems to be fix with: "reconnect: true"

Aaron Stone sodabrew closed this July 25, 2013
Aaron Stone
Collaborator

Ok, I'm letting this issue go. Thanks all, and of course if there are specific recurrences that need more / different help, please open a new issue and reference this one.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.