MySQL auto-reconnect revisited

2322 views
Skip to first unread message

dubek

unread,
Jan 22, 2009, 6:16:43 AM1/22/09
to Ruby on Rails: Core
Hi,

In commit 9051da90e4da2ab0db16530a7f7568e24a0ccaed the following line
was added to MysqlAdapter#connect:

@connection.reconnect = true if @connection.respond_to?
(:reconnect=)

There are couple of problems with this:

1. It doesn't stick. A look at the sources of the mysql gem tells that
the
real_connect method sets reconnect to 0 (false). Since real_connect
is called
after the line given about, it'll reset the value of reconnect to
0. A simple
test for that will be:

def test_connection_has_mysql_reconnect_property
@connection = ActiveRecord::Base.connection
mysql_connection = @connection.instance_variable_get
(:@connection)
assert mysql_connection.reconnect
end

This test fails. And it's easy to fix, we'll just move the line
above to
somewhere after the real_connect() call. (Unless, I'm missing
something,
of course.)


2. Even after I fixed #1, I couldn't make reconnect work in my tests.
I did a simple
test without AR (directly with mysql gem), and it works OK (you can
see that
reconnection happened because the thread_id of the connection is
modified).
However, with AR I keep getting the "MySQL server has gone away"
errors. Here's
my test (should be in connection_test_mysql.rb):

def test_select_auto_reconnects_after_connection_lost
mysql_connection = @connection.instance_variable_get
(:@connection)
assert mysql_connection.reconnect

original_thread_id = mysql_connection.thread_id
assert_equal ["42"], @connection.select_values('SELECT 42')

@connection.update('set @@wait_timeout=1')
sleep 2

# this shouldn't raise Mysql::Error if reconnect is true
assert_equal ["42"], @connection.select_values('SELECT 42')
new_thread_id = mysql_connection.thread_id
assert_not_equal original_thread_id, new_thread_id
end


3. Do we want the reconnection at all? According to Mysql docs
( http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html )
the auto-reconnect does not save any session variables. This
includes these
two settings that AR sets after connection is established:

SET NAMES 'utf8'
SET SQL_AUTO_IS_NULL=0

Which means, if the server was gone, auto-reconnect takes place on
the next
query, but the variables are back to the server default (use
SHOW VARIABLES LIKE 'character_set_client' to view the variable
value before
and after the reconnection). On my machine it's back to 'latin1'.


See previous (related) discussion at:
http://groups.google.com/group/rubyonrails-core/browse_thread/thread/a6a7eccf05b24c0

Best,
dubek.

dubek

unread,
Jan 22, 2009, 6:18:54 AM1/22/09
to Ruby on Rails: Core
I should also say that this ticket is related:

http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/1293

(and sorry about the lousy line-wraps)

dubek.

Lourens Naude

unread,
Jan 22, 2009, 6:49:55 AM1/22/09
to rubyonra...@googlegroups.com
Hey,

Just a quick note that we experienced similar difficulties with reconnection and mysqlplus.

Found sending a mysql_ping down the wire to assert that the thread identifier changed works best.
libmysqld also reuses previously opened file descriptors for reconnection attempts.



Reconnect behavior is especially important for an async interface as simply setting mysql.reconnect = true essentially
invalidates an existing operation when sent down the wire, but pending a result.


Noticed sporadic  "MySQL server has gone away" in our environment with 2.3 / Edge ( stock mysql gem ) and am aware 
of others in the same boat.

Essentially any of the following error states should warrant a reconnect :

CR_SERVER_LOST

* CR_SERVER_GONE_ERROR

* ER_SERVER_SHUTDOWN

The latter being important for MySQL server restarts as well.

Thoughts ?

- Lourens

Mislav Marohnić

unread,
Jan 22, 2009, 9:50:53 AM1/22/09
to rubyonra...@googlegroups.com
Hooray for investigating this. We've been getting "MySQL has gone away" for the past month (although it magically stopped last week).

On Thu, Jan 22, 2009 at 12:16, dubek <dov....@gmail.com> wrote:

   def test_connection_has_mysql_reconnect_property
     @connection = ActiveRecord::Base.connection
     mysql_connection = @connection.instance_variable_get
(:@connection)
     assert mysql_connection.reconnect
   end

  This test fails.

How can the test fail if the `reconnect` property is 0? Zero is true in Ruby.

dubek

unread,
Jan 22, 2009, 9:56:07 AM1/22/09
to Ruby on Rails: Core
On Jan 22, 4:50 pm, Mislav Marohnić <mislav.maroh...@gmail.com> wrote:
>
> How can the test fail if the `reconnect` property is 0? Zero is true in
> Ruby.


The reconnect accessor method of the mysql gem returns 'true' or
'false'. In the C implementation of real_connect it sets the reconnect
flag to 0 in each connect.

dubek.

Michael Koziarski

unread,
Jan 25, 2009, 10:36:47 PM1/25/09
to rubyonra...@googlegroups.com
> 1. It doesn't stick. A look at the sources of the mysql gem tells that
> the
> real_connect method sets reconnect to 0 (false). Since real_connect
> is called
> after the line given about, it'll reset the value of reconnect to
> 0. A simple
> test for that will be:
>
> def test_connection_has_mysql_reconnect_property
> @connection = ActiveRecord::Base.connection
> mysql_connection = @connection.instance_variable_get
> (:@connection)
> assert mysql_connection.reconnect
> end
>
> This test fails. And it's easy to fix, we'll just move the line
> above to
> somewhere after the real_connect() call. (Unless, I'm missing
> something,
> of course.)

Send us a patch with test for this one, nice find!

I think we should probably push reconnect into an option in
database.yml so users can make their own decisions. In my case the
character_set values are set in my.cnf so those queries don't actually
change anything for me.

A patch to fix this up and test that the value is actually set would
be greatly appreciated. Failing that, a lighthouse ticket assigned to
jeremy would be a good start.

Thanks again for spending the time to look into this.

--
Cheers

Koz

Chad Woolley

unread,
Jan 25, 2009, 11:09:33 PM1/25/09
to rubyonra...@googlegroups.com
On Sun, Jan 25, 2009 at 8:36 PM, Michael Koziarski
<mic...@koziarski.com> wrote:
> Send us a patch with test for this one, nice find!

Yeah, great catch. This totally bit me on some daemons in the past
when the mysql connection died after several days...

-- Chad

Dov Murik

unread,
Jan 26, 2009, 3:44:09 AM1/26/09
to rubyonra...@googlegroups.com
>
>
> Send us a patch with test for this one, nice find!

...


>
> I think we should probably push reconnect into an option in
> database.yml so users can make their own decisions. In my case the
> character_set values are set in my.cnf so those queries don't actually
> change anything for me.
>
> A patch to fix this up and test that the value is actually set would
> be greatly appreciated. Failing that, a lighthouse ticket assigned to
> jeremy would be a good start.
>

Sounds good.
I will send a patch later today. Should I also open a lighthouse
ticket, or just post the patch here?


--
dubek.

Michael Koziarski

unread,
Jan 26, 2009, 3:51:10 AM1/26/09
to rubyonra...@googlegroups.com
> Sounds good.
> I will send a patch later today. Should I also open a lighthouse
> ticket, or just post the patch here?

Open a lighthouse ticket as well so we don't lose track of it. Feel
free to assign it straight to me.


--
Cheers

Koz

dubek

unread,
Jan 26, 2009, 10:11:00 AM1/26/09
to Ruby on Rails: Core

> Open a lighthouse ticket as well so we don't lose track of it.  Feel
> free to assign it straight to me.

Here's the lighthouse ticket:

http://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/1797-mysql-reconnect-value-doesnt-stick

I ran my patch tests on Mac OS X 10.5.6 and MySQL 5.0.67 . Please test
it on other combinations and report your results on the ticket.

Thanks!

--
dubek
Reply all
Reply to author
Forward
0 new messages