reconnect failing in 3.51.12

--=__Part4662C3AF.0__=
Content-Type: multipart/alternative; boundary="=__Part4662C3AF.1__="

--=__Part4662C3AF.1__=
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I've got a linux daemon that logs to myodbc 3.51.12 through unixODBC driver manager. The first time my program receives a command it establishes an entire ODBC environment via the following (abbreviated) code:

int connect()
{
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hsql);
SQLSetEnvAttr(hsql, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, hsql, &hdbc);
SQLConnect(hdbc, (SQLCHAR*)dsn, SQL_NTS, (SQLCHAR*)user, SQL_NTS, (SQLCHAR*)passwd, SQL_NTS);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
SQLPrepare(hstmt, primary_stmt, SQL_NTS);

for (i = 0; i < elemcount(pa); i++)
SQLBindParameter(hstmt, j++, SQL_PARAM_INPUT, pa[i].c_type, pa[i].s_type, pa[i].csize, 0, pa[i].pvp, 0, &ind[i]);
}

void disconnect()
{
if (hstmt != 0)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != 0)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (hsql != 0)
SQLFreeHandle(SQL_HANDLE_ENV, hsql);
hstmt = hdbc = hsql = 0;
}
Of course, I check all of the error returns in the real code. The daemon monitors for intermittent request traffic and logs things to the database as requests come in. If there's an 8 hour delay between requests, the ODBC connection times out. I expect this condition in the log code as follows:

if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
{
disconnect();
if (connect() != 0)
return -1;
else if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
return -1;
}
My problem is that I cannot re-establish the connection once it's been broken like this. I get errors back from myodbc like this:

(a snippet from my daemon's log file)
[Wed Feb 7 15:26:32 2007] Entering main run loop...

(first request established initial connection)
[Wed Feb 7 15:27:48 2007] xdm_odbc: Connection established. [DSN=xdas;UID=root;PWD=;].

(second request - over 8 hours later - tries to re-establish connection)
[Fri Feb 9 02:52:56 2007] xdm_odbc: the ODBC driver reported -1 during SQLConnect:
[Fri Feb 9 02:52:56 2007] xdm_odbc: HYT00:1:2019:[unixODBC][MySQL][ODBC 3.51 Driver]Can't initialize character set latin1 (path: /usr/share/mysql/charsets/)
[Fri Feb 9 02:52:56 2007] xdm_odbc: ODBC Reconnect failed, error -1.
What am I doing wrong? It seems simple enough to me. I noticed on the mailing list that a few folks had trouble using SQLDriverConnect, so I switched to SQLConnect, and now I get other types of errors trying to reconnect - not the least of these are that my daemon segfaults down inside of libmyodbc with the following stack trace:

#0 0xb741dc5d in my_strcasecmp_8bit () from /usr/lib/mysql/libmysqlclient.so.15
#1 0xb741514d in get_charset_number () from /usr/lib/mysql/libmysqlclient.so.15
#2 0xb74151ab in get_charset_by_csname () from /usr/lib/mysql/libmysqlclient.so.15
#3 0xb74320ec in mysql_real_connect () from /usr/lib/mysql/libmysqlclient.so.15
#4 0xb753c15a in SQLConnect (hdbc=0x807fe78, szDSN=0xb7d8df51, cbDSN=-3,
szUID=0xb7d8e270, cbUID=-3, szAuthStr=0xb7d8e1f0, cbAuthStr=-3) at connect.c:253
#5 0xb7d9d394 in SQLConnect (connection_handle=0x80616f0,
server_name=0xb7d8e2f0, name_length1=-3, user_name=0xb7d8e270,
name_length2=-3, authentication=0xb7d8e1f0, name_length3=-3) at SQLConnect.c:3820
#6 0xb7f63c23 in sql_connect () at xdm_odbc.c:322
#7 0xb7f6411a in xdm_append (msg=0x80776a9, msgsz=272) at xdm_odbc.c:394
#8 0x0804a3cf in xdasd_logger_append (msg=0x8077598, msgsz=272) at xdasd_logger.c:175
#9 0x0804a0e2 in bg_logger (unused=0x0) at xdasd_lcache.c:90
#10 0xb7f3b34b in start_thread () from /lib/libpthread.so.0
#11 0xb7ed365e in clone () from /lib/libc.so.6
Any ideas would be appreciated.

Thanks,
John


-----
John Calcote (jcalcote@novell.com)
Sr. Software Engineeer
Novell, Inc.

--=__Part4662C3AF.1__=
Content-Type: text/html; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML


">

RE: reconnect failing in 3.51.12

------=_NextPart_000_0001_01C74C88.FEA507C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

John,



Lawson Cronlundlawson@vrtinc.com

+1(480)308-0641 (voice)

+1(602)996-0376 (fax)



_____

From: John Calcote [mailto:jcalcote@novell.com]
Sent: Friday, February 09, 2007 4:55 PM
To: myodbc@lists.mysql.com
Subject: reconnect failing in 3.51.12



I've got a linux daemon that logs to myodbc 3.51.12 through unixODBC driver
manager. The first time my program receives a command it establishes an
entire ODBC environment via the following (abbreviated) code:



int connect()

{

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hsql);

SQLSetEnvAttr(hsql, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, hsql, &hdbc);

SQLConnect(hdbc, (SQLCHAR*)dsn, SQL_NTS, (SQLCHAR*)user, SQL_NTS,
(SQLCHAR*)passwd, SQL_NTS);

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

SQLPrepare(hstmt, primary_stmt, SQL_NTS);



for (i = 0; i < elemcount(pa); i++)

SQLBindParameter(hstmt, j++, SQL_PARAM_INPUT, pa[i].c_type,
pa[i].s_type, pa[i].csize, 0, pa[i].pvp, 0, &ind[i]);

}



void disconnect()
{
if (hstmt != 0)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != 0)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (hsql != 0)
SQLFreeHandle(SQL_HANDLE_ENV, hsql);
hstmt = hdbc = hsql = 0;
}

Of course, I check all of the error returns in the real code. The daemon
monitors for intermittent request traffic and logs things to the database as
requests come in. If there's an 8 hour delay between requests, the ODBC
connection times out. I expect this condition in the log code as follows:



if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
{
disconnect();
if (connect() != 0)
return -1;
else if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
return -1;
}

My problem is that I cannot re-establish the connection once it's been
broken like this. I get errors back from myodbc like this:



(a snippet from my daemon's log file)

[Wed Feb 7 15:26:32 2007] Entering main run loop...



(first request established initial connection)
[Wed Feb 7 15:27:48 2007] xdm_odbc: Connection established.
[DSN=xdas;UID=root;PWD=;].



(second request - over 8 hours later - tries to re-establish connection)
[Fri Feb 9 02:52:56 2007] xdm_odbc: the ODBC driver reported -1 during
SQLConnect:
[Fri Feb 9 02:52:56 2007] xdm_odbc: HYT00:1:2019:[unixODBC][MySQL][ODBC
3.51 Driver]Can't initialize character set latin1 (path:
/usr/share/mysql/charsets/)
[Fri Feb 9 02:52:56 2007] xdm_odbc: ODBC Reconnect failed, error -1.

What am I doing wrong? It seems simple enough to me. I noticed on the
mailing list that a few folks had trouble using SQLDriverConnect, so I
switched to SQLConnect, and now I get other types of errors trying to
reconnect - not the least of these are that my daemon segfaults down inside
of libmyodbc with the following stack trace:



#0 0xb741dc5d in my_strcasecmp_8bit () from
/usr/lib/mysql/libmysqlclient.so.15
#1 0xb741514d in get_charset_number () from
/usr/lib/mysql/libmysqlclient.so.15
#2 0xb74151ab in get_charset_by_csname () from
/usr/lib/mysql/libmysqlclient.so.15
#3 0xb74320ec in mysql_real_connect () from
/usr/lib/mysql/libmysqlclient.so.15
#4 0xb753c15a in SQLConnect (hdbc=0x807fe78, szDSN=0xb7d8df51, cbDSN=-3,
szUID=0xb7d8e270, cbUID=-3, szAuthStr=0xb7d8e1f0, cbAuthStr=-3) at
connect.c:253
#5 0xb7d9d394 in SQLConnect (connection_handle=0x80616f0,
server_name=0xb7d8e2f0, name_length1=-3, user_name=0xb7d8e270,
name_length2=-3, authentication=0xb7d8e1f0, name_length3=-3) at
SQLConnect.c:3820
#6 0xb7f63c23 in sql_connect () at xdm_odbc.c:322
#7 0xb7f6411a in xdm_append (msg=0x80776a9, msgsz=272) at xdm_odbc.c:394
#8 0x0804a3cf in xdasd_logger_append (msg=0x8077598, msgsz=272) at
xdasd_logger.c:175
#9 0x0804a0e2 in bg_logger (unused=0x0) at xdasd_lcache.c:90
#10 0xb7f3b34b in start_thread () from /lib/libpthread.so.0
#11 0xb7ed365e in clone () from /lib/libc.so.6

Any ideas would be appreciated.



Thanks,

John





-----
John Calcote (jcalcote@novell.com)
Sr. Software Engineeer
Novell, Inc.


------=_NextPart_000_0001_01C74C88.FEA507C0--

RE: reconnect failing in 3.51.12

------=_NextPart_000_0009_01C74C89.5FFEE7C0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

John,

My approach to this problem was to keep track of the amount of time between
queries to the MySQL database. If it got to be more than 4 hours
(approximately, I made a "do nothing" query to the database to reset its
no-activity timer.

Kind of dirty but it's worked for over a year now.

Regards,



Lawson Cronlund

lawson@vrtinc.com

+1(480)308-0641 (voice)

+1(602)996-0376 (fax)



_____

From: John Calcote [mailto:jcalcote@novell.com]
Sent: Friday, February 09, 2007 4:55 PM
To: myodbc@lists.mysql.com
Subject: reconnect failing in 3.51.12



I've got a linux daemon that logs to myodbc 3.51.12 through unixODBC driver
manager. The first time my program receives a command it establishes an
entire ODBC environment via the following (abbreviated) code:



int connect()

{

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hsql);

SQLSetEnvAttr(hsql, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, hsql, &hdbc);

SQLConnect(hdbc, (SQLCHAR*)dsn, SQL_NTS, (SQLCHAR*)user, SQL_NTS,
(SQLCHAR*)passwd, SQL_NTS);

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

SQLPrepare(hstmt, primary_stmt, SQL_NTS);



for (i = 0; i < elemcount(pa); i++)

SQLBindParameter(hstmt, j++, SQL_PARAM_INPUT, pa[i].c_type,
pa[i].s_type, pa[i].csize, 0, pa[i].pvp, 0, &ind[i]);

}



void disconnect()
{
if (hstmt != 0)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc != 0)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (hsql != 0)
SQLFreeHandle(SQL_HANDLE_ENV, hsql);
hstmt = hdbc = hsql = 0;
}

Of course, I check all of the error returns in the real code. The daemon
monitors for intermittent request traffic and logs things to the database as
requests come in. If there's an 8 hour delay between requests, the ODBC
connection times out. I expect this condition in the log code as follows:



if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
{
disconnect();
if (connect() != 0)
return -1;
else if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
return -1;
}

My problem is that I cannot re-establish the connection once it's been
broken like this. I get errors back from myodbc like this:



(a snippet from my daemon's log file)

[Wed Feb 7 15:26:32 2007] Entering main run loop...



(first request established initial connection)
[Wed Feb 7 15:27:48 2007] xdm_odbc: Connection established.
[DSN=xdas;UID=root;PWD=;].



(second request - over 8 hours later - tries to re-establish connection)
[Fri Feb 9 02:52:56 2007] xdm_odbc: the ODBC driver reported -1 during
SQLConnect:
[Fri Feb 9 02:52:56 2007] xdm_odbc: HYT00:1:2019:[unixODBC][MySQL][ODBC
3.51 Driver]Can't initialize character set latin1 (path:
/usr/share/mysql/charsets/)
[Fri Feb 9 02:52:56 2007] xdm_odbc: ODBC Reconnect failed, error -1.

What am I doing wrong? It seems simple enough to me. I noticed on the
mailing list that a few folks had trouble using SQLDriverConnect, so I
switched to SQLConnect, and now I get other types of errors trying to
reconnect - not the least of these are that my daemon segfaults down inside
of libmyodbc with the following stack trace:



#0 0xb741dc5d in my_strcasecmp_8bit () from
/usr/lib/mysql/libmysqlclient.so.15
#1 0xb741514d in get_charset_number () from
/usr/lib/mysql/libmysqlclient.so.15
#2 0xb74151ab in get_charset_by_csname () from
/usr/lib/mysql/libmysqlclient.so.15
#3 0xb74320ec in mysql_real_connect () from
/usr/lib/mysql/libmysqlclient.so.15
#4 0xb753c15a in SQLConnect (hdbc=0x807fe78, szDSN=0xb7d8df51, cbDSN=-3,
szUID=0xb7d8e270, cbUID=-3, szAuthStr=0xb7d8e1f0, cbAuthStr=-3) at
connect.c:253
#5 0xb7d9d394 in SQLConnect (connection_handle=0x80616f0,
server_name=0xb7d8e2f0, name_length1=-3, user_name=0xb7d8e270,
name_length2=-3, authentication=0xb7d8e1f0, name_length3=-3) at
SQLConnect.c:3820
#6 0xb7f63c23 in sql_connect () at xdm_odbc.c:322
#7 0xb7f6411a in xdm_append (msg=0x80776a9, msgsz=272) at xdm_odbc.c:394
#8 0x0804a3cf in xdasd_logger_append (msg=0x8077598, msgsz=272) at
xdasd_logger.c:175
#9 0x0804a0e2 in bg_logger (unused=0x0) at xdasd_lcache.c:90
#10 0xb7f3b34b in start_thread () from /lib/libpthread.so.0
#11 0xb7ed365e in clone () from /lib/libc.so.6

Any ideas would be appreciated.



Thanks,

John





-----
John Calcote (jcalcote@novell.com)
Sr. Software Engineeer
Novell, Inc.


------=_NextPart_000_0009_01C74C89.5FFEE7C0--

RE: reconnect failing in 3.51.12

--=__PartBF9B394B.0__=
Content-Type: multipart/alternative; boundary="=__PartBF9B394B.1__="

--=__PartBF9B394B.1__=
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Lawson,
=20
Thanks for the feedback.=20
=20
I had a horrible feeling it might come down to something like this. :) If =
you don't mind, I'll hold out for a few more answers, just in case - we =
might both learn something. But in the end, I'll have to do what you =
suggest if the folks at MySQL don't see the need to fix the issue - or at =
least give us a decent work-around.
=20
Regards,
=20
John
=20
-----
John Calcote (jcalcote@novell.com)
Sr. Software Engineeer
Novell, Inc.


>>> On Fri, Feb 9, 2007 at 8:32 PM, in message <000801c74cc4$0c5dbfc0$6500=
a8c0@winxppro>, "Lawson Cronlund" wrote:

John,
My approach to this problem was to keep track of the amount of time =
between queries to the MySQL database. If it got to be more than 4 hours =
(approximately, I made a *do nothing* query to the database to reset its =
no-activity timer.
Kind of dirty but it*s worked for over a year now.
Regards,
=20

Lawson Cronlund
lawson@vrtinc.com
+1(480)308-0641 (voice)
+1(602)996-0376 (fax)
=20


From:John Calcote [mailto:jcalcote@novell.com]=20
Sent: Friday, February 09, 2007 4:55 PM
To: myodbc@lists.mysql.com=20
Subject: reconnect failing in 3.51.12

=20

I've got a linux daemon that logs to myodbc 3.51.12 through unixODBC =
driver manager. The first time my program receives a command it establishes=
an entire ODBC environment via the following (abbreviated) code:

=20

int connect()

{

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hsql);

SQLSetEnvAttr(hsql, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, hsql, &hdbc);

SQLConnect(hdbc, (SQLCHAR*)dsn, SQL_NTS, (SQLCHAR*)user, SQL_NTS, =
(SQLCHAR*)passwd, SQL_NTS);

SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

SQLPrepare(hstmt, primary_stmt, SQL_NTS);

=20

for (i =3D 0; i < elemcount(pa); i++)

SQLBindParameter(hstmt, j++, SQL_PARAM_INPUT, pa[i].c_type, =
pa[i].s_type, pa[i].csize, 0, pa[i].pvp, 0, &ind[i]);

}

=20

void disconnect()
{
if (hstmt !=3D 0)
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (hdbc !=3D 0)
{
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
}
if (hsql !=3D 0)
SQLFreeHandle(SQL_HANDLE_ENV, hsql);
hstmt =3D hdbc =3D hsql =3D 0;
}

Of course, I check all of the error returns in the real code. The daemon =
monitors for intermittent request traffic and logs things to the database =
as requests come in. If there's an 8 hour delay between requests, the ODBC =
connection times out. I expect this condition in the log code as follows:

=20

if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
{
disconnect();
if (connect() !=3D 0)
return -1;
else if (!SQL_SUCCEEDED(SQLExecute(hstmt)))
return -1;
}

My problem is that I cannot re-establish the connection once it's been =
broken like this. I get errors back from myodbc like this:

=20

(a snippet from my daemon's log file)

[Wed Feb 7 15:26:32 2007] Entering main run loop...

=20

(first request established initial connection)
[Wed Feb 7 15:27:48 2007] xdm_odbc: Connection established. [DSN=3Dxdas=
;UID=3Droot;PWD=3D;].

=20

(second request - over 8 hours later - tries to re-establish connection)=

[Fri Feb 9 02:52:56 2007] xdm_odbc: the ODBC driver reported -1 during =
SQLConnect:
[Fri Feb 9 02:52:56 2007] xdm_odbc: HYT00:1:2019:[unixODBC][MySQL][ODBC=
3.51 Driver]Can't initialize character set latin1 (path: /usr/share/mysql/=
charsets/)
[Fri Feb 9 02:52:56 2007] xdm_odbc: ODBC Reconnect failed, error -1.

What am I doing wrong? It seems simple enough to me. I noticed on the =
mailing list that a few folks had trouble using SQLDriverConnect, so I =
switched to SQLConnect, and now I get other types of errors trying to =
reconnect - not the least of these are that my daemon segfaults down =
inside of libmyodbc with the following stack trace:

=20

#0 0xb741dc5d in my_strcasecmp_8bit () from /usr/lib/mysql/libmysqlclient.=
so.15
#1 0xb741514d in get_charset_number () from /usr/lib/mysql/libmysqlclient.=
so.15
#2 0xb74151ab in get_charset_by_csname () from /usr/lib/mysql/libmysqlclie=
nt.so.15
#3 0xb74320ec in mysql_real_connect () from /usr/lib/mysql/libmysqlclient.=
so.15
#4 0xb753c15a in SQLConnect (hdbc=3D0x807fe78, szDSN=3D0xb7d8df51, =
cbDSN=3D-3,
szUID=3D0xb7d8e270, cbUID=3D-3, szAuthStr=3D0xb7d8e1f0, cbAuthStr=3D-3)=
at connect.c:253
#5 0xb7d9d394 in SQLConnect (connection_handle=3D0x80616f0,
server_name=3D0xb7d8e2f0, name_length1=3D-3, user_name=3D0xb7d8e270,
name_length2=3D-3, authentication=3D0xb7d8e1f0, name_length3=3D-3) at =
SQLConnect.c:3820
#6 0xb7f63c23 in sql_connect () at xdm_odbc.c:322
#7 0xb7f6411a in xdm_append (msg=3D0x80776a9, msgsz=3D272) at xdm_odbc.c:3=
94
#8 0x0804a3cf in xdasd_logger_append (msg=3D0x8077598, msgsz=3D272) at =
xdasd_logger.c:175
#9 0x0804a0e2 in bg_logger (unused=3D0x0) at xdasd_lcache.c:90
#10 0xb7f3b34b in start_thread () from /lib/libpthread.so.0
#11 0xb7ed365e in clone () from /lib/libc.so.6

Any ideas would be appreciated.

=20

Thanks,

John

=20

=20

-----
John Calcote (jcalcote@novell.com)
Sr. Software Engineeer
Novell, Inc.

--=__PartBF9B394B.1__=
Content-Type: text/html; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable
Content-Description: HTML


">