Rails 3.1 / mysql2 error : "MySQL server has gone away"

Question

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"

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

Example :

$ 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

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':...

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

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 ?

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

Thanks for your help.

score 13 · Accepted Answer · answered Dec 23 '11 at 17:30

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

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

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

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.

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

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

2147483 limit is for windows, right? It's in the "Type (windows)" block. For other platforms, the limit is 31536000. http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_wait_timeout — kenn, May 20 '12 at 19:51
Tested with `mysql2 0.3.16` and `activerecord 4.0.0/4.1.4` in Windows 7 - `wait_timeout` is working, but in real rails I am experiencing lost connections and even out of memory. — Paul, Oct 20 '14 at 16:35

score 2 · Answer 2 · answered Dec 23 '11 at 15:30

Had a lot of lost connections - but I couldn't say if they went away due to the following tweak or elsewise :/

Had to throw the following script into initializers and add a line of configuration to each of my databases in my database.yml like this:

...
flags: <%= 65536 | 131072 %>
...

The script looks like this:

/config/initializers/mysql2.rb

module ActiveRecord
  class Base
    # Overriding ActiveRecord::Base.mysql2_connection
    # method to allow passing options from database.yml
    #
    # Example of database.yml
    #
    #   login: &login
    #     socket: /tmp/mysql.sock
    #     adapter: mysql2
    #     host: localhost
    #     encoding: utf8
    #     flags: 131072
    #
    # @param [Hash] config hash that you define in your
    #   database.yml
    # @return [Mysql2Adapter] new MySQL adapter object
    #
    def self.mysql2_connection(config)
      config[:username] = 'root' if config[:username].nil?

      if Mysql2::Client.const_defined? :FOUND_ROWS
        config[:flags] = config[:flags] ? config[:flags] | Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
      end

      client = Mysql2::Client.new(config.symbolize_keys)
      options = [config[:host], config[:username], config[:password], config[:database], config[:port], config[:socket], 0]
      ConnectionAdapters::Mysql2Adapter.new(client, logger, options, config)
    end
  end
end

Thanks for your answer but I don't think our 2 problems are related : you probably have multi statements/multi results queries in your app ? Because the flags you're adding correspond to CLIENT_MULTI_RESULTS and CLIENT_MULTI_STATEMENTS, aren't they ? By the way using Mysql2::Client::MULTI_STATEMENTS and Mysql2::Client::PS_MULTI_RESULTS (instead of raw integers) should work. — Flackou, Dec 23 '11 at 17:41

Rails 3.1 / mysql2 error : "MySQL server has gone away"

2 Answers2

Linked