Rails 6 MySQL Connection lost after timeout

[reidmorrison]

With Rails 6.0 and Rails 6.1 if a query takes too long and the connection times out, that connection goes bad with no way to clear it without restarting the process.

If we set the read_timeout option in config/database.yml to 5, the expected behavior on Ruby 2.7.3 and Rails 6.1.3.2:

> ActiveRecord::Base.connection.select_one("select sleep(1)")
{
    "sleep(1)" => 0
}

> ActiveRecord::Base.connection.select_one("select sleep(5)")
Traceback (most recent call last):
        1: from (irb):11
ActiveRecord::AdapterTimeout (Mysql2::Error::TimeoutError: Timeout waiting for a response from the last query. (waited 3 seconds))

> ActiveRecord::Base.connection.select_one("select sleep(1)")
{
    "sleep(1)" => 0
}

If we set the socketTimeout option in config/database.yml under properties to 5000, the actual behavior on JRuby 9.2.18.0 and Rails 6.1.3.2:

> ActiveRecord::Base.connection.select_one("select sleep(1)")
{
    "sleep(1)" => 0
}

> ActiveRecord::Base.connection.select_one("select sleep(5)")
Traceback (most recent call last):
        1: from (irb):2:in `evaluate'
ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure)

The last packet successfully received from the server was 3,004 milliseconds ago.  The last packet sent successfully to the server was 3,003 milliseconds ago.

> ActiveRecord::Base.connection.select_one("select sleep(1)")
Traceback (most recent call last):
        1: from (irb):3:in `evaluate'
ActiveRecord::StatementInvalid (ActiveRecord::JDBCError: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.)

With JRuby it keeps failing with No operations allowed after connection closed until the process is restarted.

Calling any of the following methods does not restore the connection:

ActiveRecord::Base.clear_active_connections!
ActiveRecord::Base.clear_all_connections!
ActiveRecord::Base.clear_cache!

[reidmorrison]

Actually in hind sight this is actually a really bad idea, since auto-reconnected sessions are losing their session variables in Rails. It causes more problems than it solves.