| Bug #9841 | Views: Unexpected READ Lock when trying to update a view in a stored procedure | ||
|---|---|---|---|
| Submitted: | 12 Apr 2005 12:05 | Modified: | 19 May 2005 1:27 |
| Reporter: | Disha | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.3 Beta | OS: | Windows (Windows Server 2003) |
| Assigned to: | Dmitry Lenev | CPU Architecture: | Any |
[12 Apr 2005 12:07]
Disha
Command output attached
Attachment: Unexpected READ Lock error - Command Output.txt (text/plain), 1.39 KiB.
[12 Apr 2005 14:38]
MySQL Verification Team
Verified with 5.0.5. Looks linked to the same cause as: http://bugs.mysql.com/bug.php?id=9486
[6 May 2005 8:26]
Dmitry Lenev
Fixed in 5.0.6
[6 May 2005 13:34]
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/24589
[6 May 2005 13:45]
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/24585
[19 May 2005 1:27]
Paul DuBois
Noted in 5.0.6 changelog.
Description: Unexpected READ Lock error when trying to update a view that is created from an inner join of tables in a stored procedure. How to repeat: Repro Steps: 1. Start the MySQL client and connect to the database with valid user and password. 1. Select the TEST database with the USE command and set the delimiter to // as follows: USE TEST DELIMITER // 3. Run the following commands to create and populate tables. drop table if exists t1// create table t1(f1 char(0), f2 decimal (64) unsigned not null DEFAULT 9.9, f3 tinyint, f5 int)// insert into t1(f2,f3,f5) values(1,1,1),(2,2,2),(3,3,0),(4,4,4),(5,5,5),(6,6,6),(7,7,0),(8,8,8),(9,9,9),(10,10,10)// drop table if exists t2// create table t2(f4 char binary, f5 int)// insert into t2 values('a',1),('a',2),('a',0),('a',4),('a',5),('a',6),('a',0),('a',8),('a',9),('a',10)// 4. Create the view using the inner join of the two tables on f5 as follows: drop view if exists v1// create view v1 as select t1.f1, t1.f2, t1.f3, t2.f4, t2.f5 from t1 inner join t2 using (f5)// 5. Create a procedure that updates the values of the view as follows: drop procedure if exists sp01// create procedure sp01 () p1: begin update v1 set v1.f2=0 where v1.f5<=0; select * from v1; end p1// 6. After creating the procedure call the procedure as follows: CALL SP01()// Expected Results: 1. The values should be updated as per the update statement. Actual Results: 1. An error is displayed as: "ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated"