Bug #93318 Better eror message for FK violations in NDB Cluster
Submitted: 23 Nov 2018 13:59 Modified: 26 Nov 2018 9:32
Reporter: Ted Wennmark Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[23 Nov 2018 13:59] Ted Wennmark
Description:
Provide a bit more details when FK constraints fail in NDB.
Today we (NDB API + MySQL) are not giving any hints in error message of which of multiple FK's that triggered the error.
See sample below on world database:
> mysql> insert into city values (9999,"KALLE1","XYZ","KALLE3",9898);
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)

Can we add some hints in NdbError:"ERROR Details" (here: https://dev.mysql.com/doc/ndbapi/en/ndb-ndberror.html) regarding which FK that was violated and return this information to client.

For InnoDB SE we are providing this information in error message back to client:
> mysql> insert into city values (9999,"KALLE1","XYZ","KALLE3",9898);
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`world`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))

How to repeat:
Import world database into NDB:
create database world;
use world;
source /home/ted/ws/world.sql
alter table city drop foreign key city_ibfk_1;
alter table countrylanguage drop foreign key countryLanguage_ibfk_1;
alter table countrylanguage engine=ndbcluster;
alter table city engine=ndbcluster;
alter table country engine=ndbcluster;
ALTER TABLE city ADD CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`);
ALTER TABLE countrylanguage ADD CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `coun
try` (`Code`);

Run query below:
> mysql> insert into city values (9999,"KALLE1","XYZ","KALLE3",9898);
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (Unknown error code)

For InnoDB SE you will get this information in error message:
> mysql> insert into city values (9999,"KALLE1","XYZ","KALLE3",9898);
> ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`world`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`))
[24 Nov 2018 4:26] Umesh Shastry
Hello Ted,

Thank you for the report and feature request!

regards,
Umesh
[26 Nov 2018 9:31] Bernd Ocklin
Posted by developer:
 
Closing as duplicate fixed in 7.6.3:

https://dev.mysql.com/doc/relnotes/mysql-cluster/7.6/en/mysql-cluster-news-7-6-3.html#mysq...

(Bug #86241, Bug #26029485, Bug #16371292)

References: See also: Bug #16275684.