#At file:///opt/local/work/mysql-6.1-fk-stage/ based on revid:dlenev@stripped
2706 Konstantin Osipov 2009-03-20
WL#148 Milestone 9, review comments.
modified:
sql/fk.cc
sql/sql_select.h
=== modified file 'sql/fk.cc'
--- a/sql/fk.cc 2009-02-24 17:14:08 +0000
+++ b/sql/fk.cc 2009-03-20 14:22:01 +0000
@@ -102,7 +102,9 @@ public:
bool prepare();
bool check_parent_exists();
bool do_eos_check();
-
+private:
+ void can_eos_check_help(bool *can_eos_check_in_child_help,
+ bool *can_eos_check_in_parent_help);
private:
THD *m_thd;
TABLE_LIST m_parent_table_list;
@@ -163,10 +165,13 @@ private:
Field **m_new_parent_columns_in_child;
/**
- If EOS checks are applicable for this foreign key points to an object
- representing buffer where we accumulate dangling foreign key values
- for re-checking them at the end-of-statement. NULL if EOS checks are
- not applicable.
+ If EOS checks are applicable, points to a buffer (a temporary table)
+ where we accumulate "dangling" foreign key values.
+ At the end of the statement all such values are re-checked.
+ NULL if EOS checks are not applicable: if it's a
+ non-transactional table, or when we know that a condition
+ that failed in row-by-row check won't become true at the end of
+ statement.
*/
Foreign_key_eos_buffer *m_eos_buffer;
};
@@ -220,6 +225,10 @@ public:
bool do_eos_checks_for_cascade();
private:
+ void can_eos_check_help(bool *can_eos_check_in_child_help,
+ bool *can_eos_check_in_parent_help);
+
+private:
THD *m_thd;
TABLE *m_parent_table;
TABLE_LIST m_child_table_list;
@@ -351,25 +360,25 @@ class Foreign_key_eos_buffer : public Sq
public:
Foreign_key_eos_buffer(THD *thd, Foreign_key_share *fk_share,
Field **src_columns, int error,
- bool do_eos_lookup_in_child,
+ bool can_eos_check_in_child_help,
TABLE *child_table, KEY *child_key,
- bool do_eos_lookup_in_parent,
+ bool can_eos_check_in_parent_help,
TABLE *parent_table, KEY *parent_key)
: m_thd(thd), m_fk_share(fk_share), m_src_columns(src_columns),
m_column_count(fk_share->column_count), m_error(error),
- m_eos_buffer(NULL),
- m_do_eos_lookup_in_child(do_eos_lookup_in_child),
+ m_eos_tmp_table(NULL),
+ m_can_eos_check_in_child_help(can_eos_check_in_child_help),
m_child_table(child_table), m_child_key(child_key),
m_child_key_idx(child_key - child_table->key_info),
- m_do_eos_lookup_in_parent(do_eos_lookup_in_parent),
+ m_can_eos_check_in_parent_help(can_eos_check_in_parent_help),
m_parent_table(parent_table), m_parent_key(parent_key),
m_parent_key_idx(parent_key - parent_table->key_info)
{ }
~Foreign_key_eos_buffer()
{
- if (m_eos_buffer)
- free_tmp_table(m_thd, m_eos_buffer);
+ if (m_eos_tmp_table)
+ free_tmp_table(m_thd, m_eos_tmp_table);
}
bool add_fk_value();
@@ -387,8 +396,8 @@ private:
uint m_column_count;
/**
- Error code for error to be reported when foreign key violation
- is discovered during EOS check.
+ Error code for the error to be reported when foreign key
+ violation is discovered during EOS check.
*/
int m_error;
@@ -397,18 +406,18 @@ private:
foreign key values are accumulated and TMP_TABLE_PARAM which is
used for its creation.
Note that we delay creation of this table until the first
- insertion into it. NULL value is used as indicator that such
- insertion has not yet occured.
+ insertion into it. NULL value is used as an indicator that
+ such insertion has not yet occurred.
*/
- TABLE *m_eos_buffer;
- TMP_TABLE_PARAM m_eos_buffer_param;
+ TABLE *m_eos_tmp_table;
+ TMP_TABLE_PARAM m_eos_tmp_table_param;
/**
Indicates if during end-of-statement foreign key validation
we should perform lookup in the child table (to find out if
offending FK value were removed or changed).
*/
- bool m_do_eos_lookup_in_child;
+ bool m_can_eos_check_in_child_help;
/**
Auxiliary structures for lookups in the child table which has
to occur during end-of-statement foreign key validation.
@@ -424,7 +433,7 @@ private:
we should perform lookup in the parent table (to find out if
matching PK value for an orphan FK value was added).
*/
- bool m_do_eos_lookup_in_parent;
+ bool m_can_eos_check_in_parent_help;
/**
Auxiliary structures for lookups in the parent table which has
to occur during end-of-statement foreign key validation.
@@ -860,6 +869,198 @@ prepare_key_copy(THD *thd, KEY *key,
}
+/**
+ Find out if the end of statement (EOS) foreign key check can
+ be used to clear some false negatives of the row-by-row
+ checking.
+
+ TODO: Add description of multi-table operations (DELETE, UPDATE).
+
+ Generally, conditions which describe when it _makes sense_
+ to do EOS checking in i.e. when database integrity can improve
+ by the end of the statement, can be formulated as:
+
+ "The parent table can change after the row check, but before the
+ end of statement, or the offending (orphaned) row in the child
+ table may be modified before the end of statement".
+
+ To rephrase: the problem of foreign key constraint integrity is
+ the problem of a dangling reference.
+
+ The problem is always the same, but its complexity is in the
+ scenarios in which a dangling reference can appear in the row-by-row
+ check *and* consequently disappear by the end of the statement.
+
+ So, a EOS buffer is used when we for some reason believe that the
+ orphan can go away by the end of the statement. It can go away
+ in two ways: either because of a change in the parent table
+ (appearance of the parent value), or a change in the child table
+ (disappearance of the offending child value). These two cases
+ are reflected in two output values of this function.
+
+ Practically, in the following situations EOS checks make sense
+ in Foreign_key_child_rcontext:
+
+ 1) A multi-value INSERT into a table and the constraint
+ that failed is referencing the same table.
+ 2) A multi-value REPLACE or INSERT ... ON DUPLICATE KEY UPDATE
+ 3) A multi-value UPDATE into a table, again with
+ a self-referencing constraint
+ 4) UPDATE that has cascading actions that may affect the
+ parent
+ 5) When we're inside a cascading action. A new parent key may
+ be added by the main statement: e.g. as a result of the
+ update or of some other, perhaps indirect, cascading action
+ of the main statement.
+ 6) There are AFTER triggers. Currently triggers can not
+ change tables that are used in foreign key checks.
+ On such an attempt, we produce
+ ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG.
+ But unless we turn on EOS checks when there are AFTER
+ triggers, it would look to the user that FK checks are done
+ before AFTER triggers are fired, whereas our spec prescribes
+ otherwise (in other words, we'll produce a wrong error
+ message, see Bug#43520).
+
+ The code below is not trying to find out exactly what
+ case from the above list we're dealing with this time:
+ we apply several heuristics that help us optimize
+ for the most common cases. After all, use of EOS buffer
+ can never yield a false positive.
+
+ Please see comments in the code for additional details.
+*/
+
+void
+Foreign_key_child_rcontext::
+can_eos_check_help(bool *can_eos_check_in_child_help,
+ bool *can_eos_check_in_parent_help)
+{
+ *can_eos_check_in_child_help= *can_eos_check_in_parent_help= FALSE;
+
+ if (m_fk_list->action_type() == TRG_EVENT_INSERT)
+ {
+ /*
+ 1) It is an ordinary INSERT into the child table.
+ Don't set can_eos_check_in_child_help: if there
+ is an offending (orphaned) child value found in row-by-row
+ check, it won't get removed by the end of the statement,
+ since this statement doesn't modify old rows, only adds
+ new ones.
+
+ Set can_eos_check_in_parent_help when there is a self-reference:
+ a parent missing in row-by-row check may be added as part of
+ some other record in a multi-value insert.
+
+ @todo We're not checking here for multi-value insert
+ specifically, it is a possible future optimisation.
+
+ Example:
+
+ CREATE TABLE t1 (a int primary key, b int references t1 (a));
+ INSERT INTO t1 (a,b) VALUES (2, 1), (1, NULL);
+ */
+ if (m_child_table->s == m_parent_table->s)
+ *can_eos_check_in_parent_help= TRUE;
+ }
+ else if ((m_fk_list->action_type() & TRG_EVENT_INSERT) &&
+ (m_fk_list->action_type() & (TRG_EVENT_UPDATE | TRG_EVENT_DELETE)))
+ {
+ /*
+ 2) It is a REPLACE or INSERT ... ON DUPLICATE KEY UPDATE.
+ Set can_eos_check_in_child_help: offending (orphaned) child
+ value can be updated by a subsequent row in a multi-value
+ statement.
+ Example:
+ CREATE TABLE t1 (a int unique not null, b int references t1 (a));
+ REPLACE t1 VALUES (2, 1), (2, NULL);
+
+ The child value can not be updated by a cascading action,
+ since the SQL standard prohibits foreign keys to update the
+ same site twice, and thus we prohibit foreign key
+ definitions that can lead to it at DDL time.
+
+ Set can_eos_check_in_parent_help if it is a self-reference
+ or if we can update the parent table through some cascading
+ action (executed on a different row and/or constraint).
+ Check for the latter case is simplified.
+
+ Example:
+ CREATE TABLE t1 (a INT PRIMARY KEY DEFAULT 2);
+ INSERT INTO t1 (a) VALUES (1);
+ CREATE TABLE t2 (a INT PRIMARY KEY, b INT REFERENCES t1 (a));
+ INSERT INTO t2 (a, b) VALUES (1, 1);
+ ALTER TABLE t1 ADD FOREIGN KEY a REFERENCES t2 (a) ON DELETE SET DEFAULT;
+ REPLACE t2 (2, 2), (1, 2);
+ */
+ *can_eos_check_in_child_help= TRUE;
+ if (m_child_table->s == m_parent_table->s ||
+ m_child_table->s->fkeys.has_cascading_actions(m_fk_list->action_type()))
+ *can_eos_check_in_parent_help= TRUE;
+ }
+ else if (m_fk_list->action_type() == TRG_EVENT_UPDATE)
+ {
+ Fk_constraint_list *parent_fk_list= m_fk_list->get_parent_fk_list();
+ bool is_inside_cascade= test(parent_fk_list);
+
+ if (! is_inside_cascade)
+ {
+ /*
+ 3) It is an ordinary UPDATE or MULTI-UPDATE.
+ An offending (orphaned) child value can not be removed
+ since UPDATE can not modify the same site twice (only INSERT ..
+ ON DUPLICATE KEY UPDATE can; REPLACE can delete its own
+ row and insert a new one in multi-value list instead).
+ Cascading actions also can not change the same site.
+ No need to set can_eos_check_in_child_help.
+
+ A missing parent value can be added by this statement
+ if there is a self-referencing constraint, or as a result
+ of a cascading action triggered by this update
+ Set can_eos_check_in_parent_help in these cases (we use
+ a simplified check for the cascading update).
+ An example for the effect of a cascading action would be
+ similar to the one above with REPLACE.
+ */
+ if (m_child_table->s == m_parent_table->s ||
+ m_child_table->s->fkeys.has_cascading_actions(TRG_EVENT_UPDATE) ||
+ FALSE /* TODO: add condition for MULTI-UPDATE*/)
+ *can_eos_check_in_parent_help= TRUE;
+ }
+ else
+ {
+ /*
+ 4) Finally if it is a cascading change (update).
+ An offending (orphaned) child value can't be changed or
+ removed (updating same site twice is prohibited ).
+
+ A new parent value can be added by the UPDATE which caused
+ this cascading change (note that since we don't allow
+ overlapping foreign keys with cascading actions, we always
+ here perform a check for the parent key of the same
+ foreign key constraint). Other scenarios are impossible at
+ the moment.
+
+ Example (main UPDATE adds a missing parent after row-by-row check):
+ CREATE table t1 (a INT PRIMARY KEY);
+ CREATE table t2 (a INT DEFAULT 5 REFERENCES t1 (a) ON UPDATE
+ SET DEFAULT);
+ INSERT INTO t1 VALUES (1), (2);
+ INSERT INTO t2 VALUES (1);
+ UPDATE t1 SET a=a+3;
+ */
+ if (parent_fk_list->action_type() & TRG_EVENT_UPDATE)
+ *can_eos_check_in_parent_help= TRUE;
+ }
+ }
+ else if (m_fk_list->action_type() == TRG_EVENT_DELETE)
+ {
+ /*
+ If we're simply deleting a child, nothing nothing needs to
+ be done.
+ */
+ }
+}
/**
@@ -921,7 +1122,7 @@ bool Foreign_key_child_rcontext::prepare
/************* Step 2: find a supporting key. ***********/
- /* Find a PRIMARY/UNIQUE index which we will used for lookups in parent. */
+ /* Find a PRIMARY/UNIQUE index which will be used for lookups in parent. */
m_key= find_key_by_name(m_parent_table, m_fk_share->parent_constraint_name);
if (m_key == NULL || m_key->key_parts != m_fk_share->column_count)
@@ -958,8 +1159,8 @@ bool Foreign_key_child_rcontext::prepare
/*
In case of UPDATE we also have to ensure that all child columns
are read so we have enough information for checking of MATCH rule
- and constructing key for lookup in the parent table. There is no
- need. There is no need for this in case of INSERT as it always
+ and constructing key for lookup in the parent table.
+ There is no need for this in case of INSERT as it always
provides values for all columns.
*/
bitmap_union(m_child_table->read_set, &m_fk_share->column_set);
@@ -973,98 +1174,21 @@ bool Foreign_key_child_rcontext::prepare
/*** Step 4: prepare end-of-statement buffer and lookup structures. ***/
+ /*
+ EOS checks can only be used for transactional tables. In case of
+ non-transactional tables we can't roll back the statement, and
+ thus, if EOS check fails, can't remove inconsistent data from
+ the table.
+ */
if (m_child_table->file->has_transactions())
{
- bool do_eos_lookup_in_child= FALSE;
- bool do_eos_lookup_in_parent= FALSE;
- /*
- Performing data-change operation on transactional table is
- a necessary prerequisite for doing end-of-statement checks.
-
- QQ/TODO: Think about case of multi-table operations.
-
- Conditions which describe when it _makes sense_ to do such
- checking for Foreign_key_child_rcontext, i.e. when result
- with EOS checks will be any better than without them, can
- be formulated as: "It makes sense to perform EOS checks when
- there are chances that parent table will be modified before
- EOS or offending row in child table will be modified before
- EOS (e.g. due to REPLACE/INSERT ... ON DUPLICATE KEY UPDATE
- or cascading delete).
-
- Here are all the cases in which Foreign_key_child_rcontext
- is used:
+ bool can_eos_check_in_child_help;
+ bool can_eos_check_in_parent_help;
- QQ/TODO: Take into account effect of triggers...
- */
- if (m_fk_list->action_type() == TRG_EVENT_INSERT)
- {
- /*
- 1) It is an ordinary INSERT into the child table.
- Offending FK can't be removed or changed by this statement.
- Matching PK can be added by this statement only if it is
- a self-referencing constraint. Note that triggers currently
- are not allowed to update child or parent table.
- */
- if (m_child_table->s == m_parent_table->s)
- do_eos_lookup_in_parent= TRUE;
- }
- if ((m_fk_list->action_type() & TRG_EVENT_INSERT) &&
- (m_fk_list->action_type() & (TRG_EVENT_UPDATE | TRG_EVENT_DELETE)))
- {
- /*
- 2) It is a REPLACE or INSERT ON DUPLICATE KEY UPDATE.
- Offending FK can be removed or changed by upcoming rows.
- Matching PK can be added by this statement if it is a
- self-referencing constraint or as a result of cascading
- action triggered by its deletes/updates (check for this
- is simplified). Same note about triggers apply.
- */
- do_eos_lookup_in_child= TRUE;
- if (m_child_table->s == m_parent_table->s ||
- m_parent_table->s->fkeys.has_cascading_actions(m_fk_list->action_type()))
- do_eos_lookup_in_parent= TRUE;
- }
- if (m_fk_list->action_type() == TRG_EVENT_UPDATE)
- {
- Fk_constraint_list *parent_fk_list= m_fk_list->get_parent_fk_list();
-
- if (! parent_fk_list)
- {
- /*
- 3) It is an ordinary UPDATE or MULTI-UPDATE.
- Offending FK can't be removed or changed since in general case
- it is illegal to update the same site twice and because of this
- we prohibit cascade recursing into table being UPDATED.
- Matching PK can be added by this statement if it is a self-
- referencing constraint, as a result of cascading action triggered
- by this update (we use simplified check for this) or if it is a
- MULTI-UPDATE which directly or indirectly affects parent table.
- Same note about triggers apply.
- */
- if (m_child_table->s == m_parent_table->s ||
- m_parent_table->s->fkeys.has_cascading_actions(TRG_EVENT_UPDATE) ||
- FALSE /* TODO: add condition for MULTI-UPDATE*/)
- do_eos_lookup_in_parent= TRUE;
- }
- else
- {
- /*
- 4) Finally if it is a cascading change (update).
- Offending FK can't be changed or removed (updating same site
- twice is prohibited in this case).
- Matching PK can be added by UPDATE which caused this cascading
- change (note that since we don't allow overlapping foreign keys
- with cascading actions we always here perform check for the PK
- of the same foreing key constraint). Other scenarios are
- impossible at the moment. Same note about triggers apply.
- */
- if (parent_fk_list->action_type() & TRG_EVENT_UPDATE)
- do_eos_lookup_in_parent= TRUE;
- }
- }
+ can_eos_check_help(&can_eos_check_in_child_help,
+ &can_eos_check_in_parent_help);
- if (do_eos_lookup_in_child || do_eos_lookup_in_parent)
+ if (can_eos_check_in_child_help || can_eos_check_in_parent_help)
{
KEY *child_key;
@@ -1078,9 +1202,9 @@ bool Foreign_key_child_rcontext::prepare
if (!(m_eos_buffer= new (m_thd->mem_root) Foreign_key_eos_buffer(m_thd,
m_fk_share, m_child_columns,
ER_FK_CHILD_NO_MATCH,
- do_eos_lookup_in_child,
+ can_eos_check_in_child_help,
m_child_table, child_key,
- do_eos_lookup_in_parent,
+ can_eos_check_in_parent_help,
m_parent_table, m_key)))
return TRUE;
}
@@ -1126,7 +1250,7 @@ bool Foreign_key_child_rcontext::prepare
{
/*
Fill the list of Fields which correspond to new values of PK columns
- in child TABLE instance (i.e. the isntance used for INSERT/UPDATE
+ in child TABLE instance (i.e. the instance used for INSERT/UPDATE
operation).
*/
if (! (m_new_parent_columns_in_child= (Field **)
@@ -1158,7 +1282,7 @@ Foreign_key_child_rcontext::~Foreign_key
/**
Compare two sets of columns: one represented by a key, another by a
- corresponding list of fields objects.
+ corresponding list of Field objects.
Doesn't take into account "insignificant" differences. NULLs have
to match exactly -- a NULL cmp not NULL yields FALSE.
This is exactly how you look up a child for a parent and vice versa,
@@ -1337,7 +1461,7 @@ bool Foreign_key_child_rcontext::check_p
{
if (col->is_null())
{
- /* MATCH SIMPLE: at least one NULL, so non need to look for parent. */
+ /* MATCH SIMPLE: at least one NULL, so no need to look for parent. */
return FALSE;
}
}
@@ -1469,13 +1593,16 @@ bool Foreign_key_child_rcontext::check_p
/**
- Check if at the end of statement values of the foreign key which
- were added to the EOS buffer as dangling still are dangling and
- report appropriate error.
+ Re-verify dangling foreign key values which
+ were added to the EOS buffer during the row-by-row checking,
+ and if they are still dangling, report an appropriate error.
+
+ This function is called at the end of the statement (end of the
+ main sweep of DML).
@retval FALSE Success. There are no dangling references.
- @retval TRUE Failure. Some dangling references still present,
- error was reported.
+ @retval TRUE Failure. Some dangling references are still present,
+ an error was reported.
*/
bool Foreign_key_child_rcontext::do_eos_check()
@@ -1520,7 +1647,7 @@ static KEY* find_supporting_key(TABLE *t
{
/*
This index contains key part which covers only prefix of
- column, therefore it cannot serve as supporting index.
+ column, therefore it cannot serve as a supporting index.
Fast forward to the next index.
*/
key_part= key_part_end;
@@ -1541,6 +1668,219 @@ static KEY* find_supporting_key(TABLE *t
/**
+ Find out if the end of statement (EOS) foreign key check can
+ be used to clear some false negatives of the row-by-row
+ checking.
+
+ @sa The comment for
+ Foreign_key_child_rcontext::can_eos_check_help().
+
+ Generally, scenarios in which the EOS check is applicable
+ are always the same: in parent rcontext we just view them
+ from a different point -- when we add, remove or change
+ a parent value, rather than the child value.
+
+ Practical cases that are unique to Foreign_key_parent_rcontext
+ (i.e. they are not already covered in the comment for
+ child_rcontext):
+
+ 1) A DELETE that removes many rows, and the constraint is
+ referencing the same table (is self-referencing).
+ The orphan can be deleted later in the course of statement
+ execution.
+ 2) DELETE with a cascade.
+ 3) Multi-DELETE.
+ 4) A cascading action of DELETE (can be TRG_EVENT_UPDATE or
+ TRG_EVENT_DELETE).
+ 5) Update of the parent table and a self-reference. An update
+ with cascading actions.
+
+ Please see comments in the code for additional details.
+*/
+
+void
+Foreign_key_parent_rcontext::
+can_eos_check_help(bool *can_eos_check_in_child_help,
+ bool *can_eos_check_in_parent_help)
+{
+ *can_eos_check_in_child_help= *can_eos_check_in_parent_help= FALSE;
+
+ /*
+ For changes of the parent, EOS checks should be done
+ only if referential action is NO ACTION.
+ If referential action is RESTRICT, there must be no
+ orphans already at the time of row-by-row checking.
+
+ This semantics is standard.
+ */
+ if (m_ref_action != FK_OPTION_NO_ACTION)
+ return;
+
+ if (m_fk_list->action_type() == TRG_EVENT_DELETE)
+ {
+ Fk_constraint_list *parent_fk_list= m_fk_list->get_parent_fk_list();
+ bool is_inside_cascade= test(parent_fk_list);
+
+ if (! is_inside_cascade)
+ {
+ /*
+ 1) It is an ordinary DELETE.
+ An offending (orphaned) child value might be deleted later
+ in the course of this statement if it resides in the same table
+ (it is a self-referencing constraint).
+ If the subject table (here - the parent table) has
+ cascading actions, they can also delete or update the
+ orphan.
+ Set can_eos_check_in_child_help in these cases.
+ Example:
+ CREATE TABLE t1 (a int unique not null, b int references t1 (a));
+ INSERT INTO t1 (a, b) VALUES (1, NULL), (2, 1), (3,2);
+ DELETE FROM t1;
+
+ With effect of cascade:
+ CREATE TABLE t1 (a int primary key, b int unique not null);
+ INSERT INTO t1 (a, b) VALUES (1, 1), (2,2);
+ CREATE TABLE t2 (a int references t1 (a), b int references t1 (b)
+ on delete cascade);
+ INSERT INTO t2 (a,b) VALUES (1,2);
+ DELETE FROM t1;
+
+ There is no way a matching parent value can be added:
+ had it been possible, it would've meant that the same
+ site can be updated twice, and this is prohibited
+ at DDL time. No need to set can_eos_check_in_parent_help.
+
+ The case of multi-DELETE includes all that applies to a
+ normal DELETE, plus it can also result in deletion of the
+ orphan. @todo: update this section when Milestone 12 is
+ done.
+ */
+ if (m_child_table->s == m_parent_table->s ||
+ m_parent_table->s->fkeys.has_cascading_actions(TRG_EVENT_DELETE))
+ *can_eos_check_in_child_help= TRUE;
+ }
+ else
+ {
+ /*
+ 2) This is a cascading delete action. Here it's just
+ a different view of the scenario when the subject table
+ has cascading actions (case 1)) -- we're inside one of
+ them. We turn can_eos_check_in_child_help since cascading
+ actions (of the main statement) may delete the offending
+ child value. We don't attempt to make it more
+ fine-grained.
+
+ There are other reasons why we may want to turn
+ can_eos_check_in_child_help: if the orphaned child
+ resides in the same table (a self-referencing constraint),
+ if this table has cascading actions for DELETE event,
+ so that they may delete the child value, etc., but the
+ previous reason is already sufficient.
+
+ A missing parent value can not reappear, since that
+ would mean there is an overlapping constraint, and
+ we prohibit these at DDL time.
+
+ If the main statement is a multi-DELETE, all
+ that's applicable to a normal DELETE applies to it.
+ In addition, the orphaned child value may also
+ disappear due to the actions of the main statement.
+ @todo: update this section when Milestone 12 is done.
+ */
+ *can_eos_check_in_child_help= TRUE;
+ }
+ }
+ else if (m_fk_list->action_type() == TRG_EVENT_UPDATE)
+ {
+ Fk_constraint_list *parent_fk_list= m_fk_list->get_parent_fk_list();
+ bool is_inside_cascade= test(parent_fk_list);
+
+ if (! is_inside_cascade)
+ {
+ /*
+ 3) It is an ordinary UPDATE.
+ An offending (orphaned) child value can be removed or
+ changed by this statement if it is a self-referencing
+ foreign key. Set can_eos_check_in_child_help in this
+ case.
+
+
+ A missing parent value may reappear as a result of
+ update of a subsequent row. Set it in all cases.
+
+ Example:
+ CREATE TABLE t1 (a int primary key);
+ INSERT INTO t1 (a) VALUES (1), (2);
+ CREATE TABLE t1 (a int references t1 (a));
+ INSERT INTO t2 (a) VALUES (1);
+ UPDATE t1 SET a= case a when 1 then 3 when 2 then 1 end;
+
+ Cascading actions have no impact: cascading actions
+ of this constraint are run before the row-by-row check,
+ and there can be no other constraint that changes this
+ site, overlapping constraints are prohibited at DDL time.
+ */
+ if (m_child_table->s == m_parent_table->s)
+ *can_eos_check_in_child_help= TRUE;
+ *can_eos_check_in_parent_help= TRUE;
+ }
+ else
+ {
+ /*
+ 4) We're inside a cascading update action.
+
+ Since a cascading update may be caused by ON DELETE SET
+ DEFAULT, and this (main) DELETE can have other cascading
+ actions, in a way it's a different view of case 1).
+ Therefore, the same reasoning as in cases 1) and 2)
+ applies: can_eos_check_in_child_help should be set.
+
+ If that reason is not enough, an offending (orphaned)
+ child value can be changed by other cascading actions
+ caused by the main statement, even if it's an UPDATE (e.g.
+ in case of diamond-shaped relations).
+
+ A missing parent value can be added by further actions
+ of the main statement (and its cascading updates).
+ For example:
+ CREATE TABLE t1 (a INT PRIMARY KEY);
+ CREATE TABLE t2 (a INT PRIMARY KEY REFERENCES t1(a) ON UPDATE CASCADE);
+ CREATE TABLE t3 (b INT REFERENCES t2(a));
+ INSERT INTO t1 VALUES (1), (2);
+ INSERT INTO t2 VALUES (1), (2);
+ INSERT INTO t3 VALUES (1);
+ UPDATE t1 SET a= CASE a WHEN 1 THEN 4 WHEN 2 THEN 1 END;
+
+ If it is a multi-UPDATE, we have all the same reasons to
+ set both variables, and more.
+ @todo Update this section when Milestone 12 is complete.
+ */
+ *can_eos_check_in_child_help= TRUE;
+ *can_eos_check_in_parent_help= TRUE;
+ }
+ }
+ else if ((m_fk_list->action_type() & TRG_EVENT_INSERT) &&
+ (m_fk_list->action_type() & (TRG_EVENT_UPDATE | TRG_EVENT_DELETE)))
+ {
+ /*
+ 5) It is a REPLACE or INSERT ON DUPLICATE KEY UPDATE.
+ An offending (orphaned) child value can be removed or
+ changed by this statement if this is a self-referencing
+ foreign key or due to cascading delete caused by this
+ statement.
+
+ A missing parent value may be added by further inserts or
+ updates.
+ */
+ if (m_child_table->s == m_parent_table->s ||
+ m_parent_table->s->fkeys.has_cascading_actions(m_fk_list->action_type()))
+ *can_eos_check_in_child_help= TRUE;
+ *can_eos_check_in_parent_help= TRUE;
+ }
+}
+
+
+/**
Prepare runtime context for checks and cascading actions performed
for the foreign key when the statement modifies the parent table.
@@ -1557,6 +1897,7 @@ bool Foreign_key_parent_rcontext::prepar
Query_tables_list query_tables_list_backup;
LEX *lex= m_thd->lex;
Reprepare_observer *save_reprepare_observer= m_thd->m_reprepare_observer;
+ /************* Step 1: get hold of the child table. ***********/
/*
Find a pre-opened and pre-locked instance of child table which is
going to be used for checks and cascading actions.
@@ -1619,6 +1960,7 @@ bool Foreign_key_parent_rcontext::prepar
m_child_table= m_child_table_list.table;
+ /************* Step 2: find a supporting key. ***********/
/* Find index which supports this foreign key. */
if (!(m_key= find_supporting_key(m_child_table, m_fk_share->child_columns,
m_column_count)))
@@ -1632,6 +1974,7 @@ bool Foreign_key_parent_rcontext::prepar
}
m_key_idx= m_key - m_child_table->key_info;
+ /************ Step 3: prepare lookup buffers and structures. **********/
if (! (m_parent_columns_old= (Field **)
alloc_root(m_thd->mem_root, sizeof(Field*) * (m_column_count+1))))
return TRUE;
@@ -1670,125 +2013,28 @@ bool Foreign_key_parent_rcontext::prepar
&m_key_buff, &m_key_copy))
return TRUE;
- if (m_parent_table->file->has_transactions() &&
- m_ref_action == FK_OPTION_NO_ACTION)
+ /************** Step 4: do we need to do a EOS check. ***************/
+ /*
+ EOS checks can only be used for transactional tables. In case of
+ non-transactional tables we can't roll back the statement, and
+ thus, if EOS check fails, can't remove inconsistent data from
+ the table.
+ */
+ if (m_child_table->file->has_transactions())
{
- bool do_eos_lookup_in_child= FALSE;
- bool do_eos_lookup_in_parent= FALSE;
- /*
- Performing data-change operation on transactional table is
- a necessary prerequisite for doing end-of-statement checks.
+ bool can_eos_check_in_child_help;
+ bool can_eos_check_in_parent_help;
- QQ/TODO: Think about case of multi-table operations.
-
- Also standard says that EOS checks should not be done after
- updating parent table if referential action is RESTRICT.
-
- Conditions which describe when it _makes sense_ to do such
- checking for Foreign_key_parent_rcontext, i.e. when result
- with EOS checks will be any better than without them, can
- be formulated as: "It makes sense to perform EOS checks when
- it is possible that child table will be modified before EOS
- in such wayt that offending foreign key value will be replaced
- or removed or that parent table will be modifed before EOS and
- matching parent key value will be added to it".
-
- Here are all the cases in which Foreign_key_parent_rcontext
- is used:
-
- QQ/TODO: Take into account effect of triggers...
- */
- if (m_fk_list->action_type() == TRG_EVENT_DELETE)
- {
- Fk_constraint_list *parent_fk_list= m_fk_list->get_parent_fk_list();
+ can_eos_check_help(&can_eos_check_in_child_help,
+ &can_eos_check_in_parent_help);
- if (! parent_fk_list)
- {
- /*
- 1) It is an ordinary DELETE or MULTI-DELETE.
- Offending FK value might be removed or changed by this statement
- if it is a self-referencing foreign key, by cascading deletes
- (check for this is simplified) or if it is a multi-delete.
- Matching PK values can't be added.
- */
- if (m_child_table->s == m_parent_table->s ||
- m_parent_table->s->fkeys.has_cascading_actions(TRG_EVENT_DELETE) ||
- FALSE /* TODO: add condition for MULTI-DELETE */)
- do_eos_lookup_in_child= TRUE;
- }
- else
- {
- /*
- 2) This is a cascading delete action.
- Offending FK value might be removed if it is a self-referencing
- foreign key, by cascading triggered by this delete or by other
- cascading actions caused by this statement, or if it is a
- multi-delete.
- Since checking if there are other cascading actions caused by
- this statement is non-trivial we simply resort to always
- performing EOS lookup in the child in this case.
- Matching PK values can't be added (addition of PK by cascading
- action caused by this delete or other cascading actions caused
- by this statement would mean that there is overlapping FKs).
- */
- do_eos_lookup_in_child= TRUE;
- }
- }
- if (m_fk_list->action_type() == TRG_EVENT_UPDATE)
- {
- Fk_constraint_list *parent_fk_list= m_fk_list->get_parent_fk_list();
- if (! parent_fk_list)
- {
- /*
- 3) It is an ordinary UPDATE or MULTI-UPDATE.
- Offending FK value can be removed or changed by this statement if
- it is a self-referencing foreign key or if it is a MULTI-UPDATE.
- Matching PK value can be added by further updates.
- */
- if (m_child_table->s == m_parent_table->s ||
- FALSE /* TODO: add condition for MULTI-UPDATE */)
- do_eos_lookup_in_child= TRUE;
- do_eos_lookup_in_parent= TRUE;
- }
- else
- {
- /*
- 4) This is a cascading update action.
- Offending FK value can be removed or changed by other cascading
- actions caused by this statement (think of diamond-shaped
- relations) or if it is a MULTI-UPDATE (all other cases would
- require overlapping foreign keys).
- Since checking if there are other cascading actions caused by
- this statement is non-trivial we simply resort to always
- performing EOS lookup in the child in this case.
- Matching PK value can be added by further cascading updates.
- */
- do_eos_lookup_in_child= TRUE;
- do_eos_lookup_in_parent= TRUE;
- }
- }
- if ((m_fk_list->action_type() & TRG_EVENT_INSERT) &&
- (m_fk_list->action_type() & (TRG_EVENT_UPDATE | TRG_EVENT_DELETE)))
- {
- /*
- 5) It is a REPLACE or INSERT ON DUPLICATE KEY UPDATE.
- Offending FK value can be removed or changed by this statement
- if this is a self-referencing foreign key or due to cascading
- delete caused by this statement.
- Matching PK can be added by further inserts or updates.
- */
- if (m_child_table->s == m_parent_table->s ||
- m_parent_table->s->fkeys.has_cascading_actions(m_fk_list->action_type()))
- do_eos_lookup_in_child= TRUE;
- do_eos_lookup_in_parent= TRUE;
- }
- if (do_eos_lookup_in_child || do_eos_lookup_in_parent)
+ if (can_eos_check_in_child_help || can_eos_check_in_parent_help)
{
KEY *parent_key;
/*
QQ: Alternatively we can also store name of parent key in .FRM of
- parent table and use find_key_by_name here... ?
+ parent table and use find_key_by_name here... ?
*/
if (!(parent_key= find_supporting_key(m_parent_table,
m_fk_share->parent_columns,
@@ -1799,17 +2045,19 @@ bool Foreign_key_parent_rcontext::prepar
}
if (!(m_eos_buffer= new (m_thd->mem_root)
- Foreign_key_eos_buffer(m_thd,
- m_fk_share, m_parent_columns_old,
- ER_FK_CHILD_VALUE_EXISTS,
- do_eos_lookup_in_child,
- m_child_table, m_key,
- do_eos_lookup_in_parent,
- m_parent_table, parent_key)))
+ Foreign_key_eos_buffer(m_thd,
+ m_fk_share, m_parent_columns_old,
+ ER_FK_CHILD_VALUE_EXISTS,
+ can_eos_check_in_child_help,
+ m_child_table, m_key,
+ can_eos_check_in_parent_help,
+ m_parent_table, parent_key)))
return TRUE;
}
}
+ /************* Step 5: Do we need to check record in memory. *********/
+
if (m_parent_table->s == m_child_table->s &&
! m_parent_table->file->has_transactions())
{
@@ -1975,7 +2223,7 @@ bool Foreign_key_parent_rcontext::prepar
if (m_fk_list->action_type() == TRG_EVENT_UPDATE &&
- m_ref_action == FK_OPTION_CASCADE || m_ref_action == FK_OPTION_DEFAULT)
+ (m_ref_action == FK_OPTION_CASCADE || m_ref_action == FK_OPTION_DEFAULT))
{
/*
Create auxiliary objects which will be used for constructing new values
@@ -2442,8 +2690,8 @@ bool Foreign_key_eos_buffer::prepare()
uint idx;
ulong old_sql_mode;
- m_eos_buffer_param.init();
- m_eos_buffer_param.field_count= m_column_count;
+ m_eos_tmp_table_param.init();
+ m_eos_tmp_table_param.field_count= m_column_count;
for (idx= 0; idx < m_column_count; ++idx)
{
@@ -2461,27 +2709,27 @@ bool Foreign_key_eos_buffer::prepare()
old_sql_mode= m_thd->variables.sql_mode;
m_thd->variables.sql_mode= get_store_sql_mode(old_sql_mode);
- m_eos_buffer= create_tmp_table(m_thd, &m_eos_buffer_param,
- eos_buffer_fields, (ORDER*)0, 0, 0,
- TMP_TABLE_ALL_COLUMNS, HA_POS_ERROR,
- (char *) "");
+ m_eos_tmp_table= create_tmp_table(m_thd, &m_eos_tmp_table_param,
+ eos_buffer_fields, (ORDER*)0, 0, 0,
+ TMP_TABLE_ALL_COLUMNS, HA_POS_ERROR,
+ (char *) "");
m_thd->variables.sql_mode= old_sql_mode;
- if (! m_eos_buffer)
+ if (! m_eos_tmp_table)
return TRUE;
if (prepare_key_copy(m_thd, m_child_key,
- m_eos_buffer->field, m_fk_share->child_columns,
+ m_eos_tmp_table->field, m_fk_share->child_columns,
m_column_count, &m_child_key_buff, &m_child_key_copy))
return TRUE;
if (prepare_key_copy(m_thd, m_parent_key,
- m_eos_buffer->field, m_fk_share->parent_columns,
+ m_eos_tmp_table->field, m_fk_share->parent_columns,
m_column_count, &m_parent_key_buff, &m_parent_key_copy))
return TRUE;
- m_eos_buffer->file->extra(HA_EXTRA_WRITE_CACHE);
+ m_eos_tmp_table->file->extra(HA_EXTRA_WRITE_CACHE);
return FALSE;
}
@@ -2507,10 +2755,10 @@ bool Foreign_key_eos_buffer::add_fk_valu
We also create associated structures which are used for validation at
the end of statement.
*/
- if (! m_eos_buffer && prepare())
+ if (! m_eos_tmp_table && prepare())
return TRUE;
- DBUG_ASSERT(m_eos_buffer);
+ DBUG_ASSERT(m_eos_tmp_table);
/*
Ensure that values are not changed in the process copying due to
@@ -2523,22 +2771,22 @@ bool Foreign_key_eos_buffer::add_fk_valu
old_sql_mode= m_thd->variables.sql_mode;
m_thd->variables.sql_mode= get_store_sql_mode(old_sql_mode);
- for (copy= m_eos_buffer_param.copy_field;
- copy < m_eos_buffer_param.copy_field_end;
+ for (copy= m_eos_tmp_table_param.copy_field;
+ copy < m_eos_tmp_table_param.copy_field_end;
copy++)
(*copy->do_copy)(copy);
m_thd->variables.sql_mode= old_sql_mode;
- if ((error= m_eos_buffer->file->ha_write_row(m_eos_buffer->record[0])))
+ if ((error= m_eos_tmp_table->file->ha_write_row(m_eos_tmp_table->record[0])))
{
/*
If it is a "table is full" error convert in-memory temporary
table to on-disk temporary table. Report an error otherwise.
*/
- if (create_internal_tmp_table_from_heap(m_thd, m_eos_buffer,
- m_eos_buffer_param.start_recinfo,
- &m_eos_buffer_param.recinfo,
+ if (create_internal_tmp_table_from_heap(m_thd, m_eos_tmp_table,
+ m_eos_tmp_table_param.start_recinfo,
+ &m_eos_tmp_table_param.recinfo,
error, 0))
return TRUE;
}
@@ -2561,7 +2809,7 @@ bool Foreign_key_eos_buffer::check_fk_va
{
enum_fk_lookup_result result;
- if (m_do_eos_lookup_in_parent)
+ if (m_can_eos_check_in_parent_help)
{
/*
When current statement (also) inserts or updates rows in the parent
@@ -2591,11 +2839,11 @@ bool Foreign_key_eos_buffer::check_fk_va
}
}
- if (m_do_eos_lookup_in_child)
+ if (m_can_eos_check_in_child_help)
{
/*
When there is a chance that current statement will update or
- delete rows with dangling references it makes sense to check
+ delete rows with dangling references it makes sense to check
if these offending foreign keys values are still present in the
child table by the EOS. Again see prepare() method of
Foreign_key_parent/child_rcontext classes for details.
@@ -2611,7 +2859,7 @@ bool Foreign_key_eos_buffer::check_fk_va
/*
Offending foreign key value is still there.
Fallthrough to error reporting.
- */
+ */
break;
default:
/* Error from engine was already reported by fk_lookup(). */
@@ -2623,8 +2871,8 @@ bool Foreign_key_eos_buffer::check_fk_va
We have a row in child table which references to non-existing value
of parent key. Report an appropriate foreign key violation error.
*/
- fk_report_error(m_error, m_fk_share->name.str, m_eos_buffer,
- m_eos_buffer->field);
+ fk_report_error(m_error, m_fk_share->name.str, m_eos_tmp_table,
+ m_eos_tmp_table->field);
return TRUE;
}
@@ -2647,24 +2895,24 @@ bool Foreign_key_eos_buffer::do_eos_chec
were inserted into the buffer and therefore we can report
success.
*/
- if (! m_eos_buffer)
+ if (! m_eos_tmp_table)
return FALSE;
/* Switch to read caching. */
- m_eos_buffer->file->extra(HA_EXTRA_CACHE);
+ m_eos_tmp_table->file->extra(HA_EXTRA_CACHE);
- if ((error= m_eos_buffer->file->ha_rnd_init(1)))
+ if ((error= m_eos_tmp_table->file->ha_rnd_init(1)))
{
- m_eos_buffer->file->print_error(error, MYF(0));
+ m_eos_tmp_table->file->print_error(error, MYF(0));
return TRUE;
}
while (!m_thd->killed &&
- !(error= m_eos_buffer->file->rnd_next(m_eos_buffer->record[0])))
+ !(error= m_eos_tmp_table->file->rnd_next(m_eos_tmp_table->record[0])))
{
if (check_fk_value())
{
- m_eos_buffer->file->ha_rnd_end();
+ m_eos_tmp_table->file->ha_rnd_end();
return TRUE;
}
}
@@ -2676,16 +2924,16 @@ bool Foreign_key_eos_buffer::do_eos_chec
And found that all previously un-matched values have match
now or were removed from the child table.
*/
- m_eos_buffer->file->ha_rnd_end();
+ m_eos_tmp_table->file->ha_rnd_end();
return FALSE;
case 0:
/* Our statement or connection were killed. */
DBUG_ASSERT(m_thd->killed);
- m_eos_buffer->file->ha_rnd_end();
+ m_eos_tmp_table->file->ha_rnd_end();
return TRUE;
default:
- m_eos_buffer->file->print_error(error, MYF(0));
- m_eos_buffer->file->ha_rnd_end();
+ m_eos_tmp_table->file->print_error(error, MYF(0));
+ m_eos_tmp_table->file->ha_rnd_end();
return TRUE;
}
}
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2009-02-11 08:51:44 +0000
+++ b/sql/sql_select.h 2009-03-20 14:22:01 +0000
@@ -1856,7 +1856,7 @@ class store_key_field: public store_key
}
}
const char *name() const { return field_name; }
- const uint16 from_field_index() const
+ uint16 from_field_index() const
{
return copy_field.from_field->field_index;
}
| Thread |
|---|
| • bzr commit into mysql-6.1-fk branch (kostja:2706) WL#148 | Konstantin Osipov | 20 Mar |
