Skip to content

Instantly share code, notes, and snippets.

@flackou
Created December 23, 2011 13:04
Show Gist options
  • Save flackou/1514154 to your computer and use it in GitHub Desktop.
Save flackou/1514154 to your computer and use it in GitHub Desktop.
Testing mysql2 wait_timeout param
require 'rubygems'
gem 'mysql2'
gem 'activerecord', '3.1.1'
require 'active_record'
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout
MYSQL_WAIT_TIMEOUT_MAX = 2147483
# Testing the remote db connection after a 60s sleep
def test_connection(extra_conf = {})
conf = {
:host => "1.2.3.4",
:username => "root",
:database => "test",
:adapter => "mysql2"
}
ActiveRecord::Base.establish_connection(conf.merge(extra_conf))
puts ActiveRecord::Base.connection.execute("SHOW VARIABLES LIKE \"wait_timeout\"").to_a.join(" : ")
sleep 60
puts ActiveRecord::Base.connection.active? ? "Connection up !" : "Connection lost !"
end
test_connection(:wait_timeout => MYSQL_WAIT_TIMEOUT_MAX)
test_connection(:wait_timeout => MYSQL_WAIT_TIMEOUT_MAX + 1)
$ ruby mysql2_wait_timeout.rb
wait_timeout : 2147483
Connection up !
wait_timeout : 2147484
Connection lost !
@kirillrdy
Copy link

I am having same issue ? did you end up submitting a bug report to rails ? if not I will.
I have spend some time trying to find who is changing it.

I do have some news for you.
when you set it to MAX + 1. it actually uses mysql default which is 28800, which is , I suspect, 28 seconds. mysql docs says it should be seconds but i think its treating it as milliseconds.
What OS did you use to reproduce this ? i am on FreeBSD.

Cheers,

@flackou
Copy link
Author

flackou commented Jan 30, 2012

Yes I did submit a pull request to rails about this issue.
My conclusions are the same as yours... mysql2 sets the wait_timeout param with a too high value by default. But my pull request didn't have much success, probably because we're very few to face this problem. My DB is on FreeBSD too (and I tested it with a client app on FreeBSD and OS X : same behavior). So It's probably FreeBSD + MySQL related...
I'm glad that my investigation on this subject was useful for someone !

@kirillrdy
Copy link

More interesting news.
it has nothing to do with FreeBSD.
for some reason in MySQL 5.5x wait_timeout is in milliseconds, but in MySQL 5.1x its in seconds ( as per documentation )
I have confirmed this on Linux and FreeBSD

still investigating...

@andreydjason
Copy link

I'm having this same issue. Anyone that has success to fix that?

@kirillrdy
Copy link

hi guys,
just one more little discovery,
this has been fixed in mysql 5.6

@kenn
Copy link

kenn commented May 20, 2012

link? re: mysql 5.6 fix

@kirillrdy
Copy link

No particular link.just install MySQL 5.6 which has been released a while ago.
In production systems I will stick with 5.1

@johnjohndoe
Copy link

Please mind that the actual limit for wait_timeout depends on the operating system you are using. The individual values can be retrieved from the MySQL documentation (links v.5.6).

Another point is that I cannot find wait_timeout in Rails 3.2.14 while there are configuration options in Rails 4. I wonder if I am actually able to pass the setting via database.yml.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment