What is the reconnect option used for? How can I "reopen" a closed client?

[bbuchalter]

Given the following:

config = {
  username: "root",
  password: "",
  host: "localhost",
  reconnect: true
}

myclient = Mysql2::Client.new(config)
myclient.query("SELECT sleep(0) as result")
myclient.close
myclient.query("SELECT sleep(0) as result")

I would expect the reconnect option to allow the connection to be reused after being closed, but instead:

Traceback (most recent call last):
	4: from lib/demo.rb:30:in `<main>'
	3: from /usr/local/lib/ruby/gems/2.5.0/gems/mysql2-0.5.1/lib/mysql2/client.rb:130:in `query'
	2: from /usr/local/lib/ruby/gems/2.5.0/gems/mysql2-0.5.1/lib/mysql2/client.rb:130:in `handle_interrupt'
	1: from /usr/local/lib/ruby/gems/2.5.0/gems/mysql2-0.5.1/lib/mysql2/client.rb:131:in `block in query'
/usr/local/lib/ruby/gems/2.5.0/gems/mysql2-0.5.1/lib/mysql2/client.rb:131:in `_query': MySQL client is not connected (Mysql2::Error)

The use case I was hoping to implement was something similar to File.open when using a block argument, which ensures the file handle is closed when the block terminates.

Similarly, I'd like to ensure my Mysql2 client connections are closed, while avoiding the need for a new Mysql2::Client.new after each call to close. See a simple demo at https://github.com/bbuchalter/mysql2-yield-connect-demo/blob/master/lib/demo.rb.

Perhaps I'm going about this all wrong, but at this point, I'm more curious than anything and would appreciate any insight into the inner workings of this fine open source project. ❤️

[sodabrew]

You can’t reopen a closed client. The reconnect option allows a client whose connection is unexpectedly broken, such as server restart, or network timeout, to reconnect transparently to run the next query.

[bbuchalter]

Thanks for the quick reply and explanation. Perhaps you can help me understand why these two cases are handled so differently? I naively assumed that if we can reconnect for an unexpectedly broken connection, we could reconnect from an intentionally closed connection. Thanks again!

[sodabrew]

Reconnect is an internal feature of the MySQL client library since time immemorial, and helped a lot of client tool authors not to think about their own error handling, rollback, and reconnect code – way back before MySQL had transactions, reconnect was a pretty safe thing to do. Today it’s not advised, because the client library doesn’t know if your SQL is stateful, such as starting a transaction or setting the locale, and a reconnect in the middle of a transaction would result in the earlier queries rolling back and the later queries executing outside of the transaction! What you’re doing with opening and closing g the connecting before each query is going to make your script slow because connection establishment and login takes time and you’re not amortizing that over a large number of queries issued on the same live link.

[bbuchalter]

I really appreciate this thoughtful reply. I learned a lot. 👍

[froot]

The reconnect option allows a client whose connection is unexpectedly broken, such as server restart, or network timeout, to reconnect transparently to run the next query.

If the client's connection is unexpectedly broken, will it reconnect and re-run the current query or will it fail the current query then reconnect so that the next query runs with the new connection? @sodabrew

[sodabrew]

db = Mysql2::Client.new...
result = db.query... 

<your code does other things for a few minutes and the connection times out or is interrupted by the network, a server restart, etc.>

result2 = db.query...
<reconnect and issue query!>

The MySQL protocol is stateful. If the connection is idle, that is, not currently processing a query or streaming a result, then it can be reconnected. If the connection was in the middle of a query then the query fails with an error. A connection can be idle but it is still stateful: you might have set a locale, or a timezone, or a session variable, and those are all reset upon reconnect.

More details here: https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html

[froot]

I see, so in the above example, both queries would run successfully given that the reconnect succeeds. Thanks @sodabrew

[ram-gudapati]

@sodabrew
In our application we are getting the

WARNING: MYSQL_OPT_RECONNECT is deprecated and will be removed in a future version.
In the documentation I see the following.
Beginning with MySQL 8.0.34, the automatic reconnection feature is deprecated. The related MYSQL_OPT_RECONNECT option is still available but now returns a deprecation warning to the standard error output.
we are using mysql_option MYSQL_OPT_RECONNECT param.
what are alternatives so we get the same behavior without changing our application code to get same behavior as RECONNECT.

[Faq]

@ram-gudapati #1322