Bug #9585 Error 1036 Table is read only when trying to multi-update the same column

• Submitted: 2 Apr 2005 18:41
• Modified: 13 Apr 2006 16:44
• Reporter: Sergey Petrunya
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0-bk
• Assigned to: Ingo Strüwing
• CPU Architecture: Any

[2 Apr 2005 18:41] Sergey Petrunya

Description:
When using multitable update to change the value of the same column both directly and "through a view" 
one gets 
if there are row combinations to update
  "Table 't1' is read only" error 
otherwise 
  no error
.

How to repeat:
create table t1 (a int primary key);
create table t2 (a int primary key);
insert into t1 values (1),(2),(3);
insert into t2 values (1),(2),(3);
create view v12 as 
  select t1.a as a1,t2.a as a2 from t1,t2 where t1.a = t2.a;

update  v12,t1,t2 set v12.a1 = 111, t1.a=222 where t1.a=t2.a and t1.a = v12.a1;

The last query produces an error:
ERROR 1036 (HY000): Table 't1' is read only

Suggested fix:
Either always allow such double updates or never allow them (we need to consult the standard here)

[5 Apr 2005 3:03] MySQL Verification Team

Verified with 5.0.4 BK source.

[13 Apr 2006 16:44] Valeriy Kravchuk

5.0.21-BK gives different error message with the same test case:

mysql> update  v12,t1,t2 set v12.a1 = 111, t1.a=222 where t1.a=t2.a and t1.a = v12.a1;
ERROR 1062 (23000): Duplicate entry '111' for key 1

So, it looks like there is no bug any more.