stored procedure issues #307

Closed
krukid opened this Issue Sep 11, 2012 · 21 comments

Comments

Projects
None yet

krukid commented Sep 11, 2012

Firstly, the stored procedure example on the main page doesn't seem to work, failing with:
undefined methodnext_result' for #Mysql2::Client:0xb6f48538 (NoMethodError)`

Secondly, even if my stored proc returns a single result set, mysql2 doesn't seem to handle the associated resources properly, because subsequent queries throw:
Commands out of sync; you can't run this command now (Mysql2::Error)

nijikon commented Sep 11, 2012

Please provide a test case, thanks.

krukid commented Sep 11, 2012

Test case for "undefined method" issue is exactly like in the README, I just copy-pasted the given code and it doesn't work.

client = Mysql2::Client.new(:host => "localhost", :username => "root",
  :flags => Mysql2::Client::MULTI_STATEMENTS )
result = client.query( 'CALL sp_return_two_result_sets()')
while ( client.next_result) # ===> "undefined method"
  result = client.store_result
end

To get the "out of sync" error, I run a "SELECT " query on the same connection.

client = Mysql2::Client.new(:host => "localhost", :username => "root")
result = client.query( 'CALL sp_return_single_result_set()')
result.each... # ====> works fine

client.query("SELECT 1") # ====> "out of sync"

Listing dummy procedures:

DELIMITER //

CREATE PROCEDURE sp_return_single_result_set()
BEGIN
  SELECT * FROM my_table;
END//

CREATE PROCEDURE sp_return_two_result_sets()
BEGIN
  SELECT * FROM my_table ORDER BY id ASC;
  SELECT * FROM my_table ORDER BY id DESC;
END//

Ubuntu 12.04.1 LTS, MySQL 5.5, Ruby 1.8.7, Gem version 0.3.11

Owner

brianmario commented Sep 11, 2012

This is actually a bug with the multi-statement support I need to fix. Thanks for reporting!

balepc commented Sep 17, 2012

Hello brianmario,

Are you any close to resolution of this bug?

Owner

brianmario commented Sep 17, 2012

I've been out of town, I'll do my best to take a look at it soon.

On Monday, September 17, 2012 at 5:54 AM, Valery Vishnyakov wrote:

Hello brianmario,
Are you any close to resolution of this bug?


Reply to this email directly or view it on GitHub (#307 (comment)).

The current master branch supports MULTI_STATEMENTS but the 0.3.11 tag does not. I faced the similar problem yesterday and when I looked into it, I saw the client.c file did not support the next_result and store_result method in 0.3.11

I've just been looking for this feature, too. I am trying to use the code straight out of the readme with my own multiple-statement query, and it fails in the same way. Sorry "me too" -- Thanks for the tip that it's resolved in tip, I've updated my Gemfile to pull from master, remembering to bundle exec, and it behaves as expected.

next_result and more_results

Owner

brianmario commented Oct 16, 2012

Yeah only the beta release gems or master have MULTI_STATEMENTS support. I'm really hoping to get a release out soon. Gonna close this since it's coming ;)

Sorry for the README documenting an API that doesn't exist in the released gems yet... I should probably start working from a dev branch or something ;)

brianmario closed this Oct 16, 2012

You know, for a core gem such as mysql2, I would rather be able to know about the features before they arrive, especially in this case. This is not necessarily a feature that I expected, but your documentation prompted me to look deeper and I found it. So, thanks :)

Would be really nice if someone could please update the standard gem.

alinski commented Nov 6, 2012

I cloned the master branch but it still get a NoMethodError

require './mysql2/lib/mysql2'

2.times do
  gernot = Mysql2::Client.new(host: "localhost", username: "" , password: "", database: "mydb", flags: Mysql2::Client::MULTI_STATEMENTS )
  (result = gernot.query("CALL routine()")).each do |entry|
    puts entry
  end
  puts result
  result.each do |r|
    puts r
  end
  while (gernot.next_result)
    result = gernot.store_result
  end
  gernot.query("SELECT 1")
end

ankane commented May 15, 2013

Any update on this?

knappe commented Jul 11, 2013

Can we re-open this ticket @brianmario? It has been 9 months since this was closed with 'coming'.

Owner

brianmario commented Jul 11, 2013

@sodabrew and I are honing in on the last few issues before we push a new release out. We've made a ton of progress the past few days and I'm hopeful we may be able to do that next week but as you can see from my comment 9 months ago, unforeseen circumstances can always hold things up ;)

We've been trying to track down and fix a nasty, non-deterministic bug that's existed for quite a while and I was determined to get it fixed before we released the next version. I've also been extremely busy with work, traveling and personal stuff. I'm really looking forward to getting this release out as soon as we can, and we can use all the help we can get in doing so.

Also, this functionality has been in the pre-release gems for quite a while. I tend to close issues once a fix lands in master. We'll also happily accept any pull requests for fixes you all may have found in your own testing. Or at the very least, issues with as much detailed information you can provide us about what mysql2 version, version of libmysql, version of ruby, operating system, backtrace info if there is any, and most importantly a gist or something with some code to reproduce the issue.

As @XULRunner42 said, this gem is (as far as I know) the de-facto MySQL client for Ruby these days and we want to make sure it's as rock-solid as possible. I would argue that we should strive for that vs pushing a release out prematurely so people can use a single new feature from it.

Really sorry it's been so long, we're doing what we can with the time we have. Any help you can offer is appreciated :)

Is this bug fixed?
I'm using v0.3.14 and I get this error when I execute the 2nd query command. Here's an example:

client = Mysql2::Client.new(:host => "localhost", :username => "root",
  :flags => Mysql2::Client::MULTI_STATEMENTS )
client.query("UPDATE offers SET price='60.0 WHERE id=1;UPDATE offers SET price='60.0 WHERE id=2;")

client.query("UPDATE offers SET price='60.0 WHERE id=3;") # ->> Commands out of sync; you can't run this command now (Mysql2::Error)

Ok I know that I don't need multi statements for this updates but it was only for demonstration purposes only.

Collaborator

sodabrew commented Jan 27, 2014

@brianmario Should query always flush out any pending result sets?

Owner

brianmario commented Jan 28, 2014

@sodabrew it doesn't currently. Adding something similar to #377 (but throwing away pending results before the query instead) might be cool.

@viniciusoyama for the time being you need to call client.abandon_results! between queries. That will read and throw away any pending results, allowing you to send another query.

@brianmario Thx man! That's exactly what I was looking for. Now it works. I was closing and opening a new connection for each query but it is slow and sometimes throw errors.

Owner

brianmario commented Jan 28, 2014

@viniciusoyama no problem!

iRonin commented Mar 23, 2015

Is calling client.abandon_results! still needed when working with stored procedures?

Collaborator

sodabrew commented Mar 23, 2015

@iRonin Not necessarily, see the code for this test as an example: 2a7d09d

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment