List:MySQL ODBC« Previous MessageNext Message »
From:Martin J. Evans Date:December 15 2005 11:58am
Subject:RE: Some metadata functions not spotting mysql server is dead and disappearing tables
View as plain text  
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 <stdio.h>
#include <sql.h>
#include <sqlext.h>

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

Thread
Some metadata functions not spotting mysql server is dead and disappearing tablesMartin J. Evans13 Dec
RE: Some metadata functions not spotting mysql server is dead and disappearing tablesDavid Dindorp13 Dec
  • RE: Some metadata functions not spotting mysql server is dead and disappearing tablesMartin J. Evans13 Dec
RE: Some metadata functions not spotting mysql server is dead and disappearing tablesDavid Dindorp15 Dec
  • RE: Some metadata functions not spotting mysql server is dead and disappearing tablesMartin J. Evans15 Dec
RE: Some metadata functions not spotting mysql server is dead and disappearing tablesDavid Dindorp16 Dec