Lost connection to MySQL server during query [2013]

[renkun-ken]

I'm running a crontab job to query data on three servers at the same time in the morning. Some random crash may occur as the following log shows:

Loading required namespace: RMariaDB
Loading required namespace: RSQLServer
db_query: select S_INFO_WINDCODE, TRADE_DT from ASHAREEODPRICES where TRADE_DT >= '20070101' order by S_INFO_WINDCODE, TRADE_DT
Error in result_fetch(res@ptr, n) :
  Lost connection to MySQL server during query [2013]
Calls: db_query ... .local -> sqlColumnToRownames -> result_fetch -> .Call
Execution halted

While a while later, another crontab job would query this very table and some others on the three servers and no problem occurs. I'm not sure why this happens.

My session info:

> devtools::session_info()
Session info -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.4.3 (2017-11-30)
 system   x86_64, linux-gnu           
 ui       RStudio (1.1.402)           
 language (EN)                        
 collate  en_US.UTF-8                 
 tz       PRC                         
 date     2017-12-22                  

Packages ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 package    * version  date       source                        
 base       * 3.4.3    2017-12-01 local                         
 bit          1.1-12   2014-04-09 CRAN (R 3.4.1)                
 bit64        0.9-7    2017-05-08 CRAN (R 3.4.1)                
 compiler     3.4.3    2017-12-01 local                         
 data.table * 1.10.4-3 2017-10-27 CRAN (R 3.4.2)                
 datasets   * 3.4.3    2017-12-01 local                         
 DBI          0.7-13   2017-11-29 Github (rstats-db/DBI@b40e361)
 devtools     1.13.4   2017-11-09 CRAN (R 3.4.2)                
 digest       0.6.13   2017-12-14 CRAN (R 3.4.3)                
 graphics   * 3.4.3    2017-12-01 local                         
 grDevices  * 3.4.3    2017-12-01 local                         
 hms          0.4.0    2017-11-23 CRAN (R 3.4.2)                
 memoise      1.1.0    2017-04-21 CRAN (R 3.4.1)                
 methods    * 3.4.3    2017-12-01 local                         
 pkgconfig    2.0.1    2017-03-21 CRAN (R 3.4.1)                
 Rcpp         0.12.14  2017-11-23 CRAN (R 3.4.2)                
 rlang        0.1.4    2017-11-05 CRAN (R 3.4.2)                
 RMariaDB     1.0-4    2017-12-11 CRAN (R 3.4.3)                
 stats      * 3.4.3    2017-12-01 local                         
 tools        3.4.3    2017-12-01 local                         
 utils      * 3.4.3    2017-12-01 local                         
 withr        2.1.1    2017-12-19 CRAN (R 3.4.3)                
 yaml         2.1.16   2017-12-12 CRAN (R 3.4.3)      

$ /usr/bin/mysql_config
Usage: /usr/bin/mysql_config [OPTIONS]
Compiler: GNU 5.4.0
Options:
        --cflags         [-I/usr/include/mysql ]
        --cxxflags       [-I/usr/include/mysql ]
        --include        [-I/usr/include/mysql]
        --libs           [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -ldl]
        --libs_r         [-L/usr/lib/x86_64-linux-gnu -lmysqlclient -lpthread -lz -lm -lrt -ldl]
        --plugindir      [/usr/lib/mysql/plugin]
        --socket         [/var/run/mysqld/mysqld.sock]
        --port           [0]
        --version        [5.7.20]
        --libmysqld-libs [-L/usr/lib/x86_64-linux-gnu -lmysqld -lpthread -lz -lm -lrt -lcrypt -ldl -laio -llz4 -lnuma]
        --variable=VAR   VAR is one of:
                pkgincludedir [/usr/include/mysql]
                pkglibdir     [/usr/lib/x86_64-linux-gnu]
                plugindir     [/usr/lib/mysql/plugin]

[renkun-ken]

It seems that the default timeout settings in mysqld config are too strict for large tables like I queried. I modify /etc/my.cnf with the following settings:

net_read_timeout=600
net_write_timeout=180
wait_timeout=86400
interactive_timeout=86400
max_allowed_packet=128M

Let me see if this problem occurs any more.

[renkun-ken]

The config seems to work. I'll reopen this issue if this problem still occurs.

[mahendracloud]

it worked for my case. 35L data was transferred from mysql to HANA. Thanks @renkun-ken

[github-actions[bot]]

This old thread has been automatically locked. If you think you have found something related to this, please open a new issue and link to this old issue if necessary.