This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
Overview
The latest release of Azure Database for MySQL - Flexible Server provides two options for configuring high availability (HA): zone redundant HA and same-zone HA. When you use connection pooling to connect to a Flexible Server instance with an HA configuration, you need to pay special attention to handling failover events. Depending on the type of connection pooler, you may need to clear the connections during the failover to ensure that new connections will point to the new primary server. This article explains how to address these types of issues.
How connection pooling works
In the connection string, the pooling parameter determines whether connection pooling is enabled or not. You can also include other parameters such as ConnectionLifetime to specify the lifetime of the connection in the pool and MinimumPoolSize/MaximumPoolSize to specify the size of the connection pool. For more information, see the web page MySQL Connector/Net connection strings.
The simple code snippet below shows what’s required to open a database connection.
string connstring = String.Format(
"Server={0};Port=3306;Database=weather;Uid={1};Pwd={2};SslMode=Preferred;pooling=true",
Host,
User,
Password);
MySqlConnection conn = new MySqlConnection(connstring);
conn.Open();
When you open a new database connection, a driver resolves the IP address by looking up DNS records and then creates a TCP socket connection to that IP address. However, this process is time consuming.
Connection pooling, which pools existing connections and reuses them for consequent database connections, can help limit the time it takes to complete the process. This technique is widely used and highly recommended for all kinds of applications that connect to MySQL database. When you use connection pooling, however, it’s critical that you pay special attention to handling potential failover events. To better explain the problem, let’s walk through the high availability failover process.
High availability standby server
When you configure a zone redundant or same-zone high availability standby server, connections from the application are always routed to the primary server, while data in the primary server is replicated synchronously to the HA standby server. When you’ve also configured a connection pool, the communication flow is as shown in the following diagram.
For more information, see High availability concepts in Azure Database for MySQL Flexible Server.
Issues with using connection pooling during a failover event
If the database becomes unavailable, the standby server becomes the primary database server, as shown in the diagram below, and DNS is updated.
If you’re using a connection pooler, however, all connections will continue trying to connect using the IP address of the old primary server, at least until they expire.
Note: You can simulate this failover behavior by using the Forced Failover feature in the Azure management portal. For more information, see the Failover Process.
Solution
To work around this issue, look for specific MySQL exceptions (such as UnabletoConnectToHost or OptionPrecentsStatement) that occur during the failover event, and then clear connections from the pool. You can use the MySqlConnection.ClearPool Method to clear the connection pool associated with the connection. If there are additional connections in use at the time of the call, they’re marked appropriately and discarded (rather than being returned to the pool) when Close is called on them. After clearing the pool, you should throw the exception so that the upper stream will catch and retry the operation.
The code snippet below shows the exception handling.
try{
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch(MySqlException e)
{
if(
e.Number == (int)MySqlErrorCode.UnableToConnectToHost ||
e.Number == (int)MySqlErrorCode.OptionPreventsStatement
){
MySqlConnection.ClearPool(conn);
}
throw e; //Upper stream should handle this exception and retry the operation
}
After the connection pool is cleared, newly created connections will point to the new primary server, as shown in the following diagram.
Summary
Connection pooling is a useful technology for making database connections more efficient, but you need to be sure to use exception handling to address failover events, as demonstrated in this blog post. I hope this detail is valuable to developers working with Azure Database for MySQL.
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!