Many drupal lovers getting trouble at godaddy for search module.
when user try to search they will get error message as not able to create temporary tables.
I have hosted http://blogya.in in godaddy shared hosting account. I made some tweak to enable the search module working without temporary tables. User can search and get results.
This is a temporary or may be a permanent solution, if your site has no very high traffic. Because as long as no concurrent request to search, this will work perfectly.
My hack in Search module works well as long as there is no concurrent request for search. Which means, within a fraction of second, if more than one request for search will go awry. We can fix this concurrent request for search if 'LOCK TABLE' previllege is given. I guess godaddy has not given 'LOCK table' too.
Try out search at http://blogya.in which is hosted in godaddy
Concurrent search without temporary tables will not corrupt databases. The issues: the users who are making search request may get confused results. Example: if 3 users making search request exactly at 10th hour 45 minute and 29th second, results for the 3 users may be corrupted. Database will not be corrupted, but only the results may get corrupted. If the search request comes one by one like 29th, 30th, 31st second, everything will go correctly. The results will be respective to each user.
It depends upon the traffic. If your site has normal traffic, then no problem. Concurrent request is a rare probability.
Here is the code snippets for search hack:
File: search.module
Function name: do_search
Replace the function with the following code. Please backup your copy before applying the code.
-----------------------------------------
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);
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]));
//----G // Temporary table not allowed in godaddy hosting.
//$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');
$result = db_query("CREATE TABLE IF NOT EXISTS temp_search_sids 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);
// Calculate maximum relevance, to normalize it
$normalize = db_result(db_query('SELECT MAX(relevance) FROM temp_search_sids'));
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]);
//----G // Temporary table not allowed in godaddy hosting.
//$result = db_query_temporary("SELECT i.type, i.sid, $select2 FROM temp_search_sids 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');
$result = db_query("CREATE TABLE IF NOT EXISTS temp_search_results SELECT i.type, i.sid, $select2 FROM temp_search_sids i INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type $join2 WHERE $conditions $sort_parameters", $arguments);
if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_results'))) == 0) {
return array();
}
$count_query = "SELECT $count";
// Do actual search query
$result = pager_query("SELECT * FROM temp_search_results", 10, 0, $count_query);
$results = array();
while ($item = db_fetch_object($result)) {
$results[] = $item;
}
//----G // Temporary table not allowed in godaddy hosting.
db_query('DELETE LOW_PRIORITY FROM temp_search_sids');
db_query('DELETE LOW_PRIORITY FROM temp_search_results');
return $results;
}
-----------------------------------
Search Table Structure:
CREATE TABLE `temp_search_results` (
`type` varchar(16) default NULL,
`sid` int(10) unsigned NOT NULL default '0',
`score` double default NULL
) TYPE=MyISAM;
CREATE TABLE `temp_search_sids` (
`type` varchar(16) default NULL,
`sid` int(10) unsigned NOT NULL default '0',
`relevance` double default NULL,
`matches` bigint(21) NOT NULL default '0'
) TYPE=MyISAM;
-----------------------------------
It is better to create the search tables in advace before hacking the code.
