Rails MySQL multi-statement SQL execution fails

Question

Based on trying to emulate a transaction safe sequence in MySQL https://stackoverflow.com/a/2292030/48659 and executing SQL statements How do you manually execute SQL commands in Ruby On Rails using NuoDB

I'm trying to do the following:

ActiveRecord::Base.connection.execute("UPDATE sequence SET seq_current = (@next := seq_current + 1) WHERE seq_name = 'foo'; SELECT @next;")

But now I find that the Mysql2 driver doesn't cope with multiple statements!

Is there a good solution I can use? If I split the two statements:

ActiveRecord::Base.connection.execute("UPDATE sequence SET seq_current = (@next := seq_current + 1) WHERE seq_name = 'foo';")
ActiveRecord::Base.connection.execute("SELECT @next;")

It works in a console, but can I be sure this is really safe and nothing can else can sneak in and execute in the middle of these two statements?

Are there better solutions? (I don't mind using PostgreSQL if that would work; not tried it yet.)

score -1 · Answer 1 · answered Jul 13 '16 at 22:49

I am not 100% that this can work - could transactions help you?

ActiveRecord::Base.transaction do
  ActiveRecord::Base.connection.execute("UPDATE sequence SET seq_current = (@next := seq_current + 1) WHERE seq_name = 'foo';")
  ActiveRecord::Base.connection.execute("SELECT @next;")
end

Rails MySQL multi-statement SQL execution fails

1 Answers1