Bug #32352 SQL statement from command line interface locks database after shell connection

• Submitted: 14 Nov 2007 3:14
• Modified: 14 Dec 2007 10:27
• Reporter: Bryan Teague
• Status: No Feedback
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: mysql Ver 14.12 Distrib 5.0.22, for red
• OS: Linux (redhat-linux-gnu (i686) using readline 5.0)
• CPU Architecture: Any
• Tags: SQL Query database lock shell

[14 Nov 2007 3:14] Bryan Teague

Description:
I was running a sql query against a database from the mysql command line.  My shell connection dropped, and this caused the database to lock. 
sql query was: 
select * from membership.users where user_name in (select b_email from order_items,orders where orders.id=orders_id and items_id = 3260 and orderdate > '2007-11-12 20:51:00' and orderdate < '2007-11-13 14:00:00');

CPU usage went to 100 percent, and sql query processing ceased. 

I reaccessed the system via command line. 

When I did a show processlist, I saw the query that I was originally running reporting that it was trying to send the data back.  Every other query was stacked behind it. 

As soon as I killed the process thread, the system came back to normal. 

How to repeat:
See above

[14 Nov 2007 10:27] Sveta Smirnova

Thank you for the report.

But version 5.0.22 is quite old. Additionally if indexes are used not properly and this query returns large amount of data this is expected behaviour.

But if you think this can be MySQL bug please upgrade to current version 5.0.45 and if you can repeat described behaviour please provide output of SHOW CREATE TABLE and SHOW TABLE STATUS for tables membership.users, order_items and orders. Additionally provide output of EXPLAIN EXTENDED for the query.

[15 Dec 2007 0: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".