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?

Marklar · Accepted Answer · 2012-06-28 14:26:40Z

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, 2012 at 14:26

Marklar

1,0562 gold badges13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Envek Over a year ago

Note: db_write there is a ActiveRecord::Base.connection.raw_connection

Sashi Kant · Accepted Answer · 2012-06-26 09:23:18Z

1

Why Dont you just ::

No need to run it multiple times ....

UPDATE pew SET x = 10 WHERE x IS NULL

answered Jun 26, 2012 at 9:23

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

3 Comments

Marklar Over a year ago

"x = 10" is just an example I put here, in my actual code im setting this to something different for each row.

Sashi Kant Over a year ago

does the value for setting in the table comes from another table?

Marklar Over a year ago

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.

JAR.JAR.beans · Accepted Answer · 2014-12-16 06:49:49Z

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

aleksi · Accepted Answer · 2018-07-06 13:03:08Z

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 6, 2018 at 13:03

aleksi

112 bronze badges

Collectives™ on Stack Overflow

Ruby mysql2 multiple statements in single query

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related