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?

score 7 · Accepted Answer · answered Jun 28 '12 at 14:26

7

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

answered Jun 28 '12 at 14:26

Marklar

1,056
2
13
31

Note: `db_write` there is a `ActiveRecord::Base.connection.raw_connection` – Envek Apr 26 '17 at 11:48

score 1 · Answer 2 · answered Jun 26 '12 at 09:23

1

Why Dont you just ::

No need to run it multiple times ....

UPDATE pew SET x = 10 WHERE x IS NULL

answered Jun 26 '12 at 09:23

Sashi Kant

13,277
9
44
71

1

"x = 10" is just an example I put here, in my actual code im setting this to something different for each row. – Marklar Jun 26 '12 at 09:25
does the value for setting in the table comes from another table? – Sashi Kant Jun 26 '12 at 09:28
Its calculated by a couple of other columns in a couple of other tables. I could write a mysql function to do it but I'm hoping to get a solution to doing this in Ruby as it makes my life easier if I can use a few existing rubygems to do most of the work for me. – Marklar Jun 26 '12 at 09:33

score 1 · Answer 3 · answered Dec 16 '14 at 06:49

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

score 1 · Answer 4 · answered Jul 06 '18 at 13:03

1

Before the next SQL statement write the following command.

ActiveRecord::Base.connection.raw_connection.abandon_results!

It will enable a new SQL command execution.

answered Jul 06 '18 at 13:03

aleksi

11
2

Ruby mysql2 multiple statements in single query

4 Answers4

Linked