Some metadata functions not spotting mysql server is dead and disappearing tables
am 13.12.2005 15:29:44 von Martin.Evans
Hi,
(I don't particularly like cross-posting so I sent a slightly
different email to the dbi-users list in case anyone else hits this problem).
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. What DBD::ODBC does for ping is basically:
SQLTables(NOXXTABLE) returns SQL_SUCCESS
SQLDescribeCol returns SQL_SUCCESS
SQLBindCol returns SQL_SUCCESS
SQLFetch returns SQL_NO_DATA
i.e. no errors and no sign that the mysql server might be dead. In fact
if you do this:
SQLConnect/SQLDriverConnect to running mysql server
add a table to mysql database from another connection
stop the mysql server
SQLTables(the new table) works
SQLDescribeCol works
SQLBindCol works
SQLFetch returns SQL_NO_DATA
new table not seen and myodbc has no idea the mysql server has been
stopped (or so I thought):
What gave away the problem was when I reversed the above. i.e. I created a
table, connected via mysql odbc driver, ran SQLTables which showed the new
table, closed mysql server down, ran SQLTables again and my table has
disappeared. In fact, the database contains no tables at all!
I think there is a bug here, where mysql odbc driver is attempting to contact
the mysql server and when it fails it is not reporting it. This seems a rather
serious issue to me since the app can't tell the server is dead via some
metadata calls (just looks like the database has no tables) - it has to do a
SQLPrepare/SQLExecute before the dead server is seen.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org
RE: Some metadata functions not spotting mysql server is dead and disappearing tables
am 13.12.2005 17:23:32 von Martin.Evans
On 13-Dec-2005 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.
David,
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. i.e.
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.
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.
Surely, SQLTables should return an error if the server has gone away, unless
there is some sort of metadata caching going on.
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).
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org
RE: Some metadata functions not spotting mysql server is dead and disappearing tables
am 15.12.2005 11:58:00 von David Dindorp
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=3D14639 ?
> >
> > 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.
>=20
> 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.
> 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.
> 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.
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.
> 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..
--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg
RE: Some metadata functions not spotting mysql server is dead and disappearing tables
am 15.12.2005 12:58:37 von Martin.Evans
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
--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org
RE: Some metadata functions not spotting mysql server is dead and disappearing tables
am 16.12.2005 14:59:10 von David Dindorp
------_=_NextPart_001_01C60248.E3B936EA
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Martin J. Evans wrote:
> The following code demonstrates the problem you describe:
[snip code]
Cool, thanks!
> I expected SQLTables or SQLFetch to
> fail if the database connection was
> dropped - it does not fail in 3.51.12.
Really?
I've modified your code to run SQLFetch also.
On my workstation with 3.51.12, SQLFetch returns
-1 (SQL_ERROR) when I yank the server.
Could you take a look at the changed code
(attached) and see what I'm missing?
> 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.
In my case it never has.
As I've stated in bug 14639, MyODBC has never returned anything
other than "success" for SQL_ATTR_CONNECTION_DEAD.
Always returning success was in fact sort of the correct thing to
do before 3.51.12, because older versions would automatically
reconnect behind your back when the connection was lost.
(By means of mysql_ping(), I believe.)
3.51.12 does *not* reconnect, which is why the missing ping/
'remember-connection-state-after-queries'/SQL_ATTR_CONNECTIO N_DEAD
functionality has suddenly become a big problem. Most things do
not break when MyODBC reconnects silently in the background and then
return OK, but when it does _not_ reconnect and _still_ returns OK,
stuff breaks.
> dead =3D 0 <--------should be SQL_CD_TRUE =3D 1
I agree that's a big, bad bug.
But 3.51.09 also returns "dead =3D 0". So unfortunately the test
code doesn't excercise my problem.
> I've not looked at the myodbc code
Where can it be found? I know it's GPL,
but I'm missing myodbc3 on http://mysql.bkbits.net/.
> 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.
Bug 14639, which is a very simple consistent bug with a test case
which should affect a lot of users has been open for more than a
month now. I have a memory leak issue through our paid support
which is almost a year old with no fix.
MyODBC is the most stable connector for many MySQL users, yet still
it seems bugs are not fixed, developers give little feedback on them
and generally I agree with you that not much is happening :-).
I wouldn't recommend MySQL to anyone looking
for an ODBC-compatible database at this time.
------_=_NextPart_001_01C60248.E3B936EA
Content-Type: text/plain; charset=us-ascii
--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org
------_=_NextPart_001_01C60248.E3B936EA--