Determine cause of "MySQL server has gone away" I've got a long running process on a headless server. It is a C program that monitors a modem for Caller Id and hangs up on telemarketers. On startup the program opens a connection to a MariaDB 10.3 database on the local machine.

Each morning at 4:00 AM a separate C program fetches the latest data file from the FTC. It includes about 100,000 complaints from the previous day. I add them to the database, so a blacklist table adds 50,000 to 60,000 rows. The current table has about 600,000 rows.

At 9:00 AM when the calls start my queries fail with the message shown below. It is a debugging trace of the C program. It includes Error: MySQL server has gone away.

I checked dmesg and /var/log but there is no indication of the reason or the problem. I can't find it logged anywhere. It also appears the OOM killer did not kill the MariaDB server.

# cat /var/log/mariadb/mariadb.log
#
# dmesg | grep -iE 'maria|mysql|oom'
#


How can I begin to troubleshoot this problem?



I can find lots of posts regarding MySQL server has gone away (apparently this is a chronic problem that has never been fixed). For example, here and here. They are dev-2-dev "try this" answers and lack troubleshooting to determine the root cause.



Debug: ===== new call =====
Debug: ring count: 1
Debug: modem_read
Debug: Read 68:
"DATE = 0308
TIME = 0936
NMBR = 4103174745
NAME = SPAM? PASADENA"
Debug: Ring: 0, Caller Id: 1
Debug: call_read_name
Debug: name: SPAM? PASADENA
Debug: call_read_number
Debug: Number: 4103174745
Debug: database_number_is_whitelist
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in whitelist
Debug: database_number_is_blacklist_lcl
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in local blacklist
Debug: database_number_is_blacklist_ftc
Error: Failed to prepare statement, Error: MySQL server has gone away
Info: 4103174745 not found in FTC blacklist
...
I had a similar issue to this, and it turned out to be a connection time out. My program sets up a connection pool, and threads grab a connection from that pool when they need to make queries. Every morning when I would come in and try to run my program I would get the MySQL Server has gone away error. A lot of solutions I found said to increase the wait_timeout field in my.cnf, but I found in the C API you can enable automatic re-connection like this: 

// set up your connection

// enable automatic reconnect
bool reconnect = 1;
mysql_options(&amp;connection, MYSQL_OPT_RECONNECT, &amp;reconnect);


From the docs: 


  The MySQL client library can perform an automatic reconnection to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed. If auto-reconnect is enabled, the library tries once to reconnect to the server and send the statement again. 


This gave me a hard time for a while as it was pretty hard to troubleshoot, so hopefully this is helpful to somebody else!

Source: C API Automatic Reconnection Control