MySQL auto-reconnect revisited

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.

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.

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

Hooray for investigating this. We’ve been getting “MySQL has gone away” for the past month (although it magically stopped last week).

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.

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!

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

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.

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

-- Chad

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?

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.

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!