1

On my AWS RDS Mysql 8.0.16 server I have one DB (my-db) and 2 users: the master user (created automatically by RDS) and an app user created by me:

create user if not exists `appuser`@`%` identified by 's3cr3t';
grant all on `my-db`.* to `appuser`@`%`;

When I run the delete from Things where id = 123 query on behalf of the appuser, the error message I get is:

[23000][1217] Cannot delete or update a parent row: a foreign key constraint fails

(1217 and no constraint details)

When I run the same query as a master user, the error message is:

[23000][1451] Cannot delete or update a parent row: a foreign key constraint fails (my-db.Sandwiches, CONSTRAINT SandwichesThingIdFK FOREIGN KEY (thingId) REFERENCES Things (id))

(1451 and there are constraint details)

How do I make appuser get 1451 and all the details?

0

It looks like this issue is specific to Mysql 8.0.16. I've upgraded to 8.0.21 and I now get 1451 as expected.

Longer answer: 1217 (ER_ROW_IS_REFERENCED) and 1451 (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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.