#At file:///opt/local/work/mysql-6.1-fk-mil8-part2/ based on revid:dlenev@stripped
2695 Konstantin Osipov 2009-01-22
WL#148 "Foreign Keys"
Milestone 8 "DML words: INSERT, UPDATE, DELETE, no EOS checks"
Part 2: Implement "Dmitri check". A check for a conflict between
a cascading change and other operations with tables done by
a statement.
Implement an execution tree visitor interface (hierarchical visitor pattern)
to walk over all execution tree nodes, including
views, triggers, functions, procedures, base tables, foreign keys.
During such a walk-through we check for a potential semantical conflict
between various actions of the statement. The check must be done
before the statement is executed, since later we plan to change
the execution plan upon discovery of such a conflict.
modified:
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key_all_engines.test
sql/fk.cc
sql/fk.h
sql/fk_dd.cc
sql/mysql_priv.h
sql/share/errmsg.txt
sql/sp.cc
sql/sp.h
sql/sp_cache.cc
sql/sp_cache.h
sql/sp_head.cc
sql/sp_head.h
sql/sql_base.cc
sql/sql_class.cc
sql/sql_class.h
sql/sql_delete.cc
sql/sql_lex.cc
sql/sql_lex.h
sql/sql_trigger.cc
sql/sql_trigger.h
sql/sql_update.cc
sql/table.cc
sql/table.h
per-file messages:
mysql-test/r/foreign_key_all_engines.result
Update results.
mysql-test/t/foreign_key_all_engines.test
Add tests for Milestone 8, part 2.
sql/fk.cc
Implement class Cascade_conflict_check.
sql/fk.h
Declare Foreign_key_list::cascade_conflict_check().
sql/fk_dd.cc
Implement Execution_tree_visitor interface in application to
foreign keys.
sql/mysql_priv.h
Add declaration for a helper function used in Execution_tree_visitor.
sql/share/errmsg.txt
Add an appropriate error (for now) for a data change conflict between
a cascading action and the main statement. Later this conflict will
be handled by WL#4100.
sql/sp.cc
Implement Execution_tree_visitor interface.
sql/sp_cache.cc
Add a function to look up in the SP cache by key.
sql/sp_cache.h
Declare a function to look up in the SP cache by key.
sql/sp_head.cc
Implement Execution_tree_visitor interface.
sql/sql_base.cc
Implement a helper function find_prelocked_table().
Store all prelocked tables in thd->prelocked_tables (this will replaced
with a more general solution later).
sql/sql_class.cc
Implement Execution_tree_visitor base class.
sql/sql_class.h
Add declarations for Execution_tree_visitor interface.
sql/sql_delete.cc
Check for a conflict between cascading action and the main DELETE.
sql/sql_lex.cc
Implement Execution_tree_visitor interface.
sql/sql_trigger.cc
Implement Execution_tree_visitor interface.
sql/sql_update.cc
Check for a conflict between cascading actions and the main UPDATE.
sql/table.cc
Implement Execution_tree_visitor interface.
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2009-01-11 11:07:16 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2009-01-21 22:27:26 +0000
@@ -1011,6 +1011,213 @@ on update set default);
ERROR 42000: Foreign key error: Constraint 'fk_t2_m5zjv': SET DEFAULT for a column 'fk' which has no DEFAULT value
set @@sql_mode= @old_sql_mode;
drop tables t1;
+# Tests which check that we properly detect statements in which
+# cascading actions might affect other stages of statement
+# execution (e.g. WHERE clause evaluation) and thus lead to
+# non-standard results. Such statements are prohibited at this
+# point. In some cases this limitation will be raised by WL#4100.
+#
+# Checks for UPDATE
+#
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on update set null);
+insert into t1 values (1), (2);
+insert into t2 values (1), (1);
+# Cascading action affects table used by subquery
+update t1 set pk= pk - 1 where (select count(*) from t2 where fk = pk - 1) = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by view
+create view v1 as select count(*) as c from t2 where fk is not null;
+update t1 set pk= pk - 1 where (select * from v1) > 0;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+drop view v1;
+# Cascading action affects table used by view through stored function
+create function f1 () returns int return (select count(*) from t2 where fk is not null);
+create view v1 as select f1() as c;
+update t1 set pk= pk - 1 where (select * from v1) > 0;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by stored function
+update t1 set pk= pk - 1 where f1();
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by view in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t2 where fk is not null;
+create function f1 () returns int return (select c from v1);
+update t1 set pk= pk - 1 where f1();
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by stored function through
+# another stored function
+create function f2() returns int return f1();
+update t1 set pk= pk - 1 where f2();
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+drop view v1;
+drop function f2;
+drop function f1;
+# Cascading action affects table used by trigger
+create trigger t1_bu before update on t1 for each row
+set @a:=(select count(*) from t2 where fk is not null);
+update t1 set pk= pk - 1;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+drop tables t1, t2;
+# Updates by trigger invoked by cascading action
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on update set null);
+create table t3 (fk_log int);
+insert into t1 values (1), (2);
+insert into t2 values (1);
+create trigger t2_bu before update on t2 for each row insert into t3 values (old.fk);
+# Trigger invoked by cascading action affects table used by subquery
+update t1 set pk= pk - 1 where (select count(*) from t3 where fk_log <> pk) > 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by view
+create view v1 as select count(*) as c from t3;
+update t1 set pk= pk - 1 where (select * from v1) = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+drop view v1;
+# Trigger invoked by cascading action affects table used by view
+# through stored function
+create function f1 () returns int return (select count(*) from t3);
+create view v1 as select f1() as c;
+update t1 set pk= pk - 1 where (select * from v1) = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by
+# stored function
+update t1 set pk= pk - 1 where f1() = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by view
+# in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t3;
+create function f1 () returns int return (select c from v1);
+update t1 set pk= pk - 1 where f1();
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by stored
+# function through another stored function
+create function f2() returns int return f1();
+update t1 set pk= pk - 1 where f2();
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+drop view v1;
+drop function f2;
+drop function f1;
+# Trigger invoked by cascading action affects table used by trigger
+create trigger t1_bu before update on t1 for each row set @a:=(select count(*) from t3);
+update t1 set pk= pk - 1 where pk = 1;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+drop tables t1, t2, t3;
+#
+# Same checks for DELETE
+#
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on delete cascade);
+insert into t1 values (1);
+insert into t2 values (1), (1);
+# Cascading action affects table used by subquery
+delete from t1 where (select count(*) from t2 where fk = pk) > 1;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by view
+create view v1 as select count(*) as c from t2;
+delete from t1 where (select * from v1) > 1;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+drop view v1;
+# Cascading action affects table used by view through stored function
+create function f1 () returns int return (select count(*) from t2);
+create view v1 as select f1() as c;
+delete from t1 where (select * from v1) > 1;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by stored function
+delete from t1 where f1();
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by view in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t2;
+create function f1 () returns int return (select c from v1);
+delete from t1 where f1();
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+# Cascading action affects table used by stored function through
+# another stored function
+create function f2() returns int return f1();
+delete from t1 where f2();
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+drop view v1;
+drop function f2;
+drop function f1;
+# Cascading action affects table used by trigger
+create trigger t1_bd before delete on t1 for each row set @a:=(select count(*) from t2);
+delete from t1 where pk = 1;
+ERROR HY000: Foreign key error: cascading action will change table 't2' which is also used elsewhere in statement
+drop tables t1, t2;
+# Similar tests for updates by trigger invoked by cascading action
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on delete cascade);
+create table t3 (fk_log int);
+insert into t1 values (1), (2);
+insert into t2 values (1);
+create trigger t2_bd before delete on t2 for each row insert into t3 values (old.fk);
+# Trigger invoked by cascading action affects table used by subquery
+delete from t1 where (select count(*) from t3 where fk_log <> pk) > 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by view
+create view v1 as select count(*) as c from t3;
+delete from t1 where (select * from v1) = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+drop view v1;
+# Trigger invoked by cascading action affects table used by view
+# through stored function
+create function f1 () returns int return (select count(*) from t3);
+create view v1 as select f1() as c;
+delete from t1 where (select * from v1) = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by
+# stored function
+delete from t1 where f1() = 0;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by view
+# in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t3;
+create function f1 () returns int return (select c from v1);
+delete from t1 where f1();
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+# Trigger invoked by cascading action affects table used by stored
+# function through another stored function
+create function f2() returns int return f1();
+delete from t1 where f2();
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+drop view v1;
+drop function f2;
+drop function f1;
+# Trigger invoked by cascading action affects table used by trigger
+create trigger t1_bd before delete on t1 for each row set @a:=(select count(*) from t3);
+delete from t1 where pk = 1;
+ERROR HY000: Foreign key error: cascading action will change table 't3' which is also used elsewhere in statement
+drop tables t1, t2, t3;
+#
+# Test that this check is not confused by recursion in functions
+#
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on delete cascade on update set null);
+insert into t1 values (1);
+insert into t2 values (1), (1);
+create function f1 (i int) returns int
+begin
+if i <> 0 then
+return f1(i-1) + 1;
+else
+return 0;
+end if;
+end|
+# We should not hang or crash, but error about recursion
+# being disallowed in functions is OK and expected
+update t1 set pk= f1(3);
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+delete from t1 where f1(4);
+ERROR HY000: Recursive stored functions and triggers are not allowed.
+drop function f1;
+drop tables t1, t2;
#
# Foreign keys and views WITH CHECK OPTION.
#
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2009-01-11 11:07:16 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2009-01-21 22:27:26 +0000
@@ -834,6 +834,219 @@ create table t2(fk int not null referenc
set @@sql_mode= @old_sql_mode;
drop tables t1;
+--echo # Tests which check that we properly detect statements in which
+--echo # cascading actions might affect other stages of statement
+--echo # execution (e.g. WHERE clause evaluation) and thus lead to
+--echo # non-standard results. Such statements are prohibited at this
+--echo # point. In some cases this limitation will be raised by WL#4100.
+
+--echo #
+--echo # Checks for UPDATE
+--echo #
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on update set null);
+insert into t1 values (1), (2);
+insert into t2 values (1), (1);
+--echo # Cascading action affects table used by subquery
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where (select count(*) from t2 where fk = pk - 1) = 0;
+--echo # Cascading action affects table used by view
+create view v1 as select count(*) as c from t2 where fk is not null;
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where (select * from v1) > 0;
+drop view v1;
+--echo # Cascading action affects table used by view through stored function
+create function f1 () returns int return (select count(*) from t2 where fk is not null);
+create view v1 as select f1() as c;
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where (select * from v1) > 0;
+--echo # Cascading action affects table used by stored function
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where f1();
+--echo # Cascading action affects table used by view in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t2 where fk is not null;
+create function f1 () returns int return (select c from v1);
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where f1();
+--echo # Cascading action affects table used by stored function through
+--echo # another stored function
+create function f2() returns int return f1();
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where f2();
+drop view v1;
+drop function f2;
+drop function f1;
+--echo # Cascading action affects table used by trigger
+create trigger t1_bu before update on t1 for each row
+ set @a:=(select count(*) from t2 where fk is not null);
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1;
+drop tables t1, t2;
+--echo # Updates by trigger invoked by cascading action
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on update set null);
+create table t3 (fk_log int);
+insert into t1 values (1), (2);
+insert into t2 values (1);
+create trigger t2_bu before update on t2 for each row insert into t3 values (old.fk);
+--echo # Trigger invoked by cascading action affects table used by subquery
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where (select count(*) from t3 where fk_log <> pk) > 0;
+--echo # Trigger invoked by cascading action affects table used by view
+create view v1 as select count(*) as c from t3;
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where (select * from v1) = 0;
+drop view v1;
+--echo # Trigger invoked by cascading action affects table used by view
+--echo # through stored function
+create function f1 () returns int return (select count(*) from t3);
+create view v1 as select f1() as c;
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where (select * from v1) = 0;
+--echo # Trigger invoked by cascading action affects table used by
+--echo # stored function
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where f1() = 0;
+--echo # Trigger invoked by cascading action affects table used by view
+--echo # in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t3;
+create function f1 () returns int return (select c from v1);
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where f1();
+--echo # Trigger invoked by cascading action affects table used by stored
+--echo # function through another stored function
+create function f2() returns int return f1();
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where f2();
+drop view v1;
+drop function f2;
+drop function f1;
+--echo # Trigger invoked by cascading action affects table used by trigger
+create trigger t1_bu before update on t1 for each row set @a:=(select count(*) from t3);
+--error ER_FK_CHANGE_BY_CASCADE
+update t1 set pk= pk - 1 where pk = 1;
+drop tables t1, t2, t3;
+
+--echo #
+--echo # Same checks for DELETE
+--echo #
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on delete cascade);
+insert into t1 values (1);
+insert into t2 values (1), (1);
+--echo # Cascading action affects table used by subquery
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where (select count(*) from t2 where fk = pk) > 1;
+--echo # Cascading action affects table used by view
+create view v1 as select count(*) as c from t2;
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where (select * from v1) > 1;
+drop view v1;
+--echo # Cascading action affects table used by view through stored function
+create function f1 () returns int return (select count(*) from t2);
+create view v1 as select f1() as c;
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where (select * from v1) > 1;
+--echo # Cascading action affects table used by stored function
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where f1();
+--echo # Cascading action affects table used by view in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t2;
+create function f1 () returns int return (select c from v1);
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where f1();
+--echo # Cascading action affects table used by stored function through
+--echo # another stored function
+create function f2() returns int return f1();
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where f2();
+drop view v1;
+drop function f2;
+drop function f1;
+--echo # Cascading action affects table used by trigger
+create trigger t1_bd before delete on t1 for each row set @a:=(select count(*) from t2);
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where pk = 1;
+drop tables t1, t2;
+--echo # Similar tests for updates by trigger invoked by cascading action
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on delete cascade);
+create table t3 (fk_log int);
+insert into t1 values (1), (2);
+insert into t2 values (1);
+create trigger t2_bd before delete on t2 for each row insert into t3 values (old.fk);
+--echo # Trigger invoked by cascading action affects table used by subquery
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where (select count(*) from t3 where fk_log <> pk) > 0;
+--echo # Trigger invoked by cascading action affects table used by view
+create view v1 as select count(*) as c from t3;
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where (select * from v1) = 0;
+drop view v1;
+--echo # Trigger invoked by cascading action affects table used by view
+--echo # through stored function
+create function f1 () returns int return (select count(*) from t3);
+create view v1 as select f1() as c;
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where (select * from v1) = 0;
+--echo # Trigger invoked by cascading action affects table used by
+--echo # stored function
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where f1() = 0;
+--echo # Trigger invoked by cascading action affects table used by view
+--echo # in stored function
+drop view v1;
+drop function f1;
+create view v1 as select count(*) as c from t3;
+create function f1 () returns int return (select c from v1);
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where f1();
+--echo # Trigger invoked by cascading action affects table used by stored
+--echo # function through another stored function
+create function f2() returns int return f1();
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where f2();
+drop view v1;
+drop function f2;
+drop function f1;
+--echo # Trigger invoked by cascading action affects table used by trigger
+create trigger t1_bd before delete on t1 for each row set @a:=(select count(*) from t3);
+--error ER_FK_CHANGE_BY_CASCADE
+delete from t1 where pk = 1;
+drop tables t1, t2, t3;
+
+--echo #
+--echo # Test that this check is not confused by recursion in functions
+--echo #
+create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk) on delete cascade on update set null);
+insert into t1 values (1);
+insert into t2 values (1), (1);
+delimiter |;
+create function f1 (i int) returns int
+begin
+ if i <> 0 then
+ return f1(i-1) + 1;
+ else
+ return 0;
+ end if;
+end|
+delimiter ;|
+--echo # We should not hang or crash, but error about recursion
+--echo # being disallowed in functions is OK and expected
+--error ER_SP_NO_RECURSION
+update t1 set pk= f1(3);
+--error ER_SP_NO_RECURSION
+delete from t1 where f1(4);
+drop function f1;
+drop tables t1, t2;
+
--echo #
--echo # Foreign keys and views WITH CHECK OPTION.
--echo #
@@ -848,8 +1061,6 @@ insert into v2 values (0);
drop view v2;
drop tables t1, t2;
-
-#
# Test for bug #35522 "Foreign keys: 'foreign key without name' errors"
# In --foreign-key-all-engines mode we should not replace names of FKs
# in error messages with 'foreign key without name' even if though names
=== modified file 'sql/fk.cc'
--- a/sql/fk.cc 2008-12-23 20:29:54 +0000
+++ b/sql/fk.cc 2009-01-21 22:27:26 +0000
@@ -18,6 +18,7 @@
#include "fk_dd.h"
#include "sql_select.h"
#include "sp.h"
+#include "my_bitmap.h"
/**
@@ -1325,3 +1326,369 @@ err_with_index_end:
return TRUE;
}
+
+/**
+ An implementation of Execution_tree_visitor that collects
+ information about tables modified by foreign keys and tables
+ used outside foreign keys. When two sets of tables intersect,
+ an error is reported.
+*/
+
+class Cascade_conflict_check: public Execution_tree_visitor
+{
+public:
+ virtual enum_status enter_routine(void *node)
+ {
+ m_in_substatement++;
+ return S_OK;
+ }
+ virtual void leave_routine(void *node)
+ {
+ m_in_substatement--;
+ }
+
+ virtual enum_status enter_view(void *node)
+ {
+ return S_OK;
+ }
+
+ /** node == TABLE_LIST of the table. */
+ virtual enum_status enter_table(void *node)
+ {
+ TABLE_LIST *table= (TABLE_LIST*) node;
+ MY_BITMAP *bitmap= NULL;
+ if (m_in_own_cascade)
+ {
+ if (table->lock_type >= TL_WRITE_ALLOW_WRITE)
+ bitmap= &m_cascade_write_tables;
+ /*
+ Sic: READ tables inside an own CASCADE are also handled in
+ this branch.
+ */
+ }
+ else /* Add all tables. */
+ bitmap= &m_used_tables;
+
+ if (bitmap) /* Try to add the table to some set. */
+ {
+ uint id= get_unique_table_id(table);
+ if (! bitmap_is_set(bitmap, id))
+ {
+ /* It's the first time the table is added to this set. */
+ bitmap_set_bit(bitmap, id);
+ /* Some set is changed, re-check the invariant. */
+ if (bitmap_is_overlapping(&m_used_tables, &m_cascade_write_tables))
+ {
+ my_error(ER_FK_CHANGE_BY_CASCADE, MYF(0), table->table_name);
+ return S_ERROR;
+ }
+ }
+ }
+ return S_OK;
+ }
+
+ virtual enum_status enter_foreign_key_parent(void *node)
+ {
+ /* We can be inside a CASCADE of another CASCADE */
+ if (! m_in_substatement && m_in_own_cascade == NULL)
+ m_in_own_cascade= node;
+ return S_OK;
+ }
+ virtual void leave_foreign_key_parent(void *node)
+ {
+ if (m_in_own_cascade == node)
+ m_in_own_cascade= NULL;
+ }
+ virtual enum_status enter_foreign_key_child(void *node)
+ {
+ return S_OK; /* Visit the underlying tables. */
+ }
+
+ bool init(THD *thd);
+ Cascade_conflict_check();
+ ~Cascade_conflict_check();
+private:
+ unsigned int get_unique_table_id(TABLE_LIST *table);
+ /**
+ For now keep an own hash to map a unique name -> number.
+ Later we could assign numeric identifiers in open_tables(),
+ when allocating pre-locking placeholders (so that multiple
+ TABLE_LIST/TABLE may have the same identifier, but the same
+ identifier means the same name. QQQ: temporary table names?
+ Non-existent temporary tables at the time of traversal?
+ */
+ HASH m_table_names;
+ /** The set of tables that this statement or its sub-statements use. */
+ MY_BITMAP m_used_tables;
+ /**
+ The set of tables that are modified by a foreign key or in
+ a trigger invoked from this foreign key, or by some action
+ invoked further on.
+ */
+ MY_BITMAP m_cascade_write_tables;
+ void *m_in_own_cascade;
+ uint m_in_substatement;
+};
+
+
+extern "C"
+{
+
+static
+uchar *get_table_name_key(const uchar *record, size_t *length,
+ my_bool not_used __attribute__((unused)))
+{
+ TABLE_LIST *table_list= (TABLE_LIST*) record;
+ *length= table_list->mdl_lock_data->key_length;
+ return (uchar*) table_list->mdl_lock_data->key;
+}
+
+}
+
+
+Cascade_conflict_check::Cascade_conflict_check()
+ :m_in_own_cascade(NULL),
+ m_in_substatement(0)
+{
+ my_hash_clear(&m_table_names);
+}
+
+Cascade_conflict_check::~Cascade_conflict_check()
+{
+ if (my_hash_inited(&m_table_names))
+ my_hash_free(&m_table_names);
+}
+
+bool Cascade_conflict_check::init(THD *thd)
+{
+ my_bitmap_map *used_tables_buff, *cascade_write_tables_buff;
+ /*
+ XXX: this is not fully correct, we can have a lot of tmp tables.
+ We skip them, however. Anyway, it's a hack to look at this value.
+ */
+ uint max_tables= (thd->lock ? thd->lock->lock_count : 0) + 1;
+
+ if (! multi_alloc_root(thd->mem_root,
+ &used_tables_buff,
+ bitmap_buffer_size(max_tables),
+ &cascade_write_tables_buff,
+ bitmap_buffer_size(max_tables),
+ NULL) ||
+ hash_init(&m_table_names, &my_charset_bin, max_tables, 0, 0,
+ (hash_get_key) get_table_name_key, 0, 0))
+ {
+ return TRUE;
+ }
+ bitmap_init(&m_used_tables, used_tables_buff,
+ max_tables, FALSE);
+ bitmap_init(&m_cascade_write_tables, cascade_write_tables_buff,
+ max_tables, FALSE);
+ return FALSE;
+}
+
+
+unsigned int
+Cascade_conflict_check::get_unique_table_id(TABLE_LIST *table)
+{
+ TABLE_LIST *found;
+ if (! (found= (TABLE_LIST*) hash_search(&m_table_names,
+ (uchar*) table->mdl_lock_data->key,
+ table->mdl_lock_data->key_length)))
+ {
+ if (my_hash_insert(&m_table_names, (uchar*) table))
+ {
+ DBUG_ASSERT(0); /* We have preallocated max_tables in init. */
+ }
+ table->cascade_conflict_check_id= m_table_names.records;
+ found= table;
+ }
+ return found->cascade_conflict_check_id;
+}
+
+/**
+ Check if a ON UPDATE CASCADE/SET NULL/SET DEFAULT or a trigger
+ invoked by these actions may change a table (and, hence,
+ a record) that may have been already read by some "outer"
+ functionality in this statement -- in a stored function or
+ trigger involved besides the foreign key action, or even in
+ the main statement.
+
+ {{{1
+
+ In the LLD for WL#148 "Foreign Keys" this check is denoted as
+ fk_dmitri_check(). A long story follows.
+
+ What is READ/WRITE conflict
+ ---------------------------
+
+ In a fully SQL standard compliant environment, a strict
+ order is defined for execution for UPDATE/INSERT/DELETE
+ and associated checks and actions.
+
+ First, the set of records that is affected by a statement
+ is identified.
+
+ Secondly, BEFORE triggers are invoked to evaluate new
+ values for these records.
+
+ Then, the UPDATE/INSERT/DELETE itself occurs.
+
+ Then, CASCADE actions are invoked, and recursively, in
+ the order described here, triggers and checks are done for the
+ records modified by the cascade.
+
+ Then, AFTER triggers are involved.
+
+ Finally, all constraints, including PRIMARY KEY (sic) and
+ FOREIGN KEY constraints (except if this is RESTRICT FK, which is
+ checked during actual change of each row), are checked.
+
+ An important consequence of this order: the set of records
+ to change is calculated before the change is applied and
+ is not affected by foreign key actions or triggers.
+
+ WRITE/WRITE conflict
+ --------------------
+
+ Another requirement of the standard is that if a statement
+ modifies the same "site" twice (due to various checks and
+ actions), an error occurs.
+ This restriction applies to changes that happen through all
+ actions, including triggers, stored functions, etc., not just
+ foreign keys.
+
+ There is an ambiguity about what word "site" means. Is it
+ a record or a column? Is it allowed to update the same record
+ twice, or the restriction only applies when the same cell is
+ updated?
+
+ In case of foreign keys, most DBMSs (SQL Server, Oracle, Mimer)
+ stop updating same *row* twice, already at definition time.
+
+ PostgreSQL does allow dangerous definitions. However, it fails
+ to meet the above mentioned restriction: it allows to update
+ even the same column twice.
+
+ In MySQL, we agreed that site = column. We also agreed that
+ we won't allow dangerous definitions of foreign keys when two
+ foreign keys CASCADE into the same column(s). So, Like Oracle
+ and Mimer, we stop what we deem dangerous, at definition time.
+
+ But in scope of foreign key work, we're not changing the rest
+ of MySQL. We still can update the same site twice, e.g. first
+ time in BEFORE trigger, and then in AFTER trigger. No error
+ occurs.
+
+ MySQL execution order
+ ---------------------
+ In MySQL, the order in which triggers, checks and actions
+ are done is not standard.
+
+ INSERT/UPDATE/DELETE runs a loop over rows of the subject
+ table. Rows are retrieved via a sensitive cursor. For each
+ selected row we:
+ - invoke BEFORE triggers
+ - apply the change of UPDATE/INSERT/DELETE
+ - during the change, the storage engine may return
+ HA_ERR_FOUND_DUPP_KEY. In other words, the storage engine is
+ checking for unique/primary keys.
+ - invoke AFTER triggers
+
+ Foreign keys are an exception, and are checked in the
+ standard-compliant fashion. There still is a check done for each
+ row. But if such a check fails, the failure is ignored, the
+ record is put into the end-of-statement buffer and is re-checked
+ at the end of the statement. CASCADE actions are still done
+ row-by-row.
+
+ How MySQL deals with READ/WRITE conflicts
+ -----------------------------------------
+
+ Outside foreign keys, MySQL employs different approaches to
+ resolution of READ/WRITE conflicts.
+
+ In case of UPDATE, if you change columns that are also used
+ to evaluate WHERE clause, the server pre-fetches row ids of
+ affected rows before applying the change.
+
+ If you update a table and select from the same table using
+ a subquery, an error is returned. This is a non-standard
+ restriction to prevent non-standard results. Documented
+ and is a source of numerous user complains, see Bug#5037,
+ Bug#6980, Bug#14704, Bug#23316 and many others.
+
+ Same or similar error is produced if you use a view
+ instead of a subquery.
+
+ However, there is no such restriction when you update a table,
+ and select from it in a trigger of a stored function. We simply
+ may produce incorrect results. Bug#21326.
+
+ In InnoDB foreign keys, if you update a table via a CASCADE,
+ the change becomes visible immediately, which leads to
+ incorrect results. Bug#41477.
+
+ What does fk_dmitri_check() check?
+ ----------------------------------
+
+ This "optimized" approach for foreign key checks, but more
+ importantly, the non-standard order of other actions, leaves
+ a door open for non-standard results when a CASCADE affects
+ tables that are used elsewhere.
+
+ The goal of the function below is to identify such situations
+ and produce an error before starting the update.
+ Not all non-standard cases are taken care of. At present,
+ we intentionally don't resolve READ/WRITE conflicts between
+ triggers and subqueries, to preserve backward compatibility
+ with 5.0. The check only detects conflicts between CASCADE
+ and everything else (triggers, functions, subqueries and
+ the main update). The function does not duplicate functionality
+ of unique_table() and update_non_unique_table_error(),
+ though in future it can.
+
+ In order to determine that there is a conflict, a simple
+ syntactic rule is employed: if a table is used in some "outer"
+ statement, we report a conflict. Note, that due to procedural
+ nature of stored functions and triggers "the outer statement" is
+ not bound to run and thus cause a conflict. Moreover,
+ since we don't know the set of records affected by the outer
+ statement, we don't know if it intersects with rows changed by
+ CASCADE. The check also doesn't take into account 6.0 subquery
+ materialization strategy.
+
+ With this approach, a WRITE/WRITE conflict between a foreign
+ key and a function, trigger or the main UPDATE is also detected
+ automatically.
+
+ Future uses
+ -----------
+
+ In future, it's planned to extend this function to cover all
+ types of READ/WRITE conflicts. The function is designed to
+ return a boolean result, so that in case of a conflict, a simple
+ UPDATE/DELETE may be turned into MULTI-UPDATE, i.e.
+ materialization may be employed to avoid the conflict instead of
+ yielding an error.
+
+ @todo Change the signature to report the type of conflict
+ found: READ/WRITE or WRITE/WRITE.
+
+ @retval TRUE Error. ER_FK_CHANGE_BY_CASCADE is reported
+ @retval FALSE Success, no conflict.
+ }}}
+*/
+
+
+bool
+Fk_constraint_list::do_cascade_conflict_check(THD *thd, LEX *lex)
+{
+ Cascade_conflict_check cascade_conflict_check;
+
+ if (cascade_conflict_check.init(thd))
+ return TRUE;
+
+ return lex->accept_visitor(thd, &cascade_conflict_check);
+}
+
+// vim: fdm=marker
=== modified file 'sql/fk.h'
--- a/sql/fk.h 2008-12-13 23:33:23 +0000
+++ b/sql/fk.h 2009-01-21 22:27:26 +0000
@@ -67,6 +67,23 @@ public:
do_cascading_action_child_list(table));
}
+ bool cascade_conflict_check(THD *thd, LEX *lex, TABLE_LIST * /* unused */)
+ {
+ /*
+ Short cut the check if we have no cascading keys.
+
+ Why do we not check for functions and triggers? If they use
+ tables, they may indirectly involve a cascading foreign key.
+ And we perhaps would like to cover all cases when cascading
+ foreign keys cause trouble. Besides, in future, this check
+ may be extended to also yield TRUE if there is any form of
+ read/write conflict, not just from cascade.
+ */
+ return (! m_fkeys_parent_cascade.is_empty() &&
+ do_cascade_conflict_check(thd, lex));
+
+ }
+
private:
/** Implementation. */
@@ -75,6 +92,8 @@ private:
bool do_check_child_list(TABLE *table, bool use_eos);
bool do_cascading_action_child_list(TABLE *table);
+
+ bool do_cascade_conflict_check(THD *thd, LEX *lex);
private:
/**
Type of performed operation: INSERT, UPDATE or DELETE. Defines
=== modified file 'sql/fk_dd.cc'
--- a/sql/fk_dd.cc 2008-12-23 20:29:54 +0000
+++ b/sql/fk_dd.cc 2009-01-21 22:27:26 +0000
@@ -2441,3 +2441,129 @@ make_foreign_key_list(THD *thd,
}
return FALSE;
}
+
+
+/**
+ Implement Execution_tree_visitor interface.
+*/
+
+bool
+Foreign_key_share_list::
+accept_visitor(THD *thd, Execution_tree_visitor *visitor, uint8 trg_event_map)
+{
+ uint8 trg_map_insert= (uint8) (1 << (int)TRG_EVENT_INSERT);
+ uint8 trg_map_update= (uint8) (1 << (int)TRG_EVENT_UPDATE);
+ uint8 trg_map_delete= (uint8) (1 << (int)TRG_EVENT_DELETE);
+ Execution_tree_visitor::enum_status status;
+
+ if (trg_event_map & (trg_map_insert | trg_map_update))
+ {
+ List_iterator<Foreign_key_child_share> it(fkeys_child);
+ Foreign_key_child_share *fk;
+ while ((fk= it++))
+ {
+ TABLE_LIST *tl;
+
+ if ((status= visitor->enter_foreign_key_child(fk)))
+ return status == Execution_tree_visitor::S_ERROR;
+
+ tl= find_prelocked_table(thd,
+ fk->parent_table_db.str,
+ fk->parent_table_name.str,
+ TL_READ_NO_INSERT, 0);
+ if (tl && tl->accept_visitor(thd, visitor))
+ return TRUE;
+
+ visitor->leave_foreign_key_child(fk);
+ }
+ }
+ /*
+ Use "If" and not an "else if", since it's quite possible to
+ have both events, trg_map_update and trg_map_insert, e.g. in
+ case of INSERT ON DUPLICATE KEY UPDATE.
+ */
+ if (trg_event_map & (trg_map_update | trg_map_delete))
+ {
+ List_iterator<Foreign_key_parent_share> it(fkeys_parent);
+ Foreign_key_parent_share *fk;
+ while ((fk= it++))
+ {
+ TABLE_LIST *tl;
+
+ if ((status= visitor->enter_foreign_key_parent(fk)))
+ return status == Execution_tree_visitor::S_ERROR;
+
+ if (trg_event_map & trg_map_update)
+ {
+ thr_lock_type lock_type;
+ uint8 local_trg_event_map;
+
+ switch (fk->update_opt) {
+ case FK_OPTION_NO_ACTION:
+ case FK_OPTION_RESTRICT:
+ lock_type= TL_READ_NO_INSERT;
+ local_trg_event_map= 0;
+ break;
+ case FK_OPTION_CASCADE:
+ case FK_OPTION_SET_NULL:
+ case FK_OPTION_DEFAULT:
+ lock_type= TL_WRITE;
+ local_trg_event_map= trg_map_update;
+ break;
+ default:
+ DBUG_ASSERT(0);
+ return TRUE;
+ }
+
+ tl= find_prelocked_table(thd,
+ fk->child_table_db.str,
+ fk->child_table_name.str,
+ lock_type, local_trg_event_map);
+
+ if (tl && tl->accept_visitor(thd, visitor))
+ return TRUE;
+ }
+ /*
+ Quite possible to have both, update and delete, e.g.
+ in case of REPLACE.
+ */
+ if (trg_event_map & trg_map_delete)
+ {
+ thr_lock_type lock_type;
+ uint8 local_trg_event_map;
+
+ switch (fk->delete_opt) {
+ case FK_OPTION_NO_ACTION:
+ case FK_OPTION_RESTRICT:
+ lock_type= TL_READ_NO_INSERT;
+ local_trg_event_map= 0;
+ break;
+ case FK_OPTION_CASCADE:
+ lock_type= TL_WRITE;
+ local_trg_event_map= trg_map_delete;
+ break;
+ case FK_OPTION_SET_NULL:
+ case FK_OPTION_DEFAULT:
+ lock_type= TL_WRITE;
+ local_trg_event_map= trg_map_update;
+ break;
+ default:
+ DBUG_ASSERT(0);
+ return TRUE;
+ }
+
+ tl= find_prelocked_table(thd,
+ fk->child_table_db.str,
+ fk->child_table_name.str,
+ lock_type, local_trg_event_map);
+
+ if (tl && tl->accept_visitor(thd, visitor))
+ return TRUE;
+ }
+ visitor->leave_foreign_key_parent(fk);
+ }
+ }
+ return FALSE;
+}
+
+
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2008-12-13 23:33:23 +0000
+++ b/sql/mysql_priv.h 2009-01-21 22:27:26 +0000
@@ -1340,6 +1340,9 @@ bool tdc_open_view(THD *thd, TABLE_LIST
char *cache_key, uint cache_key_length,
MEM_ROOT *mem_root, uint flags);
TABLE *find_locked_table(TABLE *list, const char *db, const char *table_name);
+TABLE_LIST *
+find_prelocked_table(THD *thd, const char *db_name, const char *table_name,
+ thr_lock_type lock_type, uint8 trg_event_map);
TABLE *find_write_locked_table(TABLE *list, const char *db,
const char *table_name);
thr_lock_type read_lock_type_for_table(THD *thd, TABLE *table);
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2008-12-13 23:33:23 +0000
+++ b/sql/share/errmsg.txt 2009-01-21 22:27:26 +0000
@@ -6495,4 +6495,6 @@ ER_FK_CHILD_VALUE_EXISTS 23000
eng "Foreign key error: constraint '%-.192s': cannot change because foreign key refers to value '%s'"
ER_FK_CHILD_NO_INDEX
eng "Foreign key error: constraint '%-.192s': no supporting index on child table"
+ER_FK_CHANGE_BY_CASCADE
+ eng "Foreign key error: cascading action will change table '%s' which is also used elsewhere in statement"
=== modified file 'sql/sp.cc'
--- a/sql/sp.cc 2008-12-10 11:42:31 +0000
+++ b/sql/sp.cc 2009-01-21 22:27:26 +0000
@@ -1383,32 +1383,6 @@ sp_routine_exists_in_table(THD *thd, int
}
-/**
- Structure that represents element in the set of stored routines
- used by statement or routine.
-*/
-struct Sroutine_hash_entry;
-
-struct Sroutine_hash_entry
-{
- /**
- Set key consisting of one-byte routine type and quoted routine name.
- */
- LEX_STRING key;
- /**
- Next element in list linking all routines in set. See also comments
- for LEX::sroutine/sroutine_list and sp_head::m_sroutines.
- */
- Sroutine_hash_entry *next;
- /**
- Uppermost view which directly or indirectly uses this routine.
- 0 if routine is not used in view. Note that it also can be 0 if
- statement uses routine both via view and directly.
- */
- TABLE_LIST *belong_to_view;
-};
-
-
extern "C" uchar* sp_sroutine_key(const uchar *ptr, size_t *plen,
my_bool first)
{
@@ -2025,3 +1999,43 @@ sp_load_for_information_schema(THD *thd,
thd->lex= old_lex;
return sp;
}
+
+
+/**
+ Find a stored routine in the respective routine cache (procedure
+ or function cache), and apply visitor.
+
+ Since our execution tree has no link between a stored routine
+ and its used routines, we need an auxiliary function that will
+ look the routine up in the cache and visit it.
+ We are not necessarily visiting the instance that will
+ be used during execution: sp_cache_lookup() returns the
+ first instance of the routine in the thread cache. If it's
+ a stored procedure, and there is recursion, real execution
+ may visit other instances as well. On the other hand,
+ visitor already purposefully avoids recursion.
+*/
+
+bool sp_find_and_visit_routine(THD *thd, Sroutine_hash_entry *rt,
+ Execution_tree_visitor *visitor)
+{
+ sp_head *sp;
+ int type= rt->key.str[0];
+ sp_cache **cache= (type == TYPE_ENUM_FUNCTION ?
+ &thd->sp_func_cache : &thd->sp_proc_cache);
+ /*
+ Triggers can't happen here: they are always processed
+ in Table_trigger_list::accept_visitor().
+ */
+ DBUG_ASSERT(type == TYPE_ENUM_FUNCTION || type == TYPE_ENUM_PROCEDURE);
+
+ if (! (sp= sp_cache_lookup(cache, rt->key.str+1, rt->key.length-1)))
+ {
+ /* Pre-locking must have ensured that the routine is in the cache. */
+ DBUG_ASSERT(0);
+ return FALSE;
+ }
+ return sp->accept_visitor(thd, visitor);
+}
+
+
=== modified file 'sql/sp.h'
--- a/sql/sp.h 2008-08-03 10:13:01 +0000
+++ b/sql/sp.h 2009-01-21 22:27:26 +0000
@@ -85,6 +85,32 @@ sp_update_routine(THD *thd, int type, sp
int
sp_drop_routine(THD *thd, int type, sp_name *name);
+
+/**
+ Structure that represents element in the set of stored routines
+ used by statement or routine.
+*/
+
+struct Sroutine_hash_entry
+{
+ /**
+ Set key consisting of one-byte routine type and quoted routine name.
+ */
+ LEX_STRING key;
+ /**
+ Next element in list linking all routines in set. See also comments
+ for LEX::sroutine/sroutine_list and sp_head::m_sroutines.
+ */
+ Sroutine_hash_entry *next;
+ /**
+ Uppermost view which directly or indirectly uses this routine.
+ 0 if routine is not used in view. Note that it also can be 0 if
+ statement uses routine both via view and directly.
+ */
+ TABLE_LIST *belong_to_view;
+};
+
+
/*
Procedures for pre-caching of stored routines and building table list
for prelocking.
@@ -120,4 +146,7 @@ sp_load_for_information_schema(THD *thd,
String *name, ulong sql_mode, int type,
const char *returns, const char *params,
bool *free_sp_head);
+class Execution_tree_visitor;
+bool sp_find_and_visit_routine(THD *thd, Sroutine_hash_entry *rt,
+ Execution_tree_visitor *visitor);
#endif /* _SP_H_ */
=== modified file 'sql/sp_cache.cc'
--- a/sql/sp_cache.cc 2008-07-03 19:41:22 +0000
+++ b/sql/sp_cache.cc 2009-01-21 22:27:26 +0000
@@ -42,7 +42,7 @@ public:
my_hash_insert(&m_hashtable, (const uchar *)sp);
}
- inline sp_head *lookup(char *name, uint namelen)
+ inline sp_head *lookup(const char *name, uint namelen)
{
return (sp_head *)hash_search(&m_hashtable, (const uchar *)name, namelen);
}
@@ -155,10 +155,16 @@ void sp_cache_insert(sp_cache **cp, sp_h
sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name)
{
+ return sp_cache_lookup(cp, name->m_qname.str, name->m_qname.length);
+}
+
+
+sp_head *sp_cache_lookup(sp_cache **cp, const char *key, uint length)
+{
sp_cache *c= *cp;
if (! c)
return NULL;
- return c->lookup(name->m_qname.str, name->m_qname.length);
+ return c->lookup(key, length);
}
=== modified file 'sql/sp_cache.h'
--- a/sql/sp_cache.h 2008-07-03 19:41:22 +0000
+++ b/sql/sp_cache.h 2009-01-21 22:27:26 +0000
@@ -56,6 +56,7 @@ void sp_cache_init();
void sp_cache_clear(sp_cache **cp);
void sp_cache_insert(sp_cache **cp, sp_head *sp);
sp_head *sp_cache_lookup(sp_cache **cp, sp_name *name);
+sp_head *sp_cache_lookup(sp_cache **cp, const char *key, uint key_len);
void sp_cache_invalidate();
void sp_cache_flush_obsolete(sp_cache **cp);
ulong sp_cache_version(sp_cache **cp);
=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc 2008-12-10 11:42:31 +0000
+++ b/sql/sp_head.cc 2009-01-21 22:27:26 +0000
@@ -23,6 +23,8 @@
#include "sp_pcontext.h"
#include "sp_rcontext.h"
#include "sp_cache.h"
+#include "sql_trigger.h"
+#include "fk_dd.h"
/*
Sufficient max length of printed destinations and frame offsets (all uints).
@@ -4014,3 +4016,69 @@ sp_add_to_query_tables(THD *thd, LEX *le
return table;
}
+
+/**
+ Accept an Execution_tree_visitor.
+
+ Walk over all used tables and routines.
+ Skip temporary tables.
+*/
+
+bool sp_head::accept_visitor(THD *thd, Execution_tree_visitor *visitor)
+{
+ Execution_tree_visitor::enum_status status;
+ Execution_tree_visitor::Recursion_sentry current_sp(this, visitor);
+ uint idx;
+
+ if (current_sp.is_being_visited())
+ return FALSE;
+
+ if ((status= visitor->enter_routine(this)))
+ {
+ /*
+ If enter() failed, don't call leave().
+ Return TRUE only if S_ERROR. S_SKIP is not an error.
+ */
+ return status == Execution_tree_visitor::S_ERROR;
+ }
+
+ /* Iterate over used tables. */
+ for (idx= 0 ; idx < m_sptabs.records ; idx++)
+ {
+ SP_TABLE *tab= (SP_TABLE*) hash_element(&m_sptabs, idx);
+ TABLE_LIST *tl;
+
+ /*
+ Skip temporary tables. This is just an optimization, since
+ we never need to visit temporary tables currently.
+ */
+ if (tab->temp)
+ continue;
+
+ tl= find_prelocked_table(thd, tab->qname.str,
+ tab->qname.str+tab->db_length+1,
+ tab->lock_type, tab->trg_event_map);
+ /*
+ The table may not exist. Perhaps it will be created
+ as a temporary table during execution. Or it isn't even
+ expected to exist according to the routine logic. In
+ any case, it's not an error.
+ XXX: What about information schema tables? Are they in the list?
+ */
+ if (tl && tl->accept_visitor(thd, visitor))
+ return TRUE;/* If accept() failed, don't leave(). */
+ }
+
+ /* Iterate over used routines. */
+ for (idx= 0 ; idx < m_sroutines.records ; idx++)
+ {
+ Sroutine_hash_entry *rt;
+ rt= (Sroutine_hash_entry*) hash_element(&m_sroutines, idx);
+ if (sp_find_and_visit_routine(thd, rt, visitor))
+ return TRUE;
+ }
+
+ visitor->leave_routine(this);
+ return FALSE;
+}
+
=== modified file 'sql/sp_head.h'
--- a/sql/sp_head.h 2008-10-10 08:21:42 +0000
+++ b/sql/sp_head.h 2009-01-21 22:27:26 +0000
@@ -148,6 +148,7 @@ public:
bool
check_routine_name(LEX_STRING *ident);
+
class sp_head :private Query_arena
{
sp_head(const sp_head &); /**< Prevent use of these */
@@ -472,6 +473,8 @@ public:
sp_pcontext *get_parse_context() { return m_pcont; }
+ bool accept_visitor(THD *thd, Execution_tree_visitor *visitor);
+
private:
MEM_ROOT *m_thd_root; ///< Temp. store for thd's mem_root
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2008-12-23 20:29:54 +0000
+++ b/sql/sql_base.cc 2009-01-21 22:27:26 +0000
@@ -1406,12 +1406,16 @@ void close_thread_tables(THD *thd,
UNLOCK TABLES if needed.
*/
if (thd->locked_tables_mode == LTM_PRELOCKED_UNDER_LOCK_TABLES)
+ {
thd->locked_tables_mode= LTM_LOCK_TABLES;
+ thd->prelocked_tables= NULL;
+ }
if (thd->locked_tables_mode == LTM_LOCK_TABLES)
DBUG_VOID_RETURN;
thd->locked_tables_mode= LTM_NONE;
+ thd->prelocked_tables= NULL;
/*
Note that we are leaving prelocked mode so we don't need
@@ -2850,6 +2854,25 @@ TABLE *find_locked_table(TABLE *list, co
/**
+ QQ: Is there any better solution? Note that currently we can't use
+ THD::open_tables as it does not contains views...
+*/
+
+TABLE_LIST *
+find_prelocked_table(THD *thd, const char *db_name, const char *table_name,
+ thr_lock_type lock_type, uint8 trg_event_map)
+{
+ for (TABLE_LIST *tl= thd->prelocked_tables; tl; tl= tl->next_global)
+ if (!strcmp(db_name, tl->db) && !strcmp(table_name, tl->table_name) &&
+ tl->lock_type == lock_type && tl->trg_event_map == trg_event_map)
+ return tl;
+ return NULL;
+}
+
+
+
+
+/**
Find write locked instance of table in the list of open tables,
emit error if no such instance found.
@@ -4582,6 +4605,7 @@ int lock_tables(THD *thd, TABLE_LIST *ta
mark_real_tables_as_free_for_reuse(first_not_own);
DBUG_PRINT("info",("locked_tables_mode= LTM_PRELOCKED"));
thd->locked_tables_mode= LTM_PRELOCKED;
+ thd->prelocked_tables= tables;
}
}
else
@@ -4616,6 +4640,7 @@ int lock_tables(THD *thd, TABLE_LIST *ta
DBUG_PRINT("info",
("thd->locked_tables_mode= LTM_PRELOCKED_UNDER_LOCK_TABLES"));
thd->locked_tables_mode= LTM_PRELOCKED_UNDER_LOCK_TABLES;
+ thd->prelocked_tables= tables;
}
}
=== modified file 'sql/sql_class.cc'
--- a/sql/sql_class.cc 2008-12-10 11:42:31 +0000
+++ b/sql/sql_class.cc 2009-01-21 22:27:26 +0000
@@ -224,6 +224,13 @@ bool foreign_key_prefix(Key *a, Key *b)
#endif
}
+/***************************************************************************
+* Execution_tree_visitor
+***************************************************************************/
+
+Execution_tree_visitor::~Execution_tree_visitor()
+{
+}
/****************************************************************************
** Thread specific functions
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2008-12-13 23:33:23 +0000
+++ b/sql/sql_class.h 2009-01-21 22:27:26 +0000
@@ -24,6 +24,7 @@
#include "log.h"
#include "rpl_tblmap.h"
#include "mdl.h"
+#include "fk.h"
#include <waiting_threads.h>
@@ -1153,6 +1154,7 @@ public:
MDL_CONTEXT mdl_context;
MDL_CONTEXT handler_mdl_context;
+ TABLE_LIST *prelocked_tables;
/**
This constructor initializes Open_tables_state instance which can only
be used as backup storage. To prepare Open_tables_state instance for
@@ -1184,6 +1186,7 @@ public:
m_reprepare_observer= NULL;
mdl_context_init(&mdl_context, thd);
mdl_context_init(&handler_mdl_context, thd);
+ prelocked_tables= NULL;
}
};
@@ -1247,6 +1250,127 @@ show_system_thread(enum_thread_type thre
return "UNKNOWN"; /* keep gcc happy */
}
+
+/**
+ An interface to traverse through a parsed execution tree of
+ a (sub-)statement and iterate over all used tables.
+
+ If you have a statement, that uses stored routines, triggers,
+ views, foreign keys, and would like to iterate over all tables used
+ in this statement, create a descendant of this class and pass
+ an instance of it to the top
+
+ Why are we passing in void *, and not an instance of a virtual
+ class? Not all nodes have destructors, so not all nodes can be
+ inherit from an interface.
+
+ There is a guarantee that for each enter_node() there is a
+ leave_node() with the same node pointer.
+ There is no guarantee, however, that nodes in the execution
+ tree that stand for the same database object have the same
+ node pointer (think of two TABLE_LIST elements for the same table).
+
+ Sic: we currently don't go over temporary tables, since we don't
+ need it.
+
+ Sic: leave() is not called if you chose to S_SKIP on entrance.
+ Sic: leave() is not called if accept_visitor of a nested object
+ returns an error.
+
+ Pattern: Hierarchical Visitor.
+*/
+
+class Execution_tree_visitor
+{
+public:
+ enum enum_status {
+ S_OK= 0, /* Continue iteration. */
+ S_SKIP, /* Skip all sub-nodes of this node, return to upper node. */
+ S_ERROR /* Abort iteration. */
+ };
+
+ /**
+ node == sp_head. Used for functions, procedures and triggers.
+ Check sp_head::m_type to find out what it is.
+ @todo Maybe split into 3 calls.
+ */
+ virtual enum_status enter_routine(void *node) { return S_SKIP; }
+ virtual void leave_routine(void *node) {}
+
+ /** node == TABLE_LIST of the view */
+ virtual enum_status enter_view(void *node) { return S_SKIP; }
+ virtual void leave_view(void *node) {}
+
+ /** node == TABLE_LIST of the table. */
+ virtual enum_status enter_table(void *node) { return S_SKIP; }
+ virtual void leave_table(void *node) {}
+
+ /**
+ node == Foreign_key_parent_share.
+ We don't yet have Foreign_key_parent_rcontext.
+ */
+ virtual enum_status enter_foreign_key_parent(void *node) { return S_SKIP; }
+ virtual void leave_foreign_key_parent(void *node) {}
+
+ virtual enum_status enter_foreign_key_child(void *node) { return S_SKIP; }
+ virtual void leave_foreign_key_child(void *node) {}
+
+ Execution_tree_visitor() :m_last_sentry(NULL) {}
+
+ virtual ~Execution_tree_visitor();
+
+ class Recursion_sentry;
+protected:
+ Recursion_sentry *m_last_sentry;
+};
+
+
+/**
+ A simple sentry to prevent recursion. Used by Table_walker
+ interface implementations.
+
+ Recursion has many incarnations: recursive stored procedures,
+ a table that has a foreign key that refers to the same table,
+ etc. In an implementation of Table_walker interface, instantiate
+ this class with a unique key and call is_being_visited() to check
+ if the same key has already been supplied before.
+*/
+
+class Execution_tree_visitor::Recursion_sentry
+{
+public:
+ Recursion_sentry(void *key_arg, Execution_tree_visitor *visitor_arg)
+ : m_visitor(visitor_arg),
+ m_prev_sentry(visitor_arg->m_last_sentry),
+ m_key(key_arg)
+ {
+ visitor_arg->m_last_sentry= this;
+ }
+
+ ~Recursion_sentry()
+ {
+ DBUG_ASSERT(m_visitor->m_last_sentry= this);
+ m_visitor->m_last_sentry= m_visitor->m_last_sentry->m_prev_sentry;
+ }
+
+ bool is_being_visited() const
+ {
+ Recursion_sentry *sentry= m_prev_sentry;
+ for (; sentry; sentry= sentry->m_prev_sentry)
+ {
+ if (sentry->m_key == m_key)
+ return TRUE;
+ }
+ return FALSE;
+ }
+ const Recursion_sentry *prev_sentry() const { return m_prev_sentry; }
+private:
+ Execution_tree_visitor *m_visitor;
+ Recursion_sentry *m_prev_sentry;
+ void *m_key;
+};
+
+
/**
This class represents the interface for internal error handlers.
Internal error handlers are exception handlers used by the server
@@ -1290,6 +1414,7 @@ public:
const char *message) = 0;
};
+
/**
Tables that were locked with LOCK TABLES statement.
=== modified file 'sql/sql_delete.cc'
--- a/sql/sql_delete.cc 2008-12-23 20:29:54 +0000
+++ b/sql/sql_delete.cc 2009-01-21 22:27:26 +0000
@@ -382,7 +382,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *
table->mark_columns_needed_for_delete();
- if (fk_list.prepare_check_child(thd, table, FALSE, &table->s->all_set))
+ if (fk_list.prepare_check_child(thd, table, FALSE, &table->s->all_set) ||
+ fk_list.cascade_conflict_check(thd, thd->lex, table_list))
{
end_read_record(&info);
delete select;
=== modified file 'sql/sql_lex.cc'
--- a/sql/sql_lex.cc 2008-12-10 11:42:31 +0000
+++ b/sql/sql_lex.cc 2009-01-21 22:27:26 +0000
@@ -3092,3 +3092,43 @@ int LEX::add_db_to_list(LEX_STRING *name
return 0;
}
+
+
+bool
+LEX::accept_visitor(THD *thd, Execution_tree_visitor *visitor)
+{
+ Sroutine_hash_entry *rt;
+ TABLE_LIST *table;
+
+ for (table= query_tables; table && !table->prelocking_placeholder;
+ table= table->next_global)
+ {
+#if 0
+ if (!table->select_lex) /* skip derived tables? */
+ continue;
+
+ if (table->belong_to_view) /* How can it happen here? Add assert? */
+ continue;
+#endif
+
+ /* We're only interested in base tables and views. */
+ if (table->view ||
+ (table->table && table->table->s->tmp_table == NO_TMP_TABLE))
+ {
+ if (table->accept_visitor(thd, visitor))
+ return TRUE;
+ }
+ }
+
+ /* Walk over all all used routines. */
+ rt= (Sroutine_hash_entry*) sroutines_list.first;
+ for (; rt && rt != (Sroutine_hash_entry*) *sroutines_list_own_last;
+ rt= rt->next)
+ {
+ if (rt->key.str[0] != TYPE_ENUM_FOREIGN_KEY &&
+ rt->key.str[0] != TYPE_ENUM_TRIGGER &&
+ sp_find_and_visit_routine(thd, rt, visitor))
+ return TRUE;
+ }
+ return FALSE;
+}
=== modified file 'sql/sql_lex.h'
--- a/sql/sql_lex.h 2008-12-10 11:42:31 +0000
+++ b/sql/sql_lex.h 2009-01-21 22:27:26 +0000
@@ -30,6 +30,7 @@ class sp_pcontext;
class st_alter_tablespace;
class partition_info;
class Event_parse_data;
+class Execution_tree_visitor;
#ifdef MYSQL_SERVER
/*
@@ -1903,6 +1904,7 @@ struct LEX: public Query_tables_list
}
int add_db_to_list(LEX_STRING *name);
+ bool accept_visitor(THD *thd, Execution_tree_visitor *visitor);
};
=== modified file 'sql/sql_trigger.cc'
--- a/sql/sql_trigger.cc 2008-12-10 11:42:31 +0000
+++ b/sql/sql_trigger.cc 2009-01-21 22:27:26 +0000
@@ -1989,6 +1989,39 @@ bool Table_triggers_list::process_trigge
/**
+ Implement Execution_tree_visitor interface.
+
+ Pass on the walker to all triggers.
+
+ We don't worry for recursion here, it's done inside
+ sp_head::accept_visitor().
+
+ @param trg_event_map Only visit those triggers that have an event map
+ matching the argument.
+*/
+
+bool
+Table_triggers_list::accept_visitor(THD *thd, Execution_tree_visitor *visitor,
+ uint8 trg_event_map)
+{
+ for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
+ {
+ if (trg_event_map & static_cast<uint8>(1 << static_cast<int>(i)))
+ {
+ for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
+ {
+ sp_head *trigger= bodies[i][j];
+ if (trigger && trigger->accept_visitor(thd, visitor))
+ return TRUE;
+ }
+ }
+ }
+ return FALSE;
+}
+
+
+
+/**
Mark fields of subject table which we read/set in its triggers
as such.
=== modified file 'sql/sql_trigger.h'
--- a/sql/sql_trigger.h 2008-08-03 10:13:01 +0000
+++ b/sql/sql_trigger.h 2009-01-21 22:27:26 +0000
@@ -14,6 +14,8 @@
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
+class Table_walker;
+
/**
This class holds all information about triggers of table.
@@ -152,6 +154,8 @@ public:
friend class Item_trigger_field;
friend int sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
TABLE_LIST *table);
+ bool accept_visitor(THD *thd, Execution_tree_visitor *visitor,
+ uint8 trg_event_map);
private:
bool prepare_for_trigger_fields(TABLE *table);
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2008-12-23 20:29:54 +0000
+++ b/sql/sql_update.cc 2009-01-21 22:27:26 +0000
@@ -534,7 +534,8 @@ int mysql_update(THD *thd,
table->mark_columns_needed_for_update();
if (fk_list.prepare_check_parent(thd, table, table->write_set) ||
- fk_list.prepare_check_child(thd, table, TRUE, table->write_set))
+ fk_list.prepare_check_child(thd, table, TRUE, table->write_set) ||
+ fk_list.cascade_conflict_check(thd, thd->lex, table_list))
goto err;
/* Check if we are modifying a key that we are used to search with */
=== modified file 'sql/table.cc'
--- a/sql/table.cc 2008-12-23 20:29:54 +0000
+++ b/sql/table.cc 2009-01-21 22:27:26 +0000
@@ -21,6 +21,7 @@
#include <m_ctype.h>
#include "my_md5.h"
#include "fk_dd.h"
+#include "sp.h"
/* INFORMATION_SCHEMA name */
LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")};
@@ -4141,6 +4142,82 @@ bool TABLE_LIST::prepare_security(THD *t
}
+/**
+ Implement visitor interface for this TABLE_LIST, be it a view
+ or a base table.
+*/
+
+bool
+TABLE_LIST::accept_visitor(THD *thd, Execution_tree_visitor *visitor)
+{
+ if (view)
+ {
+ Execution_tree_visitor::enum_status status;
+ Sroutine_hash_entry *rt;
+
+ if ((status= visitor->enter_view(this)))
+ {
+ /*
+ If enter() failed, don't call leave().
+ Return TRUE only if S_ERROR. S_SKIP is not an error.
+ */
+ return status == Execution_tree_visitor::S_ERROR;
+ }
+ if (view_tables) /* Walk over all used tables. */
+ {
+ List_iterator<TABLE_LIST> table_it(*view_tables);
+ TABLE_LIST *table;
+ while ((table= table_it++))
+ {
+ /* We're only interested in base tables and views. */
+ if (table->view ||
+ (table->table && table->table->s->tmp_table == NO_TMP_TABLE))
+ {
+ /*
+ No need to check for recursion, it's checked at view DDL time.
+ Should the data dictionary become corrupt, this view would
+ have failed to open.
+ */
+ if (table->accept_visitor(thd, visitor))
+ return TRUE;
+ }
+ }
+ }
+ /* Walk over all all used routines. */
+ rt= (Sroutine_hash_entry*) view->sroutines_list.first;
+ for (; rt; rt= rt->next)
+ {
+ if (sp_find_and_visit_routine(thd, rt, visitor))
+ return TRUE;
+ }
+ visitor->leave_view(this);
+ }
+ else if (table && table->s->tmp_table == NO_TMP_TABLE)
+ {
+ /* We're only interested in base tables. */
+ Execution_tree_visitor::enum_status status;
+
+ if ((status= visitor->enter_table(this)))
+ {
+ /*
+ If enter() failed, don't call leave().
+ Return TRUE only if S_ERROR. S_SKIP is not an error.
+ */
+ return status == Execution_tree_visitor::S_ERROR;
+ }
+ /* Visit all triggers. */
+ if (table->triggers &&
+ table->triggers->accept_visitor(thd, visitor, trg_event_map))
+ return TRUE;
+ /* Visit all foreign keys. */
+ if (table->s->fkeys.accept_visitor(thd, visitor, trg_event_map))
+ return TRUE;
+
+ visitor->leave_table(this);
+ }
+ return FALSE;
+}
+
Natural_join_column::Natural_join_column(Field_translator *field_param,
TABLE_LIST *tab)
{
=== modified file 'sql/table.h'
--- a/sql/table.h 2008-12-23 20:29:54 +0000
+++ b/sql/table.h 2009-01-21 22:27:26 +0000
@@ -31,7 +31,8 @@ class Foreign_key_parent;
class Foreign_key_child;
class Foreign_key_child_share;
class Foreign_key_parent_share;
-class LEX;
+struct LEX;
+class Execution_tree_visitor;
struct MDL_LOCK_DATA;
/*************************************************************************/
@@ -331,6 +332,8 @@ public:
const char *field_name);
void get_table_constraints_for_create(THD *thd, String *str);
bool init_column_sets(TABLE_SHARE *share);
+ bool accept_visitor(THD *thd, Execution_tree_visitor *visitor,
+ uint8 trg_event_map);
};
@@ -1210,6 +1213,8 @@ struct TABLE_LIST
List<Index_hint> *index_hints;
TABLE *table; /* opened table */
uint table_id; /* table id (from binlog) for opened table */
+ /* Table id in the prelocking list used to check table uniqueness in a set */
+ uint cascade_conflict_check_id;
/*
select_result for derived table to pass it from table creation to table
filling procedure
@@ -1580,6 +1585,8 @@ struct TABLE_LIST
*/
char *get_table_name() { return view != NULL ? view_name.str : table_name; }
+ bool accept_visitor(THD *thd, Execution_tree_visitor *visitor);
+
private:
bool prep_check_option(THD *thd, uint8 check_opt_type);
bool prep_where(THD *thd, Item **conds, bool no_where_clause);
| Thread |
|---|
| • bzr commit into mysql-6.1-fk branch (kostja:2695) WL#148 | Konstantin Osipov | 21 Jan |
