Re: [Maria-discuss] "Error: MySQL server has gone away" issue while running mariadb database
![](https://secure.gravatar.com/avatar/79573204fb5075bc02698fb66dd194d4.jpg?s=120&d=mm&r=g)
On 11/04/17 15:15, Geunsik Lim wrote:
For reference, We are running web-based front service with nodejs package + pm2 (http://pm2.keymetrics.io/) on Ubuntu 16.04.1 server.
It seems that this issue results from the too big packet size and too long waiting time between mariadb server and our nodejs-based web-service.
its probably one or the other but not both.. MySQL document is still applicable https://dev.mysql.com/doc/refman/5.7/en/gone-away.html
![](https://secure.gravatar.com/avatar/e88e4f4ebfe66dd86eb52592241e91ee.jpg?s=120&d=mm&r=g)
Thank you for telling me the webpage. I have got the more appropriate
method thanks to you. :)
On Tue, Apr 11, 2017 at 2:48 PM, Daniel Black
On 11/04/17 15:15, Geunsik Lim wrote:
For reference, We are running web-based front service with nodejs package + pm2 (http://pm2.keymetrics.io/) on Ubuntu 16.04.1 server.
It seems that this issue results from the too big packet size and too long waiting time between mariadb server and our nodejs-based web-service.
its probably one or the other but not both..
MySQL document is still applicable https://dev.mysql.com/doc/refman/5.7/en/gone-away.html
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- http://leemgs.fedorapeople.org Don't try to avoid pain if you fail. If you decided to face the challenges in life, you can gain a lot by giving your best. Cheolsang Jeong's Book & life --
![](https://secure.gravatar.com/avatar/e88e4f4ebfe66dd86eb52592241e91ee.jpg?s=120&d=mm&r=g)
I have summarized a solution after finding out some hints by running "mytop" command and digging a related source code of mariadb server. I have started running an existing our web service with the below two solutions for aging test. * EVALUATION ENVIRONMENT: -------------------------------------- invain@mytizen:~$ uname -a Linux mytizen 4.4.0-45-generic #66-Ubuntu SMP Wed Oct 19 14:12:37 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux invain@mytizen:~$ cat /etc/lsb-release DISTRIB_ID=Ubuntu DISTRIB_RELEASE=16.04 DISTRIB_CODENAME=xenial DISTRIB_DESCRIPTION="Ubuntu 16.04.1 LTS" invain@mytizen:~$ mysql --version mysql Ver 15.1 Distrib 10.0.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 * PROBLEM: ------------------- The MySQL server has gone away (error 2006) has generated to a client. events.js:160 throw er; // Unhandled 'error' event ^ Error: MySQL server has gone away at Error (native) * SOLUTION: -------------------
From our experience, this issue results from two main causes such as 1) "timed out", 2) "too large packet". We recommend that you modify the existing configuration file of mariadb server as following:
FIRST, Mariadb server timed out and closed the connection.
https://dev.mysql.com/doc/refman/5.6/en/server-system-
variables.html#sysvar_wait_timeout
a. interactive_timeout : interactive time out for mysql shell sessions in
seconds like mysqldump or mysql command line tools.
b. wait_timeout: the amount of seconds during inactivity that MySQL will
wait before it will close a connection on a non-interactive connection in
seconds.
To fix this issue, check that wait_timeout mysql variable in your my.cnf
configuration file is large enough.
mariadb> SHOW SESSION VARIABLES LIKE 'wait_timeout'
mariadb> SHOW GLOBAL VARIABLES LIKE 'wait_timeout'
mariadb> SELECT @@global.wait_timeout, @@global.interactive_timeout,
@@session.wait_timeout, @@session.interactive_timeout;
# 8 hours (=28,800 secs) or maximum wait time out that you need.
mariadb> set global wait_timeout = 28800
mariadb> show full processlist;
mariadb> show variables like "%timeout%";
$ sudo systemctl restart mysql
SECOND, Server dropped an incorrect or too large packet.
https://dev.mysql.com/doc/refman/5.6/en/server-system-
variables.html#sysvar_max_allowed_packet
If mysqld gets a packet that is too large or incorrect, it assumes that
something has gone wrong with the client and closes the connection.
To fix this issue, you can increase the maximal packet size limit
'max_allowed_packet' in my.cnf file,
eg. "set max_allowed_packet = 128M", then restart your MySQL server: sudo
/etc/init.d/mysql restart
$> sudo vi /etc/my.cnf
(/etc/mysql/conf.d/mysqldump.cnf)
(/etc/mysql/mariadb.conf.d/50-server.cnf)
(/etc/mysql/mysql.conf.d/mysqld.cnf)
[mysqld]
max_allowed_packet=128M ( from 8, 16MB to 128MB)
Use "set global max_allowed_packet=104857600" (100MiB)
mariadb> SHOW VARIABLES LIKE 'max_allowed_packet'
mariadb> set global max_allowed_packet=104857600;
-------------------
mariadb> show full processlist;
+------+------+-----------------+-----------------+---------
+-------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State
| Info | Progress |
+------+------+-----------------+-----------------+---------
+-------+-------+-----------------------+----------+
| 751 | tic | localhost:56454 | pdk | Sleep | 1043 |
| NULL | 0.000 |
| 781 | root | localhost | api_development | Sleep | 20237 |
| NULL | 0.000 |
| 1065 | root | localhost | NULL | Query | 0 | init
| show full processlist | 0.000 |
+------+------+-----------------+-----------------+---------
+-------+-------+-----------------------+----------+
3 rows in set (0.00 sec)
mariadb>
$ sudo systemctl restart mysql
End of line.
On Tue, Apr 11, 2017 at 3:12 PM, Geunsik Lim
Thank you for telling me the webpage. I have got the more appropriate method thanks to you. :)
On Tue, Apr 11, 2017 at 2:48 PM, Daniel Black
wrote: On 11/04/17 15:15, Geunsik Lim wrote:
For reference, We are running web-based front service with nodejs package + pm2 (http://pm2.keymetrics.io/) on Ubuntu 16.04.1 server.
It seems that this issue results from the too big packet size and too long waiting time between mariadb server and our nodejs-based web-service.
its probably one or the other but not both..
MySQL document is still applicable https://dev.mysql.com/doc/refman/5.7/en/gone-away.html
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- http://leemgs.fedorapeople.org Don't try to avoid pain if you fail. If you decided to face the challenges in life, you can gain a lot by giving your best. Cheolsang Jeong's Book & life --
-- http://leemgs.fedorapeople.org Don't try to avoid pain if you fail. If you decided to face the challenges in life, you can gain a lot by giving your best. Cheolsang Jeong's Book & life --
![](https://secure.gravatar.com/avatar/e88e4f4ebfe66dd86eb52592241e91ee.jpg?s=120&d=mm&r=g)
4 hours is passed. The web-based service is okay with "wait_timeout=8hours" and "max_allowed_packet=100MB". I am planning to execute aging test for 3 days at least. BTW, Can we check inactivity time (e.g., wait_timeout) that MariaDB waits before it will be close a connection with "mytop" command like the below content? u164.04$ mytop -u root -p*** [enter] MySQL on localhost (10.0.29) load 0.24 0.26 0.27 1/563 18515 up 0+23:47:16 [15:22:38] Queries: 5.2k qps: 0 Slow: 0.0 Se/In/Up/De(%): 07/00/00/00 Sorts: 0 qps now: 1 Slow qps: 0.0 Threads: 3 ( 1/ 1) 00/00/00/00 Key Efficiency: 100.0% Bps in/out: 1.7/233.2 Now in/out: 21.3/ 2.9k Id User Host/IP DB Time Cmd State Query -- ---- ------- -- ---- --- ----- ---------- 781 root localhost api_develo 28048 Sleep 751 tic localhost:56454 pdk 4406 Sleep 1084 root localhost 0 Query init show full processlist BRs, Geunsik Lim.
![](https://secure.gravatar.com/avatar/e88e4f4ebfe66dd86eb52592241e91ee.jpg?s=120&d=mm&r=g)
Finally, I am using the below setting file to avoid "Error: MySQL server
has gone away" message.
I have not been getting the message for 3 days (as an aging test).
Here is the related configuration files that have to be modified for Ubuntu
16.04.1 & Mariadb Server 10.0.
mytizen$sudo vi /etc/mysql/conf.d/mysqldump.cnf
mytizen$sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
mytizen$sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
----------- content -------------------------------------
# * Fine Tuning
# max_allowed_packet: from 16M to 128M
max_allowed_packet = 128M
# timeout: from 28800 (8h) to 2592000 (30days)
wait_timeout = 2592000
interactive_timeout = 2592000
-----------------------------------------------------------
The below is how we check the values if the modified valued is applied to
Mariadb server.
MariaDB [(none)]>
MariaDB [(none)]> SELECT @@global.wait_timeout,
@@global.interactive_timeout, @@session.wait_timeout,
@@session.interactive_timeout;
+-----------------------+------------------------------+------------------------+-------------------------------+
| @@global.wait_timeout | @@global.interactive_timeout |
@@session.wait_timeout | @@session.interactive_timeout |
+-----------------------+------------------------------+------------------------+-------------------------------+
| 2592000 | 2592000 |
2592000 | 2592000 |
+-----------------------+------------------------------+------------------------+-------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show variables like 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 134217728 |
+--------------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]>
On Tue, Apr 11, 2017 at 3:34 PM, Geunsik Lim
4 hours is passed. The web-based service is okay with "wait_timeout=8hours" and "max_allowed_packet=100MB". I am planning to execute aging test for 3 days at least.
BTW, Can we check inactivity time (e.g., wait_timeout) that MariaDB waits before it will be close a connection with "mytop" command like the below content?
u164.04$ mytop -u root -p*** [enter]
MySQL on localhost (10.0.29) load 0.24 0.26 0.27 1/563 18515 up 0+23:47:16 [15:22:38] Queries: 5.2k qps: 0 Slow: 0.0 Se/In/Up/De(%): 07/00/00/00 Sorts: 0 qps now: 1 Slow qps: 0.0 Threads: 3 ( 1/ 1) 00/00/00/00 Key Efficiency: 100.0% Bps in/out: 1.7/233.2 Now in/out: 21.3/ 2.9k
Id User Host/IP DB Time Cmd State Query -- ---- ------- -- ---- --- ----- ---------- 781 root localhost api_develo 28048 Sleep
751 tic localhost:56454 pdk 4406 Sleep
1084 root localhost 0 Query init show full processlist
BRs, Geunsik Lim.
-- http://leemgs.fedorapeople.org Don't try to avoid pain if you fail. If you decided to face the challenges in life, you can gain a lot by giving your best. Cheolsang Jeong's Book & life --
participants (2)
-
Daniel Black
-
Geunsik Lim