MySQL Bugs: #41738: Foreign keys: failure if trigger

Bug #41738	Foreign keys: failure if trigger
Submitted:	24 Dec 2008 20:53	Modified:	19 Jan 2009 19:11
Reporter:	Peter Gulutzan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	6.1.0-alpha-debug	OS:	Linux (SUSE 10.0 / 32-bit)
Assigned to:		CPU Architecture:	Any

Description:
I'm using mysql-6.1-fk.
I start the server with mysqld --foreign-key-all-engines=1.

I use a trigger.
I get a non-matching foreign-key row.

How to repeat:
drop table if exists t1;
drop table if exists t2;
create table t1 (s1 int default -1, s2 int default -1, primary key (s1,s2)) engine=falcon;
create table t2 (s1 int default -1, s2 int default -1, foreign key (s1,s2) references t1 (s1,s2) on update set default on delete set default) engine=falcon;
create trigger t1_bu before update on t1 for each row set new.s1 = 2;
create trigger t2_bu before update on t2 for each row set new.s1 = 3;
insert into t1 values (1,1),(3,1);
insert into t2 values (1,1);
update t1 set s1 = 0 where s1 = 1;
select * from t1;
select * from t2;
...
The results for the SELECT statements are:
mysql> select * from t1;
+----+----+
| s1 | s2 |
+----+----+
|  2 |  1 |
|  3 |  1 |
+----+----+
2 rows in set (0.00 sec)

mysql> select * from t2;
+------+------+
| s1   | s2   |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

Thank you for the bug report. I am getting the below error in the update command so different select results than the test case, that doesn't happens on your side?.

miguel@hegel:~/dbs$ 6.1/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.1.0-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1 (s1 int default -1, s2 int default -1, primary key (s1,s2))
    -> engine=falcon;
Query OK, 0 rows affected (0.73 sec)

mysql> create table t2 (s1 int default -1, s2 int default -1, foreign key (s1,s2) references t1
    -> (s1,s2) on update set default on delete set default) engine=falcon;
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger t1_bu before update on t1 for each row set new.s1 = 2;
Query OK, 0 rows affected (0.01 sec)

mysql> create trigger t2_bu before update on t2 for each row set new.s1 = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,1),(3,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t2 values (1,1);
Query OK, 1 row affected (0.01 sec)

mysql> update t1 set s1 = 0 where s1 = 1;
ERROR 1780 (23000): Foreign key error: constraint 'fk_t2_mdq4r': no matching key for value '3--1', it is not in parent table

I am now getting the same results as Miguel gets.
That's still a bug, so I'll call this "verified".
But it's still true that we don't expect this to
be fixed until a later milestone.

Hi Peter!

Could you please clarify what results do you expect? IMO results which Miguel is getting are correct and expected ones...

Do I miss something?

The error message says
"no matching key for value '3--1', it is not in parent table"
but the parent table has a row (3,1).

Indeed parent table has (3, 1) row. But the above error message complains about
absence of row (3, -1) which is not there. So I think what the see above is
expected and correct behavior. So I am closing this report as "Not a bug".