SEARCH-Error / Temp Tablespace permissions?

[Ravey76]

Hello Everyone,

I just upgraded my board from SMF 1 beta 5 to RC1. After little fighting it runs now smoothly (file-upload still doesnt work right, but I guess thats a problem of my server).

My problem:

- when I enter search-function and want to search something, I receive this error message:

Datenbankfehler  (database error)

Access denied for user: 'DB-USERNAME@192.168.40.239' to database 'DATABASENAME'
Datei: /mnt/ja1/01/142/00000000/htdocs/forum/Sources/Search.php
Zeile: 362

What could it be?

My board:

http://www.bmw-einzylinder.de/forum/index.php

Thank you in advance,
Ravey76

Addition:

- In case I am NOT logged in to the forum, I receive following error message:

Datenbankfehler. Bitte versuchen Sie es noch einmal, sollte der Fehler noch einmal auftreten, informieren Sie bitte den Administrator.

Means:

Database Error. Please try again and in case error occurs again, contact administrator.

[Ben_S]

It could be that your MySQL user does not have the permission to create temporary tables, which is required for search.

[Ravey76]

@ Ben:

Thanks for that quick reply.

How can I change the users permission?
And how could this happen?

And: It´s not my user in particular - every other user is not allowed to use the search-function, too.
Maybe some general problem?

What I did:

Part 1:
- I had SMF 1 Beta 5 installed, running quite fine
- uploaded the update-files, run update.php
- got error message, some language files were missing
- uploaded german language files, re-run update.php
- update succeeded, started forum ->  was okay, but had some serious problems with my theme (converted from previous forum)

Part 2:
- because of my theme-problems and a problem caused by the new META-refresh-function I made a complete new installation of RC1 (parallel to the old one) -> everything went fine
- then I changed within the admin panel the address of the database to the "old" DB (the "upgraded-version") -> no problems
- after that I create a new template folder, copied content of "default template" there and made some major changes in index.template.php and style.css
- now all is going quite well ... just the SEARCH-function is bugging me ...

Thats all I made - maybe this could help you somehow ... any idea, what went wrong?

Thanks a lot,
Ravey76

[Ben_S]

By user I mean the actual mysql database user, ie the user whos username you put into settings.php.

You would need to ask your host about this, assuming this is what is causing it.

[Ravey76]

@ Ben:

Okay, I tried to reach my host and I wrote a long email about my problem - but it seems that this is not of their interests to help a customer ... 

So I tried to fix it / do something by myself and found at the MySQL manual a chapter about GRANTing privileges for TEMPORARY TABLESPACE CREATION (Link: ttp://dev.mysql.com/doc/mysql/en/GRANT.html )

Okay, this is everything well described - but now I have an other problem:

everytime I try to set the user's permission to GRANT temp.tabslespacecreation, I receive an error message like:  ERROR 1044: Access denied for user: 'blabla@blabla' to database 'blabla'

Okay, I am a DB-Nerd, but is there ANY way to solve this problem, without my host? I don't think my host will do ANYTHING for me to solve this ...

Other question:  what changed in RC1? I had BETA5 and everything was running fine ...

thank you in advance,
Ravey76

Addition:  My DB is:  MySQL 4.0.12

[[Unknown]]

Your host has not given you access to use the GRANT command.

If your host won't let you create temporary tables.... I hope you're not paying them, or at least not paying them well.  People like Lewis Media treat you right for probably the same price. ($10 a month or so.... maybe it was $8... hey, i don't work there!)

'Course I'm just biassed because they host us.... but, there are other hosts here (charlottezweb, therealms, the list is long) who all are excellent hosts.  Going with a host that won't do something as simple as this... well, I consider it to be like your mechanic not wanting to fix an issue on your car, even if you've already paid them to do it!

I can understand some things, but I don't see any reason against temporary tables.  Basically all they do is make things better on the server... unless misused horribly.  But, you can misuse anything horribly.

-[Unknown]

[Oldiesmann]

You should probably just find a new host. Even Lycos UK lets you create temporary tables and their hosting is horrible. I only use them for testing stuff (especially stuff that might have a huge effect on the server load ).

[Ravey76]

@ [Unknown]:

Yes, it seems that looking for a new host is the only way.
A few minutes ago I received an answer from my host (Strato) to my question - here it is:

"Leider müssen wir aus Sicherheits und Performancegründen Einschränkungen bei der MySQL Instalation im sheared Webhosting machen."

Means (babelfish-translation):
Unfortunately we must make restrictions from security and reasons for performance with the MySQL Instalation in sheared Webhosting.

Okay - that means no chance to get the "search-option" running on Strato-Servers ... 
Definetly NO SERVICE from Strato - and that for almost 20 $ per month!

They tell me if I want to have this feature I have to upgrade to a "Full Server"; this costs 48 $ a month .... what a bullshxx!

The only thing why I am not moving to another host is ... my website has grown (in the last 5 years) over 150 (!) MByte and needs a lot of installation work (many scripts) ... but drop by drop and some day I will kick out Strato ...

Is there any kind of "workaround" possible? You (all SMF-Developers) made a good job compatibility to "misconfigured servers".
Do you have any idea for a workaround for this situation?

Regards,
Ravey76

[Oldiesmann]

I can't believe they want you to pay them $48/month for a dedicated server just so you can get a better MySQL installation. Sounds more like "we don't know what we're talking about but we think that MySQL will be more secure if we disable some stuff".

[Ravey76]

@ Oldiesmann:

Have a look: http://www.strato.de/server/ 
The server on the left side is what they said I need (for 39 Euros/Month ... this is about 48$ ...)

In general I don't have the imrpession the guys from Strato know what they are doing ...

At the moment I have a "Premium XE"-contract; see:
http://www.strato.de/premium/index_c.html

but the only "Premium" thing on this is the bullsxxt (I'm sorry) they are doing ...

Kind regards,
Ravey76

[Oldiesmann]

For $15/month:
Lewis Media's "Medium" package (300MB of space, 15GB of bandwidth) - http://www.lewismedia.com/hosting.php
Eniki.net's new "Bulk 50G" package (5GB of space, 50GB of bandwdidth) - http://www.eniki.net/?p=bulksale (this is my host)

[neonet01]

I'm having the same sreatch problem, but I host my self and have full control, I didn't have this problem in the last vertion, why is the new vertion any different.

Forum: http://forum.caservers.net

[Ravey76]

Good question ...  I had Beta 5, too, and it was running very good. Whats the difference between Beta 5 and RC1 concerning the search-function?

Beta5 was definetly running without "temp. tablespaces" ...

[Ben_S]

I'm having the same sreatch problem, but I host my self and have full control, I didn't have this problem in the last vertion, why is the new vertion any different.

Forum: http://forum.caservers.net

Just modify your mysql user, you can do it though phpmyadmin when logged in as root, to allow the user to create temporary table.

The search in beta 5 was not too efficient if you had a large amount of messages, in RC1 it is much quicker.

There is no security issue with allowing users to create temporary tables.

[neonet01]

Can't I just go edit some config file, I can do phpmyadmin but I don't want to unless I have to.

[Ravey76]

@ Ben:

As I mentioned ... I cannot loggin as root ... no permission from Host ...
Is there really no other way than changing my host? I mean: search was running fine and now not ... is there really no "workaround" for this situation?

THX,
Ravey76

[neonet01]

I say contact your host, see if you can work something out.

[Aquilo]

if this search feature problem cant be fixed by your host or you don't want to set Create_tmp_table_priv then you can get a work-a-round by using the Search.php and Search.template.php from SMF 1.0 Beta 4, or it does for me!

but then I'd be getting a new host if they got you like that, hell mine has even bent backwards setting root crons for me! and they have massive restrictions, the one I hate is base dir restriction!

any way I thought I'd let you know SMF B4 Search files still work in RC1!

[Ravey76]

@ Aquilo:

THAT'S  IT!  Thanks a lot for that idea! I took the BETA5-files, uploaded them - perfect!

Okay, the problem itself still exist (not grantet access privileges by host), but now I have time to think about it if I will further stay @ Strato.

Regards,

Ravey76

[[Unknown]]

That was exactly what I was going to suggest.

However, I must warn you that it will be much slower that way - for example, on this forum, it was unbearably slow.  A temporary table was used to speed things up.

If they really think denying temporary tables is more secure.... they are not competent enough to be hosting you, as I said before.  Again, just my opinion... I can't understand why hosts ban things left and right when they don't even know what they are.

We are working on a solution for people who do not have CREATE TEMPORARY privileges.

-[Unknown]