Gracefully aborting a hung Client MySQL request using the C-API (on Centos-Linux)
Posted by: JG K
Date: March 08, 2012 03:06PM

I am trying to gain a better understanding of how the MySQL client API works under Linux (CentOS6) to assess the implications of abnormal query termination events on DB integrity. In particular I would like to understand what happens (should, might, etc.) when a client blocked on a pending MySQL request is interrupted by a signal. I've looked in O'Reilly's MySQL Internals, High Performance MySQL, and MySQL In a Nutshell (can one O'D on O'Reilly?), as well as the MySQL documentation. I found a references to KILL as an admin command and another spot in the C-API for the deprecated mysql_kill() function that says to use mysql_query() to issue an SQL KILL statement.

The KILL admin command syntax requires a thread_id and provides the option to kill either the whole connection or one query.
I would like to be able to issue this from a signal handler in the same Linux process that issued the MySQL request.
I'd like to keep the connection and only cause the outstanding request to terminate. It sounds as if this is possible.

According to the MySQL on-line 5.1 specs The KILL query terminates the statement that the connection is currently executing, but leaves the connection itself intact. In the C-API on Linux, what state is the client process in while waiting for the MySQL request to complete? Is it blocked on a pipe or other read() call? is it blocked on a sleep()? other? I assume that the process state is interruptable by a SIGLARM?

According to the MySQL ref manual (version 5.1), when you use KILL, a thread-specific kill flag is set for the thread which is then polled and handled depending on the operation in progress:
[*] In SELECT, ORDER BY and GROUP BY loops, the flag is checked after reading a block of rows. If the kill flag is set, the statement is aborted.
[*] During ALTER TABLE, the kill flag is checked before each block of rows are read from the original table. If the kill flag was set, the statement is aborted and the temporary table is deleted.
[*] During UPDATE or DELETE operations, the kill flag is checked after each block read and after each updated or deleted row. If the kill flag is set, the statement is aborted.
[*] GET_LOCK() aborts and returns NULL.
[*] An INSERT DELAYED thread quickly flushes (inserts) all rows it has in memory and then terminates.
[*] If the thread is in the table lock handler (state: Locked), the table lock is quickly aborted.
[*] If the thread is waiting for free disk space in a write call, the write is aborted with a “disk full” error message.

I assume that after the KILL takes effect the MySQL operation will (eventually, but deterministically ) complete with error status. When the KILL is issued from the context of the SIGALRM handler then is it safe to assume that the MySQL request will either complete normally (if the KILL loses a race with the normal request completion), OR, complete with a small set of error types that indicate the KILL "worked", OR complete with some other error (reflecting some pathological error independent of the KILL)?

When the query(KILL) completes without error then does the client then need to call mysql_store_result() before continuing to wait for the original query to complete?

If so then I'd like to be able to enumerate the "normal" set of error codes that reflect a successful KILL's termination of the outstanding request and to be able to differentiate this set from other errors which imply something else caused the termination.

For example, I assume these errors would obtain in response to a failed KILL QUERY in the signal handler:
Error: 1094 SQLSTATE: HY000 (ER_NO_SUCH_THREAD) Message: Unknown thread id: %lu
Error: 1095 SQLSTATE: HY000 (ER_KILL_DENIED_ERROR) Message: You are not owner of thread %lu

For the "normal" KILL errors .. I could find disk full
Error: 1021 SQLSTATE: HY000 (ER_DISK_FULL) Message: Disk full (%s); waiting for someone to free some space...

as for the state:Locked case I found these errors with LOCK or ABORT in them
Error: 1015 SQLSTATE: HY000 (ER_CANT_LOCK) Message: Can't lock file (errno: %d)
Error: 1205 SQLSTATE: HY000 (ER_LOCK_WAIT_TIMEOUT) Message: Lock wait timeout exceeded; try restarting transaction
Error: 1078 SQLSTATE: HY000 (ER_GOT_SIGNAL) Message: %s: Got signal %d. Aborting!

as for "kill flag set, statement aborted", there are a couple of errors with "abort" in their descriptions but they seem to apply to a connection abort instead of query abort. So I haven't quite been able to determine the error that reflects "statement is aborted". Is it obvious?

Thanks.

Options: ReplyQuote


Subject
Views
Written By
Posted
Gracefully aborting a hung Client MySQL request using the C-API (on Centos-Linux)
2491
March 08, 2012 03:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.