1

I’ve written a C program which does the following when it starts:

  1. Connects to a remote Linux MySQL server over the network
  2. Creates a new, uniquely-named database
  3. Sends a “USE _____” command to specify we want to use that database
  4. Creates a single table within the database

Here’s examples of the actual commands my code is sending:

  1. (no command sent to initiate connection)
  2. "CREATE DATABASE IF NOT EXISTS myDB_2019424_205718;"
  3. “USE myDB_2019424_205718;”
  4. “CREATE TABLE IF NOT EXISTS myTable ( timestamp VARCHAR(15) , Column1 VARCHAR(8) , Column2 VARCHAR(4) , Column3 VARCHAR(4) );”

All of the above works great. The trouble is, once the database is set up and ready, I need the C program to crunch a lot of numbers on-the-fly, and periodically send “INSERT INTO” commands to the server. Here’s the first “INSERT INTO” command my code sends:

INSERT INTO myTable VALUES ( '1556139439', NULL, NULL, NULL );

When my code sends its first “INSERT INTO” command, the server immediately sends back an error and my code reports “****** MySQL server has gone away ******”

I Googled that error, of course, and I see from posts like this that the two possible culprits are a low timeout or small packet threshold. So I’ve reset those to maximum on my server:

mysql> SELECT @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|            107373568 |
+----------------------+
1 row in set (0.00 sec)

mysql>

But still no luck. Worse, when I examine /var/log/mysql/error.log after a failed session, I see no error message recorded.

One more clue… I’ve tried manually logging into the MySQL server and pasting these commands in one-by-one. All commands are accepted on the manual interface. There’s something buggy when my code sends the “INSERT INTO” command, and I can’t figure out what. Has anyone seen this before?

Some system information… My C code is running on an Ubuntu container, version 16.04.3 LTS. I’ve installed the latest mysql library. The SQL server is running on an identical container; the actual version of MySQL Server is Ver 14.14 Distrib 5.7.25.

Sooo… what could be the problem here? I’ve included an excerpt of my C code below. (The full code is too long and confusing to include here.) Any suggestions or advice will be appreciated.

  • Test the same with direct database specifying: INSERT INTO myDB_2019424_205718.myTable VALUES ( '1556139439', NULL, NULL, NULL ); – Akina Apr 25 '19 at 4:46
  • @Akina I did, and when I issue that command directly into the local database, there's no problem. The issue seems to be the network connection between code & remote server is gone when the "INSERT INTO" command is issued. – Pete Apr 25 '19 at 14:18
  • @Akina Your tip paid off - I looked through the code, and there is indeed an issue with the code/server connection. I had added a close_connection() line that preceded the "INSERT INTO" command, and plumb forgot about it. Once I commented that line out, the connection stayed alive and now the INSERTs work great. Thanks! – Pete Apr 25 '19 at 14:26
  • @pete General_log=1 may be used in the future to get a handle on every operation. Use for a brief period of time. SET GLOBAL general_log=0 when you have completed your test to avoid filling your data storage device. Clear the General Log when your research is over because the log is appended to in most systems. It will be of no value to you in a month. – Wilson Hauck May 10 '19 at 15:46
0

User error, my apologies. I had put a "close_connection()" line in my code for troubleshooting purposes, then forgot about it. This wasn't a problem until I added an "INSERT INTO" command after the "close_connection()". So of course the connection was closed when my code tried to INSERT.

The online documentation for the "**** MySQL server has gone away ****" error assumes that the sending code wasn't closed the connection, which is what confused me. Now I know...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.