Mysql2::Error: This connection is still waiting for a result, try again once you have the result

[ghazel]

Easy to reproduce. From a console, run a query and Ctrl-C it while it's running. Then try to submit a second query:

>> client
=> #<Mysql2::Client:0x40254f0>
>> client.query("select sleep(5);")
IRB::Abort: abort then interrupt!!
      from /usr/local/ruby-enterprise-1.8.7-2010.01/lib/ruby/1.8/irb.rb:89:in `irb_abort'
      from /usr/local/ruby-enterprise-1.8.7-2010.01/lib/ruby/1.8/irb.rb:255:in `signal_handle'
      from /usr/local/ruby-enterprise-1.8.7-2010.01/lib/ruby/1.8/irb.rb:66
      from (irb):2:in `call'
      from (irb):2:in `query'
      from (irb):2
>> client.query("select sleep(2);")
Mysql2::Error: This connection is still waiting for a result, try again once you have the result
      from (irb):3:in `query'
      from (irb):3

The client object remains broken forever - long after the initial query would have returned.

[sheuer]

I'm also having this problem.

[ghazel]

This also happens when using Timeout:

>> Timeout.timeout(1) { client.query("select sleep(5);"); }
Timeout::Error: execution expired
        from /usr/local/ruby-enterprise-1.8.7-2010.01/lib/ruby/1.8/timeout.rb:60
        from (irb):17
>> Timeout.timeout(1) { client.query("select sleep(5);"); }
Mysql2::Error: This connection is still waiting for a result, try again once you have the result
        from (irb):18:in `query'
        from (irb):18
        from /usr/local/ruby-enterprise-1.8.7-2010.01/lib/ruby/1.8/timeout.rb:62:in `timeout'
        from (irb):18

[cpg]

Same here, I had to press ctrl-c as things were becoming way too slow (we were doing some load testing). This is a reliability issue, as sooner or later things can get interrupted under load, etc. An app that needs to be available reliably cannot afford to have something like this lurking. Let us know how we can help fix this. The repro above with Timeout looks like a great start!

I am on RoR 3.0.3 with rack 1.2.1, ruby ruby-1.8.7.302-2.fc14.i686 and mysql2 0.2.6

[schugabe]

I have a similar problem.
I'm sending out a bunch of emails and during the generation of those I get a "Exception ActiveRecord::StatementInvalid -> : execution expired" error. This happen every time but not on the same records.

After this error occurred all following queries fail with "Exception ActiveRecord::StatementInvalid -> Mysql2::Error: This connection is still waiting for a result, try again once you have the result"

RoR 3.0.3 in a Stalker background process, ruby 1.9.2; mysql2 0.2.6

[iGEL]

I've run into this problem in production. I'm using Timeout, so a query could have been interrupted.

Rails 3.0.3, Ruby 1.8.7, mysql2 0.2.6

[conorh]

Using the Mysql2::EM::Client.new class and running into this too. Ruby 1.9.2, Rails 2.3.9, EventMachine 0.12.10.

[ponny]

Same. Rails3/1.9.2.

[JoshMcKin]

Same time. Rails 3 (threadsafe), 1.9.2

[deltadd]

Same. Rails3.0.6/1.9.2.

[jlecour]

Same here ; Rails 3.0.6, Ruby 1.8.7

[jlecour]

When used in Rails with ActiveRecord, it's possible to recover from this exception.

# This is just to abort a query
Timeout.timeout(1) { MyModel.connection.execute("select sleep(5);"); }

# This is the recovering hack
begin
  # Any query on a model
  MyModel.first
rescue ActiveRecord::StatementInvalid => ex
  # Since all Mysql2 errors are wrapped into ActiveRecord::StatementInvalid
  # we have to retry only for this kind of error
  if ex.message["Mysql2::Error: This connection is still waiting for a result"]
    @retries ||= 0
    # Retry only 3 times
    puts @retries
    if @retries < 3
      @retries += 1
      MyModel.connection.reconnect!
      retry
    else
      raise
    end
  else
    raise
  end
else
  @retries = 0
end

[ghazel]

Here's a monkey-patch to auto-reconnect when this occurs:

module ActiveRecord
  module ConnectionAdapters
    class Mysql2Adapter

      def query_with_reconnect(sql)
        @connection.query(sql)
      rescue Mysql2::Error => exception
        if exception.message.starts_with? "This connection is still waiting for a result"
          reconnect!
          retry
        end
        raise
      end

      # Executes the SQL statement in the context of this connection.
      def execute(sql, name = nil)
        # make sure we carry over any changes to ActiveRecord::Base.default_timezone that have been
        # made since we established the connection
        @connection.query_options[:database_timezone] = ActiveRecord::Base.default_timezone
        if name == :skip_logging
          query_with_reconnect(sql)
        else
          log(sql, name) { query_with_reconnect(sql) }
        end
      rescue ActiveRecord::StatementInvalid => exception
        if exception.message.split(":").first =~ /Packets out of order/
          raise ActiveRecord::StatementInvalid, "'Packets out of order' error was received from the database. Please update your mysql bindings (gem install mysql) and read http://dev.mysql.com/doc/mysql/en/password-hashing.html for more information.  If you're on Windows, use the Instant Rails installer to get the updated mysql bindings."
        else
          raise
        end
      end
    end
  end
end

[ramsingla]

Please have a look at #168. Does this solve the issue you guys are facing? Looking for your feedback.

[ghazel]

@ramsingla: I think you've addressed a different issue with the same error message. I'm using mysql2_adapter.rb not em_mysql2_adapter.rb so any EM related solution doesn't apply.

[mhoran]

This is also an issue if you set read_timeout in database.yml, as the connection is closed but still waiting for a result. This is with the non-EM client.