DELETE FROM Syntax in boost_crawler_prune_table

I support this patch. PostgreSQL cannot accept anything between DELETE and FROM.

Thank you.
Alexis

I'm not very familiar with the crawler, but it seems to me that the query is trying to delete from the {boost_crawler} table only, and not from the {boost_cache}. (whether it really matters, I don't know)

In pgsql, it might be better to use "delete from ONLY {boost_crawler} ...". c.f. http://www.postgresql.org/docs/8.1/static/sql-delete.html

Also, in your patch, you are changing the mysql statements as well, but that syntax is correct in mysql.

Can you test and re-roll?

Thanks

bgm,

If you cannot test with PostgreSQL, then you cannot make sure this is [in]correct...

I would be very surprised that the MySQL syntax is necessary. If you look at Drupal Core they never use it anywhere. I don't see why it would be necessary.

Of course, that's not a proof.

Thank you.
Alexis

grep DELETE ../../../modules/*/*
../../../modules/aggregator/aggregator.module:      db_query('DELETE FROM {aggregator_category} WHERE cid = %d', $edit['cid']);
../../../modules/aggregator/aggregator.module:      db_query("DELETE FROM {blocks} WHERE module = '%s' AND delta = '%s'", 'aggregator', 'category-' . $edit['cid']);
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_category_feed} WHERE fid = %d', $edit['fid']);
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_feed_terms} WHERE fid = %d', $edit['fid']);
../../../modules/aggregator/aggregator.module:      db_query('DELETE FROM {aggregator_category_item} WHERE '. implode(' OR ', $items));
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_feed} WHERE fid = %d', $edit['fid']);
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_item} WHERE fid = %d', $edit['fid']);
../../../modules/aggregator/aggregator.module:    db_query("DELETE FROM {blocks} WHERE module = '%s' AND delta = '%s'", 'aggregator', 'feed-' . $edit['fid']);
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_category_item} WHERE '. implode(' OR ', $items));
../../../modules/aggregator/aggregator.module:  db_query('DELETE FROM {aggregator_item} WHERE fid = %d', $feed['fid']);
../../../modules/aggregator/aggregator.module:      db_query('DELETE FROM {aggregator_category_item} WHERE iid IN ('. implode(', ', $items) .')');
../../../modules/aggregator/aggregator.module:      db_query('DELETE FROM {aggregator_item} WHERE fid = %d AND timestamp < %d', $feed['fid'], $age);
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_item} WHERE iid = %d', $edit['iid']);
../../../modules/aggregator/aggregator.module:    db_query('DELETE FROM {aggregator_category_item} WHERE iid = %d', $edit['iid']);
../../../modules/aggregator/aggregator.pages.inc:      db_query('DELETE FROM {aggregator_category_item} WHERE iid = %d', $iid);
../../../modules/block/block.admin.inc:    db_query("DELETE FROM {blocks_roles} WHERE module = '%s' AND delta = '%s'", $form_state['values']['module'], $form_state['values']['delta']);
../../../modules/block/block.admin.inc:  db_query('DELETE FROM {boxes} WHERE bid = %d', $form_state['values']['bid']);
../../../modules/block/block.admin.inc:  db_query("DELETE FROM {blocks} WHERE module = 'block' AND delta = '%s'", $form_state['values']['bid']);
../../../modules/block/block.module:      db_query("DELETE FROM {blocks} WHERE module = '%s' AND delta = '%s' AND theme = '%s'", $module, $delta, $theme);
../../../modules/book/book.install:  db_query("DELETE FROM {menu_links} WHERE module = 'book'");
../../../modules/book/book.module:        db_query('DELETE FROM {book} WHERE mlid = %d', $node->book['mlid']);
../../../modules/book/book.pages.inc:    db_query('DELETE FROM {book} WHERE nid = %d', $node->nid);
../../../modules/comment/comment.admin.inc:  db_query('DELETE FROM {comments} WHERE cid = %d', $comment->cid);
../../../modules/comment/comment.module:      db_query('DELETE FROM {comments} WHERE nid = %d', $node->nid);
../../../modules/comment/comment.module:      db_query('DELETE FROM {node_comment_statistics} WHERE nid = %d', $node->nid);
../../../modules/contact/contact.admin.inc:  db_query("DELETE FROM {contact} WHERE cid = %d", $contact['cid']);
../../../modules/dblog/dblog.module:  db_query('DELETE FROM {watchdog} WHERE wid <= %d', $max - variable_get('dblog_row_limit', 1000));
../../../modules/filter/filter.admin.inc:  db_query("DELETE FROM {filters} WHERE format = %d", $format);
../../../modules/filter/filter.admin.inc:  db_query("DELETE FROM {filter_formats} WHERE format = %d", $form_state['values']['format']);
../../../modules/filter/filter.admin.inc:  db_query("DELETE FROM {filters} WHERE format = %d", $form_state['values']['format']);
../../../modules/forum/forum.module:          db_query('DELETE FROM {forum} WHERE nid = %d', $node->nid);
../../../modules/forum/forum.module:      db_query('DELETE FROM {forum} WHERE nid = %d', $node->nid);
../../../modules/locale/locale.install:  $ret[] = update_sql("DELETE FROM {locales_target} WHERE translation = ''");
../../../modules/locale/locale.install:  $ret[] = update_sql("DELETE FROM {locales_source} WHERE lid NOT IN (SELECT lid FROM {locales_target})");
../../../modules/menu/menu.admin.inc:  db_query("DELETE FROM {menu_links} WHERE menu_name = '%s'", $menu['menu_name']);
../../../modules/menu/menu.admin.inc:  db_query("DELETE FROM {menu_custom} WHERE menu_name = '%s'", $menu['menu_name']);
../../../modules/menu/menu.admin.inc:  db_query("DELETE FROM {blocks} WHERE module = 'menu' AND delta = '%s'", $menu['menu_name']);
../../../modules/menu/menu.admin.inc:  db_query("DELETE FROM {blocks_roles} WHERE module = 'menu' AND delta = '%s'", $menu['menu_name']);
../../../modules/node/node.module:  db_query('DELETE FROM {history} WHERE timestamp < %d', NODE_NEW_LIMIT);
../../../modules/node/node.module:  db_query("DELETE FROM {node_type} WHERE type = '%s'", $type);
../../../modules/node/node.module:    db_query('DELETE FROM {node} WHERE nid = %d', $node->nid);
../../../modules/node/node.module:    db_query('DELETE FROM {node_revisions} WHERE nid = %d', $node->nid);
../../../modules/node/node.module:    db_query('DELETE FROM {node_access} WHERE nid = %d', $node->nid);
../../../modules/node/node.module:    $query = 'DELETE FROM {node_access} WHERE nid = %d';
../../../modules/node/node.module:  db_query("DELETE FROM {node_access}");
../../../modules/node/node.pages.inc:  db_query("DELETE FROM {node_revisions} WHERE nid = %d AND vid = %d", $node_revision->nid, $node_revision->vid);
../../../modules/openid/openid.module:  db_query("DELETE FROM {openid_association} WHERE created + expires_in < %d", time());
../../../modules/openid/openid.module:  db_query("DELETE FROM {openid_nonce} WHERE expires < %d", time());
../../../modules/openid/openid.pages.inc:  db_query("DELETE FROM {authmap} WHERE uid = %d AND aid = %d AND module = 'openid'", $form_state['values']['uid'], $form_state['values']['aid']);
../../../modules/path/path.module:  db_query('DELETE FROM {url_alias} WHERE pid = %d', $pid);
../../../modules/path/path.module:      db_query('DELETE FROM {url_alias} WHERE pid = %d', $pid);
../../../modules/path/path.module:      db_query("DELETE FROM {url_alias} WHERE dst = '%s'", $alias);
../../../modules/path/path.module:      db_query("DELETE FROM {url_alias} WHERE src = '%s'", $path);
../../../modules/poll/poll.module:  db_query('DELETE FROM {poll_choices} WHERE nid = %d', $node->nid);
../../../modules/poll/poll.module:      db_query("DELETE FROM {poll_votes} WHERE nid = %d AND chorder = %d", $node->nid, $old_chorder);
../../../modules/poll/poll.module:  db_query("DELETE FROM {poll} WHERE nid = %d", $node->nid);
../../../modules/poll/poll.module:  db_query("DELETE FROM {poll_choices} WHERE nid = %d", $node->nid);
../../../modules/poll/poll.module:  db_query("DELETE FROM {poll_votes} WHERE nid = %d", $node->nid);
../../../modules/poll/poll.module:    db_query('DELETE FROM {poll_votes} WHERE nid = %d and uid = %d', $node->nid, $user->uid);
../../../modules/poll/poll.module:    db_query("DELETE FROM {poll_votes} WHERE nid = %d and hostname = '%s'", $node->nid, ip_address());
../../../modules/profile/profile.admin.inc:  db_query('DELETE FROM {profile_fields} WHERE fid = %d', $form_state['values']['fid']);
../../../modules/profile/profile.admin.inc:  db_query('DELETE FROM {profile_values} WHERE fid = %d', $form_state['values']['fid']);
../../../modules/profile/profile.module:      db_query('DELETE FROM {profile_values} WHERE uid = %d', $user->uid);
../../../modules/profile/profile.module:    db_query("DELETE FROM {profile_values} WHERE fid = %d AND uid = %d", $field->fid, $user->uid);
../../../modules/search/search.module:    db_query("DELETE FROM {search_dataset} WHERE sid = %d AND type = '%s'", $sid, $type);
../../../modules/search/search.module:    db_query("DELETE FROM {search_index} WHERE sid = %d AND type = '%s'", $sid, $type);
../../../modules/search/search.module:      db_query("DELETE FROM {search_node_links} WHERE sid = %d AND type = '%s'", $sid, $type);
../../../modules/search/search.module:    db_query("DELETE FROM {search_total} WHERE word = '%s'", $word->realword);
../../../modules/search/search.module:    db_query("DELETE FROM {search_node_links} WHERE sid = %d AND type = '%s' AND nid = %d", $sid, $type, $nid);
../../../modules/statistics/statistics.module:    db_query('DELETE FROM {accesslog} WHERE timestamp < %d', time() - variable_get('statistics_flush_accesslog_timer', 259200));
../../../modules/statistics/statistics.module:      db_query('DELETE FROM {node_counter} WHERE nid = %d', $node->nid);
../../../modules/system/system.admin.inc:   'Com_delete' => t('The number of DELETE-statements.'),
../../../modules/system/system.install:      $ret[] = update_sql("DELETE FROM {menu_links} WHERE menu_name IN ('". implode("', '", $_SESSION['menu_bogus_menus']) ."')");
../../../modules/system/system.install:        $ret[] = update_sql('DELETE FROM {search_dataset}');
../../../modules/system/system.install:        $ret[] = update_sql('DELETE FROM {search_index}');
../../../modules/system/system.install:        $ret[] = update_sql('DELETE FROM {search_total}');
../../../modules/system/system.install:  $ret[] = update_sql("DELETE FROM {term_node} WHERE vid = 0");
../../../modules/system/system.module:  db_query("DELETE FROM {system} WHERE type = 'theme'");
../../../modules/system/system.module:  db_query('DELETE FROM {flood} WHERE timestamp < %d', time() - 3600);
../../../modules/system/system.module:  db_query('DELETE FROM {batch} WHERE timestamp < %d', time() - 864000);
../../../modules/system/system.module:    db_query('DELETE FROM {files} WHERE fid = %d', $file->fid);
../../../modules/taxonomy/taxonomy.module:    db_query("DELETE FROM {vocabulary_node_types} WHERE vid = %d", $edit['vid']);
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {vocabulary} WHERE vid = %d', $vid);
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {vocabulary_node_types} WHERE vid = %d', $vid);
../../../modules/taxonomy/taxonomy.module:  return SAVED_DELETED;
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {term_relation} WHERE tid1 = %d OR tid2 = %d', $form_values['tid'], $form_values['tid']);
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {term_hierarchy} WHERE tid = %d', $form_values['tid']);
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {term_synonym} WHERE tid = %d', $form_values['tid']);
../../../modules/taxonomy/taxonomy.module:      db_query('DELETE FROM {term_data} WHERE tid = %d', $tid);
../../../modules/taxonomy/taxonomy.module:      db_query('DELETE FROM {term_hierarchy} WHERE tid = %d', $tid);
../../../modules/taxonomy/taxonomy.module:      db_query('DELETE FROM {term_relation} WHERE tid1 = %d OR tid2 = %d', $tid, $tid);
../../../modules/taxonomy/taxonomy.module:      db_query('DELETE FROM {term_synonym} WHERE tid = %d', $tid);
../../../modules/taxonomy/taxonomy.module:      db_query('DELETE FROM {term_node} WHERE tid = %d', $tid);
../../../modules/taxonomy/taxonomy.module:  return SAVED_DELETED;
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {term_node} WHERE nid = %d', $node->nid);
../../../modules/taxonomy/taxonomy.module:  db_query('DELETE FROM {term_node} WHERE vid = %d', $node->vid);
../../../modules/taxonomy/taxonomy.module:    db_query("DELETE FROM {vocabulary_node_types} WHERE type = '%s'", $info->type);
../../../modules/trigger/trigger.admin.inc:    db_query("DELETE FROM {trigger_assignments} WHERE hook = '%s' AND op = '%s' AND aid = '%s'", $form_values['hook'], $form_values['operation'], $aid);
../../../modules/trigger/trigger.admin.inc:        db_query("DELETE FROM {trigger_assignments} WHERE hook = '%s' AND op = '%s' AND aid = 'node_save_action'", $form_values['hook'], $form_values['operation']);
../../../modules/trigger/trigger.module:  db_query("DELETE FROM {trigger_assignments} WHERE aid = '%s'", $aid);
../../../modules/update/update.module:    db_query("DELETE FROM {cache_update} WHERE cid = '%s'", $cid);
../../../modules/upload/upload.module:      db_query('DELETE FROM {upload} WHERE fid = %d AND vid = %d', $fid, $node->vid);
../../../modules/upload/upload.module:        db_query('DELETE FROM {files} WHERE fid = %d', $fid);
../../../modules/upload/upload.module:    db_query('DELETE FROM {files} WHERE fid = %d', $fid);
../../../modules/upload/upload.module:  db_query('DELETE FROM {upload} WHERE nid = %d', $node->nid);
../../../modules/upload/upload.module:        db_query('DELETE FROM {files} WHERE fid = %d', $file->fid);
../../../modules/upload/upload.module:  db_query('DELETE FROM {upload} WHERE vid = %d', $node->vid);
../../../modules/user/user.admin.inc:      db_query('DELETE FROM {permission} WHERE rid = %d', $role->rid);
../../../modules/user/user.admin.inc:    db_query('DELETE FROM {role} WHERE rid = %d', $form_state['values']['rid']);
../../../modules/user/user.admin.inc:    db_query('DELETE FROM {permission} WHERE rid = %d', $form_state['values']['rid']);
../../../modules/user/user.admin.inc:    db_query('DELETE FROM {users_roles} WHERE rid = %d', $form_state['values']['rid']);
../../../modules/user/user.admin.inc:  db_query('DELETE FROM {access} WHERE aid = %d', $form_state['values']['aid']);
../../../modules/user/user.module:      db_query('DELETE FROM {users_roles} WHERE uid = %d', $account->uid);
../../../modules/user/user.module:      db_query('DELETE FROM {users_roles} WHERE uid = %d', $array['uid']);
../../../modules/user/user.module:      db_query("DELETE FROM {authmap} WHERE uid = %d AND module = '%s'", $account->uid, $module[1]);
../../../modules/user/user.module:  db_query('DELETE FROM {users} WHERE uid = %d', $uid);
../../../modules/user/user.module:  db_query('DELETE FROM {users_roles} WHERE uid = %d', $uid);
../../../modules/user/user.module:  db_query('DELETE FROM {authmap} WHERE uid = %d', $uid);

Thanks for the patch, committed to 6.x-1.x. I also found a related issue, for reference: #888816: Postgresql error in reports while prune_tables of the boot_crawler.

As for the conditions on that query, I agree, it makes no sense. From what I understand of this part of code, it would make the crawler restart from zero every time cron is run. I'll leave the issue open (even if it's a tangent issue, but it may not really be a bug) and see if Mike has feedback on this.

(There are plans to rework the crawler by using httprl on a future 6.x-2.x branch, to make the crawler less buggy and the code easier to maintain, but it needs people to step up and work on it.)

@ Alexis: check the MySQL docs. If you "delete from X left join Y", it will delete the resulting entries from both tables. So "delete X from X left join Y" will delete only X. In the examples you provided from Drupal core, all those queries are on only one table. I spend a lot of time in the issue queue, so I appreciate it when other people test and provide clean patches (ideally issues should be RTBC before being committed).

bgm,

Interesting... another good reason for me not to use MySQL. Although I very rarely find the need to use JOINs anyway. There's often a "better" solution.

PostgreSQL has an ONLY keyword, maybe MySQL would support that? Although it is not use for the exact same purpose. Would need to test to make sure that it works as expected in both DBs.

file:///usr/share/doc/postgresql-doc-8.4/html/sql-delete.html

Thank you for the info! (and for fixing the problem.)
Alexis

Closing issue. Thanks all.