[freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Tue Jan 15 09:12:33 EST 2008


> 
> Sebastien FLAESCH wrote:
> > James K. Lowden wrote:
> > > I think all the signal handler can do is record the fact 
> that a signal
> > > was caught in a more-or-less global variable.  When the called
> > > function returns an error -- e.g. a timeout on reads -- 
> the app can
> > > then decide whether or not to call SQLCancel.  
> > 
> > How would you implement
> > SQL interruption in a text-based SQL command interpreter?
> 
> Before I answer that, let me remind you:
> 
> > James K. Lowden wrote:
> > > I don't know the proper treatment of signals in an ODBC 
> application, 
> 
> 	:-)
> 
> But this for sure:
> 
> > > The
> > > handler doesn't have enough information to know whether that's the
> > > right thing to do.  
> 
> The application can't know the state of the TDS connection.  
> The handler
> can't even know (without a lot of help) the state of the application. 
> Together they definitely don't know whether the server is 
> ready to receive
> a cancel packet.  
> 
> More: Is the driver ready?  How will the driver handle a nested
> signal-driven call?  
> 
> 	SQLFetch
> 	waiting ... waiting ...
> 	[sigint]
> 		SQLCancel ... OK (or not)
> 		(handler returns control to SQLFetch)
> 	waiting ... waiting ...
> 	waiting ... waiting ...
> 
> But you asked what to do, not what not to do.
> 
> Signal handling is tricky.  The trick is to make a note and 
> adjust course
> when time permits.  The application and the driver have to 
> cooperate in a
> 12-step program back to sanity:
> 
> 1.  Application installs handler. 
> 2.  Driver *also* installs handler, stores app's handler. 
> 3.  Connect, Exec, Fetch ....
> 4.  SIGINT! 
> 5.  Driver catches.  Prepares to send SYBETIME (HY008). 
> 6.  Driver calls app's handler. 
> 7.  App catches, prepares to send Cancel. 
> 8.  App handler returns, Driver handler returns. 
> 9.  Driver "times out".  
> 10. SQLFetch fails with HY008.
> 11. App sees timeout and SIGINT evidence. 
> 12. App: 4 = 2 + 2.  Calls SQLCancel. 
> 
> > For sure GUI applications typically handle user 
> interruption by catching
> > a special GUI event (click on cancel button), and can then call
> > SQLCancel() outside a signal handler flow... be this is 
> about a non-GUI
> > application.
> 
> GUI or not makes no difference.  Win32 makes a difference.  
> Details vary
> by OS even within the Unixish world.  
> 
> An interactive program might want to query the user by 
> writing to the tty
> or putting up a message box asking what to do.  Depending on the
> environment, that might be OK to do inside the handler.  
> 
> > This is to me unexpected: The application should handle 
> signals such as
> > SIGINT, and do the DB client API calls (if available) to 
> cancel a query.
> > 
> > This works for us with:
> > 
> > - Postgresql using PQcancel()
> > - DB2 CLI using SQLCancel()
> > - ANTS DS using SQLCancel()
> > - Informix using sqlbreak() on Unix, and callback method on 
> MS Windows.
> > - Oracle using OCIBreak() (but need to register SIGINT handle after
> > connection)
> 
> To be robust, the FreeTDS could be made to tolerate having SQLCancel
> called inside a signal handler.  
> 
> 1.  SIGINT! 
> 2.  Driver catches, calls app's handler. 
> 3.  App catches, calls SQLCancel 
> 4.  Driver calls special low-level function to tidy up the 
> connection. 
> 5.  Driver completes SQLCancel, returns. 
> 6.  App handler returns, Driver handler returns. 
> 7.  Driver sees the maid was here, "times out".  
> 8.  SQLFetch fails with HY008.
> 
> With some housekeeping -- keeping track of nesting level and which API
> function is being executed -- step #2 wouldn't be needed.  
> SQLCancel could
> tell that it was on a separate, nested thread of control and 
> be mindful of
> the TDS state.  
> 
> You may know I rewrote the socket logic last year, to make it handle
> timeouts.  I focussed on db-lib semantics, which include callbacks and
> *prohibit* calling any API function inside any handler.  The "special
> low-level function to tidy up the connection" has to live in 
> that world. 
> 
> It's easy to fake, and it will work most of the time:
> 
> 3.  App catches, calls SQLCancel 
> 4.  SQLCancels set flag for odbc_errmsg_handler, returns OK. 
> 5.  App handler returns. 
> 6.  Driver handler returns. 
> 7.  Driver calls odbc_errmsg_handler (per normal)
> 8   Driver sends Cancel -- might or might not work, but OK 
> 9.  SQLFetch fails with HY008.
> 

I think this is 90% current CVS HEAD! Small differences
4. SQLCancel set flag (cancel_sent) and sent cancel packet
8. do nothing

> It would be easy to implement and hard to tell the 
> difference.  Let's see
> what Frediano says.  
> 

The "trick" to send cancel packet in SQLCancel it's that
- you don't interfere with main thread or current on signal handler (you
send data while the other is waiting or reading)
- a EINTR error or a timeout occur and timeout handler can catch this
situation. If EINTR is not detected and timeout do not raise server
returns done with cancel !

Now I think that problem that reported Sebastien is caused cause server
returns 2 DONE token. One saying error and one saying cancelled. The
problem is that server does not pack these 2 DONE in one packet but the
last one is contained in a new packet. libTDS so set state to IDLE on
first done. From sybase spec there is a tick on how to handle these
situations. On cancel read packets and consider only last 8 bytes that
is find a "last packet" that ends with a DONE with cancelled. I think
however that there are some situations where sone nasty race conditions
can occur. Setting in_cancel and sending a cancel packet is not atomic
(and can't be atomic!). What happen if client sent cancel but server
have just returned all data? In this case I think that even Sybase
returns 2 DONE tokens.

freddy77


More information about the FreeTDS mailing list