Every time we deleted a node (with spam module installed), we were getting "unknown table 'spam_tracker'" out of the trackback_nodeapi() hook.
I imagine that if this were commonly occurring, somebody would've noticed it by now, since deletes can't be that rare an operation. So, more information in case some can't replicate it:
PHP 5.1.6
MySQL 5.0.27-standard-log
running Trackback and Spam, both 5.x dev builds (currently identical to HEAD builds)
The problem was fixed, oddly enough, by taking out the table aliases from the SQL DELETE in the nodeapi() delete case.
- db_query("DELETE FROM {spam_tracker} USING ({spam_tracker} s, {trackback_received} tr) WHERE s.source='trackback' AND s.id=tr.trid AND tr.nid=%d", $node->nid);
+ db_query("DELETE FROM {spam_tracker} USING ({spam_tracker}, {trackback_received}) WHERE {spam_tracker}.source='trackback' AND {spam_tracker}.id={trackback_received}.trid AND {trackback_received}.nid=%d", $node->nid);
If anyone knows why, or (less likely, I imagine) can confirm this in the general case, I'd appreciate it. As it stands, I imagine *someone* will run into this as well, so posting a patch file.
| Comment | File | Size | Author |
|---|---|---|---|
| trackback.HEAD_.r0.patch | 921 bytes | gnassar |
Comments
Comment #1
zorac commentedI commited this patch. Thank you.
Comment #2
gnassar commentedYou're welcome.
I still don't know offhand why the aliases would've been a problem; any idea?
Comment #3
zorac commentedI don't know too. MySQL 4.0 don't have this problem. Perhaps it is a bug ;-)
The DELETE USING statement is not standard SQL. I want to rewrite it by other way at the earliest opportunity.
Comment #4
gnassar commentedYou know, that's a good idea. I wouldn't be half surprised if aliases started working again if we rewrote the DELETE statement to make it ANSI-compliant:
- db_query("DELETE FROM {spam_tracker} USING ({spam_tracker}, {trackback_received}) WHERE {spam_tracker}.source='trackback' AND {spam_tracker}.id={trackback_received}.trid AND {trackback_received}.nid=%d", $node->nid);
+ db_query("DELETE FROM {spam_tracker} WHERE {spam_tracker}.source='trackback' AND EXISTS (SELECT * FROM {trackback_received} WHERE {spam_tracker}.id={trackback_received}.trid AND {trackback_received}.nid=%d)", $node->nid);
Does that look about right? I only have a couple of minutes here -- can't even roll a patch, or try this out -- but off the top of my head, I think that's right. It would also be interesting to try it with aliases at that point:
+ db_query("DELETE FROM {spam_tracker} s WHERE s.source='trackback' AND EXISTS (SELECT * FROM {trackback_received} tr WHERE s.id=tr.trid AND tr.nid=%d)", $node->nid);
I may have a chance to try this in the next few days; if you get to it before I do, let me know how it works out.
Comment #5
gnassar commentedOn second thought, I don't think that line with aliases is right: I don't think ANSI lets you put an alias in a DELETE statement's FROM. So it should probably be:
+ db_query("DELETE FROM {spam_tracker} WHERE {spam_tracker}.source='trackback' AND EXISTS (SELECT * FROM ({spam_tracker} s, {trackback_received} tr) WHERE s.id=tr.trid AND tr.nid=%d)", $node->nid);
Which I'm not sure is all that much cleaner than just not using aliases altogether, maybe.
Comment #6
zorac commentedGood!
But EXISTS and sub-query is not supported by MySQL4.0 :-<
Comment #7
gnassar commentedAww, crud. You're right. 4.1+ only. And Drupal claims support for MySQL back to 3.2, doesn't it? It boggles my mind that we'd support a version of MySQL that wasn't even considered production-worthy. First production release was 4.1, after all.
Fortunately, Drupal 6 drops support for MySQL before 4.1, so as soon as that's in RC, and you get to create a DRUPAL-6 branch, we can use standard ANSI syntax ;-).
Comment #8
(not verified) commented