It seems my hosting provider has recently removed the MySQL query CREATE TEMPORARY TABLE from their "allowed" list.

I say recently because I've used Drupal 4.7.x for a while, and near the beginning I temporarily used the search feature, but decided to turn off not long after that. Searching seemed to work fine then.

GoDaddy is my host (for now) :-( and their "help" page doesn't list CREATE TEMPORARY TABLE as disabled, but during my tech support call to them tonight I find they just haven't updated their page yet.
http://help.godaddy.com/article.php?article_id=1589&topic_id=67&&

I have a production 4.7.6 site and am setting up a new one with 5.1. I can live without search, for now. But I am at a loss as to what might be goofed up due to not being able to create temporary tables.

Am I just going to miss some functionality, or is my database going to end up corrupted because Drupal can't CREATE TEMPORARY TABLE any more?

Help. TIA.
- Drew

Comments

rareservices’s picture

Hi,

Not only you, many drupal lovers getting trouble at godaddy for this 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.

Bairnsfather’s picture

Thanks for the pointer rareservices. As I mentioned I can live without integrated search for now.

I'm really interested in what else could go wrong without CREATE TEMPORARY TABLE besides loosing the search feature.

And mostly, am I just going to loose some functionality, or is my database going to be corrupted?

In 5.1 it seems that "comment.install" is the only file besides the search.module that calls db_query_temporary. I'm not sure if comment.install is called for each comment that's posted or just during a Drupal install. I'm guessing it's called each time a comment is added.

The good news is that I tested adding comments last night and didn't find any problems. However, based on a lot of reading here at Drupal.org, and your post, it seems one potential problem is if more than one person posts a comment at the exact same time (not sure of what constitutes "exact time" though).

Any insight appreciated. - Drew

VM’s picture

exact same time = while one comment is being submitted another is submitted at same time by another user to the same topic. Thus the saving of the comment will bump heads, and cause a headache.

Bairnsfather’s picture

Will the database become corrupted? What will happen?

lurkltd’s picture

the site I'm using doesn't use comments. Will my database be ok without temporary tables?

rareservices’s picture

Hi,

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.

That is why I said, it depends upon the traffic. If your site has normal traffic, then no problem. Concurrent request is a rare probability. I am using drupal 4.7.*. I am not aware of the issue in comment module. I will check it.

Let me know if you find any progress and better solution.

http://rareservices.com

ntripcevich’s picture

Hi Rare-services--
Can you let us know what your hack was that allowed you to get around the CREATE TEMPORARY TABLES limitation at Godaddy?

Thanks, Nicholas

---
Nico Tripcevich
UC Berkeley

rareservices’s picture

Hi,

visit the following link for the code hack in search.module.

http://www.rareservices.com/drupal_search_without_temporaty_tables_solut...

Please backup your search.module before hacking it. Also create temporary tables.

hedroom’s picture

I hacked the do_search function, in search.module, to pass a table name to the db_query_temporary function using the PHP session id. After the search is complete, the do_search function will drop the "temporary" tables. This should allow concurrent searches assuming that your session ids are unique.

You may have to check your table list occasionally for tables that were not removed properly.

The db_query_temporary function in database.mysqli.include must be edited also but that's easy. Simply remove the word TEMPORARY from the CREATE statement.

I have only done this for mysqli but I bet it will work for mysql also. For you Go Daddy users out there, you should be using mysqli anyway.

I haven't done a lot of testing and very little error trapping so use at your own risk and backup your tables and modules before trying.

The do_search function can be downloaded here:
http://www.hedgren.net/family/files/do_search.txt

Cheers,

Wade

darrenstroh’s picture

Hey Wade - the solution works! Excellent! Even a newbie like me can do it.

I've just built out a small blog site using shared hosting on GoDaddy and Search works just dandy.

Thanks.

RaRi’s picture

Hi Wade,

thanks for your hack!
I just finished the test on v5.5 and everything works.

We need to convince the core drupal developers to create a setting for the search function like:

Use flexible TEMP tables? Yes, No

*gg

smitty’s picture

This patch works perfectly on my windows-based development-server.

But on the debian-based life-server I get sql-errors:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '999e6e87027890ef92321860d8b058e7_s SELECT i.type, i.sid, SUM(i.score * t.count) ' at line 1 query: CREATE TABLE 999e6e87027890ef92321860d8b058e7_s 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 = 'shopping') AND i.type = 'node' GROUP BY i.type, i.sid HAVING COUNT(*) >= 1 in /path_to_drupal/includes/database.mysql.inc in Zeile 172.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '999e6e87027890ef92321860d8b058e7_s' at line 1 query: SELECT MAX(relevance) FROM 999e6e87027890ef92321860d8b058e7_s in path_to_drupal/includes/database.mysql.inc in Zeile 172.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '999e6e87027890ef92321860d8b058e7_s' at line 1 query: DROP TABLE 999e6e87027890ef92321860d8b058e7_s in path_to_drupal/includes/database.mysql.inc in Zeile 172.

The user only gets the message that the search did not match any results and the sql-errors are displayed. But in the logfile the correct results are logged.

And these errors do not occur every time. It is even possible, that user 1 gets the "not found"-message while user 2 gets the correct results.

Does anybody have a clue what's might be going on there?

rkendall’s picture

I noticed the CREATE TEMPORARY TABLES problem while working with a particular web host.

After digging through the support database I found the following information:

By default on all MySQL databases the current privileges are given -

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE.

CREATE TEMPORARY TABLES is the only other privilege that can be granted if a support ticket is raised, no others are available.

Maybe other hosts would be prepared to enable the needed permission if asked, at least worth a try.

--
Ross Kendall
UK based Web and IT consultant specialising in Free and Open Source Software technologies.
http://rosskendall.com