From: Martin J. Evans Date: December 15 2005 11:58am Subject: RE: Some metadata functions not spotting mysql server is dead and disappearing tables List-Archive: http://lists.mysql.com/myodbc/10529 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 8bit On 15-Dec-2005 David Dindorp wrote: > Martin J. Evans wrote: >> David Dindorp wrote: >> > Martin J. Evans wrote: >> > > I was attempting to use perl DBI and the ping method to see if >> > > the myodbc connection was still working and discovered that in >> > > myodbc 3.51.12 (at least) ping always returns true. >> > >> > Sounds a bit like the Perl version of this issue: >> > http://bugs.mysql.com/bug.php?id=14639 ? >> > >> > Issue details: >> > The ConnectionState == open/closed mechanism in the .NET ODBC >> > classes (corresponds to DBD::ODBC ping) doesn't work anymore >> > after upgrading to 3.51.12. >> > >> > .NET uses an SQL_ATTR_CONNECTION_DEAD call to the ODBC >> > driver to return the ConnectionState property's value. >> >> It is in a similar area but I don't think the same. The problem with >> SQL_ATTR_CONNECTION_DEAD has always been the statement in the spec >> which says using asking for SQL_ATTR_CONNECTION_DEAD should NOT cause >> a round-trip to the server. As a result, I'd never expect >> SQL_ATTR_CONNECTION_DEAD to be set until AFTER a failure. > > That was the behaviour before 3.51.12. Then, before 3.51.12 it looks like it was right. >> just asking for SQL_ATTR_CONNECTION_DEAD at any time will nearly >> always return not dead but asking for it after an error might >> return dead if the driver now knows the previous failing operation >> has caused it to lose connection to the server. > > Also the behaviour before 3.51.12. > With 3.51.12 it seems that SQL_ATTR_CONNECTION_DEAD > just returns "not dead", always. Ok and if that is so it would seem like a bug but I'm using myodbc behind DBD::ODBC in Perl and DBD::ODBC's ping method does: SQLTables("", "", "NOXXTABLE", "") returns successfully SQLFetch returns SQL_NO_DATA (as there are no rows) I expected SQLTables or SQLFetch to fail if the database connection was dropped - it does not fail in 3.51.12. This means if an app connects via myodbc, the connection to the server is lost and the app asks for a list of tables it will just look like there are no tables - the app won't know the database connection is broken. >> I may be wrong (the reference in the above URL to reconnection >> in particular) but I think my case is more straight forward. >> Any call to SQLTables when the server is dead does not return an >> error, neither do SQLDescribeCol, SQLBindCol or SQLFetch and there >> is no hope of reconnection because the mysql server was stopped. > > I remain unconvinced that we're not seeing > the same since the symptoms are extremely similar. I imagine that the symptoms are the same since we are both attempting to see if the connection is valid but what I meant was the reason why it is not working are different in your case and mine. Anyway, the following code demonstrates the problem you describe: #include #include #include main() { SQLHENV env; SQLHDBC dbc; SQLHSTMT stmt; SQLRETURN ret; char buf[100]; SQLINTEGER dead; SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env); SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0); SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc); SQLDriverConnect(dbc, NULL, "DSN=xxx;UID=yyy;PWD=zzz;", SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT); SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt); SQLGetConnectAttr(dbc, SQL_ATTR_CONNECTION_DEAD, &dead, sizeof(dead), NULL); printf("dead = %d\nWaiting for CR\n", dead); gets(buf); SQLGetConnectAttr(dbc, SQL_ATTR_CONNECTION_DEAD, &dead, sizeof(dead), NULL); printf("dead = %d\n", dead); printf("prepare = %d\n", SQLPrepare(stmt, "select xxx from yyy", SQL_NTS)); printf("execute = %d\n", SQLExecute(stmt)); ret = SQLGetConnectAttr(dbc, SQL_ATTR_CONNECTION_DEAD, &dead, sizeof(dead), NULL); printf("dead = %d\n", dead); } compile it, run it and when it stops, go and shut down your mysql server and hit return - it should say the connection is dead because the SQLExecute will spot the server is down - it doesn't. A run: dead = 0 Waiting for CR dead = 0 prepare = 0 execute = -1 dead = 0 bash-2.05$ ./a.out dead = 0 Waiting for CR dead = 0 prepare = 0 execute = -1 dead = 0 <--------should be SQL_CD_TRUE = 1 > But then again I know nothing of MyODBC internals. > > I'm just sort of imagining that the calls you > mentioned internally uses the same mechanics as > SQL_ATTR_CONNECTION_DEAD to determine whether the > connection is alive. I've not looked at the myodbc code but usually an ODBC driver sets SQL_ATTR_CONNECTION_DEAD to false on connection then if any communication with the database results in a status indicating the connection is broken it sets SQL_ATTR_CONNECTION_DEAD to true. Usually asking for SQL_ATTR_CONNECTION_DEAD does not do anything at all but return that flag. >> To work around this I've had to implement my own DBI ping which >> does a SQLPrepare/SQLExecute (the execute fails if the server is >> dead). However, I've posted to the dbi-users list since as far as >> I can see DBI->ping will not work for MySQL ODBC driver >> (3.51.12 at least). > > I had to use a similar workaround :-). > This should really be fixed (both, if they are in fact > separate problems) before more people upgrade to 3.51.12.. Given the other recent postings on this list re myodbc 3 and 5 it does not sound like they will get fixed unless we fix them. I/we (easysoft) have posted other patches for myodbc but I didn't see any recognition that they were accepted or even used so I'm not going to spend any time looking through myodbc to fix these new issues when I can work around them now. If the situation changes I'd be happy to look into it further. Martin -- Martin J. Evans Easysoft Ltd, UK Development