How to disconnect from ODBC to stop error: "MySQL has gone away"


#1

Hi. I have my ODBC connection working, but there isn’t a routine listed here:
http://www.amibroker.com/odbc.html

that provides for a DISCONNECT from the server. Hence, when the market closes at 4pm I had stopped making calls through the ODBC to MySQL and I was getting a “MySQL has gone away”. The only way I found to resolve that is to close AMI down and restart it. I made no changes to the MySQL server, and my AFL works again.

Since then, I have put in a hourly connection to keep the ODBC connection alive and this seems to work. Attached here.

ISSUE: Sometimes this still fails (don’t know why).

AMIBroker Support: Is there a way I can DISCONNECT my session with a function call at 4pm, and then reconnect at 9:30 AM with the standard call?


		// keep the connection alive on the MySWQL connection	
		if ( DateTimeFormat( "%M", Now(5) )  == "00" AND ( DateTimeFormat( "%S", Now(5) ) == "00"))
		{
			if (NOT MJF_ODBC_KeepAlive (ODBC_DBName, ODBC_DSN, ODBC_UID, ODBC_PWD, symbol))
			{
				StatusStr1 = "Symbol <" + symbol + "> ODBC keep alive failed - Manual intervention needed!";
				_TRACE (StatusStr1);
			}
		}

function MJF_ODBC_KeepAlive (
	ODBC_DBName,					// Database name IE: "Portfolio"; 
	ODBC_DSN,						// DNS name - IE: "AMI-Portfolio"; 
	ODBC_UID,						// User ID IE: "AmiBroker"; 
	ODBC_PWD,						// User password IE: "Password"; 
	SecName							// Security Name
	)
{
	retval = False;
	
	odbcDisplayErrors( False );
	ODBCConnectResult = odbcOpenDatabase("ODBC;DATABASE=" + ODBC_DBName + ";DSN=" + ODBC_DSN + ";OPTION=0;PORT=0;UID=" + ODBC_UID + ";PWD=" + ODBC_PWD);

	if (ODBCConnectResult)
	{
//		_TRACE("ODBCConnectResult SUCCESSFUL : " + ODBCConnectResult);
		retval = True;
	} else {
		_TRACE("ODBCConnectResult FAILED: " + ODBCConnectResult + "- get last error: " +odbcGetLastError() );	
		retval = False;
	}

	SQLQueryStr = "SELECT 1 from Trade LIMIT 1";
//	_TRACE(SecName + ": Trying - SQLQueryStr <" + SQLQueryStr + ">" ); 

	if( NOT odbcExecuteSQL(SQLQueryStr)) 
	{ 
		_TRACE(SecName + ": Keep Alive failed <" + SQLQueryStr + ">, Error <" + odbcGetLastError() + "> - ISSUE !" ); 
		retval = False;
	} else { 
//		_TRACE(SecName + ": Keep Alive Succeeded" ); 
		retval = True;
	}
	return (retval);
}

#2

Hi Just checking to see if AMIBroker support has any ideas on this one? thanks in advance


#3

Proper code tags in Discourse are [code]....your code here [/code]
You have used <code>.....</code> instead and that is incorrect. I fixed your post but please do read “How to use this site” and use proper formatting because it is not fun to fix every post here.

As to the question, mySQL ODBC DRIVER disconnects by itself after reaching timeout. You could modify ODBC plugin sources to close connection and re-open it. It will be much more reliable than using any KeepAlives as it would reset the timeout counter.


#4

Not trying to hijack your thread. But, if you would have searched forum before asking, you did not have to wait for 30 days to get answer. It has already been answered in older threads.


#5

Hi Tomasz thanks. I have set my timeout to the max now. I still think we should have a way to close the connection programmatically rather than having the SQL server close it on us - and I don’t see a way to do that in the ODBC manual (yes I read it many times).

For those of us that don’t know how to do this, here is how you change your timeout.

https://dev.mysql.com/doc/refman/5.5/en/gone-away.html - this will give you a description

GOTO phpMyAdmin to administer your server
GOTO Variables
GOTO the last variable called: wait-timeout
Edit this and set it to the max of 2147483 (default is 8 hours - or 28800)


#6

Thanks for giving back that info to others.