Avatar of apj68
apj68
 asked on

MySQL FK Constraint Error (1451) - But I have no FKs!

This is driving me crazy.  My table has no foreign keys and no indices but when I try to delete rows, I get:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed)

This table used to have constraints, but I removed them.  I did refresh MySql workbench and also reconnected with the client.  If I describe the table, it does not show constraints other than the PK.

select * from information_schema.table_constraints
where table_schema = schema()
and table_name = 'AppVersion';

[returns]
NULL, 'cmdb', 'PRIMARY', 'cmdb', 'AppVersion', 'PRIMARY KEY'

show create table AppVersion;

[returns]
'AppVersion', 'CREATE TABLE `AppVersion` (
  `appVersionID` int(11) NOT NULL AUTO_INCREMENT,
  `Environment_environmentID` int(11) NOT NULL,
  `rollback_version` varchar(45) NOT NULL,
  `app_version` varchar(45) NOT NULL,
  PRIMARY KEY (`appVersionID`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1'

Open in new window


The table that originally had the FK (Environment) in the AppVersion has nothing either:

show create table Environment

[returns]
'Environment', 'CREATE TABLE `Environment` (
  `environmentID` int(11) NOT NULL AUTO_INCREMENT,
  `env_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`environmentID`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1'

Open in new window


Is there something I need to do to shake out that old constraint??  Thanks for your suggestions!

Andy
MySQL Server

Avatar of undefined
Last Comment
apj68

8/22/2022 - Mon
Pieter Marais

Hi there,
The key to your solution might be here:

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (temp file operation failed)

This usually happens when your server's temp directory runs out of space, and in this case, I asume it is a completely different partition on a unix server. So try to figure out where exactly mySQL is doing its temporary calculations by looking at its config file. If you don't have access to clean out the temp directory(which should be interesting), try to find someone that has access to do it.

Usually the directory is "/tmp".

Hope it helps ^_^

Regards,
EZFrag
apj68

ASKER
The configured tempdir was, in fact, /tmp, and it did have plenty of space.  I cleaned it up anyhow in case there was some sort of cache there.  So, it didn't exactly help, however, in the process of verifying whether or not it helped, I did find something else that might be helpful.

Previously, I was trying to remove rows like this:

delete from AppVersion where appVersionID > 5;

Just now, I tried this:

delete from AppVersion where appVersionID = 9;

and it worked.  However, this did not:

delete from AppVersion where appVersionID = 8;

So I inserted additional rows which I could then remove.  Long story short, it seems that rows created while the constraint was in place are still constrained.

Does that make sense?  I.e., is that to be expected??
Sandeep Kothari

try this...and see what it outputs..
select
    concat(table_name, '.', column_name) as 'foreign key',
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

Open in new window


Reference:http://www.binarytides.com/blog/list-foreign-keys-in-mysql/
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Pieter Marais

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
apj68

ASKER
@EZFrag - you were right!  Stopping apparmor resolves the problem!  Really strange!

@kshna - by golly, running that script I do get in the response the culprit column:

Foreign Key                                                  reference
'AppVersion.Environment_environmentID', 'Environment.environmentID'

But it doesn't appear in MySql workbench as a FK.

Weird!  Do you know how I can drop the FK??  

I believe EZFrag hit on the root cause but I'm still left with the dangling FK.  Can either of you help there?

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
apj68

ASKER
Very nice, thank you both for your help!  All information provided was a great assistance!

Andy