MySQL Server :: My C Code can remotely create a database, but not “INSERT INTO” it

cconnectionsMySQLtroubleshooting

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.

Best Answer

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

Related Question