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

[bala1203006]

Hi all,

We're getting the below error on the production site.

[ActiveRecord::StatementInvalid - Mysql2::Error: This connection is still waiting for a result, try again once you have the result: ROLLBACK]: /home/site/wwwroot/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in _query' | /home/site/wwwroot/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:131:in block in query' | /home/site/wwwroot/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in handle_interrupt' | /home/site/wwwroot/vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.2/lib/mysql2/client.rb:130:in query' | /home/site/wwwroot/vendor/bundle/ruby/2.6.0/gems/activerecord-5.2.3/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:187:in block (2 levels) in execute'`

Analyzed few discussion about this bug, as per guidance one of the suggestion handled the exception by using below code. It will disconnect the pool connection.

module ActiveRecord
    module Mysql2ReadonlyReconnect
      def execute(*)
        super
      rescue Mysql2::Error, ActiveRecord::StatementInvalid => e
        # Since all Mysql2 errors are wrapped into ActiveRecord::StatementInvalid
        # we have to retry only for this kind of error
        if e.message =~ /This connection is still waiting for a result/ || e.message.include?('The MySQL server is running with the --read-only option')
          Rails.logger.error("Mysql2::Error #{e.message} : disconnecting before raising error")
          pool.disconnect!
        end
        raise
      end
    end
end
ActiveRecord::ConnectionAdapters::Mysql2Adapter.prepend(ActiveRecord::Mysql2ReadonlyReconnect)

FYR: #772

We using below gems,

ruby - 2.6.2
rails - 5.2.3
puma - 4.3.8
mysql2 - 0.5.2

Is recently released mysql2 - 0.5.4 having fix for this issue? please advise here. We need your help.

[sodabrew]

tl;dr - look for circumstances by which a connection is being returned to the pool while still in an in-use state. Could be an exception handler, could be improper logic, could be a background worker, check a few things.

The problem isn't at the point of this error, it's in the prior use. A connection handle is getting checked out from the connection pool, a transaction is started, a query is issued, then the connection is returned to the pool.

The query result has not been read and the transaction has not been closed, which may also be causing you to lose any changes in those open transactions.

The error handler you propose would close out the connection on the subsequent use, but doesn't fix the code that is leaving the handles in an in-use state when they are returned to the connection pool.

[bala1203006]

Thanks @sodabrew ,I want to share some more informations about this issue.

It's happening whenever there is an race condition. 16 requests per second. It's happening when via wss call. We using action cable. So requests are sharing by wss protocol.

We saw the memory level and cpu level of database. There is no evidence of abnormalities in the app services. It my concern about this issue. We analysed few discussion about this bug, based on the suggested solutions we added exception handler.
FYR: #772

You can share your thoughts here?

[sodabrew]

Yes, this can be caused by a race condition. Your code is responsible for managing the connection pool and for handling how threads check out a connection and return it to the pool.

[bala1203006]

Okay @sodabrew let me analyse the how to handle pool connection in the race condition.

Thanks for your support.