Bug #49744 System tables manual locking enforcement is inconsistent

• Submitted: 16 Dec 2009 19:34
• Reporter: Matthew Montgomery
• Status: Verified
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.1.39
• OS: Any
• CPU Architecture: Any

[16 Dec 2009 19:34] Matthew Montgomery

Description:
Trying to lock a table to prevent writes to it, while also dropping and recreating a procedure one of its triggers references, you get an error stating that mysql.proc is not locked.  Attempting to lock mysql.proc and your table at the same results in an error.
"You can't combine write-locking of system tables with other tables or lock types"

Attempting to create a procedure while you have a table locked gives you an errors about mysql.user (and old other grants tables) and mysql.proc not being locked.  Locking mysql.user and your own table does not throw an error.

Issues with modifying a stored procedure in a production system are another bug altogether.  Bug #9588.

For this bug I'm only concerned with consistency in locking system tables.

How to repeat:
mysql> use test;
Database changed
mysql> create table foo (a int) engine=myisam;
Query OK, 0 rows affected (0.03 sec)

mysql> lock tables test.foo write, mysql.user write;  
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables test.foo write, mysql.proc write;
ERROR 1428 (HY000): You can't combine write-locking of system tables with other tables or lock types

Suggested fix:
Enforce consistent locking protection of all mysql.* tables or none.