Rails 3.1 / mysql2 Error: "MySQL Server is gone"

Question

I am having problems updating my rails 2.3.14 / ruby ​​1.8.7 app to 3.1.1 / 1.9.2: I have

(ActiveRecord::StatementInvalid) "Mysql2::Error: MySQL server has gone away" 

errors occur sporadically. The important thing is that I never had such problems with the mysql gem on 2.3.14 and the exact same db (so the error should not 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 only happens with my (remote) production database, no problem with my local db development. I tried setting "reconnect: true" in my .yml database, but this led to

 Mysql2::Error: Host '****' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts':... 

I tried to isolate the problem with a little rb script by only loading mysql2 and activerecord, but I was not able to reproduce the error in this way (so that it could be associated with the rails stack).

I cannot return from "mysql2" to "mysql" due to encoding problems ( http://www.rorra.com.ar/2010/07/30/rails-3-mysql-and-utf-8/ ) . As a result, I had to roll back my production to my rails 2.3.14 application, which upsets me very much ...

Do you see what I can do to debug this? I can’t even find the right way to reproduce the error ... Has anyone encountered the same error?

I just found a few people mentioning this error (for example: https://github.com/brianmario/mysql2/issues/213 ) but not a solution.

Thanks for your help.

Answer 1

Ok, I decided that I solved my problem. I did not notice this when I posted my question, but it seemed that the error was due to a timeout: after about 20 seconds, activerecord lost 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 do not apply to the MySQL wait_timeout parameter in the same way: mysql gem does not set it, so it uses the default MySQL value 28800 , whereas mysql2 gem sets it at 2592000 if not defined in database.yml. But I get the impression that the value of 2592000 exceeds the maximum value for this parameter: 2147483 ! Which can lead to unexpected behavior that I described ...

I am creating a test script showing an error: https://gist.github.com/1514154

And if I had some seemingly random shutdown when loading the rails console (cf my question), I think this is because my application takes a lot of time, and I sometimes wait a few seconds before than enter your team.

I can not explain why we are faced with this problem so little. Perhaps this is specific to my conf (remote database, MySQL version?). I tried with another remote staging database: the error did not reproduce ...

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

Answer 2

There were a lot of lost links, but I could not say that they left due to the following setting or in another way: /

I had to output the following script to initializers and add a configuration line to each of my databases in my database. For instance:

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