Bug #9998 MySQL client hangs on USE "database"
Submitted: 19 Apr 2005 13:04 Modified: 24 Jun 2005 2:58
Reporter: Stefan Hinz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:N/A
Assigned to: Magnus Blåudd CPU Architecture:Any

[19 Apr 2005 13:04] Stefan Hinz
Description:
I wanted to test what happens when you try to get a lock on a table
that is locked by another thread, and I found this behaviour in 5.0.4:

1. Client A WRITE locks a MyISAM table in '<database>'.
2. Client B issues 'USE <database>' and hangs until the lock is released by client A. 'SHOW PROCESSLIST' issued in client A shows client B is in status 'LOCKED'.

I've asked on dev-public@ whether this is intended/expected behaviour, and Guilhem believes this is a bug:

What may have happened is that you used a 'mysql' command-line client, without the -A option. Then when you type "USE database;" your client wants to list all tables in this database, and all columns of these tables (this is for auto-completion to work). And probably it hangs in mysql_list_fields(), because that's reading the table's definition:

MASTER> show processlist;
+----+------+-----------+------+------------+------+--------+------------------+
| Id | User | Host      | db   | Command    | Time | State  | Info             |
+----+------+-----------+------+------------+------+--------+------------------+
|  2 | root | localhost | NULL | Query      |    0 | NULL   | show processlist |
|  3 | root | localhost | test | Field List |    3 | Locked |                  |
+----+------+-----------+------+------------+------+--------+------------------+

Now I'm far from sure that reading the table definition is supposed to block on a write lock. Normally a LOCK TABLES WRITE is just that you're updating _data_ (not metadata) and you don't want others to read data at this time.
And one more suspicious thing is... in 5.0 I can reproduce the hang but not in 4.1 :)

So I would file this as a bug!!

How to repeat:
See description.

Suggested fix:
Make it behave as in 4.1, i.e. not hang for client B. ;-)
[3 Jun 2005 13:29] 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/internals/25573
[7 Jun 2005 10:19] Magnus Blåudd
Pushed to 5.0.7
[24 Jun 2005 2:58] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented bugfix in 5.0.7 changelog; closed.