Commands out of sync

[markkuit]

I'm having issues with mysql2 gem when issuing queries in a rapid succession. Basically, I'm initializing Mysql2::Client and issuing a bunch (up to hundreds) of SELECT and INSERT low-complexity .query()s one after the other on a prepare-execute basis, always saving the .execute result in a var even though not always reading its value.

Every once in a while, the execution stops and I get a 'commands out of sync' error..

/var/lib/gems/1.9.1/gems/mysql2-0.4.2/lib/mysql2/statement.rb:14:in _execute': Commands out of sync; you can't run this command now (Mysql2::Error) from /var/lib/gems/1.9.1/gems/mysql2-0.4.2/lib/mysql2/statement.rb:14:inexecute'

..almost as if the previous query hasn't been executed yet or w/e. I'm not using MULTI_STATEMENTS nor have set any options in particular. Simply starting the script again may result in it going all the way through without any issue or randomly stop once again at one of the queries. I tried waiting for !.more_results? but that didn't achieve anything. Using short sleep()s in between the queries seems to help, making me think once again it's a matter of queries still waiting to be executed, yet I couldn't find anything that could let me choose to wait for the query to be executed before proceeding.

mysql2 (0.4.2)
ruby 1.9.3p194 (2012-04-20 revision 35410) [x86_64-linux]

Any help is greatly appreciated.

EDIT: Little note, using mysql gem (the outdated one) goes flawless, although outdated.

[sodabrew]

Resolved by #705 will be in the 0.4.3 release in a few days.

[daveroberts]

I'm using 0.5.2 and still getting this error

[markkuit]

Could you show us the stack trace (and, possibly, the snippet) so we can check where it's happening?

[psulightning]

I am also getting this issue still. I have multiple prepared statements that I run in a row.
Gem stack trace as follows:

$errid:(020023) $errstatus:(500) $errtxt:(Commands out of sync; you can't run this command now)
".rbenv/versions/2.6.5/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/lib/mysql2/statement.rb:7:in `_execute'",
".rbenv/versions/2.6.5/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/lib/mysql2/statement.rb:7:in `block in execute'",
".rbenv/versions/2.6.5/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/lib/mysql2/statement.rb:6:in `handle_interrupt'",
".rbenv/versions/2.6.5/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/lib/mysql2/statement.rb:6:in `execute'"

I was originally setting variables with the client.prepare in succession before calling execute, but I have changed that to be prepare-execute, prepare-execute order. I also have client.abandon_results! in an ensure.

[markkuit]

Could you provide us with the relevant code snippet?

[psulightning]

initial design

# params - hash 
# conn - Mysql2::Client
# db_name - string
# proc - string

def execute(conn, params, db_name, proc)
  params.each do |k, v|
    next if !v.is_a?(TrueClass) && !v.is_a?(FalseClass)

    params[k] = v ? 'on' : 'off'
  end

  mapped_keys = params.keys.map { |k| "@#{k}" }

  # setup parameters
  set_stmt = conn.prepare('set ' + mapped_keys.map { |k| "#{k} = ?" }.join(', ')) if params.present?

  exec_stmt = conn.prepare("call #{db_name}.#{proc}(" + mapped_keys.join(', ') + ')')

  result_set = params.keys.collect { |k| "@#{k}" if k =~ /^out/ }.compact
  result_stmt = conn.prepare('select ' + result_set.join(', ')) if result_set.present?

  set_stmt.execute(*params.values)
  begin
    query_results = exec_stmt.execute
  rescue => e
    # log out Commands out of sync
  end

  if result_stmt.present?
    logger.log 'retrieving results from environment'
    begin
      query_results = result_stmt.execute
    rescue => e
      log_error(logger, e, error_base + 2, proc, result_set)
      return {success: false}
    end
  end

  if query_results.present?
    logger.log 'processing procedure results'
    if block_given?
      query_results.each do |row|
        yield row
      end
    else
      params.each_key do |key|
        next unless key.match(/^out/)

        results[key] = query_results.first["@#{key}"].to_s
      end
    end
  end
  results

ensure
  # drain any extra results that may be present
  while conn.next_result
    query_results = conn.store_result
    next if query_results.blank?

    query_results.each do |row|
      logger.log "Draining results: #{row}"
    end
  end
end

That is the gist of it. I have tried moving the prepare-executes in sequence so the set prepare and execute together, call stored procedure prepare and execute together, etc. I have also tried with abandon_results! after each execute and also setting the multiple data sets flag. I was looking at https://dev.mysql.com/doc/c-api/5.7/en/c-api-prepared-call-statements.html to see if there was anything in the client.c file that might be missing.

[psulightning]

I should add that I have a connection pool that will reuse the client objects in a multi threaded application. Not sure if maybe hitting a limit on number of prepared statements or something like that.

[sodabrew]

Preparing a statement for each query is not efficient; you're better off constructing a SQL string in this case. You may prefer the type safety of letting the library do the escaping for you, but the number of back-and-forth server interactions and the allocation of memory on the server to hold the prepared statement is going to slow you down.

Using the same handle from different threads requires locking. The handle is stateful, so if a query is sent and results are being fetched by one thread, a second thread cannot issue a new query on that same handle. That will result in a command out of sync error.

A pattern I've seen is to "check out" a handle, use it, then "release" it back to the pool. The checkout needs a mutex so that you're not relying on Ruby's GIL because it is going away eventually and is not present at all in some of the alternative implementations.