records not removed from project_releases (and other tables) when project nodes are deleted

could be. i noticed that code and was wondering what it was about. ;) so, do you agree with this patch? is it RTBC?

committed to head, 4.7 and 4.6.

the drupal.org DB still has stale records, so i created http://drupal.org/node/67934.

maybe i should make a project_update_N() hook to delete stale records from these tables in case any other sites have this problem? i guess that'd be an easy way to resolve #67934, too. ;)

attached patch for project_update_3() works fine on a local test site running MySQL. i have no idea if this is valid in pgsql or not, and i don't have access to my forthcoming pgsql test site yet. :( this obviously needs serious review before i commit it and have killes or dries run it. ;)

also, it'd be nice to propagate to the results page how many rows were deleted with each query. anyone know how to do that?

thanks,
-derek

this might be kind of a hack, but it works to display how many rows we're deleting with each query... maybe update.php should do this automatically for update_sql()? or update_sql() should take an optional arg to indicate you want to see this? i guess i should file that as a separate issue against core. ;)

for the interested reader, on my test site, this produces output like this:

<b>Update #3</b>
<ul>
<li>DELETE p FROM {project_releases} p LEFT JOIN {node} n ON p.nid = n.nid WHERE n.nid IS NULL
<li>5 rows deleted
<li>DELETE p FROM {project_comments} p LEFT JOIN {node} n ON p.nid = n.nid WHERE n.nid IS NULL
<li>0 rows deleted
<li>DELETE p FROM {project_subscriptions} p LEFT JOIN {node} n ON p.nid = n.nid WHERE n.nid IS NULL
<li>4 rows deleted
</ul>

i'm curious how much of a problem this really is on drupal.org. can anyone run the read-only versions manually?

SELECT p.nid FROM project_releases p LEFT JOIN node n ON p.nid = n.nid WHERE n.nid IS NULL;
SELECT p.nid FROM project_comments p LEFT JOIN node n ON p.nid = n.nid WHERE n.nid IS NULL;
SELECT p.nid FROM project_subscriptions p LEFT JOIN node n ON p.nid = n.nid WHERE n.nid IS NULL;

good news: i finally have a pgsql test site setup on my laptop. :) bad news: #7 is definitely broken in pgsql. :(
i'll see if i can find a simple way to accomplish the same thing in pgsql.

woo hoo. in IRC, halkeye clued me into a syntax that works on both mysql and pgsql for this. ;) new patch that works everywhere. it also is nice enough to show you how many rows were deleted. i think this is RTBC, and RTB run on d.o. however, someone should review, since this is clearly a very important update to get right. ;)

thanks,
-derek

argh, apparently subselects are only in mysql 4.1.x, and d.o is still running 4.0.

new version that figures out if we can use subselects. if so, we do the simple thing. if not, we do a separate LEFT JOIN to find all the nids we want to delete from each table, then do a DELETE with a WHERE clause constructed by implode()'ing the array. ok, *this* should hopefully do the trick. ;)

killes said this was RTBC in IRC. applied to HEAD and 4.7. phew! glad to have this complete. ;)