turn subquery into join in the cron

This change will not work on MySQL 4.0, as it doesn't support sub-queries. Since Drupal 5 still supports MySQL 4.0, it would be good if this module did, too.

Still doesn't work in MySQL 4.0 in 5.x-1.2. According to http://dev.mysql.com/doc/refman/4.1/en/delete.html, MySQL 4.0 and 4.1 use different syntax when table aliases are involved. It'd probably be better to not use aliases at all:

DELETE masquerade FROM masquerade LEFT JOIN sessions AS s ON masquerade.sid = s.sid WHERE s.sid IS NULL;

Nevermind, the problem I'm having is unrelated to this.

OK. The problem I ran into after upgrading is described in #285472: Parse error with PHP 4.

Now that I've fixed it, I do get the following error when running cron, though:

user warning: Not unique table/alias: 'm' query: DELETE m FROM masquerade AS m LEFT JOIN sessions AS s ON m.sid = s.sid WHERE s.sid IS NULL in /var/home/team/volunteers/public_html/includes/database.mysql.inc on line 172.

Your latest suggested query seems to fix it.

Unfortunately the solution that was used here does not work
in PostgreSQL as the syntax for DELETE with joins is also
different than that of MySQL.

The best solution for portability is to use subqueries as was
suggested. It's true that Drupal 5 still supports MySQL 4.0
and lower which do not have subqueries but Drupal 6 requires
MySQL 4.1 which does support subqueries.

So any chance the query can be reverted to use subqueries
in the Drupal 6 version of masquerade so that PostgreSQL
users and MySQL users both run w/o error?

Yep my PostgreSQL system is banging its head against this error every time Cron runs. Annoying. My fix is attached for 5.x 1.3.

Just missing a closing paren...

  db_query('DELETE FROM {masquerade} WHERE sid NOT IN (SELECT s.sid FROM {sessions} AS s');

should be

  db_query('DELETE FROM {masquerade} WHERE sid NOT IN (SELECT s.sid FROM {sessions} AS s)');

Any chance of getting this fix in the Drupal 5 branch? I made the modification manually to my local copy of 5.x-1.3 and it works flawlessly on Postgres. It looks like the patch here will apply cleanly to 5.x-1.3.

Original code was this:

function masquerade_cron() {
  // see http://drupal.org/node/268487 before modifying this query
  db_query('DELETE {masquerade} FROM {masquerade} LEFT JOIN {sessions} ON {masquerade}.sid = {sessions}.sid WHERE {sessions}.sid IS NULL
');
}

which did not work with Postgres. I changed it to this:

function masquerade_cron() {
  // see http://drupal.org/node/268487 before modifying this query
  if (in_array($GLOBALS['db_type'], array('mysql', 'mysqli'))) {
    db_query('DELETE {masquerade} FROM {masquerade} LEFT JOIN {sessions} ON {masquerade}.sid = {sessions}.sid WHERE
 {sessions}.sid IS NULL');
  } elseif (in_array($GLOBALS['db_type'], array('pgsql'))) {
    db_query('DELETE FROM {masquerade} WHERE {masquerade}.sid NOT IN ( SELECT {sessions}.sid FROM {sessions} )');
  }
}

and it now works properly for me.

Thanks, I'll give it a try!

Shouldn't that query check the timestamp too instead of selecting all sid's from the sessions table? Like, you should only look back 24 hours before destroying the masquerade session, maybe less.

I am trying to pin down why PHP runs out of memory when cron runs and saw this hook.

**edit**

CAPTCHA employs this in its cron:

function captcha_cron() {
  // remove challenges older than 1 day
  db_query('DELETE FROM {captcha_sessions} WHERE timestamp < %d', time() - 60*60*24);
}

Seems like this would make it a lot faster if you have thousands of sessions in the sessions table. Just a thought.

I forgot to mention that I've been running 5.x-1.5 with Postgres for a few months now with no issues.