Query running in async mode, without it being specified.

[64kramsystem]

I'm loading my rails structure.sql file via the mysql2 gem, but the query executes asynchronously, withoutasync: true being specified.

The sql file contains the typical rails content,with the exception of including trigger definitions, resulting in the sequence (CREATE TABLEs... CREATE TRIGGERs... INSERT INTO schema_migrations's).

The connection is established using Mysql2::Client::MULTI_STATEMENTS.

The problem is that when I execute Mysql2::Client#query(), nil is returned immediately, while I can see the sql being executed in the background.
Out of curiosity, I've tried adding async: true and waiting for client.async_result to return some value, but even when the background work was finished, such call was returning nil.

Based on what I see in the gem documentation, this should not happen.

My O/S is an Ubuntu 64 15.10, with the standard libmysqlclient packages installed.
I've tried with the 0.3.17 and 0.4.2, and the behavior is the same.

[justincase]

AFAIK that's simply how MULTI_STATEMENTS works. MySQL executes all statements in the query and the developer is responsible for iterating over the result sets.

The reason client.query returns a nil value is likely because the first statement in your query doesn't produce a result set. For example, CREATE TABLE and INSERT INTO only return affected row counts.

client.query("INSERT INTO foo VALUES ('bar'); SELECT SLEEP(20)")
# => nil
client.affected_rows
# => 1

sleep 10
# => 10 

Time.now
# 2016-01-22 21:47:09 UTC
# => nil

client.next_result # blocks until the remaining 10 seconds pass
# => true

Time.now
# => 2016-01-22 21:47:19 UTC

client.store_result
# => #<Mysql2::Result:0x007fbdfa29db90

The async setting only affects the first statement in the query:

client.query("SELECT SLEEP(20); INSERT INTO foobar VALUES ('bar'), ('baz')", async: false)
# => Blocks for 20 seconds

client.query("SELECT SLEEP(20); INSERT INTO foobar VALUES ('bar'), ('baz')", async: true)
# => Returns `nil` immediately

client.async_result
# => #<Mysql2::Result:0x007fbdfab339a8

client.next_result
# => true

client.affected_rows
# => 2

/cc #356

[sodabrew]

Interesting findings, @justincase - thank you - I guess I would ask, is there anything that mysql2 gem is doing obviously wrong, or should do differently, or should be better documented?

[64kramsystem]

After reviewing @justincase comment, I think that it would be helpful if the documentation would do a bit of clarification regarding the a/synchronous nature of the MULTI_STATEMENTS execution, mentioning that (if what I think is true), the first statement is executed synchronously, and the following ones asynchronously. This would also explain the behaviour of other "curios" cases like:

• client.query("SELECT SLEEP(0); SELECT SLEEP(5); CREATE TABLE pizza(id INT)")
• client.query("SELECT SLEEP(5); SELECT SLEEP(0); CREATE TABLE pizza(id INT)")

where, without calling next_result, the first call returns immediately, but the second waits.

[Edit: made more precise, and summarized]

[justincase]

As fas as I can tell the implementation is working as intended. We could add a note describing the effects of the async option on MULTI_STATEMENTS.

[sodabrew]

PR #807 has proposed code to make next_result? non-blocking.