Bug #94492 ON DELETE SET NULL clause foreign key
Submitted: 27 Feb 2019 12:20 Modified: 5 Mar 2019 6:38
Reporter: Rajesh malla Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql Ver 8.0.14-commercial for Linux OS:Linux
Assigned to: CPU Architecture:Any

[27 Feb 2019 12:20] Rajesh malla
Description:
ON DELETE SET NULL clause foreign key innodb
We have set above foreign key constraint in our database.
Parent Table -> id column
Child Table  -> parent_id column with foreign key constraint

Now if parent_table -> id record deletes, child table column not set to null.
To check whether constraint is active or not, we have updated column parent_id on child table with value other than parent table record. Then it throw error.
If we update/add it is throwing an error, where as on deletion of record from parent table, child is still exists. Can you help us.

How to repeat:
This is reproducible only for duration in our environments. so we also dont know reproduction steps. If you find we miss any configuration/or anything please suggest
[27 Feb 2019 14:06] MySQL Verification Team
Hi,

Thank you for your report.

Our manual says loud and clear:

SET NULL: Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

Hence, you have to have a row in child table with matching value and a column that is NULLable.

Let us know whether the above conditions are fully met.

If they are met, send us the short test case, include CREATE TABLE statements, the relevant INSERT commands and the DELETE that does not work as our manual stipulates.
[28 Feb 2019 5:12] Rajesh malla
create table employee(eid int primary key auto_increment, ename varchar(40)) ;

create table employee_det(emp_eid int default null, address varchar(50), CONSTRAINT `new_test_key` FOREIGN KEY(emp_eid) REFERENCES employee(eid) ON DELETE SET NULL) ;

after inserting information into above two tables with valid values. When we delete information from employee table , still employee_det contains value of it.
actually it should mark it as null which is not happening.
[28 Feb 2019 5:24] Rajesh malla
insert into employee values(225, 'test') ;
insert into employee_det values (225, 'vsk') ;
delete from employee where eid = 225 ;

when we delete record from parent, child record value still exists, where as on child table if we try to update with any value [ other than parent value ] on column emp_eid  then it throws constraint error.
So constraint is working but only this set default to null part is not working.
This observing only for certain duration. Once we apply new build again working for some hours and again same problem. We know it is mistake from our side, however we need your help is there any chance like this to happen ? what causes system to behave like this ? any input will be helpful for us.
[28 Feb 2019 10:27] MySQL Verification Team
C:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > " --default-character-set=utf8mb4
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.16 Source distribution BUILD: 2019-FEB-20

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > use test
Database changed
mysql 8.0 > create table employee(eid int primary key auto_increment, ename varchar(40)) ;
Query OK, 0 rows affected (0.05 sec)

mysql 8.0 > create table employee_det(emp_eid int default null, address varchar(50), CONSTRAINT `new_test_key` FOREIGN KEY(emp_eid) REFERENCES employee(eid) ON DELETE SET NULL) ;
Query OK, 0 rows affected (0.04 sec)

mysql 8.0 > insert into employee values(225, 'test') ;
Query OK, 1 row affected (0.02 sec)

mysql 8.0 > insert into employee_det values (225, 'vsk') ;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > delete from employee where eid = 225 ;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > select * from employee_det;
+---------+---------+
| emp_eid | address |
+---------+---------+
|    NULL | vsk     |
+---------+---------+
1 row in set (0.00 sec)

mysql 8.0 >
[28 Feb 2019 10:37] MySQL Verification Team
-- 8.0.15

Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Database changed
mysql> create table employee(eid int primary key auto_increment, ename varchar(4
0)) ;
Query OK, 0 rows affected (0.66 sec)

mysql> create table employee_det(emp_eid int default null, address varchar(50),
CONSTRAINT `new_test_key` FOREIGN KEY(emp_eid) REFERENCES employee(eid) ON DELET
E SET NULL) ;
Query OK, 0 rows affected (0.73 sec)

mysql> insert into employee values(225, 'test') ;
Query OK, 1 row affected (0.11 sec)

mysql> insert into employee_det values (225, 'vsk') ;
Query OK, 1 row affected (0.14 sec)

mysql> delete from employee where eid = 225 ;
Query OK, 1 row affected (0.13 sec)

mysql> select * from employee_det;
+---------+---------+
| emp_eid | address |
+---------+---------+
|    NULL | vsk     |
+---------+---------+
1 row in set (0.00 sec)
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.05 sec)
[28 Feb 2019 11:54] Rajesh malla
This is the information

mysql> show variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.01 sec)

mysql> select @@foreign_key_checks;
+----------------------+
| @@foreign_key_checks |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)

mysql> select id, child_id from child where id = '99.99.99.1';
+------------+---------------+
| id         | child_id |
+------------+---------------+
| 99.99.99.1 |          1001 |
+------------+---------------+
1 row in set (0.00 sec)
 
mysql> select * from parent ;
Empty set (0.00 sec)

mysql> update child set child_id = 900 where id = '99.99.99.1' ;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dummy`.`child`, CONSTRAINT `new_test` FOREIGN KEY (`child_id`) REFERENCES `parent` (`id`) ON DELETE SET NULL)
[28 Feb 2019 11:56] Rajesh malla
child_id should mark as null where as it is not doing. One more thing we found, if we create employee and employee_det tables and will do similar thing, it worked well. I mean updating it as null immediately on delete of employee record.
That means, it is working on thsi employee table. only thing is on this particular fail table it is not working.
[28 Feb 2019 12:12] Rajesh malla
At this time, if we drop constraint and re-create it, then everything works. Please don't think I am confusing you,  here is summary

1) database table going into one state in which only set null on delete trigger is not working but constraint is active
2) if we create a new table and do this scenario it is working properly on employee table it is new.
3) if we drop constraint on constraint failed table and re-create it then it is working properly.
[28 Feb 2019 14:43] MySQL Verification Team
Hi,

We understand that you have problems, but this is a forum for bugs. In order to verify a bug, we need a fully repeatable test case. In your case, it would had to include a procedure that would stop proper functioning of the foreign keys.

You asked for some help, so I will provide you with two hints ...

You should see whether anyone has disabled foreign key checks and you should check the relevant privileges of the users that performs the CREATE and DELETE operations.

This is all fully explained in our Reference Manual.
[28 Feb 2019 14:47] MySQL Verification Team
Hi,

There is a third possibility in case that you are using Docker.

Inspect this bug report:

https://bugs.mysql.com/bug.php?id=94400
[5 Mar 2019 3:37] Rajesh malla
Thank you for providing information. One important observation we found is 

1) Recent change is from mysql-connector-java-8.0.13.jar to mysql- connector-java-8.0.14.jar.
2) After this change, once we restart mysql server then only foreignkey constraints works.
3) When we use mysql-connector-java-8.0.13.jar, no need to restart db for first time, foreign key constraint works.

From this can we get any help from you ?
[5 Mar 2019 6:38] Rajesh malla
Please ignore my last comment, this https://bugs.mysql.com/bug.php?id=94400 link is helpful for us.
Thank you so much for providing information.
[5 Mar 2019 13:51] MySQL Verification Team
You are truly welcome.