I've got a custom view which uses usernode fields, filters and exposed filters.
This view works fine most of the time. However, once a while when I's loaded, the table view itself isn't loaded (some of the exposed filters are) and I get this SQL error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= ',,') GROUP BY node.nid ORDER BY profile_initials_value ASC LIMIT 0, 50' at line 1 query: 

SELECT DISTINCT(node.nid), profile_initials.value AS profile_initials_value, profile_name_between.value AS profile_name_between_value, profile_surname.value AS profile_surname_value, profile_gender.value AS profile_gender_value, profile_birth_date.value AS profile_birth_date_value, usernode_users.name AS usernode_users_name, usernode_users.uid AS usernode_users_uid, usernode_users_roles.rid AS usernode_users_roles_rid, usernode_users_roles.uid AS usernode_users_roles_uid FROM node node INNER JOIN usernode usernode ON node.nid = usernode.nid LEFT JOIN users_roles usernode_users_roles ON usernode.uid = usernode_users_roles.uid LEFT JOIN users usernode_users ON usernode.uid = usernode_users.uid WHERE (node.type IN ('usernode')) AND (usernode_users_roles.rid IN ('2','9','5','6','4')) AND (.value = ',,') GROUP BY node.nid ORDER BY profile_initials_value ASC LIMIT 0, 50

As a temporary workaround, I manually enforced the views cache to be cleared on this particular view, but that doesn't prevent that sometimes you get a partially white screen. After a reload it works fine again.

Any ideas? I would be happy to follow any pointers or provide more information.

Comments

askibinski’s picture

After looking more closely, it seems the part "AND (.value = ',,')" should not be in the query and causes the error. But it only occurs 'sometimes' ... no clue why, since it's the same view with the same arguments, filters etc.
When an error occurs, the views cache has to be cleared. And then still, there is a chance the next query will be invalid...

I'm wondering if this is related to problems with DISTINCT queries? I've read there are issues with these.
I also wonder if the order in which filters are applied might cause this weird unpredictable behaviour.

askibinski’s picture

Allright, I think I solved it.

Here are two related issues:
http://drupal.org/node/186104
http://drupal.org/node/167889

The problem was within the privacy settings of the profile fields. These were set on 'private' which (I guess) somehow caused problems with visitors who didn't have enough permission. When I changed the privacy settings to public (which doesn't mean everybody can access them), the problem was gone. The fact it only occurred sometimes (and only on the live-site) can only be explained by visitors who triggered this.

It's still a bit strange though, because the view which kept on breaking is only accessible for admins.

I'm not sure whether this is a bug which arises in combination with usernode/views and profile module or if it is 'by design', but I'm glad it's gone.

Pomliane’s picture

Status: Active » Closed (won't fix)

This version of Usernode is not supported anymore. The issue is closed for this reason.
Please upgrade to a supported version and feel free to reopen the issue on the new version if applicable.

This issue has been automagically closed by a script.