Overview
While the MySQL Auto-Reconnect feature is both handy and interesting, it has to be used with great care, along with an understanding of the behavioral implications and side effects.
In this blog post we will discuss some of the risks of using the MySQL driver Auto-Reconnect feature, and what happens when the internal version of it is enabled in the Tungsten Connector.
This blog is related to our latest Continuent white paper: The Basic Guide to Proxies for MySQL Databases - please review this white paper if you're interested in the details!
Auto-Reconnect: Drawbacks & Disadvantages
By itself, the MySQL Server Auto-Reconnect implementation is risky, and the MySQL Reference Manual even states: "Use of the autoReconnect option is not recommended becausethere is no safe method of reconnecting to the MySQL server without risking some corruption of the connection state or database state information" (https://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-troubleshooting.html)
Why is this? When doing a write operation, if you get a failure, there is no immediate way for the application to know whether your write has reached the database or not. If a subsequent operation relies on this write, you simply end up risking data corruption.
Even on the read-only front, a long-running or blocking request that is forcibly killed might be replayed right away. You can reproduce this easily by trying to kill a "select sleep(30)" query running through the MySQL CLI, and see it redoing the sleep immediately after being killed. In the following example, a 30s sleep lasts 48s, showing the Auto-Reconnect feature re-running the sleep(30); after the connection was re-established:
mysql> select now(); select sleep(30); select now();
+---------------------+
| now() |
+---------------------+
| 2024-05-30 15:08:29 |
+---------------------+
1 row in set (0.00 sec)
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY.
Connection id: 126383
Current database: *** NONE ***
+-----------+
| sleep(30) |
+-----------+
| 0 |
+-----------+
1 row in set (47.79 sec)
+---------------------+
| now() |
+---------------------+
| 2024-05-30 15:09:17 |
+---------------------+
1 row in set (0.00 sec)Furthermore, there are a number of temporary settings and objects that are lost or reset when a connection is closed (which is what happens to invoke auto-reconnect). Here are just a few:
- temporary tables
- table locks
- session information
- prepared statements
- etc.
The full list can be found here: https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html.
On the Tungsten side, our Connector has an internal reconnection feature that allows seamless failover and switch. A subset of these limitations are still applicable to this feature. However, the Connector is more "clever" and only reconnects when it is safe to do so (connection broken before executing the request, non-autocommit writes, etc.) Also, the connector retains and replays session-specific objects like temporary tables and session variables on the new connection, which makes this internal feature safer for applications.
Conclusion
In this blog post, we discussed some of the risks of using the MySQL client Auto-Reconnect feature, and how automatic reconnection is handled in the Tungsten Connector.
We saw that MySQL documentation strongly discourages its use, but there are still a few use cases where the Auto-Reconnect feature could be used safely:
- short read-only operations with no risk of being blocked by any lock
- independent updates like writing statistics in a table
- prototyping, testing and debugging through test application or command line interface
- and more generally, having an Auto-Reconnect-aware application that would check that writes have reached the database before relying upon the written data.
Comments
Add new comment