[MDEV-6193] Problems with multi-table updates that JOIN against read-only table Created: 2014-04-30  Updated: 2014-05-08  Resolved: 2014-05-08

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.37, 10.0.10
Fix Version/s: 5.5.38, 10.0.12

Type: Bug Priority: Major
Reporter: Kolbe Kegel (Inactive) Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None


 Description   

These items may be related to MDEV-5981 or MDEV-6139:

 
drop table if exists t1, t2, t2_0; 
CREATE TABLE `t1` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `a` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM; 
 
 
CREATE TABLE `t2_0` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `b` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=MyISAM; 
 
 
CREATE TABLE `t2` ( 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `b` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`) 
) ENGINE=MRG_MyISAM UNION=(`t2_0`); 
 
 
FLUSH TABLES; 

 
myisampack -f ./data/test/t2_0 
myisamchk -rq ./data/test/t2_0 

mysql 5.5.37-MariaDB (root) [test]> create view v2 as select * from t2;
 
Query OK, 0 rows affected (0.01 sec)
 
mysql 5.5.37-MariaDB (root) [test]> update t1 join v2 using (id) set t1.a=0;
 
ERROR 1036 (HY000): Table 't2_0' is read only
 

mysql 5.5.37-MariaDB (root) [test]> create view v2_0 as select * from t2_0;
 
Query OK, 0 rows affected (0.01 sec)
 
mysql 5.5.37-MariaDB (root) [test]> update t1 join v2_0 using (id) set t1.a=0;
 
ERROR 1036 (HY000): Table 't2_0' is read only



 Comments   
Comment by Kolbe Kegel (Inactive) [ 2014-04-30 ]

Both of these items were reproduced using a binary built with the patch for MDEV-6139.

Comment by Elena Stepanova [ 2014-05-01 ]

Assigned to Sanja as a part of the listed collection.
Please make sure to set the fix version(s) appropriately when you know in which version the bug group will be fixed.

Comment by Oleksandr Byelkin [ 2014-05-05 ]

CREATE TABLE t1 (
id int(10) unsigned,
a int(11)
) ENGINE=MyISAM;
CREATE TABLE t3 (
id int(10) unsigned,
b int(11)
) ENGINE=MyISAM;
CREATE TABLE t2 (
id int(10) unsigned,
b int(11)
) ENGINE=MRG_MyISAM UNION=(t3);
let $MYSQLD_DATADIR= `select @@datadir`;
--exec $MYISAMPACK -f $MYSQLD_DATADIR/test/t3
--exec $MYISAMCHK -rq $MYSQLD_DATADIR/test/t3
update t1 join t2 using (id) set t1.a=t2.b;
create view v2 as select * from t2;
update t1 join v2 using (id) set t1.a=0;

create view v1 as select * from t3;
update t1 join v1 using (id) set t1.a=0;

drop view v1, v2;
drop table t2, t3, t1;

Comment by Oleksandr Byelkin [ 2014-05-05 ]

Second part of the test is repatable in 5.3.

Comment by Oleksandr Byelkin [ 2014-05-05 ]

the patch sent for review

Generated at Thu Feb 08 07:10:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.