Under what circumstances would you want Rails to be set NOT to reconnect to MYSQL

Question

I'm experiencing a few errors on a rails app, along the lines of:

ActiveRecord::StatementInvalid: Mysql::Error: Lost connection to MySQL server during query: SELECT * FROM `actions` WHERE (`foo`.`id` = 16)

What appears to be happening is that mysql connection is being closed after a timeout, and rails isn't noticing until it's too late.

The remedies I find appear to be to set the reconnect flag to true in database.yaml, or for any database action adding some code like so:

def some_database_operation
  begin
    Account.find(1)
    # or some other database operations here...
  rescue ActiveRecord::StatementInvalid
    ActiveRecord::Base.connection.reconnect!
    unless @already_retried
      @already_retried = true
      retry 
    end
    raise
  else
    @already_retried = false
  end
end
end

I'm listing this option over this one visible here, because this option is apparently unsafe for transactions:

ActiveRecord::ConnectionAdapters::MysqlAdapter.module_eval do
  def execute_with_retry_once(sql, name = nil)
    retried = false
    begin
      execute_without_retry_once(sql, name)
    rescue ActiveRecord::StatementInvalid => exception
      ActiveRecord::Base.logger.info "#{exception}, retried? #{retried}"

      # Our database connection has gone away, reconnect and retry this method
      reconnect!
      unless retried
        retried = true
        retry
      end
    end
  end

  alias_method_chain :execute, :retry_once
end

Of the options to avoid this annoying error, the reconnect option in the yaml file seems by the far the tidiest option - but I'm curious; why you wouldn't set this value to be true by default in your database?

I'd rather not solve one problem by causing a load of others further down the line.

Thanks,

score 14 · Accepted Answer · edited Dec 09 '20 at 12:17

14

As you pointed out in the question, one possible side-effect of automatically reconnecting (if done at a per-statement level), is that it is not transaction-safe.

The MySQL documentation in fact explicitly states that the auto-reconnect feature affects transactions:

Any active transactions are rolled back and autocommit mode is reset.

Applications that are not written to deal with this could easily break. The documentation also lists a number of other side effects caused by the auto-reconnect feature, all of which could cause applications not written to anticipate the behavior to function incorrectly or fail.

Also, if the connection to the database is suddenly lost, the server might not properly release locks that were being held by the connection, so it sounds like an application could deadlock in some cases:

If the connection drops, it is possible that the session associated with the connection on the server side will still be running if the server has not yet detected that the client is no longer connected. In this case, any locks held by the original connection still belong to that session, so you may want to kill it by calling mysql_kill().

Edit: The MySQL documentation link in the answer doesn't seem to exist now. Find the updated documentation here

edited Dec 09 '20 at 12:17

Sebin

1,044
1
8
21

answered Oct 10 '09 at 03:00

Mike Spross

7,999
6
49
75

2

Okay now I'm totally confused. This suggests to me that setting `reconnect` to `true` could be harmful, because transactions being rolled back when they're not supposed sounds like a bad thing. What is the usual workaround to avoid this state of affairs? – Chris Adams Oct 10 '09 at 15:58
The solution is to make sure your transactions are "atomic" - that is, if you lose the connection and reconnect, the whole transaction should be retried, not just a single statement within the transaction. I'm not sure how this works in Rails, but I guess one solution would be to put the transaction in a stored procedure - in the Rails code, you execute a single SQL statement to run the stored procedure, then if it auto-reconnects, the whole transaction starts over from the beginning. – Mike Spross Oct 11 '09 at 01:02
OTOH, if you aren't using transactions, then using the auto-reconnect feature is probably less of an issue. – Mike Spross Oct 11 '09 at 01:04
Can you please explain what does it mean "if done at a per-statement level"? – sekmo Sep 14 '18 at 12:33

score 6 · Answer 2 · edited Jun 20 '20 at 09:12

From the Rails 2.3 release notes (emphasis mine):

4.8 Reconnecting MySQL Connections

MySQL supports a reconnect flag in its connections – if set to true, then the client will try reconnecting to the server before giving up in case of a lost connection. You can now set reconnect = true for your MySQL connections in database.yml to get this behavior from a Rails application. The default is false, so the behavior of existing applications doesn’t change.

Under what circumstances would you want Rails to be set NOT to reconnect to MYSQL

2 Answers2

Linked