Ruby mysql2 multiple statements in single query

Question

I have an issue where I'm updating millions of rows in my DB, so rather than updating each one individually I want to join groups of ~1000 statements into a single query.

I have enabled MULTI_STATEMENTS like so

client = Mysql2::Client.new(:host => 'localhost', :database => 'mehdb', :username => "root", :password => "", :flags => Mysql2::Client::MULTI_STATEMENTS)

Here's an example of the code I'm running

sql = "SELECT id, x FROM pew WHERE x IS NULL LIMIT 1000"

results = db_read.query(sql)

while results.count > 0

  updates = ''

  results.each do |r|
    updates += "UPDATE pew SET x = 10 WHERE id = #{r['id']};"
  end

  db_write.query(updates) unless updates.empty?

  results = db_read.query(sql)
end

This work's alright during the first run through but then when it fires off the second set of updates I get this error message

`query': Commands out of sync; you can't run this command now (Mysql2::Error)

Has anyone come across this before? Or any advise on another approach?

Answer 1

Short answer to this problem is when MULTI_STATEMENTS are enabled mysql expects you to handle the result of your query.

A quick fix is to do something similar to this after each set of multiple update statements

  while db_write.next_result
    db_write.store_result rescue ''
  end

Answer 2

Why Dont you just ::

No need to run it multiple times ....

UPDATE pew SET x = 10 WHERE x IS NULL

Answer 3

As I understand it, this is a result Mysql internal protection - You are in the midst of querying DB, and streaming the results, if during that you'll also update the results, you can not guarantee any level of consistency.

If you KNOW you are safe to make changes as part of the flow, you could work around that by simply creating a second connection:

reading_client = Mysql2::Client.new(:host => 'localhost', :database => 'mehdb', :username => "root", :password => "", :flags => Mysql2::Client::MULTI_STATEMENTS)

updating_client = Mysql2::Client.new(:host => 'localhost', :database => 'mehdb', :username => "root", :password => "", :flags => Mysql2::Client::MULTI_STATEMENTS)


sql = "SELECT id, x FROM pew WHERE x IS NULL LIMIT 1000"

results = reading_client.query(sql)

while results.count > 0

  updates = ''

  results.each do |r|
    updates += "UPDATE pew SET x = 10 WHERE id = #{r['id']};"
  end

  updating_client.query(updates) unless updates.empty?

  results = reading_client.query(sql)
end

Answer 4

Before the next SQL statement write the following command.

ActiveRecord::Base.connection.raw_connection.abandon_results!

It will enable a new SQL command execution.