Ruby mysql2 multiple statements in single query

mysql, mysql2, ruby

Solution

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

Problem

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?

Original source