Whenever I click on the statistics link for ALL PROJECTS, I get this error. However, for individual projects it seems I'm error-free. I have pathauto installed but it's set to not create URLs for project pages as it was giving me problems with loosing project names each time I edited an issue... could this still be the culprit?

Thanks.

user warning: 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 'DISTINCT(n.nid), pn.title, p.sid, COUNT(n.nid) AS total FROM node n INNER JOIN p' at line 1 query: SELECT p DISTINCT(n.nid), pn.title, p.sid, COUNT(n.nid) AS total FROM node n INNER JOIN project_issues p ON n.nid = p.nid INNER JOIN node pn ON p.pid = pn.nid WHERE n.status = 1 AND pn.status = 1 GROUP BY pn.nid, p.sid, pn.title in /var/http/gaimtheory.com/includes/database.mysql.inc on line 172.

Comments

hunmonk’s picture

hunmonk commented
Status: Active » Postponed (maintainer needs more info)

i'm not able to reproduce this problem on either a local test install, or on drupal.org. this looks like an sql rewrite error. i would first try disabling any other contrib modules you have running, and see if the problem persists.

pxlpshr’s picture

pxlpshr commented

Contrib modules like BOOK? That's the only other thing I have running that could be constituted as a 'contrib' module... I thought it might have something to do with node_privacy_by_role, so I dupped the site on a local WAMP and removed it... didn't fix the issue and the same SQL error persisted.

hunmonk’s picture

hunmonk commented

you might try w/ a totally clean install, using just project/project_issue. every install that i've tried on my end works just fine. it's hard to fix a bug when you can't reproduce it :)

pxlpshr’s picture

pxlpshr commented

I understand... unfortunately, I don't have the time to be able to reinstall the site... is there anything else I can provide you that might make debugging easier?

Could it have anything to do with the fact that we modified the project_issue so that we can assign our team members to issues?

aclight’s picture

aclight commented

@pxlpshr

I agree with hunmonk that this looks like an SQL rewrite error--specifically, this part of the query looks weird:

SELECT p DISTINCT(n.nid)....

You said in your original post that you have pathauto installed but then in #2 you say you don't have any contribs--pathauto is a contrib module and has been reported to conflict with project/project_issue. There have also been reports of problems using project and book module together (see http://drupal.org/node/98278).

As hunmonk suggested, you'll need to either disable ALL contribs other than project and project_issue (or just start with a clean site) and go from there to see where the problem is coming from. But, if you want a potential quick fix, disable pathauto and see if you still have problems.

pxlpshr’s picture

pxlpshr commented

I didn't realize pathauto was a contrib module... I have fixed one of the issue with pathauto which caused the project name to drop anytime we editted an issue. I've done some more testing and have figured a few things out:

- if i'm logged in as administrator, I don't get the bug because I can view all the issues that are submitted just fine

- if i'm logged in as a group member (OG Project) and I click on statistics, the error is thrown because there are issues but this account can't see them... even though all the permissions are, as they appear, setup properly.

That said, I also have the issue where a group member can only see issues they've submitted... they can't see anything else... even though their Role Permissions are checked-off appropriately. I assume they should be able to see the project issues just like on Drupal.org.

i've removed node_privacy_by_role and rebuilt permissions... and that didn't seem to fix this problem either.

aclight’s picture

aclight commented

@pxlpshr

When we say disable contrib, we mean to disable all modules that are not part of Drupal core. OG Project, node_privacy_by_role, and pathauth are all contrib modules. From this recent information it sounds like OG Project could be the problem. Try disabling that first to see if the problem goes away. If not, disable all contrib modules other than project and project_issue and add one back at a time to see where the problem lies.

pxlpshr’s picture

pxlpshr commented

Aaahh... I need to edumicate myself on Drupal vernacular. ;) This was an issue prior to installing OG Project... I'll keep digging around, I have soo many additional modules... :(

Regarding the second issue I was having above: nevermind... any group member can see the posted issues now... (that was a bug i was having prior to installing OG Project)... now if i could just figure out this SQL issue. :( Thanks for ya'lls help thus far... I really appreciate everything ya'll do to keep this going.

I'm not as concerned about the bug because administrators can see the statistics fine without the error... and that's really all that matters at this point. However, it would be nice for it to go away. :P

pxlpshr’s picture

pxlpshr commented

Well,

I spent a good part of this early morning duplicating the site on a local box and completely removing all the contrib modules... nothing seemed to fix it... I remember reading an issue with pathauto and project issue due to similar naming conventions (pid?) from another thread so not only did I uninstall pathauto but I emptied the url alias table... so far nothing seems to fix it.

The error ONLY pops up whenever I view statistics for ALL the projects... but if I view statistics on a per project basis, the error is not displayed. Can't seem to figure this out... :(

With the exception of this tiny little glitch, everything else is running marvelous... I'm so thrilled with Drupal's flexibility and more specifically, Project/P.Issues for our developer extranet. Thanks again.

hunmonk’s picture

hunmonk commented

do you still have OG and OG Project installed? if so, disable those as well. neither are part of the project* suite.

pxlpshr’s picture

pxlpshr commented

I do have those installed but I need them to be installed... it's essential to our work flow unfortunately.

aclight’s picture

aclight commented

I do have those installed but I need them to be installed... it's essential to our work flow unfortunately.

hunmonk's point is that if you don't see the problem when the project and project_issue modules are the ONLY contributed modules you have enabled, then the problem is likely in some other contributed module. You'll never get your problem fixed unless the offending module can first be identified, so it's in your best interest to follow the instructions we're giving you regarding finding out which module is causing the problem.

If there is a bug in the project or project_issue modules, we'll work on fixing it at some point. But if the bug is not in one of those modules, you need to move this issue to the appropriate issue queue so that the maintainer of the offending module can first reproduce the problem and then hopefully fix it.

So, again, here are the steps you need to go through for us to investigate this problem further. These would ideally be done on a clean drupal 5.3 site with clean downloads of the project and project_issues modules.

1. Enable ONLY project.module and project_issue.module (and project_releases.module if you're using releases). Do NOT enable any other contrib modules (this includes OG, OG project, and pathauto). See if you still get the error.

2a. If yes, then give a complete description of what you are doing to get the error message. Provide the URL/path of the page on which the error is displaying (we don't need your whole URL, just the part that contains the drupal paths, like "/project/myproject/<something>".

2b. If no, then enable your contributed modules, ONE module at a time, and after enabling each module check to see if the error exists. If it does not, enable another module. Keep enabling modules until you get the error message.

3. Once you get the error message, disable as many contrib modules as you can (not including project* and the error causing module). Assuming that in step 2b you got the error message after enabling bad.module, ideally you would then go back and disable all contrib modules except for project.module, project_issues.module, project_releases.module, and bad.module. Then test to make sure that you still get the error message. If so, then move this issue to the queue for bad.module, whatever that is.

Given your responses to this issue so far, I get the impression that you're not reading or following the troubleshooting instructions hunmonk and I are giving you. If that's because you don't understand them please ask for more detailed instructions. But until you follow these instructions, we can't help you get your site fixed and error free because we don't know what the source of the problem is.

pxlpshr’s picture

pxlpshr commented

"I spent a good part of this early morning duplicating the site on a local box and completely removing all the contrib modules... nothing seemed to fix it... I remember reading an issue with pathauto and project issue due to similar naming conventions (pid?) from another thread so not only did I uninstall pathauto but I emptied the url alias table... so far nothing seems to fix it."

as you can see aclight, i have already done what you and hunmock have requested. i think their might be something else that's affecting it, unrelated to any of the modules... perhaps when I was doing some early testing a few months ago and installing/uninstalling a variety of modules... maybe something damaged the SQL db, I don't know.

I'm pretty much over it now but thanks for the help.

aclight’s picture

aclight commented

Comment #9 (pxlpshr):

I spent a good part of this early morning duplicating the site on a local box and completely removing all the contrib modules... nothing seemed to fix it... I remember reading an issue with pathauto and project issue due to similar naming conventions (pid?) from another thread so not only did I uninstall pathauto but I emptied the url alias table... so far nothing seems to fix it.

Comment #10 (hunmonk):

do you still have OG and OG Project installed? if so, disable those as well. neither are part of the project* suite.

Comment #11 (pxlpshr):

I do have those installed but I need them to be installed... it's essential to our work flow unfortunately.

From your response in #11 I assumed that you had not disabled all contributed modules since it sounded like you still had OG and OG Project enabled. I'm sorry if that wasn't the case.

If you do think that perhaps your database is damaged, the only real way to solve this is to start from scratch with a clean database. It sounds like you have a complicated site, and so it might be difficult to rebuild, but maybe you can start by just enabling the project* modules, and then start enabling the modules that have the greatest chance of being related to the problem you're reporting (OG, OG project, pathauto, etc.). If you still cant reproduce then maybe it is your database.

Good luck!

dww’s picture

dww
we/he/they
commented
Status: Postponed (maintainer needs more info) » Active

Steps to reproduce:
1) Start with a clean DB and install the d.o testing install profile (to get all of project*) http://drupal.org/project/drupalorg_testing
2) Enable *any* access control module. I just tried with http://drupal.org/project/simple_access and OG, both triggered it.
3) Rebuild node access permissions (visit admin/content/node-settings and hit the "Rebuild permissions" button).
4) Visit project/issues/statistics

I'm seeing the SQL error both in 4.7.x and 5.x.

pxlpshr’s picture

pxlpshr commented

I understand how you could have been confused ACLight... By no means am I trying to make ya'lls job difficult... I'm not a programmer so I'm just trying to provide as much information as I can. No hard feelings, I freakin love ya'lls dedication to everything-drupal. It's an amazing platform and Projects/Project Issues is icing on the cake; we're a game development studio. No hard feelings... I've torn apart the site I have the box duped on.

At one point, I did have node_privacy_by_role but I no longer use it and was certain I un-installed it properly. I've also tried rebuilding my permissions numerous times. That didn't seem to work. Right now we're not in a position to start over from scratch but once Drupal 6 launches, that will be the time to essentially start over.

Dww,

Is there a way I can manually clean up anything an access control module might have messed up? Also, you said OG triggers the error too... does that conclude that this is an OG Project issue and I should repost the bug there?

Best,
Colin

dww’s picture

dww
we/he/they
commented

@pxlpshr: no, this isn't specific to OG, and OG project has nothing to do with it. I suspect it's a bug in project_db_rewrite_sql() (a big nasty, complicated function) that only gets triggered when a per-node access control module (simple_access, node_privacy_by_role, og, etc) is enabled. This is a fairly common bug, in fact, since these access control modules almost always have their own db_rewrite_sql() implementations, and conflicts/clashes are both easy to introduce and hard to notice. In this case, project_db_rewrite_sql() works fine if it's the only game in town, but when other *_db_rewrite_sql() hook implementations get into the mix, things can go haywire on certain queries.

pxlpshr’s picture

pxlpshr commented

Well fud... I've double checked and confirmed there are no per-node access control modules enabled to my knowledge... I removed the only one I had (node_privacy_by_role) after discovering the flexibility of OG. At one point I had ACL and ACL Forum enabled but I disabled that... I think traces of it are also causing a big headache with the OG forums... meh. Perhaps the two are related.

Question: if I've uninstalled them, why does the error persist? Do those modules make hard-changes to mysql.inc ?

dww’s picture

dww
we/he/they
commented
Status: Active » Closed (duplicate)

Whoops, just noticed this is duplicate with http://drupal.org/node/172035 which more info. Let's move discussion there.