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.