MySQL auto-reconnect revisited

[dubek]

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]

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]

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.

http://github.com/oldmoe/mysqlplus/commit/8e6f300f9db75f07c03049efd00a6b91fe16538d

http://github.com/oldmoe/mysqlplus/blob/8e6f300f9db75f07c03049efd00a6b91fe16538d/test/reconnected_test.rb

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.

http://github.com/oldmoe/mysqlplus/commit/85141abf097a375520ca5cf17df4bccc48523d3e

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ć]

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]

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]

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

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]

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

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

> 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