orphaned project issues

i wonder if this is because the video project doesn't belong to the "Modules" category:
http://drupal.org/project/video has it listed as just belonging to "media". webchick reminds us to make sure our modules are classified in both "modules" and whatever else makes sense. just a thought.

I tried adding video to the "Modules" category, but it doesn't fix the orphaned issues problem. This might require some SQL magic from killes. :\

webchick just added video to the "Modules" category. so, now you can file new issues against video. unfortunately, the orphaned issues mentioned above are still orphaned. :( no other ideas about that (yet). sorry.

re: "modules missing in issue dropdown select", i think that's trivially solved by adding the project in question to the "Modules" category. if you find other projects that are missing this, make a new issue in drupal.org maintainance.

however, this thread has exposed a much more nasty problem. killes was kind enough to furnish me with a copy of the node and project_issues tables from drupal.org. turns out there are 56 project issues where the pid (project id) contains a number which is *not* a node on the system. that's why it appears that the pid was empty... it was there, just pointing to something that doesn't exist. further sql hackery shows that all 56 project issues correspond to 10 unique bogus nids. my best guess at this point is that somehow, the nid for a given project's page was changed. i have no idea how that could have happened. :(

the next step to debug this would be to run some of these SQL queries against a copy of a DB dump from before the 4.7 upgrade, and other points in history, to see if we can find the point where these things changed. that might help narrow it down. ugh. anyway, here are the queries and the results:

SELECT p.nid, p.pid FROM project_issues p LEFT JOIN node n ON p.pid = n.nid WHERE n.nid IS NULL ORDER BY p.nid;

yields:

+-------+-------+
| nid   | pid   |
+-------+-------+
|  1320 |  3043 |
|  1337 |  3347 |
|  1498 |  3043 |
|  1532 |  3043 |
|  1533 |  3043 |
|  1579 |  3043 |
|  1583 |  3043 |
|  1664 |  3043 |
|  1706 |  3043 |
|  1772 |  3043 |
|  1793 |  3043 |
|  1829 |  3043 |
|  2346 |  3043 |
|  2438 |  3043 |
|  6916 |     0 |
|  8133 |  6709 |
|  8378 |  6709 |
|  8466 |  9992 |
|  8518 |  6709 |
|  8601 |  6709 |
|  8925 |  9992 |
|  9060 |  6709 |
|  9803 |  8091 |
|  9969 |  9992 |
|  9990 |  9992 |
| 10220 |  4836 |
| 10411 |  9992 |
| 10578 |  9992 |
| 10674 |  9992 |
| 10832 |  6709 |
| 10962 |  9992 |
| 11034 |  6709 |
| 11125 |     0 |
| 11127 |     0 |
| 11364 |  9992 |
| 11376 |  9992 |
| 11409 |  6709 |
| 11421 |  9992 |
| 11476 |  9992 |
| 11589 |  9992 |
| 11857 |  6709 |
| 12570 |  8091 |
| 12615 |  8091 |
| 13165 | 13122 |
| 14111 | 11650 |
| 16035 | 15814 |
| 16274 | 15814 |
| 16331 | 15814 |
| 16396 | 15814 |
| 16757 | 15814 |
| 16896 | 15814 |
| 16897 | 15814 |
| 32504 |     0 |
| 33413 |     0 |
| 33461 |     0 |
| 55744 |     0 |
+-------+-------+
56 rows in set (0.83 sec)

or, to find the unique pid's where this is a problem:

SELECT DISTINCT p.pid FROM project_issues p LEFT JOIN node n ON p.pid = n.nid WHERE n.nid IS NULL ORDER BY p.nid;

which gives:

+-------+
| pid   |
+-------+
|  3347 |
|  3043 |
|     0 |
|  6709 |
|  9992 |
|  8091 |
|  4836 |
| 13122 |
| 11650 |
| 15814 |
+-------+
10 rows in set (0.53 sec)

oh and of course, it's entirely possible there's a bug in project.module that's causing this. however, one weird thing is that a brief survey of the above issues shows that some of them were updated very recently, while others haven't been touched in years. so, i don't think it's a new bug in project that's randomly orphaning issues as people try to follow-up. i still don't have a good theory as to what's causing this. i'd most like to see the above queries run against the DB from right before the 4.7 upgrade, and see what those look like...

I am not really interested in finding out at which point in time something failed. I'd appreciate if I could get a set of sql commands to fix the current problem.

I suggest we fix the current issue and then monitor if the error occurs again.

I'd appreciate if I could get a set of sql commands to fix the current problem.

unless there's some way to easily figure out what projects those 10 bogus pids correspond to (and the ones marked as 0 are clearly going to be a problem), there's no SQL query to automatically fix this. we need someone to first figure out if all of the issues that go with each bogus pid belong to the same project (a random sampling would probably be good enough for me). if so, we just need to know what the current nid of the real project is. then, we could do something like this for each of the 10 bogus pids:

INSERT INTO project_isses SET pid = [new_good_value] WHERE pid = [old_bad_value]

if all issues with the same bogus pid don't belong to the same project, then for all 56 issues, someone just has to figure out what project each should belong to. at that point, it's probably easier (especially since anyone can do it, not just someone w/ direct DB write access) to re-attach the issues by a new follow-up that manually sets the project field (like i did in /node/38313#comment-86010. the only concern here is if a project doesn't belong to the "Modules" category, then it won't be in the drop-down menu. however, that's a problem anyway, and needs to be fixed, regardless of this bug.

i was hoping to do a little historical investigation to help figure out when/why this happened, to attempt to find the underlying problem. but, i understand if there are bigger fish to fry, and folks just want to fix the current badness and see if it ever crops up again. sadly, it appears that this will require a bunch of manual effort, so i'd recommend some of the drupal.org site admins + documentation folks who aren't particularly strong coders could take this to the next step: find out what projects those issues belong to...

-derek

p.s. here's the list, sorted by pid, not nid, which will make this task easier. obviously, a few of the pids only show up once or twice, so those should just be fixed manually, instead of messing with the SQL solution.

+-------+-------+
| nid   | pid   |
+-------+-------+
|  6916 |     0 |
| 11125 |     0 |
| 11127 |     0 |
| 32504 |     0 |
| 33413 |     0 |
| 33461 |     0 |
| 55744 |     0 |
|  1829 |  3043 |
|  1772 |  3043 |
|  1320 |  3043 |
|  1498 |  3043 |
|  1532 |  3043 |
|  1533 |  3043 |
|  1579 |  3043 |
|  1583 |  3043 |
|  1664 |  3043 |
|  1706 |  3043 |
|  1793 |  3043 |
|  2346 |  3043 |
|  2438 |  3043 |
|  1337 |  3347 |
| 10220 |  4836 |
|  8133 |  6709 |
|  8378 |  6709 |
|  8518 |  6709 |
|  8601 |  6709 |
|  9060 |  6709 |
| 10832 |  6709 |
| 11034 |  6709 |
| 11409 |  6709 |
| 11857 |  6709 |
|  9803 |  8091 |
| 12570 |  8091 |
| 12615 |  8091 |
|  8466 |  9992 |
| 11589 |  9992 |
|  8925 |  9992 |
|  9969 |  9992 |
|  9990 |  9992 |
| 10411 |  9992 |
| 10578 |  9992 |
| 10674 |  9992 |
| 10962 |  9992 |
| 11364 |  9992 |
| 11376 |  9992 |
| 11421 |  9992 |
| 11476 |  9992 |
| 14111 | 11650 |
| 13165 | 13122 |
| 16035 | 15814 |
| 16274 | 15814 |
| 16331 | 15814 |
| 16396 | 15814 |
| 16757 | 15814 |
| 16896 | 15814 |
| 16897 | 15814 |
+-------+-------+
56 rows in set (0.36 sec)

this just happened again in http://drupal.org/node/64221 when heine replied to an issue about the project module i was fixing. just wanted to bump this issue again. sadly, heine and i couldn't reproduce this, so we're not sure what caused it. i posted a little bit more info about what he did into the end of #64221 for the interested reader...

woo hoo! heine found the bug in project that's causing this (issue.inc, ~line 597 these days).

if the node object happens to not have a pid field for some reason, project's code confuses the nid of the issue with the nid of the underlying project, and attaches the project to a bogus pid, which is what screws up the issue.

however, i'm not yet going to move this to the project issue queue, since there are still all of these orphaned issues i pointed out from my previous DB hackery (based on SQL dumps killes sent me months ago) which are definitely a maintainance issue...

anyway, i'll work on a fix for the underlying bug and upload a patch here when i get the chance (unless heine does it first).

so glad to finally know the cause of this. looks like this bug has been around since 2003 (revision 1.28). ;)

Closing this... if there haven't been any 'me too' replies since June 2006, it's probably safe to say that it's been fixed.