By gezzcc on

Hi,

I am new to this forum. Our company recently started using Drupal and it is a great system! Thank you everyone for creating this great open source system.

I wish to report a problem and how we fix it. Hopefully it will benefit the community. If I am doing this the wrong way please forgive me and let me know how it can be improved. If there is a better fix please also let me know.

We recently turned on persistent connections by changing mysql_connect to mysql_pconnect in the file includes/database.mysql.inc, after that, search would sporadically return no results, or return old / same results from a different search, done by a different user.

The conditions for this to happen:

  • turn on mysql_pconnect
  • when the problem happens, two "database table exists" errors will show up in the log: Table 'temp_search_results' already exists query: ...

The fix is simple - make sure you drop the temporary tables right before the search:

@@ -853,6 +854,88 @@
       $conditions = $conditions . " AND (i.sid NOT IN (SELECT n.nid from {node}
 n INNER JOIN {livepc_version} v WHERE (v.private = TRUE AND n.vid = v.vid)))";
   }
   $arguments = array_merge($arguments1, $query[3], array($type, $query[4]));
+
+  db_query_temporary("DROP table temp_search_results");
+  db_query_temporary("DROP table temp_search_sids");
+
   $result = db_query_temporary("SELECT i.type, i.sid, SUM(i.score * t.count) AS
 relevance, COUNT(*) AS matches FROM {search_index} i INNER JOIN {search_total}
t ON i.word = t.word $join1 WHERE $conditions GROUP BY i.type, i.sid HAVING COUN
T(*) >= %d", $arguments, 'temp_search_sids');

I have also included my detailed investigation of this problem below.

Thanks,
-Gezz

DETAILED NOTES:

SYMPTOMS
The symptoms for the search problem are:
 * sporadically, search will result either no result, or results from
   a previous search (done by someone else)
 * when this happens, the following error shows up in drupal log:
 
Table 'temp_search_results' already exists query: CREATE TEMPORARY
TABLE temp_search_results SELECT i.type, i.sid, 5 * (3.4145967042809 *
i.relevance) + 5 * POW(2, (GREATEST(n.created, n.changed,
c.last_comment_timestamp) - 1170363016) * 6.43e-8) + 5 * (2.0 - 2.0 /
(1.0 + c.comment_count * 0.076923076923077)) AS score FROM
temp_search_sids i INNER JOIN search_dataset d ON i.sid = d.sid AND
i.type = d.type INNER JOIN node n ON n.nid = i.sid LEFT JOIN
node_comment_statistics c ON c.nid = i.sid WHERE (d.data LIKE '%
windows %') ORDER BY score DESC in
/home/repo/directories/www/2007-02-01-13-24-30/html/includes/database.mysql.inc
on line 103.
CAUSE
 1. search module puts search results into temporary table -- this
should be ok because temporary tables are only visible for that
particular session, and they are closed after connections are closed
http://dev.mysql.com/doc/refman/4.1/en/create-table.html
"A TEMPORARY  table is visible only to the current connection, and is dropped automatically when the connection is closed."
 2. usually connections are indeed closed after the execution of a php
script too
 3. because of the implicit assumptions, search module does not drop
the temporary tables after the search
 4. however, we enabled persistent connections in the following changelist:
      https://uber.skyblue-technologies.com/trac/changeset/6518
    and the behaviour of the persistent connection is described below, therefore, tables are no longer dropped
http://www.php.net/manual/en/features.persistent-connections.php:
"Persistent connections are links that do not close when the execution of your script ends. "
 5. reverting one line mysql_pconnect --> mysql_connect in the file
    includes/database.mysql.inc resolves the problem -- however, this
    fix is not acceptable because it slows down the website
THIS FIX
 * drop the unnecessary search result tables after the search is
   finished
⋅ Categories: Drupal 4.7.x