#At file:///home/dlenev/src/bzr/mysql-6.1-bg41692/
2703 Dmitry Lenev 2009-02-06
Fix for bug #41692 "Foreign keys: failure if mixed engines".
Foreign keys in which one participating table was transactional
and another was not didn't preserve integrity in scenarios when
both parent and child table were modified in the same transaction
and then this transaction was rolled back.
This problem stems from the nature of such foreign keys. Changes
to transactional tables can be rolled back and therefore cannot
be fully relied upon when performing foreign key checks before
modification of non-transactional table (changes to which are
not rollbackable).
This fix solves this problem by prohibiting such foreign keys.
In future we might consider raising this limitation by allowing
them in certain "safe" scenarios.
modified:
mysql-test/r/foreign_key_all_engines.result
mysql-test/t/foreign_key_all_engines.test
sql/fk_dd.cc
sql/share/errmsg.txt
per-file messages:
mysql-test/r/foreign_key_all_engines.result
Added coverage for fix for bug #41692 "Foreign keys: failure if
mixed engines". Adjusted existing test cases to the fact that
mixing transactional and non-transactional tables in one foreign
key is now prohibited.
mysql-test/t/foreign_key_all_engines.test
Added coverage for fix for bug #41692 "Foreign keys: failure if
mixed engines". Adjusted existing test cases to the fact that
mixing transactional and non-transactional tables in one foreign
key is now prohibited.
sql/fk_dd.cc
Prohibit foreign keys between transactional and non-transactional
tables as they don't guarantee referential integrity in some
scenarios.
sql/share/errmsg.txt
Added error message to be emitted when one tries to create a
foreign key between transactional and non-transactional tables.
=== modified file 'mysql-test/r/foreign_key_all_engines.result'
--- a/mysql-test/r/foreign_key_all_engines.result 2009-01-29 09:00:19 +0000
+++ b/mysql-test/r/foreign_key_all_engines.result 2009-02-06 14:10:39 +0000
@@ -429,48 +429,37 @@ create table t2 (a int, b int,
foreign key (a, b) references t1 (a, b));
ERROR 42000: Foreign key error: Constraint 'fk_t2_bi94t': Parent columns don't correspond to a PRIMARY KEY or an UNIQUE constraint
drop table t1;
-# Check for action types incompatible with non-transactional engines.
-# It is enough for one of tables in foreign key not to support
-# transactions to make it incompatible with NO ACTION/CASCADE/SET NULL
-# SET DEFAULT actions.
+# Check that we prohibit foreign keys in which one table is
+# transactional and another is not (see bug #41692 "Foreign keys:
+# failure if mixed engines").
create table t1 (pk int primary key) engine=myisam;
-create table t2 (fk int references t1 (pk) on delete cascade);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_wxqzm': Engine of table 't1' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
-create table t2 (fk int references t1 (pk) on delete set null);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_m5zjv': Engine of table 't1' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
-create table t2 (fk int references t1 (pk) on delete set default);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_c0osi': Engine of table 't1' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
-create table t2 (fk int references t1 (pk) on update cascade);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_tlhay': Engine of table 't1' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
-create table t2 (fk int references t1 (pk) on update set null);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_3xc5c': Engine of table 't1' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
-create table t2 (fk int references t1 (pk) on update set default);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_2u8tv': Engine of table 't1' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
-create table t2 (fk int references t1 (pk) on delete no action);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_2f7pg': Engine of table 't1' is non-transactional and NO ACTION is specified
-create table t2 (fk int references t1 (pk) on update no action);
-ERROR 42000: Foreign key error: Constraint 'fk_t2_3lc1m': Engine of table 't1' is non-transactional and NO ACTION is specified
-# Creating foreign key with both RESTRICT actions should be OK
-create table t2 (fk int references t1 (pk)
-on delete restrict on update restrict);
-drop table t1, t2;
+create table t2 (fk int references t1 (pk));
+ERROR 42000: Foreign key error: Constraint 'fk_t2_wxqzm': Mixing transactional and non-transactional engines in one foreign key is not allowed
+drop table t1;
create table t1 (pk int primary key);
+create table t2 (fk int references t1 (pk)) engine=myisam;
+ERROR 42000: Foreign key error: Constraint 'fk_t2_m5zjv': Mixing transactional and non-transactional engines in one foreign key is not allowed
+drop table t1;
+# Check for action types incompatible with non-transactional engines.
+# Non-transactional tables are incompatible with NO ACTION/CASCADE/
+# SET NULL/SET DEFAULT actions.
+create table t1 (pk int primary key) engine=myisam;
create table t2 (fk int references t1 (pk) on delete cascade) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_6p5e9': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_c0osi': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
create table t2 (fk int references t1 (pk) on delete set null) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_1ennw': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_tlhay': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
create table t2 (fk int references t1 (pk) on delete set default) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_mxu4r': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_3xc5c': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
create table t2 (fk int references t1 (pk) on update cascade) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_2h7o9': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_2u8tv': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
create table t2 (fk int references t1 (pk) on update set null) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_fkjz2': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_2f7pg': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
create table t2 (fk int references t1 (pk) on update set default) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_yf4ul': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_3lc1m': Engine of table 't2' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified
create table t2 (fk int references t1 (pk) on delete no action) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_he0bv': Engine of table 't2' is non-transactional and NO ACTION is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_ap13t': Engine of table 't2' is non-transactional and NO ACTION is specified
create table t2 (fk int references t1 (pk) on update no action) engine=myisam;
-ERROR 42000: Foreign key error: Constraint 'fk_t2_jon3o': Engine of table 't2' is non-transactional and NO ACTION is specified
+ERROR 42000: Foreign key error: Constraint 'fk_t2_6p5e9': Engine of table 't2' is non-transactional and NO ACTION is specified
# Creating foreign key with both RESTRICT actions should be OK
create table t2 (fk int references t1 (pk)
on delete restrict on update restrict) engine=myisam;
@@ -928,7 +917,7 @@ drop tables t1, t2;
# this method at the moment.
create table t1 (pk int primary key) engine=myisam;
create table t2 (fk int references t1 (pk) on update restrict
-on delete restrict);
+on delete restrict) engine=myisam;
insert into t1 values (1), (2);
insert into t2 values (2);
delete from t1;
=== modified file 'mysql-test/t/foreign_key_all_engines.test'
--- a/mysql-test/t/foreign_key_all_engines.test 2009-01-29 09:00:19 +0000
+++ b/mysql-test/t/foreign_key_all_engines.test 2009-02-06 14:10:39 +0000
@@ -333,32 +333,21 @@ create table t1 (a int not null, b int n
create table t2 (a int, b int,
foreign key (a, b) references t1 (a, b));
drop table t1;
---echo # Check for action types incompatible with non-transactional engines.
---echo # It is enough for one of tables in foreign key not to support
---echo # transactions to make it incompatible with NO ACTION/CASCADE/SET NULL
---echo # SET DEFAULT actions.
+--echo # Check that we prohibit foreign keys in which one table is
+--echo # transactional and another is not (see bug #41692 "Foreign keys:
+--echo # failure if mixed engines").
create table t1 (pk int primary key) engine=myisam;
---error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
-create table t2 (fk int references t1 (pk) on delete cascade);
---error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
-create table t2 (fk int references t1 (pk) on delete set null);
---error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
-create table t2 (fk int references t1 (pk) on delete set default);
---error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
-create table t2 (fk int references t1 (pk) on update cascade);
---error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
-create table t2 (fk int references t1 (pk) on update set null);
---error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
-create table t2 (fk int references t1 (pk) on update set default);
---error ER_FK_NON_TRANSACTIONAL_NO_ACTION
-create table t2 (fk int references t1 (pk) on delete no action);
---error ER_FK_NON_TRANSACTIONAL_NO_ACTION
-create table t2 (fk int references t1 (pk) on update no action);
---echo # Creating foreign key with both RESTRICT actions should be OK
-create table t2 (fk int references t1 (pk)
- on delete restrict on update restrict);
-drop table t1, t2;
+--error ER_FK_NON_TRANSACTIONAL_AND_TRANSACTIONAL
+create table t2 (fk int references t1 (pk));
+drop table t1;
create table t1 (pk int primary key);
+--error ER_FK_NON_TRANSACTIONAL_AND_TRANSACTIONAL
+create table t2 (fk int references t1 (pk)) engine=myisam;
+drop table t1;
+--echo # Check for action types incompatible with non-transactional engines.
+--echo # Non-transactional tables are incompatible with NO ACTION/CASCADE/
+--echo # SET NULL/SET DEFAULT actions.
+create table t1 (pk int primary key) engine=myisam;
--error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
create table t2 (fk int references t1 (pk) on delete cascade) engine=myisam;
--error ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION
@@ -759,7 +748,7 @@ drop tables t1, t2;
--echo # this method at the moment.
create table t1 (pk int primary key) engine=myisam;
create table t2 (fk int references t1 (pk) on update restrict
- on delete restrict);
+ on delete restrict) engine=myisam;
insert into t1 values (1), (2);
insert into t2 values (2);
--error ER_FK_CHILD_VALUE_EXISTS
=== modified file 'sql/fk_dd.cc'
--- a/sql/fk_dd.cc 2009-01-26 13:40:43 +0000
+++ b/sql/fk_dd.cc 2009-02-06 14:10:39 +0000
@@ -1648,6 +1648,31 @@ bool fk_check_constraint_added(THD *thd,
if (!(thd->options & OPTION_NO_FOREIGN_KEY_CHECKS))
{
/*
+ We can't guarantee integrity for foreign keys in which one
+ of participating tables is transactional and another is not.
+ So such foreign keys are disallowed.
+
+ Here is an example of a problematic scenario:
+ CREATE TABLE t1 (pk int primary key) engine=Falcon;
+ CREATE TABLE t2 (fk int references t1 (pk)) engine=MyISAM;
+ BEGIN
+ INSERT INTO t1 VALUES (1);
+ INSERT INTO t2 VALUES (1);
+ ROLLBACK;
+ After ROLLBACK we will get a dangling record in t2;
+
+ In future we might raise this limitation by allowing certain
+ scenarios in which integrity can be preserved.
+ */
+ if (parent_file &&
+ ((file->ha_table_flags() & HA_NO_TRANSACTIONS) !=
+ (parent_file->ha_table_flags() & HA_NO_TRANSACTIONS)))
+ {
+ my_error(ER_FK_NON_TRANSACTIONAL_AND_TRANSACTIONAL, MYF(0),
+ fkey->name.str);
+ DBUG_RETURN(TRUE);
+ }
+ /*
We can't guarantee integrity if non-transactional update/delete,
and (CASCADE | SET NULL | SET DEFAULT). (For example, imagine
what will happen if we encounter some problem in the middle of
=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt 2009-01-21 22:27:26 +0000
+++ b/sql/share/errmsg.txt 2009-02-06 14:10:39 +0000
@@ -6483,6 +6483,8 @@ ER_FK_PARENT_KEY_NO_PK_OR_UNIQUE 42000
eng "Foreign key error: Constraint '%-.192s': Parent columns don't correspond to a PRIMARY KEY or an UNIQUE constraint"
ER_FK_CHILD_COLUMNS_OVERLAP 42000
eng "Foreign key error: Constraints '%-.192s' and '%-.192s': Have overlapping columns and CASCADE/SET NULL/SET DEFAULT as cascading action"
+ER_FK_NON_TRANSACTIONAL_AND_TRANSACTIONAL 42000
+ eng "Foreign key error: Constraint '%-.192s': Mixing transactional and non-transactional engines in one foreign key is not allowed"
ER_FK_NON_TRANSACTIONAL_CASCADING_ACTION 42000
eng "Foreign key error: Constraint '%-.192s': Engine of table '%s' is non-transactional and CASCADE/SET NULL/SET DEFAULT is specified"
ER_FK_NON_TRANSACTIONAL_NO_ACTION 42000
