Bug #10656 Stored Procedure - Create index and Truncate table command error
Submitted: 16 May 2005 8:23 Modified: 10 Mar 2006 11:17
Reporter: conor meegan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.4-beta-nt OS:Windows (Windox XP Pro SP2)
Assigned to: Magnus Blåudd CPU Architecture:Any

[16 May 2005 8:23] conor meegan 
Description:
When the CREATE INDEX or TRUNCATE table commands are included in a STORED PROCEDURE, an error message is returned indicating that the target table is "locked with a read lock and can't be updated".

How to repeat:
I have included a sample database schema and test stored procedures.
[16 May 2005 8:24] conor meegan 
database schema

Attachment: test_database 20050516 0919.sql (text/plain), 1.95 KiB.
[16 May 2005 8:24] conor meegan 
stored procedures

Attachment: test_procedures.txt (text/plain), 812 bytes.
[16 May 2005 18:06] MySQL Verification Team 
Thank you for the bug report.

mysql> call create_index()  $$
ERROR 1099 (HY000): Table 'tbl1' was locked with a READ lock and can't be updated
mysql> DROP PROCEDURE IF EXISTS `test`.`myjoin`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `test`.`myjoin`()
    -> BEGIN
    ->   update tbl1, tbl2 set tbl1.txt = tbl2.txt where tbl1.id = tbl2.id;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> call myjoin() $$
Query OK, 0 rows affected (0.02 sec)

mysql> DROP PROCEDURE IF EXISTS `test`.`truncate_table`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `test`.`truncate_table`()
    -> BEGIN
    ->   TRUNCATE TABLE tbl1;
    -> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> call truncate_table() $$
ERROR 1099 (HY000): Table 'tbl1' was locked with a READ lock and can't be updated
mysql> select version()$$
+---------------+
| version()     |
+---------------+
| 5.0.6-beta-nt |
+---------------+
1 row in set (0.00 sec)

mysql>
[3 Aug 2005 11:37] conor meegan 
I am still having the same problem in version 5.0.9-beta-nt.
[8 Mar 2006 15:24] 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/3584
[8 Mar 2006 16:18] Magnus Blåudd 
Could not repeat eother on FC4 or windows. Since there now exists a test case for this I would like to push it. Please review.
[9 Mar 2006 11:08] 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/3628
[10 Mar 2006 11:17] Magnus Blåudd 
Pushed the testcase to 5.0.20 and 5.1.8. We have not been able to reproduce the problem. Please let us know if the problem still exists.