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:Microsoft Windows
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[27 Apr 2007 17:56] Todd Farmer
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.
[7 Jan 2008 8:39] Tatiana Azundris Nuernberg
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.
[7 Jan 2008 8:53] Tatiana Azundris Nuernberg
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)
[22 Feb 2008 13:31] Bugs System
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.
[22 Feb 2008 13:34] Tatiana Azundris Nuernberg
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.
[22 Feb 2008 13:39] Tatiana Azundris Nuernberg
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").
[22 Feb 2008 14:15] Tatiana Azundris Nuernberg
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.
[4 Mar 2008 21:50] Jim Winstead
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.
[5 Mar 2008 7:24] Tatiana Azundris Nuernberg
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.
[20 Oct 2009 4:42] Bugs System
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.
[4 Nov 2009 9:25] Bugs System
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)
[6 Nov 2009 1:24] Paul Dubois
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.
[11 Nov 2009 6:51] Bugs System
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)
[11 Nov 2009 6:59] Bugs System
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)
[11 Nov 2009 14:23] Paul Dubois
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 10:35] Bugs System
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)
[18 Dec 2009 10:50] Bugs System
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)
[18 Dec 2009 11:05] Bugs System
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)
[18 Dec 2009 11:20] Bugs System
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)