mysql 5.0 stored procedures

teknowebworks

Active Member
Mar 9, 2004
30
0
156
I have a user that wants to use stored procedures (the main reason to upgrade to 5), anyway i did the upgrade it went well, however when she tried to create a one she get's an access denied.

PHPmyadmin seems to be in a wierd state, it says the client version is 4.x but sql says it's 5.x. I can't see any permissions in phpmyadmin.

I tried to do permissions manual

use db
grante create procedure * to user blah

that didn't work, anyone get stored procedures to work from a user account?
 

cyon

Well-Known Member
PartnerNOC
Jan 15, 2003
320
0
166
I have the same problems over here, so I bump this thread and ask again: Has anybody a solution for this task?

Thanks!
 

Franzh

Member
Dec 16, 2004
9
0
151
The MySQL client is staticly compiled into PHP. Just recompile Apache/PHP afterwards. Also SP requires that you use the mysqli connection.
Hope this works!
 

teknowebworks

Active Member
Mar 9, 2004
30
0
156
Actually, recompliling doesn't seem to help. I solved the problem by running mysql_upgrade as root, to update the system tables from 4 to 5. Seems to have worked, since users can now do sprocs.
 

manokiss

Well-Known Member
Mar 31, 2002
575
0
316
Hi,
Wondering what was the workaround to restrict the procedures only to the user dbases.

As far as i know doing:

grant create procedure * to user blah

is allowing to the user create and execute the procedure in and dbase in the server.

Ty!
 

talkrowing

Registered
Jul 24, 2007
2
0
51
Sorry for digging up an old thread, but this issue is killing me. My host (ukhost4u) seem to be struggling to fix this issue. One minute stored procs were fine and dandy, next minute I can't create edit or execute any. Same error as above eg on execute:

execute command denied to user 'XXX'@'%' for routine YYY

Did anyone have a workable solution?
 

teknowebworks

Active Member
Mar 9, 2004
30
0
156
It sounds like your host recompiled mysql again, check to see if they ran mysql_upgrade
 

talkrowing

Registered
Jul 24, 2007
2
0
51
Thanks for the suggestion - Is there a way to check this without asking them? I think they're getting fed up of me!
 

mtindor

Well-Known Member
Sep 14, 2004
1,363
65
178
inside a catfish
cPanel Access Level
Root Administrator
I'm also having this problem.

MySQL 5.0.51
PHP 5.26
Apache 1.3.39

I just recompiled PHP 5 to support mysqli. mysqli is working fine. However, none of my users are able to do the necessary things to allow their sql users that they create to execute a procedure.

I ran mysql_upgrade (no problems) and then restarted mysql via /scripts/restartsrv_mysql.

Here is what happens

[email protected] [/home/oht/public_html/dev]# /scripts/restartsrv_mysql
[email protected] [/home/oht/public_html/dev]# mysql -u oht -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 277
Server version: 5.0.51a-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant execute on procedure oht_database.GNZ to 'oht_sql'@'localhost';
ERROR 1370 (42000): grant command denied to user 'oht'@'localhost' for routine 'oht_database.GNZ'
mysql>


oht is the primary account username
- access to mysql is given

oht then tries to run the grant command to grant oht_sql the ability to execute the stored procedure oht_database.GNZ - that's when we get the error.

Obviously as root I am able to issue the grant and everything works fine. But, if users cannot set up their own mysql users with the ability to create/execute stored procedures, there is no use having this functionality on the server... right?

Any suggestions folks?

Mike
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,607
77
308
cPanel Access Level
Root Administrator
I'm also having this problem.

MySQL 5.0.51
PHP 5.26
Apache 1.3.39

I just recompiled PHP 5 to support mysqli. mysqli is working fine. However, none of my users are able to do the necessary things to allow their sql users that they create to execute a procedure.

I ran mysql_upgrade (no problems) and then restarted mysql via /scripts/restartsrv_mysql.

Here is what happens

[email protected] [/home/oht/public_html/dev]# /scripts/restartsrv_mysql
[email protected] [/home/oht/public_html/dev]# mysql -u oht -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 277
Server version: 5.0.51a-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant execute on procedure oht_database.GNZ to 'oht_sql'@'localhost';
ERROR 1370 (42000): grant command denied to user 'oht'@'localhost' for routine 'oht_database.GNZ'
mysql>


oht is the primary account username
- access to mysql is given

oht then tries to run the grant command to grant oht_sql the ability to execute the stored procedure oht_database.GNZ - that's when we get the error.

Obviously as root I am able to issue the grant and everything works fine. But, if users cannot set up their own mysql users with the ability to create/execute stored procedures, there is no use having this functionality on the server... right?

Any suggestions folks?

Mike
Do it from within the cPanel interface.

Granting privileges requires administrative level privileges and thus is limited to the MySQL root account.