Support for temporary tables is broken

To fix this issue you must make a few changes to the code for the search_api_db module.

Add to top of class around line 12

    protected $temporaryNameIndex = 0;

Change line 1097 in sites\all\modules\search_api_db\service.inc

$table = db_query_temporary((string)  $db_query, $args, $this->query_options);

To

    $args = $db_query->getArguments();
    $table = "db_temporary_" . $this->temporaryNameIndex++;
    db_query(preg_replace('/^SELECT(.*?)FROM/i', 'SELECT$1 INTO ' . $table . ' FROM', (string) trim(str_replace(array("\n","\r")," ", $db_query))), $args, $this->query_options);

Add at end of function around line 1173 before the return statement

db_drop_table($table);


Temporary tables are dropped when the connection drops so the table vanishes when the initial query is complete thus making all subsequent queries to the table to fail. Instead this creates a concrete table and drops it when done with it.

Just noting for folks that this doesn't work on Azure: http://blogs.msdn.com/b/sqlazure/archive/2010/05/04/10007212.aspx

The real solution would be to update the sqlsvr project to change the way it extends: http://api.drupal.org/api/drupal/includes%21database%21database.inc/func... . Not changing projects since I'm not 100% sure about that. Hopefully I will have the time to work on fixing this.

So, two issues:

• Apparently SELECT INTO is not supported on SQL Azure. Someone should research if it is still true and update the code accordingly. INSERT INTO inside a transaction should make a good replacement.
• Someone has to explain where the initial error message comes from (SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '#db_temporary_0'. As far as I know, #db_temporary_0 is the correct way to create a temporary table.

I apologize I was conflating two issues as you noted above, however the solution for the first created a solution for the second.

From my testing I found that the error message above was created when using SELECT INTO statements with joins. For whatever reason the temporary table was not created when using joins.

This would create a temporary table SELECT DISTINCT t.[item_id] AS [item_id] FROM {search_api_db_datasets_search_api_language} t while this wouldn't SELECT DISTINCT t.[item_id] AS [item_id] FROM {search_api_db_datasets_search_api_language} t INNER JOIN {search_api_db_datasets_field_tags} t_2 ON t.item_id = t_2.item_id WHERE ( ( ([t_2].[value] = :db_condition_placeholder_0) ) ).

I switched to INSERT INTO for queryTemporary() and found that the queries with joins didn't cause an issue. Attached is a patch.

As noted in the TODOs the current solution is ugly because I couldn't determine the column type for the temporary table fields. In this case, I couldn't determine the column types from hook_schema because they are created outside of it. I'm not sure how else to try and grab that information. @Damien Tournoud if you have any suggestions about how to clean this up I would be happy to implement and test them.

Maybe @darstan was on the right path back in #1 when hinting that new lines are the problem?

Would simply adding a /s modifier to the regular expression the problem?

$query = preg_replace('/^SELECT(.*?) FROM /is', 'SELECT$1 INTO ' . $tablename . ' FROM ', $query);

If the regexp didn't match, the query would remain a simple SELECT query and the temporary table would never be created. That actually explain the Invalid object name '#db_temporary_0' error message triggered on the query right after that.

I didn't find that adding the /s actually removed the new lines. I removed new lines through separate str_replace functions and manually but it still wasn't creating the temporary table.

Would you like to focus on this or on the INSERT INTO solution. Should I make a separate ticket for that?

The suggestions in #1 are hard to apply (line numbers doesn't correspond with the current version ?); nevertheless I tried to apply them. The table gets created, but doesn't get deleted and raises an error the next run that the table already exists.

The suggestion in #5 doesn't work.

The patch in #4 works; the temporay table gets indeed created en deleted which was not the case before.
Remarks:
- it is a very case specific patch (todo's)
- the preg_match for the fields only matches fieldnames with [...] and not fieldnames like _field_0 -> I needed to adjust the regex
- the int fieldtype was not sufficient (conversion warning) on creating the table (nvarchar(max) did the trick)
- it made facetapi work -> thank you!

Can you provide the updated regex?

Sorry: /AS (.*)/

#4 patch worked perfect for me! I'm using SQL Azure.

Thanks for the info and patches. Trying to get Search API working for MS SQL here, but running into issues when i do a query:

"Column 'X' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Any ideas? Thanks!

I had the same error and tried patch #4.
Facets now work.
Tested on WebMatrix 3 and SQL Server 2008 R2.

@virusakos Can you provide a copy of the exact query that failed. Then it is possible to write a test.

Query that fails:

PDOException while trying to calculate facets:
SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name '#db_temporary_0'.: SELECT TOP(50) t_2.[field_product_category_name_field] AS [value], COUNT(DISTINCT t.item_id) AS num FROM {#db_temporary_0} t INNER JOIN {search_api_db_product_index} t_2 ON t.item_id = t_2.item_id WHERE ( ([t_2].[field_product_category_name_field] IS NOT NULL ) ) GROUP BY t_2.[field_product_category_name_field] ORDER BY num DESC;
Array ( ) in SearchApiDbService->getFacets() (line 1871 of modules\search_api_db\service.inc).

To replicate the issue:

• Modules needed: search_api, search_api_db, search_api_facetapi, search_api_views, facetapi
• Configuration needed: A server and an index in admin/config/search/search_api. In the index I have enabled a facet. Enabled the facet block. Created a page view based on the index and visited the page. At this point, the page works fine but no facets are shown. You can see the error in admin/reports/dblog after visiting the page.

Ok figured this out.

[1] The regex is incorrect in queryTemporary() and should include the 's' modifier:

$query = preg_replace('/^SELECT(.*?)FROM/is', 'SELECT$1 INTO ' . $tablename . ' FROM', $query);

[2] Local temporary tables seem not to be supported by the PDO driver, I filed an issue for this:

https://github.com/Azure/msphpsql/issues/48

I did some findings and GLOBAL temporary tables do work properly.

The problem of moving to global temporary tables is that we would need interprocess unique table name generation (or use GUID's to reduce colision probability):

generateTemporaryTableName()

I am not kind of the patch in #6 because it's a workaround more than a solution, and the driver is already full of workarounds :(

If this is not to be compatible with AZURE then so be it. It makes no sense so say this supports Azure because the actual implementation is not production ready for AZURE. Cloud databases need Connection Resiliency / Retry Logic.

@virusakos You can try moving to GLOBAL temporary queries, give the attached patch a shot an see how it works.

The problem with global temporary queries is that there is (I guess....) no control on the scope of the temporary table, so there's in no guarantee that it is going to be available for the full request.

After applying patch #17 I'm getting the following error for every page I visit (admin or not)

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near the keyword 'WHERE'.: DELETE FROM {} WHERE ([name] = :db_condition_placeholder_0) AND ([value] = :db_condition_placeholder_1) AND ([expire] <= :db_condition_placeholder_2) ; Array ( [:db_condition_placeholder_0] => variable_init [:db_condition_placeholder_1] => 6741027285545d0fb3cfb01.86064157 [:db_condition_placeholder_2] => 1430638844.2403 ) in lock_may_be_available() (line 181 of includes\lock.inc).

Well that means that escapeTable() is returning an empty string, but I have tested this locally and reviewed the code and see nothing wrong with it.

Make sure that the code you have in escapeTable() makes sense. Depending on your driver version it should look something like this:

  public function escapeTable($table) {
    // Rescue the # prefix from the escaping.
    $result = ($table[0] == '#' ? '#' : '') . ((isset($table[1]) && $table[1] == '#') ? '#' : '') . preg_replace('/[^A-Za-z0-9_.]+/', '', $table);
    return $result;
  }

BTW, if you are running this on a windows environment, unless you have horizontally scaled your frontend servers with load balancing or something alike, you should be using memory based locking backend (and probably session backend too) provided by wincache.

I verified the escapeTable($table) code.
It's exactly the same with patch #17.
I used a breakpoint at the end of escapeTable($table) and I saw that it does not return an empty string.
Driver version is 7.x-1.3

I don't have wincache (yet) but this is something I'm interested on having.
Any references will be much appreciated :)

@virusakos: The query you posted is very strange:

DELETE FROM {} WHERE [...]

Basically the table name is missing. If you could breakpoint at escapeTable() follow the execution path and find out where is the table name dissapearing.

For wincache integration refer to the wincache module.

This is worth considering:

      // We run the statements in "direct mode" because the way PDO prepares
      // statement in non-direct mode cause temporary tables to be destroyed
      // at the end of the statement.
      // If you are using the PDO_SQLSRV driver and you want to execute a query that 
      // changes a database setting (e.g. SET NOCOUNT ON), use the PDO::query method with 
      // the PDO::SQLSRV_ATTR_DIRECT_QUERY attribute.
      // http://blogs.iis.net/bswan/archive/2010/12/09/how-to-change-database-settings-with-the-pdo-sqlsrv-driver.aspx
      $pdo_options[PDO::SQLSRV_ATTR_DIRECT_QUERY] = TRUE;

Although I said that escapeTable($table) was exactly as the patch #17, I checked again and it wasn't !?
Not sure what has happened.
Now that it is the same it's working!
But I had to remove the fastcache::cache_set($table, $result, 'schema_escapeTable'); code in escapeTable($table) because the patch #17 is for the -dev version and I have the non -dev version.

So, both patches #4 and #17 work.

Moved to 7.x-2.x.

This is already fixed in our internal dev version, with prefetching close to working.

Just for the record:

There are 3 ways to get this working:

1) Use local temporary tables, but requires using direct queries. There is supposed to be a performance penalty with that because execution plans do not get cached.

2) Use global temporary tables.

3) Manually create the tables, and drop them at the end of the request in a shutdown callback.

I've implemented [2] because it looked like the best approach.

@virusakos If you are brave enough, can you give a test ride of the 7.x-2.x branch? (just copy paste the driver files and revert if anything fails).

Temporary tables support was fixed (among other things).

To use 7.x-2.x you need the latest PDO SqlServer extension though.

Thanks!

Fixed on 7.x-2.x and 8.x-1.x with tests. I won't backport this to 7.x-1.x but if someone want's to, it's just a handful of code lines.