Yury Velikanau's Blog

Adventures with Ruby and Rails

Rails 2, MySQL 2, and Stored Procedures

Rails community doesn’t like deriving business logic to the database, but in some cases stored procedures are very helpful and many people trying to use them in Rails, however it’s not so easy as you can imagine.

Running ActiveRecord::Base.connection.execute("CALL proc01") will give you a bunch of errors in different cases.

Let’s say your procedure returns some result set. So running that procedure will give you exception

1
ActiveRecord::StatementInvalid: Mysql2::Error: PROCEDURE can't return a result set in the given context

In case your procedure doesn’t return any result set, then running it twice will give you another exception

1
ActiveRecord::StatementInvalid: Mysql2::Error: Commands out of sync; you can't run this command now

In other case when stored procedure doesn’t return any result set at all, you’ll get NoMethodError.

All these issues are well known, however they aren’t fixed yet, even in Rails 3.

Let’s look at the first issue. 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. It’s not a surprise that neither Rails or MySQL2 doesn’t do this, because in most projects you don’t need multiple result sets. In the future we’ll probably have an option to configure this, but until then let’s create a workaround.

We use MySQL2. Its latest 0.2.6 gem release is kind of outdated for Ruby 1.9.2, so we did a fork from edge version at some stable point. MySQL2 defines its own mysql adapter for Rails in lib/active_record/connection_adapters/mysql2_adapter.rb. We’re interested in a method that creates connection object:

lib/active_record/connection_adapters/mysql2_adapter.rb
1
2
3
4
5
6
7
8
9
10
11
def self.mysql2_connection(config)
config[:username] = 'root' if config[:username].nil?
if Mysql2::Client.const_defined? :FOUND_ROWS
config[:flags] = 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

This place looks good to put our additional flag for MySQL, but wait! There is other flags already, so let’s just re-use this and let adapter pass it further.

Create a file in config/initializers with the following content:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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. Not so clear, because you have to know those magic numbers, but OK for the beginning.

If you want to pass more options, remember that you must use bitwise OR operator, so in database.yml it will be

database.yml
1
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.

Comments