Prepared statements randomly fail to return results

[YujiSoftware]

Environment

• Ruby (3.2.3)
• mysql2 (0.5.6)

Test Code

require 'mysql2'

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => '')
stmt = client.prepare("SELECT 1 AS FOUND WHERE 1 = ?")

[0, 1].cycle.each do |i|
  found = 0
  stmt.execute(i).each do |row|
     found = row["FOUND"]
  end

  puts "where 1 = #{i}, found = #{found}"
  raise "Mismatch." if i != found
end

Note

If line 6 is [0].cycle.each or [1].cycle.each, this issue does not occur.

Expected Result

Repeat where 1 = 0, found = 0 and where 1 = 1, found = 1

Actual Result

Sometimes returns where 1 = 1, found = 0

... Snip ...
"where 1 = 1, found = 1"
"where 1 = 0, found = 0"
"where 1 = 1, found = 1"
"where 1 = 0, found = 0"
"where 1 = 1, found = 0"
main.rb:13:in `block in <main>': Mismatch. (RuntimeError)
	from main.rb:6:in `cycle'
	from main.rb:6:in `each'
	from main.rb:6:in `<main>'

or raise Mysql2::Error

... Snip ...
where 1 = 0, found = 0
where 1 = 1, found = 1
where 1 = 0, found = 0
where 1 = 1, found = 1
where 1 = 0, found = 0
/var/lib/gems/3.2.0/gems/mysql2-0.5.6/lib/mysql2/statement.rb:5:in `each': Attempt to read a row while there is no result set associated with the statement (Mysql2::Error)
	from /var/lib/gems/3.2.0/gems/mysql2-0.5.6/lib/mysql2/statement.rb:5:in `_execute'
	from /var/lib/gems/3.2.0/gems/mysql2-0.5.6/lib/mysql2/statement.rb:5:in `block in execute'
	from /var/lib/gems/3.2.0/gems/mysql2-0.5.6/lib/mysql2/statement.rb:4:in `handle_interrupt'
	from /var/lib/gems/3.2.0/gems/mysql2-0.5.6/lib/mysql2/statement.rb:4:in `execute'
	from main.rb:8:in `block in <main>'
	from main.rb:6:in `cycle'
	from main.rb:6:in `each'
	from main.rb:6:in `<main>'

Workarounds

Don't use prepared statements, or Explicitly call Result#free.

results = stmt.execute(i)
results.each do |row|
  # each row
end
results.free

Cause

I tried running this code with ltrace -f -x '@libmysql*' -x '@mysql2*' ruby test.rb .

require 'mysql2'

client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => 'mysql')
stmt = client.prepare("SELECT 1 AS FOUND WHERE 1 = ?")
stmt.execute(1).each do |row|
  puts row
end
sleep(30)

It shows that rb_mysql_result_free_result is called in rb_mysql_result_each.

... Snip ...
[pid 13990] rb_mysql_result_each@mysql2.so(0, 0, 0x71bc740be6c0, 0x71bc7405cdc0 <unfinished ...>
[pid 13990] mysql_stmt_num_rows@libmysqlclient.so.21(0x6153f6bd4200, 0xfb010c, 0, 0x7ffe09024fb0) = 1
[pid 13990] mysql_fetch_fields@libmysqlclient.so.21(0x6153f6bd51f0, 0x71bc74063cc0, 0x71bc758ff0f8, 0) = 0x6153f6bd47b8
[pid 13990] rb_mysql_result_fetch_row_stmt@mysql2.so(0x71bc740be6c0, 0x6153f6bd47b8, 0x7ffe09025010, 0 <unfinished ...>
[pid 13990] mysql_num_fields@libmysqlclient.so.21(0x6153f6bd51f0, 0x71bc74063cc0, 0, 0)       = 1
[pid 13990] mysql_stmt_bind_result@libmysqlclient.so.21(0x6153f6bd4200, 0x6153f6bd5430, 0, 0) = 0
[pid 13990] nogvl_stmt_fetch@mysql2.so(0x6153f6bd4200, 0, 0, 0 <unfinished ...>
[pid 13990] mysql_stmt_fetch@libmysqlclient.so.21(0x6153f6bd4200, 0, 0, 0)                    = 0
[pid 13990] <... nogvl_stmt_fetch resumed> )                                                  = 0
[pid 13990] rb_mysql_result_fetch_field@mysql2.so(0x71bc740be6c0, 0, 0, 0 <unfinished ...>
[pid 13990] mysql_fetch_field_direct@libmysqlclient.so.21(0x6153f6bd51f0, 0, 0, 0)            = 0x6153f6bd47b8
[pid 13990] <... rb_mysql_result_fetch_field resumed> )                                       = 0x71bc740be558
[pid 13990] <... rb_mysql_result_fetch_row_stmt resumed> )                                    = 0x71bc740be5a8
[pid 13990] rb_mysql_result_free_result.part.0@mysql2.so(0x6153f6bd5260, 0x71bc740be5a8, 0, 0x10000 <unfinished ...>
[pid 13990] mysql_stmt_free_result@libmysqlclient.so.21(0x6153f6bd4200, 0x71bc740be5a8, 0, 0x10000) = 0
[pid 13990] decr_mysql2_stmt@mysql2.so(0x6153f6bd41c0, 14, 0, 1)                              = 0x6153f6bd4200
[pid 13990] mysql_free_result@libmysqlclient.so.21(0x6153f6bd51f0, 0x6153f6932740, 24, 0xace40) = 7
[pid 13990] <... rb_mysql_result_free_result.part.0 resumed> )                                = 7
[pid 13990] <... rb_mysql_result_each resumed> )                                              = 0x71bc740be620
[pid 13990] <... rb_mysql_stmt_execute resumed> )                                             = 0x71bc740be6c0
[pid 13990] rb_mysql_result_each@mysql2.so(0, 0x71bc758ff058, 0x71bc740be6c0, 0x71bc7405cdc0) = 4
{"FOUND"=>1}

However, when changed to stmt.execute(0).each, rb_mysql_result_free_result is not called.

... Snip ...
[pid 13990] rb_mysql_result_each@mysql2.so(0, 0, 0x7669751de688, 0x766975160dc0 <unfinished ...>
[pid 13990] mysql_stmt_num_rows@libmysqlclient.so.21(0x5f6fe2c7c920, 0xfb010c, 0, 0x7ffd037e9ae0) = 0
[pid 13990] <... rb_mysql_result_each resumed> )                                              = 0x7669751de5e8
[pid 13990] <... rb_mysql_stmt_execute resumed> )                                             = 0x7669751de688
[pid 13990] rb_mysql_result_each@mysql2.so(0, 0x766976c16058, 0x7669751de688, 0x766975160dc0) = 0x7669751de5e8

When comparing with the code, it seems that if there are 0 rows in the result, the cache hit process (wrapper->lastRowProcessed == wrapper->numberOfRows) is entered at this point, and rb_mysql_result_free_result is skipped.

mysql2/ext/mysql2/result.c

Lines 1018 to 1057 in 57b8df1

	if (args->cacheRows && wrapper->lastRowProcessed == wrapper->numberOfRows) {
	/* we've already read the entire dataset from the C result into our */
	/* internal array. Lets hand that over to the user since it's ready to go */
	for (i = 0; i < wrapper->numberOfRows; i++) {
	rb_yield(rb_ary_entry(wrapper->rows, i));
	}
	} else {
	unsigned long rowsProcessed = 0;
	rowsProcessed = RARRAY_LEN(wrapper->rows);
	fields = mysql_fetch_fields(wrapper->result);
	for (i = 0; i < wrapper->numberOfRows; i++) {
	VALUE row;
	if (args->cacheRows && i < rowsProcessed) {
	row = rb_ary_entry(wrapper->rows, i);
	} else {
	row = fetch_row_func(self, fields, args);
	if (args->cacheRows) {
	rb_ary_store(wrapper->rows, i, row);
	}
	wrapper->lastRowProcessed++;
	}
	if (row == Qnil) {
	/* we don't need the mysql C dataset around anymore, peace it */
	if (args->cacheRows) {
	rb_mysql_result_free_result(wrapper);
	}
	return Qnil;
	}
	if (args->block_given) {
	rb_yield(row);
	}
	}
	if (wrapper->lastRowProcessed == wrapper->numberOfRows && args->cacheRows) {
	/* we don't need the mysql C dataset around anymore, peace it */
	rb_mysql_result_free_result(wrapper);
	}
	}

As a result, free is performed later depending on the timing of garbage collection, resulting in strange behavior.

#1258 is related.
However, in most cases, all rows are obtained with .each or .to_a. Therefore, I think that the problem will not occur if free is called even if there are 0 rows in the result.