On our staging server, though not on localhost, User Relationship Node Access module was denying view access on a node to people in the node author's contact list, despite the fact that the user_relationship_node_access_1 record was in the node access table.
Armed with dsm(), I traced this to the following in user_relationship_node_access_node_grants():
// get this user's relationships
$relationships = user_relationships_load(array(
'user' => $user->uid,
'approved' => TRUE
));
For whatever reason, that array is coming back as empty, therefore URNA thinks the user has no relations, and therefore none of their buddies can see things they should be able to see.
I traced this further in, to the user_relationships_load() function, which is generating the following query:
SELECT DISTINCT ur.rid,ur.*,urt.* FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) WHERE (ur.requester_id = 220 OR ((ur.approved <> 1 OR urt.is_oneway <> 0) AND ur.requestee_id = 220)) AND ur.approved = 1;
I confirmed that this query returned 0 records, when executed against the DB directly. What's interesting is that the count query:
SELECT COUNT(DISTINCT rid) AS count FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) WHERE (ur.requester_id = 220 OR ((ur.approved <> 1 OR urt.is_oneway <> 0) AND ur.requestee_id = 220)) AND ur.approved = 1;
...does return the proper number of results (16, in my case).
Because the /relationships page shows the correct list of contacts, I decided to dig in deeper there and try and discover the 'secret sauce' that was working on that page and not in the grants hook. Inside template_preprocess_user_relationships() in user_relationships_ui.module, the only difference I could see was the addition of array('include_user_info' => TRUE) to the arguments of the _user_relationships_generate_query() function:
$args = array('user' => $account->uid, 'approved' => TRUE);
[snip]
$query = _user_relationships_generate_query($args, array('include_user_info' => TRUE));
So I made the same modification to user_relationship_node_access_node_grants(). This changed the query to:
SELECT DISTINCT ur.rid,ur.*,urt.*,requesters.name AS requester_name,requestees.name AS requestee_name,requesters.mail AS requester_mail,requestees.mail AS requestee_mail,requesters.data AS requester_data,requestees.data AS requestee_data,requesters.picture AS requester_picture,requestees.picture AS requestee_picture FROM user_relationships ur INNER JOIN user_relationship_types urt USING ( rtid ) INNER JOIN users requesters ON ur.requester_id = requesters.uid INNER JOIN users requestees ON ur.requestee_id = requestees.uid WHERE (ur.requester_id = 220 OR ((ur.approved <> 1 OR urt.is_oneway <> 0) AND ur.requestee_id = 220)) AND ur.approved = 1
...which, despite the fact that the WHERE clauses in each query seem to be completely identical, actually returns results successfully and get URNA working again. WTF?
Server info, fwiw:
PHP: 5.2.11
MySQL: 5.1.37
I would greatly appreciate any insight as to what the heck is going on here. :P~
Comment | File | Size | Author |
---|---|---|---|
#1 | urna-relationships-747798.patch | 671 bytes | webchick |
Comments
Comment #1
webchickHere's our patch. I set it "needs work" because it's almost surely not the correct fix, although it does seem to act as a workaround for our immediate issue.
Comment #2
webchickComment #3
jbrown CreditAttribution: jbrown commentedI'm the original author of the UR node access module (long time ago).
I think there is some problem with your server setup. You mention that it works on localhost, but not on your staging server.
Also, it is very odd that the count query returns rows when the normal query does not.
I suspect your fix is just working around the problem. There probably isn't a bug in the module.
Have you tried restarting your database server? Are you using the query cache?
Comment #4
chx CreditAttribution: chx commentedPlease format as SELECT |linebreak| FROM |linebreak| JOIN |linebreak| JOIN |linebreak| WHERE and attach your table dumps. Otherwise I cant help.
Comment #5
jbrown CreditAttribution: jbrown commentedI just did this myself:
Comment #6
andremolnar CreditAttribution: andremolnar commentedNot sure if this is THE problem, but it is A problem.
I didn't dig deep, I just glanced at the queries - but it seems to me that two conditions could come to play at the same time and would never return a result:
(Left of the last AND) ur.approved <> 1
(Right of the last AND) ur.approved = 1
I can't imagine why the where clause is built up to produce a test of ur.approved <> 1 AND ur.approved = 1
Comment #7
chx CreditAttribution: chx commentedYeah but that just means ur.approved is 1 so you know ur.approved <> 1 is FALSE so this can be simplified as
(ur.requester_id = 220 OR (urt.is_oneway <> 0 AND ur.requestee_id = 220)) AND ur.approved = 1
that in itself cant cause a problem. We need table dumps.Comment #8
jbrown CreditAttribution: jbrown commentedwebchick: what versions of mysql are you using on staging server vs. localhost?
Comment #9
jbrown CreditAttribution: jbrown commentedYes - dumps of these tables would be useful.
Comment #10
sirkitree CreditAttribution: sirkitree commentedThe localhost environment where this works:
PHP 5.2.6
mysql 5.0.41
Comment #11
webchickUm, wow! Thanks for all the offers to help! :D I really appreciate it!
I'm also getting this "zero rows returned" problem on my localhost, which is also MySQL 5.1.37. But what's interesting is if I dump the UR and URT tables and re-import them into a brand new database, even on my same localhost environment that has MySQL 5.1.37 and the "zero rows returned" issue in the Drupal database dump, that first query works fine. WTF?!
Well, this is definitely down to some sort of wack-ass environment issue... changing to a support request and "needs more info". I guess I'll go down the road of Googling weird-ass bugs that can cause MySQL to return 0 results when there clearly are results there to return.
Comment #12
webchickI'm going to close this; we found a workaround, even though I don't understand why on earth it works, and if I ever figure out what the root cause is, I'll come back and re-open this.