Doctrine 1

Object1->Unlink(Relation, Id2) doesn't work, if primary key order in table of m2m reference isnt 1:Object 2: Id to unlink, when relation set to EQUAL= TRUE

Details

  • Type: Bug Bug
  • Status: Reopened Reopened
  • Priority: Minor Minor
  • Resolution: Unresolved
  • Affects Version/s: 1.2.0
  • Fix Version/s: None
  • Component/s: Relations
  • Labels:
    None
  • Environment:
    Windows XP, Apache 2.2, PHP 5, Doctrine 1.2 on Symfony 1.4

Description

Object1->Unlink(Relation, Id2) doesn't work, if primary key order in table of m2m reference isnt 1:Object 2: Id to unlink, when relation set to EQUAL= TRUE

User:
  actAs: 
    Timestampable: ~
  columns:    
    name:         { type: string(255), notnull: false }
    last_name:    { type: string(255), notnull: false }
    picture:      { type: string(255), notnull: false }
    birthday:     { type: timestamp, notnull: false }
    gender:       { type: string(255) }
    rating:       { type: integer, default:0} 
    num_comments: { type: integer, default:0} 
    num_groups:   { type: integer, default:0} 
  inheritance:
    extends: Member
    type: column_aggregation
    keyField: type
    keyValue: 1                    
  relations:
    Friends:    { onDelete: CASCADE, class: User, foreignAlias: Friends, refClass: FriendReference, local: user1, foreign: user2, equal: true }
    Request:    { onDelete: CASCADE, class: User, foreignAlias: Pending, refClass: PendingReference, local: user1, foreign: user2 }
    Blocker:    { onDelete: CASCADE, class: User, foreignAlias: Blocked, refClass: BlockedReference, local: user1, foreign: user2 }


 
			if(isset($refToRemove))
			{
				switch ($refToRemove)
				{
					case 'FriendReference': $references = $currentUser->Friends; $relation = 'Friends';	break;
					case 'PendingReference': $references = $currentUser->Pending; $relation = 'Pending'; break;
					case 'BlockerReference': $references = $currentUser->Blocker; $relation = 'Blocker'; break;
					case 'RequestReference': $references = $currentUser->Request; $relation = 'Request'; break;
				}
				 
				foreach($references as $reference)
				{

					if ($reference->id == $user2->id)
                        $currentUser->unlink($relation, array($user2->id), $now = true);
				}
                
			}

when you count relations from one side and the other it show that when set to EQUAL = TRUE relations are reciprocal, but unlink still minds primary keys order in order to function

Activity

Ascending order - Click to sort in descending order
Roman S. Borschel made changes -
Field Original Value New Value
Assignee Roman S. Borschel [ romanb ] Jonathan H. Wage [ jwage ]
Hide
Permalink
Jonathan H. Wage added a comment -

I am unable to reproduce the issue. When I test everything works as expected. We really need some more information and a test case in order to be sure of any issue.

Show
Jonathan H. Wage added a comment - I am unable to reproduce the issue. When I test everything works as expected. We really need some more information and a test case in order to be sure of any issue.
Jonathan H. Wage made changes -
Status Open [ 1 ] Resolved [ 5 ]
Resolution Cannot Reproduce [ 5 ]
Hide
Permalink
Ian Ricketson added a comment - - edited

I was able to reproduce this issue in Doctrine 1.2. The issue is a two part scenerio, which makes it kind of hard to debug. But here is how to reproduce it.

1.) Lets assume you have a Product model with ID 14.
2.) Lets assume you have an "equal" M:M table that links to itself, (product_product_group). "product_id_1" and "product_id_2" are a compound primary key which link back to the Product model.
3.) Lets assume you that Product 14 is currently linked to products with IDs 2, 5, and 13.
4.) Assuming $object is an instance of Product id 14, call $object->unlink(array(2));

  1. SQL QUERY LOG for Step 4.)
    482 Query START TRANSACTION
    482 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2'))
    482 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5'
    482 Query UPDATE product_product_group SET product_id_1 = '13' WHERE product_id_1 = '14' AND product_id_2 = '13'
    482 Query DELETE FROM product_product_group WHERE product_id_2 = '2' AND product_id_1 = '14'
    482 Query COMMIT

The result is as follows:
a.) Doctrine properly unlinks ID 2
b.) Doctrine updates the M:M table and sets the remaining two IDs (5 and 13) and links them to themselves (it shouldn't run these updates). Now, all we have in our database are two links (5-5 and 13-13).

5.) Now, Product ID 14 has no links anymore in the M:M table. So we call $object->link(array(2, 5, 13));

  1. SQL QUERY LOG for Step 5.)
    512 Query START TRANSACTION
    512 Query SELECT ... FROM to_product_id t WHERE (t.id IN ('13', '2', '5'))
    512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('2', '14')
    512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('5', '14')
    512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('13', '14')
    512 Query COMMIT

This works as expected.

6.) Now, in our database, Product ID 14 properly links to Ids 2, 5, and 13. BUT IDs 5 and 13 also have a link to themselves as a byproduct of the UPDATE queries called in step 4.).
7.) Now, we call $object->unlink(array(2)) again.

  1. SQL QUERY LOG for Step 7.)
    432 Query START TRANSACTION
    432 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2'))
    432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '2' AND product_id_2 = '5'
    432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5'
    432 Query ROLLBACK

The following error occurs:

  1. Mar 30 20:40:41 symfony [err] exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-5' for key 1' in /path/to/symfony/1.4/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php:1082

So basically, it repeated the same queries from step 4.), however this time around, when attempting to set ID 5 to itself, it fails because there is already an entry this pair in the database. Since we have a compound primary key, this non-unique combination is not allowed. I don't know why its trying to run these UPDATE queries, but its definitely something to do with thre relationship being "equal".

Show
Ian Ricketson added a comment - - edited I was able to reproduce this issue in Doctrine 1.2. The issue is a two part scenerio, which makes it kind of hard to debug. But here is how to reproduce it. 1.) Lets assume you have a Product model with ID 14. 2.) Lets assume you have an "equal" M:M table that links to itself, (product_product_group). "product_id_1" and "product_id_2" are a compound primary key which link back to the Product model. 3.) Lets assume you that Product 14 is currently linked to products with IDs 2, 5, and 13. 4.) Assuming $object is an instance of Product id 14, call $object->unlink(array(2)); SQL QUERY LOG for Step 4.) 482 Query START TRANSACTION 482 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2')) 482 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5' 482 Query UPDATE product_product_group SET product_id_1 = '13' WHERE product_id_1 = '14' AND product_id_2 = '13' 482 Query DELETE FROM product_product_group WHERE product_id_2 = '2' AND product_id_1 = '14' 482 Query COMMIT The result is as follows: a.) Doctrine properly unlinks ID 2 b.) Doctrine updates the M:M table and sets the remaining two IDs (5 and 13) and links them to themselves (it shouldn't run these updates). Now, all we have in our database are two links (5-5 and 13-13). 5.) Now, Product ID 14 has no links anymore in the M:M table. So we call $object->link(array(2, 5, 13)); SQL QUERY LOG for Step 5.) 512 Query START TRANSACTION 512 Query SELECT ... FROM to_product_id t WHERE (t.id IN ('13', '2', '5')) 512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('2', '14') 512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('5', '14') 512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('13', '14') 512 Query COMMIT This works as expected. 6.) Now, in our database, Product ID 14 properly links to Ids 2, 5, and 13. BUT IDs 5 and 13 also have a link to themselves as a byproduct of the UPDATE queries called in step 4.). 7.) Now, we call $object->unlink(array(2)) again. SQL QUERY LOG for Step 7.) 432 Query START TRANSACTION 432 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2')) 432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '2' AND product_id_2 = '5' 432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5' 432 Query ROLLBACK The following error occurs: Mar 30 20:40:41 symfony [err] exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE [23000] : Integrity constraint violation: 1062 Duplicate entry '5-5' for key 1' in /path/to/symfony/1.4/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php:1082 So basically, it repeated the same queries from step 4.), however this time around, when attempting to set ID 5 to itself, it fails because there is already an entry this pair in the database. Since we have a compound primary key, this non-unique combination is not allowed. I don't know why its trying to run these UPDATE queries, but its definitely something to do with thre relationship being "equal".
Jonathan H. Wage made changes -
Resolution Cannot Reproduce [ 5 ]
Status Resolved [ 5 ] Reopened [ 4 ]
Hide
Permalink
Jonathan H. Wage added a comment -

It is hard to decipher the problem. It is best and most clear to provide a test case.

Show
Jonathan H. Wage added a comment - It is hard to decipher the problem. It is best and most clear to provide a test case.
Hide
Permalink
Ian Ricketson added a comment -

I know this is old, and is likely to never be resolved, but the problem does still exist.

Basically, "unlinking" an equal relation only unlinks one side of the relationship:

DELETE FROM user_user_group WHERE (user_id_1 = '20' AND user_id_2 IN ('9'))

However, if the relationship was created from the other side (i.e. user_id_1 = 9 and user_id_2 = 20), then it doesn't properly get rid of the relationship. Again, this only applies when a relationship is marked as "equal".

Here is another post about the same issue, with their work-around:
http://stackoverflow.com/questions/1385281/how-do-i-remove-a-self-referencing-nn-relation-in-doctrine

Show
Ian Ricketson added a comment - I know this is old, and is likely to never be resolved, but the problem does still exist. Basically, "unlinking" an equal relation only unlinks one side of the relationship: DELETE FROM user_user_group WHERE (user_id_1 = '20' AND user_id_2 IN ('9')) However, if the relationship was created from the other side (i.e. user_id_1 = 9 and user_id_2 = 20), then it doesn't properly get rid of the relationship. Again, this only applies when a relationship is marked as "equal". Here is another post about the same issue, with their work-around: http://stackoverflow.com/questions/1385281/how-do-i-remove-a-self-referencing-nn-relation-in-doctrine
This list may be incomplete, as errors occurred whilst retrieving source from linked applications:
  • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-560, expand=changesets[-21:-1].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

People

  • Assignee:
    Jonathan H. Wage
    Reporter:
    Mariano Ramon
Vote (1)
Watch (1)

Dates

  • Created:
    Updated: