Solved

Sybase, SQL Anywhere, 9.0.2, HTTP Tunneling, application hangs after many successful query operations

Posted on 2008-10-02
6
624 Views
Last Modified: 2013-11-20
I work for a company that wants to do Sybase ODBC SQL traffic over the Internet using HTTP transactions.  I've developed HTTP tunneling software to enable this.  The tunnel software works great.  After lots of searching and refinements, I think it's just about perfect.  The problem we're having is that when we operate our MFC application, which talks to the Sybase ODBC driver through the CDatabase / CRecordset layer, when we operate this, it will work fine for dozens or hundreds of operations through the tunnel, and then it will just hang up.  I've got diagnostics within my tunnel software that show that there's nothing going on inside of it, and all requests have been responded to.  You can see in the attached screenshots of the call stacks with the Sybase ODBC driver on them that the Sybase code is not doing anything socket/network related.  Both threads with the ODBC driver on the stack are waiting on something.  Running the same application and not going through the tunnel results in no problems whatsoever, it ran overnight just fine.  I'm hoping that there's an expert in Sybase ODBC and HTTP tunneling who can show me the light.  I think the key to solving this problem will be understanding the stack traces in the screenshots I attached.  dbodbc9.dll is the Sybase ODBC driver.
callstack.JPG
callstack2.JPG
0
Comment
Question by:Michael_Balloni
  • 2
  • 2
  • 2
6 Comments
 
LVL 19

Accepted Solution

by:
grant300 earned 400 total points
Comment Utility
Did you write the HTTP Tunnel software or are you using one of the free and/or open source facilities out there?

I can think of a couple of different things.  The first is that there is a memory leak on one end or the other of your tunnel software.  It runs until it hits a wall.  The second thing is to look at is what might be happening on the server side of things.  Look for some unexpected state or something generating an asynchronous message on the server side.

Other than that, I would say you are down to basic debugging techniques.  Instead of just running whatever comes through though, I would setup a client-side load generator that sends a series of one or more fixed statements at the database.  This will let you see if the problem is specific to certain operations or the number of statements or the volume of the result sets.  I would also run the load generator in parallel without the HTTP tunnel and take a look at it's logging information at the time of failure.  It is best if you can arrange for the generators to run at the same rate with the same statements so that you can compare apples to apples.

Another thing you might try, although you will be wading through a bunch of information, is to turn ODBC logging on at the client side.  That may give you more of an idea what is going on just before and at the time of failure.

If you are a real glutton for punishment, you can double hop the TDS traffic through RIBO, a TDS listener/analyzer utility from Sybase.  It looks to the client(s) like a server and to the server like a client.  It will log Tabular Data Streams (TDS is the underlying protocol that Sybase products use) in excruciating detail.  It will give you access to the very last messages going both directions across the link.

Sounds like you have your work cut out for you with this one.

Best of luck,
Bill
0
 

Author Comment

by:Michael_Balloni
Comment Utility
Bill,

Thanks for the insights.

I found a clue, but I don't know how to move forward on it.  When the system hangs, using Wireshark to view the packet activity, the last thing that happens is that the tunnel software hands back a duplicate ACK packet to the ODBC driver.  In looking around, I read that duplicate ACKs are signs of either packet loss or packets out of order.  I can't imagine much packet loss between my two computers connected via a GigE switch, but anything's possible, right?  Also, this problem reproduced, and I really only care about it when, the ODBC driver connects to the tunnel using localhost.  I've only separated the two to enable packet sniffing by Wireshark.  And thank goodness I did, otherwise I wouldn't have even this clue.

To answer some of your questions...

Yes, I wrote the tunnel software.  I think it's pretty solid.  I've followed all best practices for CAsyncSocket.  The solution works quite well right up until it hangs, so the code is apparently generally alright.  I've read about all of the "don't call Receive more than once in OnReceive," etc., and I'm following all the right rules.

There's no memory leaks on either side of the tunnel or in the test app.

The test app I use is a load-testing application.  It keeps issuing the same queries in the same ways over and over again.  I'm not able to run the load-testing application in parallel going through the tunnel and not.  I don't see how that would turn anything up, maybe you can clarify?

ODBC logging causes my hard drive to make a terrible sound.  It's just too much detail to be of any use.  I think if you slow down any part of this system too much then the problem just goes away.

I couldn't find where to download the RIBO utility from, can you find that for me?
0
 
LVL 6

Assisted Solution

by:IncisiveOne
IncisiveOne earned 100 total points
Comment Utility
Michael

This is not an advisable thing to do, TDS is not intended to run through a tunnel, or on the web.  The tunnel is not capable of entirely hiding HTTP from TDS.  Let's see, normally we have (very fast):
TDS on top of
TCP

But for people who like ODBC (slow):
TDS on top of
ODBC on top of
TCP

And you have (molasses in Nome):
TDS on top of
ODBC on top of
your tunnel on top of
HTTP on top of
TCP

1.  The design is not sound, and it will break under stres, as you have fund out.  When you fix one problem, the next problem (which is not visible now) will become visible.
2.  Even if you do get it working, it will be slower, and there is no way to increase the speed.
You might as well accept that sooner rather than later, and spend your time running HTTP on HTTP services and TDS[ODBC] on TCP services.  There is a reason no one else has tried to do what you are doing.


0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 19

Assisted Solution

by:grant300
grant300 earned 400 total points
Comment Utility
I have to concur with IncisiveOne.  I think things (probably result sets streaming back) are just getting ahead of the latencies and your tunnel is dropping something on the floor.

Since you wrote your own tunnel software, it probably runs in user mode and not in kernel mode where it should.  (Sorry, using Linux terms instead of Windows but the principal is the same.)  At some point, a higher priority system operation causes the tunnel to hickup and you are done.  On top of that, you are probably losing some of the robustness of TCP which is causing ODBC to be unhappy.  Normally, TCP will resend lost or damaged packets and guarantee order of delivery.  I suspect you did not build that level of sophistication into your tunnel software.

A better way to do this if you are looking for something to tunnel with is SSL/SSH.  SSH uses a well know port that is or can be opened up easily and usually does not present a security risk because it uses an encrypted connection.  There are open source implementations of clients and servers for every platform and it is simple to implement, at least on Linux since the ssh server is already there.  Take a look at Putty and find an SSH server for Windows and see if that does not do the trick for you.

Best of luck,
Bill
0
 

Author Closing Comment

by:Michael_Balloni
Comment Utility
Thanks for trying to help me...  I'll have to go it alone...
0
 
LVL 6

Expert Comment

by:IncisiveOne
Comment Utility
Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now