Bug #114799 ERROR 1216 vs ERROR 1452 in MySQL 8.0
Submitted: 26 Apr 16:41 Modified: 29 Apr 12:26
Reporter: Vinicius Malvestio Grippa Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.33, 8.0.36 OS:Any
Assigned to: CPU Architecture:Any

[26 Apr 16:41] Vinicius Malvestio Grippa
Description:
Limited-privilege users see fewer verbose errors (1216/1217) per the design intent. This is described in an old bug:

https://bugs.mysql.com/bug.php?id=3443

I've granted reference privileges to show more verbose errors (1451/1452) for all users. But my user still sees the less verbose versions.

The description:
Longer answer: 1217 (ER_ROW_IS_REFERENCED) and 1452 (ER_ROW_IS_REFERENCED_2) both describe the same situation. As far as I understand, Mysql will use 1451 if user has a REFERENCES privilege for the table of interest, and 1217 otherwise. And it's the same story about 1216 (ER_NO_REFERENCED_ROW) vs 1452 (ER_NO_REFERENCED_ROW_2).

How to repeat:
/* CREATE THE USER */
create user vgrippa@'%' identified by 'vgrippa';
GRANT SELECT, INSERT, UPDATE, DELETE, PROCESS, INDEX, SUPER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON *.* TO 'vgrippa'@'%';      

/* And for MySQL 8 you can use the same or the following grants */
mysql> show grants for current_user();
+-------------------------------------------------------------------------------GRANT SELECT, INSERT, UPDATE, DELETE, PROCESS, REFERENCES, INDEX, SUPER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT ON *.* TO `vgrippa`@`%` |

/* TEST CASE */

CREATE DATABASE IF NOT EXISTS test;

USE test;

DROP TABLE IF EXISTS `joinit`;

CREATE TABLE `joinit` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(64) DEFAULT NULL,
  `t` time NOT NULL,
  `g` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

INSERT INTO joinit VALUES (NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )));
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;
INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;

DROP TABLE joinit_fk;
CREATE TABLE joinit_fk (i int NOT NULL PRIMARY KEY) engine innodb;
INSERT INTO joinit_fk SELECT DISTINCT g FROM joinit;
ALTER TABLE joinit ADD FOREIGN KEY g_fk (g) REFERENCES joinit_fk (i) ON UPDATE cascade ON DELETE restrict;

/* INSERT a ROW that violates the constraints */
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.33-25 |
+-----------+
1 row in set (0.00 sec)

mysql> INSERT INTO joinit VALUES (NULL, uuid(), time(now()), 999);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

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

mysql> INSERT INTO joinit VALUES (NULL, uuid(), time(now()), 999);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`joinit`, CONSTRAINT `joinit_ibfk_1` FOREIGN KEY (`g`) REFERENCES `joinit_fk` (`i`) ON UPDATE CASCADE)

Suggested fix:
Add the correct privilege to the documentation. If the REFERENCE is the correct grant but it not working properly, fix the bug.
[29 Apr 5:23] MySQL Verification Team
Hello Vinicius,

Thank you for report and feedback.

regards,
Umesh
[29 Apr 12:26] Vinicius Malvestio Grippa
Hi, 

Thanks for the confirmation.

I forgot to mention:

#### Workaround

On MySQL 8, instead of SUPER, use ALL PRIVILEGES. Example:

GRANT ALL PRIVILEGES On *.* TO vgrippa@'%';