CODEWITHSUNDEEP
mysqlruby-on-rails
Chris Adams
Chris Adams

Reputation: 2861

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

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,

Upvotes: 15

Views: 7187

Answers (2)

Mike Spross
Mike Spross

Reputation: 8099

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

Upvotes: 14

SFEley
SFEley

Reputation: 7766

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.

Upvotes: 6

Related Questions