Clarification on ProxySQL's OFFLINE_SOFT Behavior and Query Delays

[Rajin Kim]

I hope this message finds you well. I am currently working on a project involving the migration of a MySQL database to another MySQL database using ProxySQL. My setup is illustrated in the attached diagram.

I have been following the method suggested in this article: https://planet.mysql.com/entry/?id=5992282, which involves setting the current database to OFFLINE_SOFT, waiting for some time, and then adding the new database to ProxySQL.

However, I encountered an issue where setting a server to OFFLINE_SOFT does not delay requests from already connected clients. My expectation was that ProxySQL would allow ongoing transactions to complete but would hold any subsequent queries. Specifically, if an application uses a single connection for two transactions and the server's status is set to OFFLINE_SOFT during the first transaction, I expected ProxySQL to hold the second transaction. However, it seems ProxySQL proceeds with the second transaction without delay.

This behavior could lead to issues since application programs typically use a connection pool library (e.g., Hikari CP for Spring applications). The application’s connection pool maintains the connection to ProxySQL (as shown by the blue arrow in the diagram) for extended periods. Thus, after setting the server to OFFLINE_SOFT, the Spring application continues to query the OFFLINE_SOFT database instead of holding the query for the switchover time, resulting in unavoidable connection errors.

I have the following questions:

1. Is my understanding of OFFLINE_SOFT correct? Does ProxySQL not delay requests from already connected clients when a server is set to OFFLINE_SOFT, even if a new transaction starts on the same connection?
2. If this is correct, is there an option or flag to make OFFLINE_SOFT delay queries on reused connections?

I appreciate your assistance and look forward to your response.