Facets require "CREATE TEMPORARY TABLES" permission

Hi JemBijoux.

Facet API is simply a display layer an abstraction for interaction with the backend. Drupal Commerce = Search API, so I would start there actually performs the facet calculations.

Thanks,
Chris

For anyone else having this issue, I solved this by giving the database user connected with the drupal website "CREATE TEMPORARY TABLES" permissions. Once I enabled this, everything worked properly. Hope it helps someone else!

.

Moving to the Search API Database Search's queue.

.

@pinkonomy:

Sorry for the delay (been on holidays). Not sure if you've figured it out by now but how you'll set the "CREATE TEMPORARY TABLES" permission will depend on you are able to administer your website's database and it's users. For me, that was using the cPanel control panel that came with the hosting. The hosting company this website was using had the ability enabled for me to create database users and set their corresponding permissions (click on the the database user name assigned to the database). Your setup may be different in cPanel if that is what your hosting is using. If you are still having issues I'd recommend contacting your hosting company as they should be able to help you. If you're working locally, you can use the command line to set permissions.

I hope that helps.

While I don't think we can/should do anything about this not working without the proper permission, we should still properly catch and report the exception, return the other search results normally and also note this problem in the README.txt. Thanks for bringing it to my attention!

Patch attached, please review!

Re-rolled and committed.

Thanks. The title of this issue saved me after 3 hours of trying to figure out why my facets where empty.

Needs to be ported to D8.

Please take a look at the patch.

Since in the proposed solution try/catch used inside Database::getTemporaryResultsTable() function, it should cover also usage of temp tables in getAutocompleteSuggestions(), not only in getfacets().

I haven't looked in to the issue yet but it looks like the original patch was much broader? For example, it also adds documentation to the readme file that we need temporary table permissions etc. Also the error message is different? If you are radically changing a patch, please add some information why and that it covers the whole use-case.

And maybe a silly question, but can we somehow test if a certain permission failed?

Nick,

1) thanks for pointing my attention about README.txt update, I added that in next version of the patch;
2) changing error message text: maybe I was not clear, but now this can be generated not only while working with Facets;
3) lesser amount of code changed: I wrapped with try/catch only the place where we do "create temp table"; if there something wrong with query manipulations like $db_query->getExpressions() and $db_query->distinct() this IMHO not related to the ticket (but I am OK if it is better to wrap entire function context);
4) about testing - have no idea how to test this even manually :) I did not found anything related in UI. But I had generated exception and tested that current catch/watchdog_exception stores readable and informative message in log.

I'll un-assign myself from the issue in case someone with better knowledge of SearchAPI internals wants to chime in.

Thanks a lot for your work, looks good!
Just a few minor things:

1. +++ b/search_api_db/README.txt
   @@ -45,6 +45,10 @@ See [1] for details.
   +Also, using facets and autocomplete suggestions with a database server will
   +only work if the database user Drupal is using has the
   +"CREATE TEMPORARY TABLES" permission (or similar, in DBMSs other than MySQL).
   

   Should break at as close to 80 characters as possible.

2. +++ b/search_api_db/src/Plugin/search_api/backend/Database.php
   @@ -2062,7 +2062,14 @@ class Database extends BackendPluginBase {
   +    catch(\Exception $e) {
   

   As in D7, we should just catch the PDOException here, I'd say.

3. +++ b/search_api_db/src/Plugin/search_api/backend/Database.php
   @@ -2062,7 +2062,14 @@ class Database extends BackendPluginBase {
   +      watchdog_exception('search_api', $e, "Error creating temporary table: %message", array('%message' => $e->getMessage()));
   

   Please use the same pattern as used elsewhere for watchdog_exception() calls. (With that, the error message variable will also automatically be provided.)

Please see the attache patch, which I'd call RTBC.

And maybe a silly question, but can we somehow test if a certain permission failed?

I don't think so, especially not in a DBMS-independent way. And I don't think it's worth it to try, just for a better error message in an edge case.

I agree with RTBC.

Thanks for reviewing!
Committed.
Thanks again, everyone!

I got this issue with Drupal 8.4.2, Facet dev version (released alpha-11) trying to use the "Range slider" with a Date Range field and PostgreSQL 9.6.6.

I have a view with a list of elements and a block with the facet widget "Range slider" but when I try to move the slider then I get the error:

Drupal\Core\Database\DatabaseExceptionWrapper while trying to create a temporary table: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near ")" LINE 4: ...IN ('event')) AND (t.field_date_period BETWEEN '1479917913') ^: CREATE TEMPORARY TABLE {db_temporary_1} AS SELECT DISTINCT t.item_id AS item_id FROM {search_api_db_default_index} t WHERE (t.type IN (:db_condition_placeholder_0)) AND (t.field_date_period BETWEEN :db_condition_placeholder_1); Array ( [:db_condition_placeholder_0] => event [:db_condition_placeholder_1] => 1479917913 ) in Drupal\search_api\Entity\Server->search() (line 461 of web/modules/contrib/search_api/src/Entity/Server.php).