Ruby binding throws TRILOGY_INVALID_SEQUENCE_ID when a multi-statement is followed by another query

[iandelahorne]

👋 Hello! We discovered that the ruby binding will throw TRILOGY_INVALID_SEQUENCE_ID when executing a multi-statement query followed directly another query, without fetching all results with next_result.

For example, the following snippet works fine:

client.query("SELECT 1; SELECT 2;")
client.next_result while client.more_results_exist?
client.query("SELECT 3")

This next snippet, however, throws TRILOGY_INVALID_SEQUENCE_ID

client.query("SELECT 1; SELECT 2;")
client.query("SELECT 3")

Here's a script to reproduce:

require "trilogy"

config = { host: "127.0.01", port: 3306, username: "root", password: "", ssl: false, multi_statement: true}
client = Trilogy.new config

# OK
client.query("SELECT 1; SELECT 2;")
client.next_result while client.more_results_exist?
client.query("SELECT 3")

# Not OK
client.query("SELECT 1; SELECT 2;")
client.query("SELECT 3")

Running this yields:

$ ruby test.rb
test.rb:13:in `query': trilogy_query_recv: TRILOGY_INVALID_SEQUENCE_ID (Trilogy::QueryError)
	from test.rb:13:in `<main>'

We're seeing this with Trilogy 2.8.1 against MySQL 8.3.0 on OSX using Ruby 3.3.2 (ruby 3.3.2 (2024-05-30 revision e5a195edf6) [arm64-darwin23])

From what I can tell (i'm not familiar with the codebase and it's been a minute since I've written C) it seems that something isn't getting reset correctly when client.query is called and trilogy_packet_parser_execute() isn't agreeing with it's current state vs what is coming in off the wire.

[composerinteralia]

I think that is expected (although we might be able to improve the error message and documentation around this). We need to read all the results before we can make another query, and client.next_result while client.more_results_exist? accomplishes that.

If you do the same sort of thing with the mysql2 gem the error message is Commands out of sync; you can't run this command now which is probably a bit more user friendly.

[iandelahorne]

Thanks for the clarification @composerinteralia - I agree that the error documentation could stand to be improved.

To add some more context:
We ran in to this in a situation where we were issuing multi-statement DELETEs in a Rails fixture teardown, but weren't checking the results.
As part of an experiment, we added resource group optimizer hints (/*+ RESOURCE_GROUP(foo) */) with a non-existent resource group. The queries execute cleanly but set the warning flag in the response. It seems that in our case, something in the chain isn't checking for all results (since it expects no results as it's a big delete), and when ActiveRecord::ConnectionAdapters::AbstractMysqlAdapters#handle_warnings calls SHOW WARNINGS we get the TRILOGY_INVALID_SEQUENCE_ID error.

I'll follow up internally to make sure we're handling this scenario correctly if this is indeed intended behavior. I can confirm the above test case also fails in MySQL.

[composerinteralia]

Ah, interesting. I wonder if rails/rails@86f0259 will have solved that.

[iandelahorne]

Thanks for sharing that - it doesn't immediately solve it in our codebase, but i'll try to make a smaller test case with a vanilla Rails installation and see if that would fix it.