Fix: MySQL server has gone away according to wait_timeout 99/125099/1
authorGeunsik Lim <geunsik.lim@samsung.com>
Thu, 13 Apr 2017 22:55:08 +0000 (07:55 +0900)
committerGeunsik Lim <geunsik.lim@samsung.com>
Thu, 13 Apr 2017 23:08:39 +0000 (08:08 +0900)
commit24ff318c51f92d81fb0d47889109b87482672a8b
treef1f58f96c82aa1347a8e99a88015daf89869024f
parent285f65c94731252d7f82709f59d8013040a307e1
Fix: MySQL server has gone away according to wait_timeout

According to https://github.com/mscdex/node-mariasql/blob/master/lib/Client.js#L633 ,
The protocol-level pings can be enabled by setting both 'pingInactive' and
'pingWaitRes' variablei as following:

a. 'pingInactive' to how many milliseconds to wait before sending a ping
   when no queries are pending,
b. AND 'pingWaitRes' to how many milliseconds to wait for a ping response
   before assuming a lost/dead connection.

For example, If you want to check a connection state per 60 seconds,
pingInactive: 60000,
pingWaitRes: 60000

* Before applying this patch:
events.js:160
      throw er; // Unhandled 'error' event
      ^
Error: MySQL server has gone away
    at Error (native)

* How to monitor a sleep time of TIC on MariaDB server:
$ sudo apt-get install mytop [enter]
$ mytop -u root -p**** tic [enter]
MySQL on localhost (10.0.29)   load 0.32 0.30 0.26 2/580 9391 up 0+14:33:20 [07:47:10]
 Queries: 1.7k     qps:    0 Slow:     0.0         Se/In/Up/De(%):    02/00/00/00
 Sorts:      0 qps now:    1 Slow qps: 0.0  Threads:    3 (   1/   2) 00/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.9/127.1   Now in/out:  21.3/ 2.9k

       Id      User         Host/IP         DB       Time    Cmd    State Query
       --      ----         -------         --       ----    ---    ----- ----------
      754       tic localhost:34766        pdk         38  Sleep
$ cat /proc/754/stat
$ cat /proc/754/status

* How to change a default timeout of Mariadb server:
$ sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
wait_timeout = 2592000
interactive_timeout = 2592000

Change-Id: I4af9d8bc32099a69772ed60fd08076acfc65cfd5
Signed-off-by: Geunsik Lim <geunsik.lim@samsung.com>
Reported-by: Taejun Ha <taejun.ha@samsung.com>
controller/dbpool.js