MySQL Bugs: #30641: Error 2013 received on execute query with reconnect enabled on local LAN

Bug #30641	Error 2013 received on execute query with reconnect enabled on local LAN
Submitted:	26 Aug 2007 20:16	Modified:	12 Aug 2008 7:52
Reporter:	shaun neal	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S2 (Serious)
Version:	5.0.27 community-nt on TCPIP	OS:	Windows (XP Pro)
Assigned to:		CPU Architecture:	Any
Tags:	2013

Description:
I have a C++ server written using MS DEV 2003.  This server runs on a desktop computer.  I am running MySQL database on another desktop computer.  Both computers are hardwired to a hub.  Both computers have 192.168.1.x addresses with proper subnets.

After what seems to be a random period of time (sometimes hours, sometimes days), the application server will execute a query and receive an error 2013 from the MySQL server.  The queries that the server is running are all basically the same.  And I have the following code included which is supposed to reconnect the connection when its dropped:

my_bool reconnect = 1;
mysql_options(DBHandle, MYSQL_OPT_RECONNECT, &reconnect);

I dont think that there is any network issue, partly because its just two computers right next to each other on the same LAN and hub, but also because I have other network connections open between these machines (file sharing and remote control), which do not get interrupted.  The query that fails frequently is "select last_insert_id()", although other queries fail as well.

How to repeat:
Not really sure...this is somewhat of a nebulous problem that doesnt seem directly correlated to any sepcific steps.

Suggested fix:
If reconnect is specified as above, then it should reconnect even if the connection has dropped.

Screencap of the error occuring

Attachment: screencap.zip (application/x-zip-compressed, text), 165.65 KiB.

Thank you for a problem report. Please, send your MySQL server's error log (<hostname>.err in the data directory usually).

host error file

Attachment: shaun.err (application/octet-stream, text), 34.15 KiB.

You server was shutted down at:

070819  2:38:53 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown

070815 23:19:45 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown

070814 23:49:23 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown

... and so on. Have you got your 2013 errors during these times?

No, my server was not shut down at the times that I received the 2013 error.  I wouldn't be posting a bug if that was the case.

You can see from the screenshot what the date and time was of the error.

Sorry for asking obvious questions. I just wanted to be sure.

Do you have mysql_ping() calls in your application server's code?

Listed below is the entire code of the database call.  I do not understand how a call to mysql_ping will make any difference.  According to the manual:

"Checks whether the connection to the server is working. If the connection has gone down, an attempt to reconnect is made unless auto-reconnect is disabled. 

This function can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary."

That is not the case in this instance.  The server is making calls to the database almost continously.  Additionally, according to the manual entry for reconnect behavior:

"The MySQL client library can perform an automatic reconnect to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed. In this case, the library tries once to reconnect to the server and send the statement again. "

I have set the reconnect behavior according to the instructions in this section, so making a call to mysql_ping should not be required.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	Recordcount = 0;
	RowsUpdated = 0;

	int Result = mysql_query( DBHandle, SQL );

	if (! Result) {
		RowsUpdated = (int)mysql_affected_rows(DBHandle);

		QueryResult = mysql_store_result( DBHandle ) ;

		if (QueryResult != NULL) {

			Recordcount = (int)mysql_num_rows ( QueryResult );

			if (Recordcount > MaxRows) {
				printf ("%s\nExceeded row count", SQL);
				getch();
				exit(0);
			}

			Fieldcount = mysql_num_fields ( QueryResult );

			if (Fieldcount > MaxCols) {
				printf ("%s\nExceeded field count", SQL);
				getch();
				exit(0);
			}

			for ( int r = 0; r < Recordcount; r++ ) {
				CurrentRow = mysql_fetch_row ( QueryResult );
				for ( int c = 0; c < Fieldcount; c++ )
					if (CurrentRow[c] == NULL)
						strcpy(Data[r][c], "");
					else {
						if (strlen(CurrentRow[c]) < MaxData) 
							strcpy (Data[r][c], CurrentRow[c] );
						else {
							printf ("%s\nExceeded data field size", SQL);
							getch();
							exit(0);
						}
					}
			}
		}

	    mysql_free_result ( QueryResult ) ;
	}
	else {
		Result = mysql_errno(DBHandle);
		printf ("Error %d in SQL: %s\n\n", Result, SQL );
	}

	SQLCount++;

	if (SQLLogging)
		fprintf (Logfile, "%s\n", SQL );

happened again just now, posting screencap and error log

screencap and error file- 2nd occurance

Attachment: screencap.zip (application/x-zip-compressed, text), 165.65 KiB.

Opening the RTF file in the provided ZIP file i only see the left half of the screen shot and no server error log ... could you provide both in a more portable format (the screenshot as GIF or PNG, even a ZIPed BMP will do, and the server error log just "as is")?

error file

Attachment: Copy of shaun.err (application/octet-stream, text), 34.15 KiB.

second screencap

Attachment: screencap.png (image/x-png, text), 73.31 KiB.

Attached are the new screencap and error file in plain formats.  The error file is the same as last time and the screencap is basically showing the same error occuring...2013 on GET_LAST_ID.

Is there any debug mode on the server that I can set so it will log more info?  Is there any code I can add to the client to get more details on when the connection is being dropped?

error file

Attachment: shaun.err (application/octet-stream, text), 34.47 KiB.

error screencap

Attachment: errorcap.png (image/x-png, text), 62.13 KiB.

screencap of handle structure - 1

Attachment: dbhandle1.png (image/x-png, text), 66.46 KiB.

screencap of handle structure-2

Attachment: dbhandle2.png (image/x-png, text), 66.31 KiB.

This error has been recurring fairly regularly over the past few days.  As no one has repsonded to this I'm guessing that you have given up on fixing it.  If not, please let me know, otherwise I will have to go with another database.

Could you please provide the complete test case code?. Thanks in advance.

I've already posted the code and screenshots which show the error.

We have migrated to SQL Server, so you can close this bug if you like.

Thank you for the feedback.

In the error log I see "070829 19:39:04 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown". Screenshot shows 7:36 PM when error occurs. Do both machines have same time? If not and server was restarting, it is expected what client application gets error 2013 occasionally. Although client application reconnects when it is possible, user should handle error 2013 herself.

We only can consider such behavior as a bug if error 2013 happened if client didn't reconnect to server or disconnected to running server. But to prove it we need repeatable test case which shows problem if server is running. Information you provided is not enough. So I close the report as "Can't repeat". Feel free to reopen the report when you are able to provide such test case.