RE: reconnect failing in 3.51.12

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@xxxxxxxxxx

+1(480)308-0641 (voice)

+1(602)996-0376 (fax)

 

  _____  

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