'Cannot add or update child row' - irregular occurrences

[JamieB]

Hey guys,

 

I'm working on trying to code a job site style CMS at the minute, and am sometimes getting the following error:

 

"Cannot add or update a child row: a foreign key constraint fails (`eyr`.`users`, CONSTRAINT `users_ibfk_2` FOREIGN KEY (`user_pref1`) REFERENCES `categories` (`cat_name`) ON UPDATE CASCADE)1"

 

'eyr' = Database name

'users' = Table name

'user_pref1' = Field in 'users' (user_pref1 varchar(255) latin1_swedish_ci (Null?) Yes NULL )

'categories' = Table name

'cat_name' = Field in 'categories' (cat_name varchar(255) latin1_swedish_ci (Null?) No None )

 

The issue also occurs with

'user_pref2' = Field in 'users' (user_pref2 varchar(255) latin1_swedish_ci (Null?) Yes NULL )

 

'user_pref1' and 'user_pref2' both reference categories.cat_name, set to "ON UPDATE CASCADE"

 

The infuriating thing is - the error only happens when it feels like it. Sometimes it will modify the table fine and other times it will give the error.

 

Here's a quick summary of where the problem is happening:

 

Users can register

Registered users can have a profile visible to recruiters including "industry preferences"

There are 2 industry preferences

Both industry preferences must be set (there is no blank option in the drop down menu)

When editing a user profile, changing either of the preferred industries will sometimes (but not always, and I cannot figure out what determines whether or not it does) trigger the error. The option is changed using a drop down menu which takes the list of industries from the database - so it isn't possible to enter 'incorrect' data. I have flushed the tables before when I was getting the error every time which I  though had solved it but clearly not.

 

Any ideas as to what would be triggering the error and how it could be prevented?

 

Having just copied that field information maybe I'll see if it's anything to do with cat_name not being able to be null - but as user_pref1 and user_pref2 always have a value I don't know if this will be it. I stopped working on this for a good few weeks so am a bit out of touch with it and the things I've changed regarding the error but any extra information I'm sure I can provide.

 

It usually seems to work fine the first few times after logging in - could it be anything to do with sessions or anything like that?

[JamieB]

Apologies, my MySQL server version is 5.5.8

 

I haven't included any code as I don't think that's where the issue lies.

[JamieB]

Sorry to triple post.

 

I think I've found out when the problem occurs, but still don't know how to sort it.

 

It seems that the problem is triggered when:

 

user_pref1 and user_pref2 are changed to the same value at the same time

(Changing one, then "saving profile", then changing the other and saving again doesn't seem to do it, they must be done at the same time)

AND

The value that both fields use contains a space

AND

A user attempts to change ONE value from the two same values

 

To escape the error you need to change BOTH values at the same time.

 

So I'm guessing this must be something to do with the space? Which doesn't help me solve it but could be useful to anyone who may be able to.

 

Any help would be massively appreciated.

 

Here is the query which causes the error, which seems fine to me (and I'm guessing is or it wouldn't work most of the time)

 

		$userid = $_SESSION['user_id'];
		$sql = "UPDATE
				users
				SET
				user_firstname = '" . mysql_real_escape_string($_POST['user_firstname']) . "', 
				user_lastname = '" . mysql_real_escape_string($_POST['user_lastname']) . "',
				user_email = '" . mysql_real_escape_string($_POST['user_email']) . "',
				user_location = '" . mysql_real_escape_string($_POST['user_location']) . "',  
				user_phone = '" . mysql_real_escape_string($_POST['user_phone']) . "', 
				user_pref1 = '" . mysql_real_escape_string($_POST['user_pref1']) . "',
				user_pref2 = '" . mysql_real_escape_string($_POST['user_pref2']) . "',
				user_profile = '" . mysql_real_escape_string($_POST['user_profile']) . "'
				WHERE
				user_id = $userid";  
		$result = mysql_query($sql) or die(mysql_error(). include 'footer.php');

 

[kickstart]

Hi

 

Not sure the cascade should happen (I assume you don't want the value of a selected industry to cascade to the industries table, and not sure of any others you would want to update).

 

However from the mysql documentation:-

 

http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html

 

CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

 

Looks like cascade wouldn't appreciate the 2 columns on the users table which both related to the same column of the categories table.

 

All the best

 

Keith

[JamieB]

Okay, I could be making a pretty huge mistake here early on which I should probably get out of my system right now.

 

When I have been making foreign keys, I had assumed that the referenced field (in this case, cat_name) was the parent.

 

So if I was to edit the cat_name of a field (say from 'Administrative' to 'Admin & Office'), with ON UPDATE CASCADE; the users preference would automatically update from 'Administrative' to 'Admin & Office' (preferences need to exactly match cat_names as they are used in modules such as 'recommended jobs').

 

Have I had this the wrong way round?

[JamieB]

Okay I've just checked and was right about that, but your first comment confused me?

 

 

I've tried to work around this by simply creating a 'cat_name2' field in 'categories' so that two fields aren't ON UPDATE CASCADE-ing the same field (user_pref1 -> cat_name, user_pref2 -> cat_name2) - only that doesn't seem to have worked either.

 

 

 

Finally found an edit button.

 

I've also discovered even if user_pref1 and user_pref2 are different, if you 'save profile' and no change is made to either I get the error, and that is with them referencing different fields (cat_name and cat_name2 respectively). But I definitely need that ON UPDATE CASCADE in place to change preference values with industry values.

[kickstart]

Hi

 

You do have the parent / child thing right I think.

 

However it seems that the cascade doesn't work if you have 2 columns on a child table both referring to the same parent (that is what the quote I posted seems to say).

 

Try taking the "on update cascade" out for now and see if it fixes it.

 

If it does then I think what you will need to do is split those 2 columns off to instead be a pair of rows on a further table (this would be better design anyway, and would allow you any number of preferred industries per person in the future).

 

All the best

 

Keith

[JamieB]

Many thanks for your help Keith, however, I've tried removing the cascade and the error still occurs, just without the "ON UPDATE CASCADE" towards the end.

 

Also where it said ibfk_2 it now says ibfk_7 - I did a bit of removing and reapplying foreign keys which I guess is leading to that?

 

 

I tried using 2 tables (categories and categories2 - that's what you meant right?) which I thought had worked but still no luck. Again the problem still seems to lie in values with a space. E.g.

 

(users.user_pref1 (Pref1) now references categories.cat_name)

(users.user_pref2 (Pref2) now references categories2.cat_name)

 

Pref1: Engineering change to Engineering

Pref2: Engineering change to Engineering

 

Works fine (I *think* this used to be a problem with one table). But

 

Pref1: Customer Service change to Customer Service

Pref2: Customer Service change to Customer Service

 

Will not. And

 

Pref1: Engineering change to Engineering

Pref2: Customer Service change to Customer Service

(The error on this one is with Pref2 so clearly the Engineering 'change' has worked, but the CS one is failing)

 

Will not either.

 

I know there are no changes there but there is every possibility a user would make amends to their profile but leave the preferred industries as they were which would cause the above issue.

[kickstart]

Hi

 

Need to know what ibfk_7 is.

 

What I meant by splitting them onto a separate table is you would have something like:--

 

Table of industries.

Industries

Id, Name

1, Food

2, Electrics

3, Spannering

 

Table of people

People

Id, Name, etc

1, Jo Bloggs, etc

2, Joe Bloggs, etc

3, Bilbo Baggins, etc

 

Then you have a table of preferred industries

PrefInd

Id, IndId, PeopleId

1, 1, 1

2, 1, 2

3, 2, 2

4, 2, 3

5, 3, 1

6, 3, 3

 

This way on the last table IndId refers to the Id field on the industries table while PeopleId refers to the Id on the People table.

 

All the best

 

Keith