diff options
| -rw-r--r-- | mysql-test/r/merge.result | 64 | ||||
| -rw-r--r-- | mysql-test/t/merge.test | 57 | ||||
| -rw-r--r-- | storage/myisammrg/ha_myisammrg.cc | 21 |
3 files changed, 134 insertions, 8 deletions
diff --git a/mysql-test/r/merge.result b/mysql-test/r/merge.result index a215c818b0f..62981adaf8a 100644 --- a/mysql-test/r/merge.result +++ b/mysql-test/r/merge.result @@ -2466,7 +2466,9 @@ UNLOCK TABLES; DROP TRIGGER t2_ai; DROP TABLE tm1, t1, t2; # -# Don't select MERGE child when trying to get prelocked table. +# Don't allow an update of a MERGE child in a trigger +# if the table's already being modified by the main +# statement. # CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) @@ -2475,19 +2477,45 @@ CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 FOR EACH ROW INSERT INTO t1 VALUES(11); LOCK TABLE tm1 WRITE, t1 WRITE; INSERT INTO tm1 VALUES (1); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +SELECT * FROM tm1; +c1 +1 +UNLOCK TABLES; +LOCK TABLE t1 WRITE, tm1 WRITE; +INSERT INTO tm1 VALUES (1); +ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. +SELECT * FROM tm1; +c1 +1 +1 +UNLOCK TABLES; +DROP TRIGGER tm1_ai; +DROP TABLE tm1, t1; +# +# Don't select MERGE child when trying to get a prelocked table. +# +# Due to a limitation demonstrated by the previous test +# we can no longer use a write-locked prelocked table. +# The test is kept for historical purposes. +# +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) +INSERT_METHOD=LAST; +CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 +FOR EACH ROW SELECT max(c1) FROM t1 INTO @var; +LOCK TABLE tm1 WRITE, t1 WRITE; +INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 -11 UNLOCK TABLES; LOCK TABLE t1 WRITE, tm1 WRITE; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; c1 1 -11 1 -11 UNLOCK TABLES; DROP TRIGGER tm1_ai; DROP TABLE tm1, t1; @@ -2499,7 +2527,7 @@ CREATE TABLE t5 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5) INSERT_METHOD=LAST; CREATE TRIGGER t2_au AFTER UPDATE ON t2 -FOR EACH ROW INSERT INTO t3 VALUES(33); +FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var; CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t4); LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE; @@ -2517,7 +2545,6 @@ c1 1 4 3 -33 4 5 DROP TRIGGER t2_au; @@ -2553,4 +2580,29 @@ ERROR HY000: Unable to open underlying table which is differently defined or of DROP TABLE t1, t3; # Connection default. # Disconnecting con1, all mdl_tickets must have been released. +# +# A test case for Bug#47648 main.merge fails sporadically +# +# Make sure we correctly maintain lex->query_tables_last_own. +# +create table t1 (c1 int not null); +create table t2 (c1 int not null); +create table t3 (c1 int not null); +create function f1 () returns int return (select max(c1) from t3); +create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ; +select * from t4 where c1 < f1(); +c1 +prepare stmt from "select * from t4 where c1 < f1()"; +execute stmt; +c1 +execute stmt; +c1 +execute stmt; +c1 +drop function f1; +execute stmt; +ERROR 42000: FUNCTION test.f1 does not exist +execute stmt; +ERROR 42000: FUNCTION test.f1 does not exist +drop table t4, t3, t2, t1; End of 6.0 tests diff --git a/mysql-test/t/merge.test b/mysql-test/t/merge.test index c3499037e97..f0d8960322e 100644 --- a/mysql-test/t/merge.test +++ b/mysql-test/t/merge.test @@ -1941,7 +1941,9 @@ UNLOCK TABLES; DROP TRIGGER t2_ai; DROP TABLE tm1, t1, t2; --echo # ---echo # Don't select MERGE child when trying to get prelocked table. +--echo # Don't allow an update of a MERGE child in a trigger +--echo # if the table's already being modified by the main +--echo # statement. --echo # CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) @@ -1949,6 +1951,31 @@ CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 FOR EACH ROW INSERT INTO t1 VALUES(11); LOCK TABLE tm1 WRITE, t1 WRITE; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO tm1 VALUES (1); +SELECT * FROM tm1; +UNLOCK TABLES; +LOCK TABLE t1 WRITE, tm1 WRITE; +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO tm1 VALUES (1); +SELECT * FROM tm1; +UNLOCK TABLES; +DROP TRIGGER tm1_ai; +DROP TABLE tm1, t1; + +--echo # +--echo # Don't select MERGE child when trying to get a prelocked table. +--echo # +--echo # Due to a limitation demonstrated by the previous test +--echo # we can no longer use a write-locked prelocked table. +--echo # The test is kept for historical purposes. +--echo # +CREATE TABLE t1 (c1 INT) ENGINE=MyISAM; +CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1) + INSERT_METHOD=LAST; +CREATE TRIGGER tm1_ai AFTER INSERT ON tm1 + FOR EACH ROW SELECT max(c1) FROM t1 INTO @var; +LOCK TABLE tm1 WRITE, t1 WRITE; INSERT INTO tm1 VALUES (1); SELECT * FROM tm1; UNLOCK TABLES; @@ -1970,7 +1997,7 @@ CREATE TABLE t5 (c1 INT) ENGINE=MyISAM; CREATE TABLE tm1 (c1 INT) ENGINE=MRG_MYISAM UNION=(t1,t2,t3,t4,t5) INSERT_METHOD=LAST; CREATE TRIGGER t2_au AFTER UPDATE ON t2 - FOR EACH ROW INSERT INTO t3 VALUES(33); + FOR EACH ROW SELECT MAX(c1) FROM t1 INTO @var; CREATE FUNCTION f1() RETURNS INT RETURN (SELECT MAX(c1) FROM t4); LOCK TABLE tm1 WRITE, t1 WRITE, t2 WRITE, t3 WRITE, t4 WRITE, t5 WRITE; @@ -2037,4 +2064,30 @@ connection default; --echo # Disconnecting con1, all mdl_tickets must have been released. disconnect con1; +--echo # +--echo # A test case for Bug#47648 main.merge fails sporadically +--echo # +--echo # Make sure we correctly maintain lex->query_tables_last_own. +--echo # +create table t1 (c1 int not null); +create table t2 (c1 int not null); +create table t3 (c1 int not null); + +create function f1 () returns int return (select max(c1) from t3); + +create table t4 (c1 int not null) engine=merge union=(t1,t2) insert_method=last ; + +select * from t4 where c1 < f1(); +prepare stmt from "select * from t4 where c1 < f1()"; +execute stmt; +execute stmt; +execute stmt; +drop function f1; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +--error ER_SP_DOES_NOT_EXIST +execute stmt; +drop table t4, t3, t2, t1; + --echo End of 6.0 tests + diff --git a/storage/myisammrg/ha_myisammrg.cc b/storage/myisammrg/ha_myisammrg.cc index 60de6d361e5..066290ab515 100644 --- a/storage/myisammrg/ha_myisammrg.cc +++ b/storage/myisammrg/ha_myisammrg.cc @@ -458,6 +458,18 @@ int ha_myisammrg::add_children_list(void) */ if (thd->lex->query_tables_last == &parent_l->next_global) thd->lex->query_tables_last= this->children_last_l; + /* + The branch below works only when re-executing a prepared + statement or a stored routine statement: + We've just modified query_tables_last. Keep it in sync with + query_tables_last_own, if it was set by the prelocking code. + This ensures that the check that prohibits double updates (*) + can correctly identify what tables belong to the main statement. + (*) A double update is, e.g. when a user issues UPDATE t1 and + t1 has an AFTER UPDATE trigger that also modifies t1. + */ + if (thd->lex->query_tables_own_last == &parent_l->next_global) + thd->lex->query_tables_own_last= this->children_last_l; end: DBUG_RETURN(0); @@ -888,6 +900,15 @@ int ha_myisammrg::detach_children(void) if (thd->lex->query_tables_last == this->children_last_l) thd->lex->query_tables_last= this->children_l->prev_global; + /* + If the statement requires prelocking, and prelocked + tables were added right after merge children, modify the + last own table pointer to point at prev_global of the merge + parent. + */ + if (thd->lex->query_tables_own_last == this->children_last_l) + thd->lex->query_tables_own_last= this->children_l->prev_global; + /* Terminate child list. So it cannot be tried to remove again. */ *this->children_last_l= NULL; this->children_l->prev_global= NULL; |