Mysql2::Client::MULTI_STATEMENTS and abandon_results!

Posted on by Michael Orr

If you are using the Mysql2 ruby gem, you have a need to use the Mysql2::Client::MULTI_STATEMENTS flag to enable multiple query statements, and you do not care getting back the results from those queries— then you also need to know about the abandon_results! method. You need to call this method on your mysql connection variable after you issue a query call with multiple statements to get your connection to reset it’s state to allow you to keep issuing queries.

First be sure to add the MULTI_STATEMENTS flag to your connection.

I found documentation on the MULTI_STATEMENTS flag with a quick Google search and quickly ran a little test in irb. I gave a string of multiple queries to the query method and it worked. My values had been updated in the database.

Then I integrated the code with the application I was building and started it up. In this case the code existed inside of a loop subscribed to a queue. The first messages came in, a set of queries was created, and they executed just fine. More messages came in and when it was time to execute the second set of queries my code rescued from an error with the class ‘Mysql2::Error’ and the message ‘closed MySQL connection’.

I did a bit more digging on Google. The ‘Mysql2::Client::MULTI_STATEMENTS’ search led me to the Mysql2::Client documentation, which had this to say:

#abandon_results! -> Object When using MULTI_STATEMENTS support, calling this will throw away any unprocessed results as fast as it can in order to put the connection back into a state where queries can be issued again.

So I added a call to the abandon_results! method just after I make a call to the query method with multiple queries. This causes the MySQL connection to throw away all the result handles that I don’t handle the data from and put itself back in the state where it can continue to receive and execute queries. Here is a small example showing how this would be done.

comments powered by Disqus