Temporary Tables - Please Allow Alternative

Even Drupal friendly hosting providers like siteground.com will not allow create temp table on mySQL for shared hosting accounts. The alternative seems to be to move to a VPS or dedicated server. For some users this is too expensive an option, and not necessary for any other reason.

I think we should have an alternative. Please!

The "good news" is a quick search through the core files reveals that only search.module and comment.install call the function db_query_temporary located in the database.*.inc files. So I guess that means, with regard to core files only, we lose the ability for built-in search, and perhaps comments (haven't tested it though).

Of core, only the database.*.inc files use CREATE TEMPORARY TABLES.

I bumped this to "base system" since it seems to affect more than just the search.module and there's the possibility other, non-core, modules will call the db_query_temporary function.

However, I am not at the programming point to be able to provide a workaround.

I just called my hosting provider and even though they don't list that SQL query as disabled, I was told their page would soon be updated to reflect CREATE TEMPORARY TABLES is not allowed.

I would like to use Drupal for some personal web sites with no revenue generation so it doesn't make sense for me to go with virtual dedicated or dedicated hosting.

So I too would very much like to see some kind of work around to needing that query/function.

I can live without offering search to my visitors since there are alternatives (Google), but what else could go wrong with my site if this function fails when called?

TIA

Sorry, should have bumped the version # too.

You don't need a dedicated host. Lots of perfectly affordable hosts allow temporary tables. Maybe not the "free with ads" ones (I've never used 'em), but really any host that doesn't trust me with temporary tables or alter-table from the app itself (something else Drupal does regularly) is one that I don't want to deal with.

This has come up before, and the answer is usually the same: Get a real host. Temp tables are not a threat to a host that knows what they're doing.

You know, this isn't a very good resolution for this problem.

I don't see a huge problem with the solution outlined by the initial post:
- Put error handling around CREATE TEMPORARY TABLE
- If it errors, invoke some alternate thing that does CREATE / DELETE on regular tables.

we don't want the fallback to happen after a failed query all the time -- this is something that should be addressed at a configuration level. my suggestion is a config setting in settings.php. this setting can be auto-configured by the installer initially based on the current db perms, but can be overridden later by the admin if the db perms do happen to change.

also, very doubtful this will be a fix for Drupal 5. setting for Drupal 6.

Given the presentation by Dries at OSCRM, making Drupal available to the widest possible audience of site builders should be a higher priority. Having a dependency of CREATE TEMPORARY TABLES (in core search) is the issue. Also, some folks argue that CREATE TEMPORARY TABLES is actually not recommended for performance reasons. I would like to discuss alternatives to this approach. Anybody out there who wishes to collaborate on alternatives?

Moving version back to 6.x ... has to be fixed in HEAD first. Depending on the fix, may be able to be backported.

Drupal's searh module uses a very effective but also very resource intensive approach to searching. This requires the use of temporary tables. If you hosting doesn't allow for this you need to either get better hosting or not use Drupal's search module. Alternatives are available: Either trip_search module or a google search bar.