Avoid prefixing temporary tables

can you try a View that doesn't involve og but does involve Search index? I think this is a views_search.inc bug

Make a View using Search in the Filter section. See if it works with prefixes.

Moving to Views project. I think some part of Views thinks that temp tables are prefixed - they are not AFAIK since they are only valid to the caller and thus can't collide.

Confirming this bug with a (not-so-clean) install of Organic Groups, Views 1.6.

Probably the error is coming from this line number:

/var/www/wsf2008/modules/contributed/views-5.x-1.6/views/views.module: 412

Wish it were that simple...

This is where the table prefixes are added, near as I can tell, for every views query:

Line 599 of views_query.inc:

      // the { is a special character which seems to be treated differently
      // in PHP5 than PHP4 so we do this a little oddly.
      $join_type = $joininfo['type'] == 'inner' ? 'INNER' : 'LEFT';
      $joins .= " $join_type JOIN {" . $table_real . "} $table_alias ON " . $left_table_alias .".".
        $joininfo['left']['field'] . " = $table_alias." . $joininfo['right']['field'];

But I don't see any way to turn this off for the temporary table except with an ugly conditional statement. Would it be easier to prefix the temporary table when it's created (wherever that may be)?

I have the same issue with an argument handler that needs to join a temporary table. Would this patch be a good idea?

I have applied your patch but still get errors. Do you have do change anything else then apply this patch?

This patch does not actually fix the problem some modules might have at the moment, but provides them a way to say "add this database table to the view, but please, do not prefix it". If this patch is accepted in Views, modules will then be able to specify an additional $use_table_prefix argument when calling add_table().

this is hurting more than a few people.

As a workaround I suggest you set that particular table not to prefix in settings.php using the $db_prefix array. I believe that workaround will get around the problem.

$db_prefix = array(
  'default' => 'prj_',
  'temp_search_results' => '',
);

this really hurts.
subscribing.

any progress on this?

A related question that may belong here, but probably in OG

Any way to simply remove the broken search box from the Organic Groups group details block?

Relax. I suggested a workaround, it should alleviate your problems.

Merlins Workaround does work here for me!

Oops,

I forgot to check back in and thank merlin.

The workaround also works for me.

Thankyou! (belatedly)

well, this doesn't fix the temporary tables issue in general, but this patch does fix the issue for the search filter's use of temporary tables, and suggests a pattern when using them.

Anyone who implemented the workaround should also be safe patching, even if they don't reconfig the $db_prefix

I have tested to see if this error still occurs in Views 2 for Drupal 6.x, and it appears to do so.

Has anyone else had the same experience with Views 2, and, if so, is it possible for a patch to be created? I am grateful to merlinofchaos for the suggestion in #11, but I would ideally not like to have to set a separate prefix setting for dozens of temporary tables, as I am currently doing.

Drupal core in D6 no longer utilizes temporary tables, so there isn't really a problem anymore.

In Views 2, you can create a new join handler, which can be utilized to create the join without the prefixing, if desired. You can then use that handler in your join definitions when adding temporary tables.

Also, there's a sitting patch for 5.x here, so moving this issue to 6.x causes unnecessary confusion.

merlin, thanks for the update. So you're saying that the way to handle temporary tables in Views 2 is to use a join handler?

The views that I created to integrate with faceted search (see Faceted Search issue #227634: Views tries to prefix temporary tables) still have issues without changes to my settings.php, but I converted them straight from Views 1 so they don't use a join handler.

As far as the issue's version number, I wasn't sure whether I should bump it or not. I only bumped it because I was encountering the issue in 6.x, and I know that the practice for core at least is to bump issues to the highest version number, and then backport. If your practice is different, I apologize for creating confusion.

Yes, create a custom join handler for the temporary table. While custom join handlers are extremely uncommon, that's the way to deal with it without needing to patch Views.

At this time, only security fixes will be made to the 5.x version of Views.