search not working on a zeus server

After some debugging I may have found the problem, I don't think my mysql account allows me to create temporary tables. If this is the case, is there any way around this to get searching working again?

It is the create temporary tables problem. Surely Drupal should give some kind of warning that this permission is needed but not available rather than just failing almost gracefully.

I'm going to look at the various work arounds on offer and see what I can do.

I've fixed the problem with the following code. I would love to put it into a patch and give full instructions on how to install it but I have a client desperate for a working system so don't have time at the moment. If you need help with it reply to this and I'll give help.

File includes/database.mysql.inc change the db_query_temporary function to:

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

// Start of Fix
// Fix created by Robin Wood - 2007-09-20

if ($tablename == "temp_search_sids" || $tablename == "temp_search_results") {
$my_query = "DELETE FROM $tablename WHERE session_key = '" . session_id() . "'";
_db_query($my_query);

$squery = 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);
$squery = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $squery);

$result = _db_query($squery);

$data = array();
while ($row = mysql_fetch_assoc ($result)) {
if ($tablename == "temp_search_sids") {
$ins_query = 'INSERT INTO temp_search_sids (session_key, type, sid, relevance, matches) VALUES ("' . session_id() . '", "' . $row['type'] . '", ' . $row['sid'] . ', ' . $row['relevance'] . ',' . $row['matches'] . ')';
} elseif ($tablename == "temp_search_results") {
$ins_query = 'INSERT INTO temp_search_results (session_key, type, sid, score) VALUES ("' . session_id() . '", "' . $row['type'] . '", ' . $row['sid'] . ', ' . $row['score'] . ')';
} else {
print "shouldn't be here";
exit;
}
_db_query ($ins_query);
}

return true;
}
// End Fix

$query = preg_replace('/^SELECT/i', 'CREATE TEMPORARY TABLE '. $tablename .' 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);
$ret = _db_query($query);
return $ret;
}

And create the following tables in your database:

CREATE TABLE `temp_search_sids` (
`type` VARCHAR( 16 ) NULL DEFAULT NULL ,
`sid` INT UNSIGNED NOT NULL DEFAULT '0',
`relevance` DOUBLE NULL DEFAULT NULL ,
`matches` BIGINT NOT NULL DEFAULT '0',
`session_key` VARCHAR( 32 ) NOT NULL
);

ALTER TABLE `temp_search_sids` ADD INDEX ( `session_key` );

CREATE TABLE `temp_search_results` (
`type` VARCHAR( 16 ) NULL DEFAULT NULL ,
`sid` INT UNSIGNED NOT NULL DEFAULT '0',
`score` DOUBLE NULL DEFAULT NULL ,
`session_key` VARCHAR( 32 ) NOT NULL
) ;

ALTER TABLE `temp_search_results` ADD INDEX ( `session_key` );

This isn't necessarily a secure fix, there may be the possibility of SQL injection attacks, I don't know how drupal escapes arguments before passing them around, if you are not sure or are worried about security then please don't use this. I am going to investigate this further, again when I get time, and I'll try to report back.