I know this problem has been asked many times. But I tried the solutions and they didn't work for me. I have a web application built on rails 3.2.12 and ruby 1.9.2p180. I have a stored procedure in it which returns me data of a query having 5 inner joins. Multiple rows approximately 600 are returned in present case. On the local the stored procedure runs fine with no issues. But when I tried it on the server it is throwing:
ActiveRecord::StatementInvalid: Mysql2::Error: PROCEDURE test.sp_procedure can't return a result set in the given context: call sp_procedure('2015-02-14 00:00:00 -0500', '2015-03-03 23:59:00 -0500', 5, '13')
I have searched for this issue and found that I need to set CLIENT_MULTI_RESULTS flag when establishing connection to MySQL server. For this I have done monkey patching as said. Here is the file in initializers:
module ActiveRecord
class Base
def self.mysql2_connection(config)
config[:username] = 'deploy' 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
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
end
end
In my database.yml I have added: flags: <%= 65536 | 131072 %> and also tried with flags: 131072. But it didn't work.
However using the following works:
client = Mysql2::Client.new(:host => "localhost", :username => "root", :flags => Mysql2::Client::MULTI_STATEMENTS )
result = client.query( 'CALL sp_procedure('2015-02-14 00:00:00 -0500', '2015-03-03 23:59:00 -0500', 5, '13')')
This worked on the server too. But each time the stored procedure will run it will create a new connection which I don't want.
And also one thing to note while I am doing this on local as soon as I call the stored procedure I have to execute this:
ActiveRecord::Base.connection.reconnect!
If I don't write this it throws an error:
ActiveRecord::StatementInvalid: Mysql2::Error: Commands out of sync; you can't run this command now
So this is also the same thing means it creates a new connection each time. So I am finding for a solution which saves me from doing this. And if the monkey patching is correct then what am I missing. Please help.
