Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6193

Problems with multi-table updates that JOIN against read-only table

        Details

        • Type: Bug
        • Status: Closed
        • Priority: Major
        • Resolution: Fixed
        • Affects Version/s: 5.5.37, 10.0.10
        • Fix Version/s: 5.5.38, 10.0.12
        • Component/s: None
        • 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

            Gliffy Diagrams

              Attachments

                Activity

                Ascending order - Click to sort in descending order
                Hide
                Permalink
                kolbe Kolbe Kegel added a comment -

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

                Show
                kolbe Kolbe Kegel added a comment - Both of these items were reproduced using a binary built with the patch for MDEV-6139 .
                Hide
                Permalink
                elenst Elena Stepanova added a comment -

                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.

                Show
                elenst Elena Stepanova added a comment - 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.
                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                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;

                Show
                sanja Oleksandr Byelkin added a comment - 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;
                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                Second part of the test is repatable in 5.3.

                Show
                sanja Oleksandr Byelkin added a comment - Second part of the test is repatable in 5.3.
                Hide
                Permalink
                sanja Oleksandr Byelkin added a comment -

                the patch sent for review

                Show
                sanja Oleksandr Byelkin added a comment - the patch sent for review

                  People

                  • Assignee:
                    sanja Oleksandr Byelkin
                    Reporter:
                    kolbe Kolbe Kegel
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    4 Start watching this issue

                    Dates

                    • Created:
                      Updated:
                      Resolved: