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 <leemgs@gmail.com> wrote:

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 <daniel.black@au1.ibm.com> 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 --

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 <leemgs@gmail.com> wrote:

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 --