Published
Mar 13 2019 07:34 PM
11K
Views
First published on MSDN on Nov 20, 2018
One of the common MySQL server errors is “MySQL server has gone away”.
This is a pretty generic error and can happen because of a server error or a client-side error. While Azure has automated bots which are used to detect and address server side issues, this tutorial will discuss client-side issues that can cause this error.
Ask yourself If this issue is happening consistently or intermittently first. If it is happening constantly look at the first part of this guide but If it is happening intermittently please jump to the second part for some common reasons for that error.
Part 1: Constantly seeing "MySQL server has gone away" error
a) Old or outdated driver:
Microsoft recommends you to connect to Azure Database for MySQL using the latest client version for your application. Check the documentation for the driver that you are using to find the latest version.
Under "Connect & Query" section of the below documentation are a few popular connectors with instructions on how to install the driver and how to connect.
https://docs.microsoft.com/en-us/azure/mysql/
MySQL documentation also lists connectors for using MySQL with applications and tools that are compatible with industry standards ODBC and JDBC. Any system that works with ODBC or JDBC can use Azure Database for MySQL.
https://dev.mysql.com/downloads/connector/
b) init_connect improperly configured
Avoid using this parameter as it can cause this error but, if necessary, ensure that you have the correct format for the argument as this parameter has different possibilities for the argument. That’s why it is not validated at the portal level and should be validated by the user.
For Example: Setting the parameter init_connect for the following values will cause an error.
“set tmp_table_size=512M; set max_heap_table_size=512M”.
To fix this issue, set the correct arguments as a full length data sizes for the parameter. It is mandatory here and the right syntax will be as the following:
“set tmp_table_size=536870912; set max_heap_table_size=536870912”.
c) Privilege issue
A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host. Please make sure you white listed you application IP address in the Azure Database for MySQL Firewall.
https://docs.microsoft.com/en-us/azure/mysql/concepts-firewall-rules
Part 2: intermittently seeing "MySQL server has gone away" error
a) Server dropped an incorrect or too large packet
Please increase the value for “max_allowed_packet” from the Azure Database for MySQL server parameter pane to avoid issues due to the size of the packet.
b) Timeout
You got a timeout from the TCP/IP connection on the client side. Please note that there are a number of parameters that can cause this timeout to happen. After the server waits for activity to happen, a number of seconds before it closes the connection, all these parameters can be edited from the Azure MySQL server parameters pane as the following.
c) Killing a running process
DBA killed the running thread with a KILL statement or a mysqladmin kill
d) Already Closed connection with the database
You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.
Reference:
https://dev.mysql.com/doc/refman/5.7/en/gone-away.html
One of the common MySQL server errors is “MySQL server has gone away”.
This is a pretty generic error and can happen because of a server error or a client-side error. While Azure has automated bots which are used to detect and address server side issues, this tutorial will discuss client-side issues that can cause this error.
Ask yourself If this issue is happening consistently or intermittently first. If it is happening constantly look at the first part of this guide but If it is happening intermittently please jump to the second part for some common reasons for that error.
Part 1: Constantly seeing "MySQL server has gone away" error
a) Old or outdated driver:
Microsoft recommends you to connect to Azure Database for MySQL using the latest client version for your application. Check the documentation for the driver that you are using to find the latest version.
Under "Connect & Query" section of the below documentation are a few popular connectors with instructions on how to install the driver and how to connect.
https://docs.microsoft.com/en-us/azure/mysql/
MySQL documentation also lists connectors for using MySQL with applications and tools that are compatible with industry standards ODBC and JDBC. Any system that works with ODBC or JDBC can use Azure Database for MySQL.
https://dev.mysql.com/downloads/connector/
b) init_connect improperly configured
Avoid using this parameter as it can cause this error but, if necessary, ensure that you have the correct format for the argument as this parameter has different possibilities for the argument. That’s why it is not validated at the portal level and should be validated by the user.
For Example: Setting the parameter init_connect for the following values will cause an error.
“set tmp_table_size=512M; set max_heap_table_size=512M”.
To fix this issue, set the correct arguments as a full length data sizes for the parameter. It is mandatory here and the right syntax will be as the following:
“set tmp_table_size=536870912; set max_heap_table_size=536870912”.
c) Privilege issue
A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host. Please make sure you white listed you application IP address in the Azure Database for MySQL Firewall.
https://docs.microsoft.com/en-us/azure/mysql/concepts-firewall-rules
Part 2: intermittently seeing "MySQL server has gone away" error
a) Server dropped an incorrect or too large packet
Please increase the value for “max_allowed_packet” from the Azure Database for MySQL server parameter pane to avoid issues due to the size of the packet.
b) Timeout
You got a timeout from the TCP/IP connection on the client side. Please note that there are a number of parameters that can cause this timeout to happen. After the server waits for activity to happen, a number of seconds before it closes the connection, all these parameters can be edited from the Azure MySQL server parameters pane as the following.
c) Killing a running process
DBA killed the running thread with a KILL statement or a mysqladmin kill
d) Already Closed connection with the database
You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.
Reference:
https://dev.mysql.com/doc/refman/5.7/en/gone-away.html