MySQL2 and Stored Procedures in Rails 3

Running ActiveRecord::Base.connection.execute("CALL procedure()") will give you a bunch of errors in different cases while using mysql2 adapter.

Suppose your procedure returns some result set. So running that procedure will give you following exception:
ActiveRecord::StatementInvalid: Mysql2::Error: PROCEDURE can't return a result set in the given context: procedure() PROCEDURE

In case your procedure doesn’t return any result set, then it will throw you this exception
ActiveRecord::StatementInvalid: Mysql2::Error: Commands out of sync; you can't run this command now

Or, If connection is dropped then you’ll get NoMethodError exception.

When MySQL runs stored procedure it has to know that client can handle multiple result sets. By default MySQL assumes that client cannot handle this unless you set CLIENT_MULTI_RESULTS flag when establishing connection to MySQL server. This settings is not by default. One have to do it.

mysql adapter for Rails in lib/active_record/connection_adapters/mysql2_adapter.rb looks good to put our additional flag for MySQL, but wait! There is other flags already, so just re-use this and let adapter pass it further.

Create mysql2_adapter.rb file in config/initializers with the following content:
module ActiveRecord
  class Base
    # Overriding ActiveRecord::Base.mysql2_connection
    # method to allow passing options from database.yml
    #
    # Example of database.yml
    #
    #   login: &login
    #     socket: /tmp/mysql.sock
    #     adapter: mysql2
    #     host: localhost
    #     encoding: utf8
    #     flags: 131072
    #
    # @param [Hash] config hash that you define in your
    #   database.yml
    # @return [Mysql2Adapter] new MySQL adapter object
    #
    def self.mysql2_connection(config)
      config[:username] = 'root' if config[:username].nil?

      if Mysql2::Client.const_defined? :FOUND_ROWS
        config[:flags] = config[:flags] ? config[:flags] | Mysql2::Client::FOUND_ROWS : Mysql2::Client::FOUND_ROWS
      end

      client = Mysql2::Client.new(config.symbolize_keys)
      options = [config[:host], config[:username], config[:password], config[:database], config[:port], config[:socket], 0]
      ConnectionAdapters::Mysql2Adapter.new(client, logger, options, config)
    end
  end
end

So now you can pass any additional options from your database.yml. See that 131072 ? This is the value of CLIENT_MULTI_RESULTS constant.

If you want to pass more options, remember that you must use bitwise OR operator, so in database.yml it should be like this
flags: <%= 65536 | 131072 %>

where 65536 is the value of CLIENT_MULTI_STATEMENTS constant. BTW, enabling only CLIENT_MULTI_STATEMENTS will automatically enable CLIENT_MULTI_RESULTS.


By some reason MySQL drops connection after executing stored procedure, so we have to restore it before doing anything else. Now create a method for convenience and put it in the same place as the previous one.

# This method is for running stored procedures.
#
# @return [Hash]
#
def self.select_sp(sql, name = nil)
  connection = ActiveRecord::Base.connection
  begin
    connection.select_all(sql, name)
  rescue NoMethodError
  ensure
    connection.reconnect! unless connection.active?
  end
end

This method does several things:

    * it intercepts NoMethodError that could be raised when your procedure returns nothing
    * it restores connection if it was dropped

So now you can run ActiveRecord::Base.procedure("CALL procedure()") without any errors and get a hash with results.