FreeTDS Development Group

[Ian Harding <harding.ian AT gmail.com>]

Vlad,

I am crosssposting to freetds.

I use a Sybase server that times out connections after a certain
amount of time.  Whenever this happens, the nsfreetds handle becomes
useless as you can see in the TDS dump.  The sequence of events was,
issue query, break connection, issue query, issue query.  The first
query after connection was broken says "Read...failed" and the second
(and all subsequent) say "Write...failed."

I am not smart enough to fix this, but it seems like nsfreetds should
detect the dead connection, return an error and tell AOLServer the
connection handle is no good.  I assume that the query would return
the error, but it could be submitted again (get a new connection) and
it would succeed.  It looks like freetds is just trying to "flush" the
handle but not kill it.

What do you think?

util.c:277:Starting log file for FreeTDS 0.64.dev.20050526
        on 2005-05-27 10:08:06 with debug flags 0x4fff.
iconv.c:198:names for ISO-8859-1: ISO-8859-1
iconv.c:198:names for UTF-8: UTF-8 
iconv.c:198:names for UCS-2LE: UCS-2LE
iconv.c:198:names for UCS-2BE: UCS-2BE               
iconv.c:364:iconv to convert client-side data to the "ISO-8859-1" character 
set
iconv.c:517:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
net.c:164:Connecting to 192.103.155.48 port 2638.
net.c:667:Sending packet
0000 02 00 02 00 00 00 00 00-41 4f 4c 73 65 72 76 65 |........ AOLserve|
0010 72 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |r....... ........|
0020 00 00 00 00 00 00 09 69-61 68 00 00 00 00 00 00 |.......<username>......|
0030 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0040 00 00 00 00 00 03 65 6c-76 69 73 00 00 00 00 00 |......<password>.....|
0050 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0060 00 00 00 00 05 33 37 38-37 36 00 00 00 00 00 00 |.....378 76......|
0070 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0080 00 00 00 05 03 01 06 0a-09 01 00 00 00 00 00 00 |........ ........|
0090 00 00 00 00 6e 73 66 72-65 65 74 64 73 00 00 00 |....nsfr eetds...|
00a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00b0 00 00 09 65 6e 76 69 73-69 6f 6e 00 00 00 00 00 |...envis ion.....|
00c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00d0 00 08 00 05 65 6c 76 69-73 00 00 00 00 00 00 00 |....<password>.......|
00e0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
00f0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0100 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0110 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0120 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0130 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0140 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0150 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0160 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0170 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0180 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
0190 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01a0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01b0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01c0 00 00 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |........ ........|
01d0 00 07 05 00 00 00 54 44-53 2d 4c 69 62 72 61 72 |......TD S-Librar|
01e0 0a 05 00 00 00 00 0d 11-75 73 5f 65 6e 67 6c 69 |........ us_engli|
01f0 73 68 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |sh...... ........|

net.c:667:Sending packet
0000 02 01 00 61 00 00 00 00-00 00 00 00 00 00 0a 00 |...a.... ........|
0010 00 00 00 00 00 00 00 00-00 00 00 00 00 69 73 6f |........ .....iso|
0020 5f 31 00 00 00 00 00 00-00 00 00 00 00 00 00 00 |_1...... ........|
0030 00 00 00 00 00 00 00 00-00 00 00 05 01 32 30 34 |........ .....204|
0040 38 00 00 04 00 00 00 00-e2 16 00 01 09 00 00 06 |8....... ........|
0050 6d 7f ff ff ff fe 02 09-00 00 00 00 0a 68 00 00 |m....... .....h..|
0060 00                     -                        |.|

token.c:309:tds_process_login_tokens()
net.c:454:Received header
0000 04 01 00 5a 00 00 00 00-                        |...Z....|

net.c:548:Received packet
0000 e3 08 00 03 05 69 73 6f-5f 31 00 ad 22 00 05 05 |.....iso _1.."...|
0010 00 00 00 18 41 64 61 70-74 69 76 65 20 53 65 72 |....Adap tive Ser|
0020 76 65 72 20 41 6e 79 77-68 65 72 65 08 00 00 00 |ver Anyw here....|
0030 e2 16 00 01 09 00 00 06-61 01 ff ff fe e6 02 09 |........ a.......|
0040 00 00 07 fe ea 68 00 00-0a fd 00 00 01 00 00 00 |.....h.. ........|
0050 00 00                  -                        |..|

token.c:313:looking for login token, got  e3(ENVCHANGE)
token.c:105:tds_process_default_tokens() marker is e3(ENVCHANGE)
read.c:178:tds_get_string: reading 5 from wire to give 5 to client.
token.c:313:looking for login token, got  ad(LOGINACK)
read.c:178:tds_get_string: reading 24 from wire to give 24 to client.
token.c:313:looking for login token, got  e2(CAPABILITY)
token.c:105:tds_process_default_tokens() marker is e2(CAPABILITY)
token.c:313:looking for login token, got  fd(DONE)
token.c:105:tds_process_default_tokens() marker is fd(DONE)
token.c:2115:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2131:tds_process_end() state set to TDS_IDLE
util.c:118:Changing query state from TDS_IDLE to TDS_IDLE
token.c:386:leaving tds_process_login_tokens() returning 1
mem.c:449:tds_free_all_results()
util.c:118:Changing query state from TDS_IDLE to TDS_QUERYING
mem.c:449:tds_free_all_results()
util.c:118:Changing query state from TDS_QUERYING to TDS_PENDING
net.c:667:Sending packet
0000 0f 01 00 26 00 00 00 00-21 19 00 00 00 00 53 45 |...&.... !.....SE|
0010 4c 45 43 54 20 20 64 62-5f 6e 61 6d 65 28 29 20 |LECT  db _name() |
0020 64 62 6e 61 6d 65      -                        |dbname|

util.c:118:Changing query state from TDS_PENDING to TDS_READING
net.c:454:Received header
0000 04 01 00 2f 00 00 00 00-                        |.../....|

net.c:548:Received packet
0000 ee 11 00 01 00 06 64 62-6e 61 6d 65 00 02 00 00 |......db name....|
0010 00 27 80 00 d1 08 45 4e-56 49 53 49 4f 4e fd 10 |.'....EN VISION..|
0020 00 01 00 01 00 00 00   -                        |.......|

token.c:525:processing result tokens.  marker is  ee(RESULT)
mem.c:449:tds_free_all_results()
read.c:178:tds_get_string: reading 6 from wire to give 6 to client.
token.c:1507:processing result. type = 39(varchar), varint_size 1
token.c:1528:processing result. column_size 128
util.c:118:Changing query state from TDS_READING to TDS_PENDING
util.c:118:Changing query state from TDS_PENDING to TDS_READING
token.c:525:processing result tokens.  marker is  d1(ROW)
util.c:118:Changing query state from TDS_READING to TDS_PENDING
util.c:118:Changing query state from TDS_PENDING to TDS_READING
token.c:525:processing result tokens.  marker is  d1(ROW)
token.c:1842:processing row.  column is 0 varint size = 1
token.c:1899:processing row.  column size is 8
util.c:118:Changing query state from TDS_READING to TDS_PENDING
util.c:118:Changing query state from TDS_PENDING to TDS_READING
token.c:525:processing result tokens.  marker is  fd(DONE)
token.c:2115:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 1
token.c:2131:tds_process_end() state set to TDS_IDLE
util.c:118:Changing query state from TDS_READING to TDS_IDLE
token.c:2147:                rows_affected = 1
util.c:118:Changing query state from TDS_IDLE to TDS_PENDING
mem.c:449:tds_free_all_results()
mem.c:449:tds_free_all_results()
util.c:118:Changing query state from TDS_IDLE to TDS_QUERYING
mem.c:449:tds_free_all_results()
util.c:118:Changing query state from TDS_QUERYING to TDS_PENDING
net.c:667:Sending packet
0000 0f 01 00 26 00 00 00 00-21 19 00 00 00 00 53 45 |...&.... !.....SE|
0010 4c 45 43 54 20 20 64 62-5f 6e 61 6d 65 28 29 20 |LECT  db _name() |
0020 64 62 6e 61 6d 65      -                        |dbname|

util.c:118:Changing query state from TDS_PENDING to TDS_READING
token.c:2224:tds_client_msg: #20004: "Read from SQL server failed.". 
Connection state is now 3.
util.c:118:Changing query state from TDS_READING to TDS_DEAD
token.c:525:processing result tokens.  marker is  0()
token.c:105:tds_process_default_tokens() marker is 0()
token.c:108:leaving tds_process_default_tokens() connection dead
util.c:118:Changing query state from TDS_DEAD to TDS_DEAD
util.c:118:Changing query state from TDS_DEAD to TDS_PENDING
util.c:118:Changing query state from TDS_DEAD to TDS_READING
mem.c:449:tds_free_all_results()
mem.c:449:tds_free_all_results()
util.c:118:Changing query state from TDS_DEAD to TDS_QUERYING
token.c:2224:tds_client_msg: #20006: "Write to SQL Server failed.". 
Connection state is now 4.
mem.c:449:tds_free_all_results()


On 5/26/05, Vlad Seryakov <vlad AT crystalballinc.com> wrote:
> Thank you, i was experimenting yesterday and i think i got it working
> right, but with 0.64+ version.
> With versions 0.63+ DB_Cancel should call
> tds_process_simple_query to clear pending results, tds_freeall_resulst
> does not do this.
> 
> Ian Harding wrote:
> > Sorry, that was not really right.
> >
> > "The severity level of an error message provides an indication of the
> > type of problem that Microsoft(r) SQL Serverï¾™ 2000 has encountered.
> >
> >  Severity level 10 messages are informational  and indicate problems
> > caused by mistakes in the information you have entered. Severity
> > levels from 11 through 16 are generated by the user, and can be
> > corrected by the user."
> >
> > I remember experimenting and finding that it was safe to consider
> > error levels 1 - 10 "Notice" information and > 10 always indicating a
> > real "Error" that should be returned to the caller.
> >
> > On 5/25/05, Ian Harding <harding.ian AT gmail.com> wrote:
> >
> >>>10 indicates a critical error.
> >>
> >>On 5/25/05, Vlad Seryakov <vlad AT crystalballinc.com> wrote:
> >>
> >>>Why msg_level > 10, why 10, and not 0?
> >>>
> >>>Ian Harding wrote:
> >>>
> >>>>Vlad,
> >>>>
> >>>>I installed 0.63 and compiled and installed your module.
> >>>>
> >>>>It seems to work fine, but has a glitch I ran into as well which is
> >>>>that it doesn't return error codes when something went wrong with the
> >>>>query itself.  For example, with my hack I get this
> >>>>
> >>>>SQL:
> >>>>
> >>>>    SELECT cast('foo' as int)
> >>>>
> >>>>Results:
> >>>>
> >>>>    error: Database operation "exec" failed (exception NSDB, "Syntax
> >>>>error converting the varchar value 'foo' to a column of data type
> >>>>int.")
> >>>>
> >>>>Rows affected: -1
> >>>>
> >>>>With your version I get
> >>>>
> >>>>SQL:
> >>>>
> >>>>    SELECT cast('foo' as int)
> >>>>
> >>>>Results:
> >>>>
> >>>>Rows affected: -1
> >>>>
> >>>>While the log indicates
> >>>>[25/May/2005:12:46:20][17746.1090534320][-conn:server1::0] Notice:
> >>>>Db_Msg_Handler(fundware): Syntax error converting the varchar value
> >>>>'foo' to a column of data type int.
> >>>>
> >>>>This has to do with a fundamental change in error handling where
> >>>>non-network communication errors are not errors to freetds anymore.
> >>>>They are simply passed back to the application for it to handle.
> >>>>
> >>>>Here is my hack which seems to work but may be terribly wrong for other 
> >>>>reasons.
> >>>>
> >>>>int
> >>>>Ns_FreeTDS_Msg_Handler(TDSCONTEXT *ctx, TDSSOCKET *tds, TDSMESSAGE *msg)
> >>>>{
> >>>>    Ns_DbHandle *handle = (Ns_DbHandle *) tds->parent;
> >>>>
> >>>>    Ns_Log(Notice, "nsfreetds: Ns_FreeTDS_Msg_Handler(%s): %s",
> >>>>        handle->datasource, msg->message);
> >>>>
> >>>>// Begin hack
> >>>>    if ( msg->msg_level > 10 )
> >>>>        Ns_DbSetException(handle, "NSDB", msg->message);
> >>>>// End hack
> >>>>
> >>>>    return 0;
> >>>>}
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>On 5/25/05, Vlad Seryakov <vlad AT crystalballinc.com> wrote:
> >>>>
> >>>>
> >>>>>Try newest FreeTDS 0.63 with my modified nsfreetds driver.
> >>>>>
> >>>>>http://cvs.sourceforge.net/viewcvs.py/naviserver/modules/nsfreetds/
> >>>>>
> >>>>>It does not support interfaces anymore, use freetds.conf and describe
> >>>>>datasource there.
> >>>>>
> >>>>>---- nsd.tcl
> >>>>>
> >>>>>ns_section      "ns/db/pool/cbill"
> >>>>>ns_param        driver                  freetds
> >>>>>ns_param        connections             10
> >>>>>ns_param        user                    cbill
> >>>>>ns_param        password                cbill
> >>>>>ns_param        datasource              Billing
> >>>>>ns_param        verbose                 Off
> >>>>>ns_param        logsqlerrors            On
> >>>>>ns_param        maxidle                 31536000
> >>>>>
> >>>>>---- freetds.conf
> >>>>>[Billing]
> >>>>>        host = cbill
> >>>>>        port = 1433
> >>>>>        tds version = 7.0
> >>>>>
> >>>>>Cory Grimster wrote:
> >>>>>
> >>>>>
> >>>>>>Hi everyone,
> >>>>>>
> >>>>>>I'm having trouble getting nsfreetds running, and I'm hoping that
> >>>>>>someone can spot what I'm doing wrong.  I noticed that several people 
> >>>>>>on
> >>>>>>the list have gotten it working at various times, so I assume it's 
> >>>>>>just me.
> >>>>>>
> >>>>>>I'm using nsfreetds 0.4 with FreeTDS 0.6.1.2 (newer versions of 
> >>>>>>FreeTDS
> >>>>>>won't compile on this server) and AOLserver 4.0.10
> >>>>>>running on Mandrake 10.0.  I'm trying to connect to Microsoft 
> >>>>>>SQLServer
> >>>>>>Desktop Engine 2000 SP4 running on my Windows XP workstation.  I'm
> >>>>>>getting the "could not allocate a handle from database pool "pool4""
> >>>>>>error page when I hit my site.
> >>>>>>
> >>>>>>I can connect to the SQL server with tsql, though it doesn't give me 
> >>>>>>any
> >>>>>>output when I type in SQL commands.  Not sure what that's about.
> >>>>>>Authentication works fine, and it rejects me if I don't give it the
> >>>>>>correct credentials.  Anyway, I'm assuming that it works for now and 
> >>>>>>am
> >>>>>>trying to get nsfreetds working.
> >>>>>>
> >>>>>>The relevent sections from my config.tcl (copied from the nsfreetds 
> >>>>>>FAQ)
> >>>>>>are as follows:
> >>>>>>
> >>>>>>ns_section ns/db/drivers
> >>>>>>   ns_param    freetds            ${bindir}/nsfreetds.so
> >>>>>>
> >>>>>>ns_section ns/db/pools
> >>>>>>   ns_param    pool4              "FreeTDS Pool"
> >>>>>>
> >>>>>>ns_section ns/db/pool/pool4
> >>>>>>   ns_param    maxidle            1000000000
> >>>>>>   ns_param    maxopen            1000000000
> >>>>>>   ns_param    connections        5
> >>>>>>   ns_param    verbose            $debug
> >>>>>>   ns_param    extendedtableinfo  true
> >>>>>>   ns_param    logsqlerrors       $debug
> >>>>>>
> >>>>>>   ns_param    driver             freetds
> >>>>>>   ns_param    datasource         fooserver [also tried 
> >>>>>> fooserver:bardb]
> >>>>>>   ns_param    user               sa
> >>>>>>   ns_param    password           bazpassword
> >>>>>>
> >>>>>>ns_section ns/server/${server}/db
> >>>>>>   ns_param    pools              "*"
> >>>>>>   ns_param    defaultpool        pool4
> >>>>>>
> >>>>>>I have the SYBASE environment variable set to /usr/local/etc, which is
> >>>>>>where my interfaces file lives.  It looks like this:
> >>>>>>
> >>>>>>fooserver
> >>>>>>   query tcp 8.0 192.168.42.107 1433 [I have tried protocol version
> >>>>>>4.2 instead of 8.0 and it makes no difference]
> >>>>>>
> >>>>>>Hopefully someone can point me in the right direction.  I've been
> >>>>>>banging my head against it for a few days now and I'm afraid that I'm
> >>>>>>missing something obvious but am at a loss as to what it might be.
> >>>>>>
> >>>>>>Thanks in advance,
> >>>>>>
> >>>>>>-Cory
> >>>>>>
> >>>>>>
> >>>>>>--
> >>>>>>AOLserver - http://www.aolserver.com/
> >>>>>>
> >>>>>>To Remove yourself from this list, simply send an email to
> >>>>>><listserv AT listserv.aol.com> with the
> >>>>>>body of "SIGNOFF AOLSERVER" in the email message. You can leave the
> >>>>>>Subject: field of your email blank.
> >>>>>
> >>>>>--
> >>>>>Vlad Seryakov
> >>>>>571 262-8608 office
> >>>>>vlad AT crystalballinc.com
> >>>>>http://www.crystalballinc.com/vlad/
> >>>>>
> >>>>>
> >>>>>--
> >>>>>AOLserver - http://www.aolserver.com/
> >>>>>
> >>>>>To Remove yourself from this list, simply send an email to 
> >>>>><listserv AT listserv.aol.com> with the
> >>>>>body of "SIGNOFF AOLSERVER" in the email message. You can leave the 
> >>>>>Subject: field of your email blank.
> >>>>>
> >>>>
> >>>>
> >>>>
> >>>>--
> >>>>AOLserver - http://www.aolserver.com/
> >>>>
> >>>>To Remove yourself from this list, simply send an email to 
> >>>><listserv AT listserv.aol.com> with the
> >>>>body of "SIGNOFF AOLSERVER" in the email message. You can leave the 
> >>>>Subject: field of your email blank.
> >>>
> >>>--
> >>>Vlad Seryakov
> >>>571 262-8608 office
> >>>vlad AT crystalballinc.com
> >>>http://www.crystalballinc.com/vlad/
> >>>
> >>>
> >>>--
> >>>AOLserver - http://www.aolserver.com/
> >>>
> >>>To Remove yourself from this list, simply send an email to 
> >>><listserv AT listserv.aol.com> with the
> >>>body of "SIGNOFF AOLSERVER" in the email message. You can leave the 
> >>>Subject: field of your email blank.
> >>>
> >>
> >
> >
> > --
> > AOLserver - http://www.aolserver.com/
> >
> > To Remove yourself from this list, simply send an email to 
> > <listserv AT listserv.aol.com> with the
> > body of "SIGNOFF AOLSERVER" in the email message. You can leave the 
> > Subject: field of your email blank.
> 
> --
> Vlad Seryakov
> 571 262-8608 office
> vlad AT crystalballinc.com
> http://www.crystalballinc.com/vlad/
> 
> 
> --
> AOLserver - http://www.aolserver.com/
> 
> To Remove yourself from this list, simply send an email to 
> <listserv AT listserv.aol.com> with the
> body of "SIGNOFF AOLSERVER" in the email message. You can leave the 
> Subject: field of your email blank.
>