Re: SQL-08S01 error in FreeTDS/Azure when executing stored proc for a few minutes from Linux (but not from Windows)

Can you try this patch?

It decrease keepalive times to 30 seconds. Well.. where supported
(Linux for sure).

Frediano

2012/11/21 Cade Roux <[email protected]>:
> I see - that explains the problem when the flat->star-model transform
> proc after the load was taking a long time (but again, that was
> working from a Windows-based run of the identical script, but if there
> is similar one-sided termination for some reason only Azure knows,
> then that is explained).
>
> Now that the flat->star transform is basically instant, and there
> should be constant client traffic from the truncate, the inserts, the
> row count and the transform, I need to break down the log from a
> reproduced case adding some timings to make it easier to post here.
>
> Thanks,
>
> Cade
> Cade Roux
> [email protected]
> 504-717-4887
>
>
> On Wed, Nov 21, 2012 at 2:36 PM, James K. Lowden <[email protected]> wrote:
>> On Wed, 21 Nov 2012 09:03:28 -0600
>> Cade Roux <[email protected]> wrote:
>>
>>> I'm thinking that Azure "pauses" the connection at
>>> some point and the inserts fail and then the connection starts working
>>> again (I reuse the same connection for all the files).  Why this
>>> happens only on the start of a file is a bit of a mystery, although
>>> obviously there is a small time gap between files for the count to be
>>> take, the file to be close, the new file to be opened, and the table
>>> to be truncated before the next set of inserts.
>>
>> Looking back at your first message, I see this error in your log:
>>
>>     net.c:1257:GNUTLS: level 4:
>>       REC[0x9a833c8]: Sent Packet[6] Application Data(23) with length:
>> 101
>>     token.c:540:tds_process_tokens(0x9a574b8, 0xbfcb9ca8, 0xbfcb9cac,
>> 0x6914)
>>     util.c:156:Changed query state from PENDING to READING
>>     net.c:1199:in tds_pull_func
>>     util.c:331:tdserror(0x9a54e80, 0x9a574b8, 20004, 104)
>>     odbc.c:2270:msgno 20004 20003
>>     util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
>>     util.c:384:tdserror: returning TDS_INT_CANCEL(2)
>>     util.c:156:Changed query state from READING to DEAD
>>     net.c:1257:GNUTLS: level 7:
>>       READ: -1 returned from 0x9a574b8, errno=104 gerrno=0
>>
>> tds_pull_func reads encrypted data.  From the log messages, it would
>> appear it called tds_goodread, which eventually called read(2), which
>> failed with errno 104.  Unfortunately the error processing doesn't
>> write the error message associated with 104 for your system.  However,
>> it seems that the server drops the connection, and SQLExecute then fails
>> with "Read from SQL Server failed".
>>
>> The server closed the connection.  Why?  It doesn't say.  Searching
>> Google for "azure timeout", I found
>>
>> http://blogs.msdn.com/b/avkashchauhan/archive/2011/11/12/windows-azure-load-balancer-timeout-details.aspx
>>
>> which suggests that Azure will close the connection after some
>> time, depending on load.  Hard to tell exactly what's going on between
>> all the HTTP and .Net frou-frou.
>>
>> There is of course no need to silently terminate the connection.  The
>> client is patiently waiting for the server to send data.  If the server
>> wishes to close the connection for any reason, it's still free to issue
>> an error message first, allowing the client to notify the user.
>>
>> --jkl
>> _______________________________________________
>> FreeTDS mailing list
>> [email protected]
>> http://lists.ibiblio.org/mailman/listinfo/freetds
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
[email protected]
http://lists.ibiblio.org/mailman/listinfo/freetds

diff --git a/src/tds/net.c b/src/tds/net.c
index d139579..496077c 100644
--- a/src/tds/net.c
+++ b/src/tds/net.c
@@ -217,6 +217,30 @@ tds_open_socket(TDSSOCKET * tds, const char *ip_addr, unsigned int port, int tim
 	setsockopt(conn->s, SOL_SOCKET, SO_KEEPALIVE, (const void *) &len, sizeof(len));
 #endif
 
+#ifdef TCP_KEEPIDLE
+	optlen = sizeof(len);
+	len = 30;
+	setsockopt(conn->s, IPPROTO_TCP, TCP_KEEPIDLE, (const void *) &len, sizeof(len));
+	if (tds_getsockopt(conn->s, IPPROTO_TCP, TCP_KEEPIDLE, (char *) &len, &optlen) == 0)
+		tdsdump_log(TDS_DBG_INFO2, "keepalive idle %d\n", len);
+
+#endif
+#ifdef TCP_KEEPCNT
+	optlen = sizeof(len);
+	len = 0;
+	if (tds_getsockopt(conn->s, IPPROTO_TCP, TCP_KEEPCNT, (char *) &len, &optlen) == 0)
+		tdsdump_log(TDS_DBG_INFO2, "keepalive count %d\n", len);
+
+#endif
+#ifdef TCP_KEEPINTVL
+	optlen = sizeof(len);
+	len = 30;
+	setsockopt(conn->s, IPPROTO_TCP, TCP_KEEPINTVL, (const void *) &len, sizeof(len));
+	if (tds_getsockopt(conn->s, IPPROTO_TCP, TCP_KEEPINTVL, (char *) &len, &optlen) == 0)
+		tdsdump_log(TDS_DBG_INFO2, "keepalive interval %d\n", len);
+
+#endif
+
 #if defined(__APPLE__) && defined(SO_NOSIGPIPE)
 	len = 1;
 	if (setsockopt(conn->s, SOL_SOCKET, SO_NOSIGPIPE, (const void *) &len, sizeof(len))) {