This may be a design choice, but newertheless it makes the search feature unavailable to some. Below are the errors logged when I tried search on my site, http://oldrup.dk/drupaltest/?q=search/node

user warning: Access denied for user: 'web34197@%' to database 'web34197' query: CREATE TEMPORARY TABLE 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 INNER JOIN node n ON n.nid = i.sid INNER JOIN users u ON n.uid = u.uid WHERE n.status = 1 AND (i.word = 'small') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 in /usr/home/web/web34197/drupaltest/includes/database.mysql.inc on line 108.

user warning: Table 'web34197.temp_search_sids' doesn't exist query: SELECT MAX(relevance) FROM temp_search_sids in /usr/home/web/web34197/drupaltest/includes/database.mysql.inc on line 108.

Seem that my hoster (web10.dk) doesn't allow CREATE TEMPORARY TABLE - don't know if that is normal.

CommentFileSizeAuthor
#9 search.module_7.patch2.26 KBpaddy_deburca

Comments

Thox’s picture

Thox commented

Some hosters allow temp tables as long as they are prefixed with the hosting account username. Prefixing might be worth looking in to.

netbjarne’s picture

netbjarne commented

I have filed a support request at my webhost asking if prefixing is a sollution they support.

Not being a sql expert; is it possible to modify/hack the search code to use "regular" tables instead of temporary tables, or would this be a bad design choice? - If hintet in the right direction, I'd be glad to experiment with an alternate sollution.

Best regards
Bjarne

moshe weitzman’s picture

moshe weitzman commented
Status: Active » Closed (works as designed)
netbjarne’s picture

netbjarne commented

Got reply from host today. Sadly they do not grant the CREATE TEMPORARY TABLE right, nor do they support the prefixing approach.

Sadly, this prohibits me from using the search feature of Drupal. Any recommendations for a workaround?

killes@www.drop.org’s picture

killes@www.drop.org commented

two possible solutions:

1) change hosters
2) use the trip_search module instead. It has some shortcomings, but is probably better than no search at all.

netbjarne’s picture

netbjarne commented

I've found another host, surftown.dk, offering approx the same services at the same low price as my current host, web10.dk, and that apparently supports both create temporary table and lock table rights. So I purchased a webhotel there, and until now it looks good - users can post and search pages w/o any problems...

the trip_search module I'll will save for a project that doesn't permits a host change.

Thank you all for your replies :-)
Bjarne

luebbe’s picture

luebbe commented

The two options mentioned by killes do not really work for me.

1) I don't want to change my hoster, because I'm happy with everything else they offer.
2) The trip_search maintainers say they want to discontinue the module due to the improvements in 4.7 core search.

So what? Not everyone has the funds for a more expensive web hoster or an own server.

Drupal 4.7 is getting a really frustrating experience for me. I can work around the LOCK TABLE restriction, because my website is low traffic and simultaneous write accesses are rarely going to hit me, but I can't work around the safe_mode and CREATE TEMPORARY TABLE restrictions :-(

Why can't drupal give a good user experience in a restricted hosting environment?

paddy_deburca’s picture

paddy_deburca commented
Version: 4.7.0-beta2 » 4.7.2

To solve this problem, I have done the following

  • permanently created the two temporary tables
  • updated the search.module to use a INSERT INTO rather than a CREATE TEMPORARY TABLE
  • added a field to the two tables that contains the current session ID (session_id())
  • before each INSERT INTO, issue a DELETE FROM for this session ID only

What needs to be done

  • clean up stale records in the two tables
    the idea here is to add a cron hook that wipes both tables clean on a regular basis

If anybody is interested I can provide a patch against the search module.

Paddy.

paddy_deburca’s picture

paddy_deburca commented
StatusFileSize
new search.module_7.patch2.26 KB

To solve this problem with the attached patch, you need to create the two temporary tables

-- 
-- Table structure for table `temp_search_results`
-- 

CREATE TABLE `temp_search_results` (
  `type` varchar(16) default NULL,
  `sid` int(10) unsigned NOT NULL default '0',
  `score` double default NULL,
  `ssid` varchar(32) NOT NULL default ''
) TYPE=MyISAM;

and

-- 
-- Table structure for table `temp_search_sids`
-- 

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',
  `ssid` varchar(32) NOT NULL default ''
) TYPE=MyISAM;

Apply the attached patch, and enjoy searching

Paddy.

Treesong’s picture

Treesong commented

It works! Thanks for the help. And for people like me who didn't know how to apply patches, here are two articles worth checking out:

HOWTO: Apply patches
http://drupal.org/node/60108

how to manually apply patches?
http://drupal.org/node/34422

I used the manual method described and it worked well for me.

cybe’s picture

cybe commented

Thanks for the patch. It allowed me to update my site from 4.6 to 4.7.

It works, but only briefly. After a while any search, "qwertyasdfqweasd" for isntance, will result in a huge list of nodes (that do not contain the searched word, until I truncate the temporary search tables..

Looking forward to getting this fixed. Meanwhile I guess I'll leave a a script looping that truncates the temporary tables every ... 10 seconds or so =(.

nick1@neoforts.com’s picture

nick1@neoforts.com commented
Version: 4.7.2 » 4.7.3

I am with godaddy hosting. My site www.neoforts.com was recently converted to Drupal. Our search is fully indexed and does not work with search because of this issue. I will contact godaddy to see if they can help me resolve this issue.

If godaddy does not support this right??? what should i do??

Crell’s picture

Crell commented

If godaddy does not support this right??? what should i do??

1) Get a real web host.

2) Use GoogleSearch.