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!