Investigating connection issues with Azure Database for MySQL

by | Feb 16, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

Azure Database for MySQL is a fully managed database service, based on the MySQL Community Edition, that runs in the Microsoft cloud. While the service provides features such as patching, backups, high availability, and security, there are some requirements and best practices to understand when you are using the service. 


Possible-Causes-for-MySQL-Connection-Issues-mainblog.jpg


This article lists the most common connection issues that users may encounter, together with suggestions and recommended solutions for addressing those  issues.


 


This detail applies specifically to the Azure Database for MySQL Single Server deployment model. 


 


Scenario: After provisioning an Azure Database for MySQL server, the connection fails 


There can be multiple reasons for this. Please see the following guidance. 


 


Invalid connection String or password 



  • Symptom: ERROR 1045 (28000): Access denied for user ‘username’@’IP address’ (using password: YES)”


Recommended solution 



  • Be sure that the “username” exists as a valid user on the server, as it may have been inadvertently deleted. 

  • Check Users and Privileges in MySQL Workbench or run query:  select user from mysql.user. This will provide a list of all users that can connect and help you to determine if the host IP is allowed to connect for the user in question. You may be able to connect with another user from the list. 



  • Symptom: Invalid Username specified.  


Recommended solution: Confirm that the username you are passing ends with the correct server name/hostname field. The username needs to be passed in the format username@servername. 


 



  • Symptom: Failed to log in to Azure Database for MySQL to run the query using the admin account. 


Recommended solution: You can reset the admin password by using the Azure portal. The reset password option in the Azure portal helps recreate the user, reset the password, and restore the admin permissions, which will allow you to log in using the server admin account and perform further operations. 


 



Firewall and Network 


Start by testing the connection from the Azure Cloud shell in the portal to see if you can connect. This can also help determine whether it’s a database availability issue or an issue with your client network. 


Server firewall configuration 



  • Symptom: Client with IP address ‘XXX.XX.XXX.X’ is not allowed to connect to this MySQL server 


Recommended solution: Make sure the IP address is allowed on the server Firewall rule and that you are using correct the username format, your_user@servername, and the right password. For temporary testing purposes only, set up a firewall rule using 0.0.0.0 as the starting IP address and using 255.255.255.255 as the ending IP address. This will open the server to all IP addresses. If this resolves your connectivity issue, remove this rule, and create a firewall rule for an appropriately limited IP address or address range. 


 


Client firewall configuration 


The firewall on your client must allow connections to your database server.



  • Confirm that your network allows outbound connections on port 3306(we cannot change this port number). You can try to telnet to your server. When using single server deployment mode, confirm your network/firewall does not block connection to the regional Azure Database for MySQL Gateway IP.  

  • Test the connection to the MySQL database server using psping. Ping the FQDN and see if it resolves to our Gateway IP correctly when using MySQL Single Server deployment mode. If you’re using the private endpoint, it should resolve to your private IP for the private endpoint. 

  • If you are connecting from Azure VM (virtual machines), check NSG (network security groups) rules to see if it blocks the connection. Also check the route table and see if there is any VPN device which may need to be configured.  

  • If you are using VNET rules, ensure that the service endpoints are correctly configured and you can find some limitations in this doc.  

  • If you are using the Private link and deny the public access is set to on, it means you can only connect from the VNET of private endpoint or peered VNET or VPN/express route.  



  • Symptom: Server is not configured to allow IPv6 connections. 


Recommended solution: Note that the Basic tier does not support VNet service endpoints. You must remove the endpoint Microsoft.Sql from the subnet attempting to connect to the Basic tier server.  



  • With the Single Server deployment model, a gateway is used to redirect the connections to server instances. After the connection is established, the MySQL client displays the version of MySQL set in the gateway, not the actual version running on your MySQL server instance. To determine the version of your MySQL server instance, use the SELECT VERSION(); command at the MySQL prompt. For more information about supported versions, see Supported MySQL server versions. 


TLS/SSL 



  • Ensure that you’re using the correct SSL configuration and the right certificate. 

  • As a part of our maintenance activity, we’re working on changing the root certificate for the client application/driver enabled with SSL. To mitigate the issue, see this article. 

  • Be sure that you’re using the correct TLS configuration. To configure TLS for single server, see TLS configuration. 

  • If you use the Flexible Server deployment mode, TLS/SSL is enabled by default and can’t be disabled. The minimum TLS version supported on the server is TLS1.2. All incoming connections with TLS 1.0 and TLS 1.1 will be denied. You cannot disable or change the TLS version. 


DRIVER 


It is highly recommended to use only the supported drivers and tools and latest client version. Note that you can’t connect to Azure Database for MySQL using SSMS. 


 


Scenario: It takes longer to establish connections than before 



  • Symptom: If you recently migrated to MySQL single server in Azure from on premise server or Azure MySQL flexible server, you may notice the higher connection time which was due to the Single Server architecture.  This can impact your workload performance if there are large number of short-lived connections, such as when a user creates a connection, runs a simple query, and closes the connection.  


Recommended solution: We highly recommend connection pooling if you have not done it yet and exam your pool configuration. Learn more about this.  



  • Symptom: If you notice the connection latency suddenly increases. 


Recommended solution: You can start checking if you have increased workload and if you have made any changes to application code as well as server parameters. 


 


Scenario: Connection dropped 



  • Symptom: ERROR 1184 (08S01): Aborted connection 22 to db: ‘db-name’ user: ‘user’ host: ‘hostIP‘ (init_connect command failed) 


Recommended solution: You should reset init_connect value in server parameters tab in Azure portal and use only the supported server parameter. More information can be found here and in this blog post. 



  • Symptom: Error Code: 2013. Lost connection to MySQL server during query error or receive MySQL server has gone away intermittently.  


Recommended solution 



  • The server dropped an incorrect or too large packet. Please increase value max_allowed_packet in Azure portal server parameter tab.  

  • Timeout from the TCP/IP connection. From server side, consider increasing connect_timeout, net_write_timeout, net_read_timeout, wait_timeout. From client side, check your client timeout value. 

  • The running thread was killed manually, or the query was executed after closing the connection. In this case, check who may killed the thread and your application logic. 

  • For more information, check out this blog and performance suggestions. 



  • Symptom: ERROR 1040 (08004): Too many connections 


Recommended solution: Check out the max_connnections. You can find the limit for each tier in this doc and increase the max_connections. For best experience, we recommend that you use a connection pooler like ProxySQL to efficiently manage connections as mentioned above. If you see unexpected high number of connections, check your application code, and retry configuration as well.  



  • It’s possible that you may experience either connection failures or timeouts during peak hours. Please check your active connections as well as CPU/memory/IO usage percentage in the portal metrics tab. Consider upgrading your server if any resource is hitting 100%. For more information, see performance troubleshooting basics. 

  • Symptom: The last packet sent successfully to the server was X milliseconds ago. The driver has not received any packets from the server. 


Recommended solution: Consider either expiring and/or testing connection validity before using it in your application. To avoid this issue, increase the server configured values for client timeouts, or use the Connector/J connection property autoReconnect=true to avoid this problem. 



  • Retry logic is recommended to handle transient errors. However, you may want to limit the total retry count and delay between each retry to avoid excessive retry causing further issues. For details, please check this link. 


 


Scenario: All connections are failing 


This could be because of issue in Azure infra or maintenance activities.  



 


Conclusion 


Hopefully, if you’ve been through all the guidance above, you’ve identified the cause of the connectivity issue. If you have any questions, please feel free to let me know.   


 


Reference 


Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment