Search Error -> Access denied for user -> database.mysql.inc

Hi,

Here's what I have, and it seems to be working fine. It's ugly, I haven't had a chance to clean it up, but I'm pretty busy right now and don't know when I will, so I figure you could use it until I get a chance. This was based off 4.7_rc2 code, so if you're using 4.6 you'll probably have to modify them yourself.

As you can see, instead of making temp tables, it just creates normal tables suffixed with the session ID to avoid collisions. You may want to check the database occasionally and clean up any tables that don't get dropped. Shouldn't happen unless people hit the stop button in the middle of a search.

In modules/search.module, replace the do_search function w/ this:

function do_search($keywords, $type, $join1 = '', $where1 = '1', $arguments1 = array(), $select2 = 'i.relevance AS score', $join2 = '', $arguments2 = array(), $sort_parameters = 'ORDER BY score DESC') {
  $query = search_parse_query($keywords);
  global $user;

  if ($query[2] == '') {
    form_set_error('keys', t('You must include at least one positive keyword with %count characters or more.', array('%count' => variable_get('minimum_word_size', 3))));
  }
  if ($query === NULL || $query[0] == '' || $query[2] == '') {
    return array();
  }

  // First pass: select all possible matching sids, doing a simple index-based OR matching on the keywords.
  // 'matches' is used to reject those items that cannot possibly match the query.
  $conditions = $where1 .' AND ('. $query[2] .") AND i.type = '%s'";
  $arguments = array_merge($arguments1, $query[3], array($type, $query[4]));
  $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 COUNT(*) >= %d", $arguments, 'temp_search_sids');

  // Calculate maximum relevance, to normalize it
  $normalize = db_result(db_query('SELECT MAX(relevance) FROM temp_search_sids'.$user->sid));
  if (!$normalize) {
    return array();
  }
  $select2 = str_replace('i.relevance', '('. (1.0 / $normalize) .' * i.relevance)', $select2);

  // Second pass: only keep items that match the complicated keywords conditions (phrase search, negative keywords, ...)
  $conditions = '('. $query[0] .')';
  $arguments = array_merge($arguments2, $query[1]);
  $result = db_query_temporary("SELECT i.type, i.sid, $select2 FROM temp_search_sids".$user->sid." i INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type $join2 WHERE $conditions $sort_parameters", $arguments, 'temp_search_results');
  if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_results'.$user->sid))) == 0) {
    db_query('DROP TABLE temp_search_results'.$user->sid);
    db_query('DROP TABLE temp_search_sids'.$user->sid);
    return array();
  }
  $count_query = "SELECT $count";

  // Do actual search query
  $result = pager_query("SELECT * FROM temp_search_results".$user->sid, 10, 0, $count_query);
  $results = array();
  while ($item = db_fetch_object($result)) {
    $results[] = $item;
  }

  db_query('DROP TABLE temp_search_results'.$user->sid);
  db_query('DROP TABLE temp_search_sids'.$user->sid);

  return $results;
}

Then in includes/database.mysql.inc change the function db_query_temporary to the following:

function db_query_temporary($query) {
  $args = func_get_args();
  $tablename = array_pop($args);
  array_shift($args);

  global $user;

  db_query("DROP TABLE IF EXISTS ".$tablename.$user->sid);
  $query = preg_replace('/^SELECT/i', 'CREATE TABLE '.$tablename . $user->sid.' SELECT', db_prefix_tables($query));
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  _db_query_callback($args, TRUE);
  $query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  return _db_query($query);
}

This will require that every module that uses db_query_temporary be modified, but of all the ones I have installed, search.module is the only one that uses it.

Let me know if you have any problems with it. And if there's any Drupal/MySQL guru's out there reading this, please feel free to tell me why this is a crappy way to do it :)

Niosop

For some reason one batch of backdated blog posts was searchable but another batch wasn't. By re-indexing the site (admin-settings-search) and then running cron, it would appear my blog posts are all now searchable. This page was also updated, however: http://www.deandi.com/adventure/galleries - all of the content at the bottom of the page (the ones that don't have thumbnails) were just added in the last couple of days, and none of them appear in searches (I searched "muertos", "chinese new year", etc- nothin'.) Older stuff on that page (the stuff with thumbnails) is searchable. Any input would be appreciated. Thank you!

Got my Provider into enabling "create temporary table privs". Luckily. That resolved it. So I have not further tested the workarounds.