Bug #52314 Query-"Kill Query threadid" wrongly returns error message-"Unknown thread id..."
Submitted: 23 Mar 2010 19:26 Modified: 29 Apr 2010 5:17
Reporter: Rohit Sharma Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.33-log OS:Linux
Assigned to: CPU Architecture:Any

[23 Mar 2010 19:26] Rohit Sharma
Description:
We are using MySql server version 5.1.33-log with ISAM tables.
When a query takes too long time to execute, we try to abort the query by issuing a 'Kill Query connection-id' query to mysql server but the server responds with 'unknown thread id xxxxx' error message. That particular thread id was found to be running in mysql server in locked state at that time using 'show processlist' query. Sometimes 'kill query id' succeeds but it fails a lot of times causing unnecessary load on db. So it most likely seems to be issue with mysql server. Let me know if more information is required to debug this.

How to repeat:
1. Run the mysql server with few large tables in MyISAM.
2. Run some client code to give significant load to db so that queries began to take some time to execute.
3. Kill queries that take time more than say 30 secs by issuing 'kill query thread-id' on a new mysql connection.
4. Sometimes "kill query ...' will succeed and sometimes it fails with 'unknown thread-id" error message.
[24 Mar 2010 4:28] Valeriy Kravchuk
Please, check with a newer version, 5.1.45, and inform about the results.
[24 Mar 2010 9:08] Rohit Sharma
Its a production site and it may not be possible for us to upgrade mysql server without causing other side effects. Is there any other way I can help you?
[24 Mar 2010 11:02] Sveta Smirnova
Thank you for the feedback.

Maybe you are just too late and thread finished to work? After getting "Unknown thread id..." error do you see same thread id in the SHOW PROCESSLIST output?
[24 Mar 2010 13:35] MySQL Verification Team
also note if you have event_scheduler running that can't be killed:

mysql> kill 1;
ERROR 1094 (HY000): Unknown thread id: 1
mysql> show processlist;
+----+-----------------+-----------------+
| Id | User            | Host            |
+----+-----------------+-----------------+
|  1 | event_scheduler | localhost       |
|  2 | root            | 127.0.0.1:46015 |
+----+-----------------+-----------------+
[25 Mar 2010 11:02] Rohit Sharma
Sveta, That's not the case. I found the thread to be in locked state in show processlist output after 'kill query id' failed.
Also one thing I missed to mention is that the error message 'unknown thread id' is returned only when kill queries are executed through JDBC using Connector/J from java application.
Executing kill query at mysql prompt has so far worked fine for same thread id which failed with java application.

Shane, I will check the event_scheduler and report back.
[25 Mar 2010 16:30] Sveta Smirnova
Thank you for the feedback.

> Also one thing I missed to mention is that the error message 'unknown thread id' is
returned only when kill queries are executed through JDBC using Connector/J from java
application.

This looks like problem of communication between your Java application and MySQL server. Most likely not a bug, but problem with connection handling (maybe connection pooling or persistent connections).

To prove this is MySQL or Connector/J bug we need repeatable test case which we can repeat in our environment.
[25 Mar 2010 19:25] Rohit Sharma
Thanks Sveta.
I took tcpdump on machine running the java application and found that client created a new connection and sent the 'kill query thread-id" query to sql server and got "unknown thread id" in response. During the same time output of 'show processlist' was also logged and that thread was found to be in locked state before and after kill query was sent from client. So I believe issue is with MySQL server only.
[25 Mar 2010 19:34] Rohit Sharma
Shane, We are getting this issue while canceling normal select queries on our db not the event_scheduler.
[25 Mar 2010 21:24] Sveta Smirnova
Thank you for the feedback.

----<q>----
I took tcpdump on machine running the java application and found that client created a
new connection and sent the 'kill query thread-id" query to sql server and got "unknown
thread id" in response. During the same time output of 'show processlist' was also logged
and that thread was found to be in locked state before and after kill query was sent from
client. So I believe issue is with MySQL server only.
----</q>----

This is not enough information to say if this is MySQL bug.

We need:

1. Output of SHOW PROCESSLIST before you run KILL query
2. Way you used to kill query: Java code which you use
3. Exact error message you get.
4. Output of SHOW PROCESSLIST after you run KILL query
[26 Mar 2010 22:54] Rohit Sharma
Thanks Sveta.
We can't share the existing code as it is. I will need some time to get the sample java code ready with which issue can be seen.
[29 Mar 2010 5:17] Sveta Smirnova
Rohit,

thank you for the update. We will wait working test case from you.
[29 Apr 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".