MySQL Bugs: #28141: Control C on query waiting on lock causes ERROR 1053 (server shutdown)

Bug #28141	Control C on query waiting on lock causes ERROR 1053 (server shutdown)
Submitted:	27 Apr 2007 17:56	Modified:	18 Dec 2009 15:05
Reporter:	Todd Farmer (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.37	OS:	Windows
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
When a query is waiting on a lock, pressing Control-C eventually generates "ERROR 1053 (08S01): Server shutdown in progress" message.  This is not accurate - the server is never shut down.  In fact, the connection is never terminated - the same CONNECTION_ID() is observed before and after the message.

How to repeat:
Using two clients, execute the following:

[client #1]

CREATE TABLE l (i INT PRIMARY KEY) ENGINE = InnoDB;
INSERT INTO l VALUES (1);
BEGIN;
SELECT * FROM l WHERE i = 1 FOR UPDATE;

[client #2]
SELECT CONNECTION_ID();
BEGIN;
SELECT * FROM l WHERE i = 1 FOR UPDATE;
[control - c and wait for error and new prompt]
SELECT CONNECTION_ID();

Suggested fix:
Return control to client immediately after Control-C and do not generate error.

On control-C, the client sends
"KILL /*!50000 QUERY */ threadID"
to the server. Meaning that on < 5.0,
we'd kill the connection, and on 5.0+,
just the offending query (which is
why CONNECTION_ID() remains the same
on 5.0+ -- the connection is never
dropped, just the current query is
cancelled in medias res). That is
correct and intended behaviour.

In this ticket's example, this is
what presumably happens next:
On the server, a flag is set on the
thread executing the query in question,
asking the thread to abort processing
the query ASAP:
thd->killed= KILL_QUERY (ER_QUERY_INTERRUPTED)
In records.cc:rr_quick(), the reading is interrupted,
and (info->)thd->killed is seen. This results in
ER_SERVER_SHUTDOWN being raised / sent to the client,
regardless of whether the server is really going down,
and regardless of whether even the connexion (and not
just the query) is terminated.
If this theory proves to be correct, then the server
behaves correct and as intended, except that it sends
a rather misleading error-message. That it sends one
at all on cancel is correct behaviour, the exact
phrasing however is unfortunate. Check whether we have
more appropriate errmsg that we could send instead when
we are not actually doing server shutdown. Check whether
we can see in rr_quick() whether we have a real shutdown
on our hands (that is, distinguish between a shutdown
and a connection-KILL); if not, we can at least distinguish
between KILL QUERY and KILL CONNECTION.

FWIW, with 5.0.52 on linux, I see

[...]
mysql> SELECT * FROM l WHERE i = 1 FOR UPDATE;
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted

once client#1 COMMITs, which is less confusing.
(1317: KILL_QUERY/ER_QUERY_INTERRUPTED)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42827

ChangeSet@1.2594, 2008-02-22 14:30:50+01:00, tnurnberg@mysql.com +1 -0
  Bug#28141: Control C on query waiting on lock causes ERROR 1053 (server shutdown)
  
  Control-C in the mysql command-line client is "incremental" now.
  First Control-C sends KILL QUERY (when connected to 5.0+ server, otherwise, see next)
  Next  Control-C sends KILL CONNECTION
  Next  Control-C aborts client.
  
  As the first two steps only pertain to an existing query,
  Control-C will abort the client right away if no query is running.
  
  client will give more detailed/consistent feedback on Control-C now.

Note for testing in cygwin: when starting the client from a script, the shell might trap some stuff, so Control-C might abort right away or cursor keys may not work as expected.

Could not reproduce exact error message by the way; I get a lock timeout warning instead, which makes so much more sense. Hence no change in the server. Messages from the client on the other hand are hopefully a little clearer now ("at last all too well").

Was able to create situation on Linux(!) where I got the "shutdown". Making error dependent on "shutdown_in_progress", throwing "shutdown" or "query interrupted" as needed.

I don't see how adding the behavior of first sending KILL QUERY, and then just KILL, is related to fixing this bug. Seems to me that the whole problem with the bug is resolved by the change to sql/records.cc.

Jim, records.cc / sql_class.h change results in server sending clearer error message. mysql.cc change results in client sending clearer diagnostics; specifically, it also addresses the "return control immediately" part of the bug report.

The main benefit of this CS (IMO, YMMV) is getting more uniform behaviour for the rr_*() function suite, which wasn't even p/o the original ticket. :)  The ticket is more, "this use case gives a bad UI experience", and while you are certainly right saying that key to fixing that is for the server to send a better message, I think having the client send better diagnostics adds further benefit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87387

2820 Tatiana A. Nurnberg	2009-10-19
      Bug#28141: Control C on query waiting on lock causes ERROR 1053 (server shutdown)
      
      If a thread is killed in the server, we throw "shutdown" only if one is actually in
      progress; otherwise, we throw "query interrupted".
      
      Control-C in the mysql command-line client is "incremental" now.
      First Control-C sends KILL QUERY (when connected to 5.0+ server, otherwise, see next)
      Next  Control-C sends KILL CONNECTION
      Next  Control-C aborts client.
      
      As the first two steps only pertain to an existing query,
      Control-C will abort the client right away if no query is running.
      
      client will give more detailed/consistent feedback on Control-C now.
     @ client/mysql.cc
        Extends Control-C handling; enhances up feedback to user.
        
        On 5.0+ servers, we try to be nice and send KILL QUERY first
        if Control-C is pressed in the command-line client, but if
        that doesn't work, we now give the user the opportunity to
        send KILL CONNECTION with another Control-C (and to kill the
        client with another Control-C if that somehow doesn't work
        either).
     @ mysql-test/t/flush_read_lock_kill.test
        we're getting correct "thread killed" rather than
        "in shutdown" error now
     @ mysql-test/t/kill.test
        we're getting correct "thread killed" rather than
        "in shutdown" error now
     @ mysql-test/t/rpl000001.test
        we're getting correct "thread killed" rather than
        "in shutdown" error now
     @ mysql-test/t/rpl_error_ignored_table.test
        we're getting correct "thread killed" rather than
        "in shutdown" error now
     @ sql/records.cc
        make error messages on KILL uniform for rr_*()
        by folding that handling into rr_handle_error()
     @ sql/sql_class.h
        Only throw "shutdown" when we have one flagged as being in progress;
        otherwise, throw "query interrupted" as it's likely to be "KILL CONNECTION"
        or related.

Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

Noted in 5.1.41 changelog.

In mysql, using Control-C to kill the current query resulted in a
ERROR 1053 (08S01): Server shutdown in progress" message if the query
was waiting for a lock.

Setting report to NDI pending push to 5.0.x, 5.5.x.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092041-sp6eyod7sdlfuj3b) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0, 6.0.14 changelogs.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)