RE: reconnect failing in 3.51.12

Lawson,
 
Thanks for the feedback. 
 
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.
 
Regards,
 
John
 
-----
John Calcote ([email protected])
Sr. Software Engineeer
Novell, Inc.


>>> On Fri, Feb 9, 2007 at  8:32 PM, in message <000801c74cc4$0c5dbfc0$6500a8c0@winxppro>, "Lawson Cronlund" <[email protected]> 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,
 

Lawson Cronlund
[email protected]
+1(480)308-0641 (voice)
+1(602)996-0376 (fax)
 


From:John Calcote [mailto:[email protected]] 
Sent: Friday, February 09, 2007 4:55 PM
To: [email protected] 
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 ([email protected])
Sr. Software Engineeer
Novell, Inc.


-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/[email protected]

BEGIN:VCARD
VERSION:2.1
X-GWTYPE:USER
FN:John Calcote
TEL;WORK:1-801-861-7517
ORG:;Unified Identity System Eng TE
TEL;PREF;FAX:801/861-2292
EMAIL;WORK;PREF;NGW:[email protected]
N:Calcote;John;;Sr. Software Engineer
TITLE:Sr. Software Engineer
ADR;DOM;WORK;PARCEL;POSTAL:;PRV-H-511;;Provo
LABEL;DOM;WORK;PARCEL;POSTAL;ENCODING=QUOTED-PRINTABLE:John Calcote=0A=
PRV-H-511=0A=
Provo
END:VCARD