Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

mysql 5.0 stored procedures

Discussion in 'General Discussion' started by teknowebworks, Nov 28, 2006.

  1. teknowebworks

    teknowebworks Active Member

    Joined:
    Mar 9, 2004
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    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?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #1 teknowebworks, Nov 28, 2006
  2. cyon

    cyon Well-Known Member
    PartnerNOC

    Joined:
    Jan 15, 2003
    Messages:
    320
    Likes Received:
    0
    Trophy Points:
    166
    I have the same problems over here, so I bump this thread and ask again: Has anybody a solution for this task?

    Thanks!
     
    #2 cyon, Dec 4, 2006
  3. merm

    merm Member

    Joined:
    Aug 24, 2006
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    151
    I'm having exactly the same issue. Haven't found a solution yet.
     
    #3 merm, Dec 30, 2006
  4. dru383

    dru383 Registered

    Joined:
    Apr 5, 2006
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    151
    Same problem here too.
     
    #4 dru383, Jan 3, 2007
  5. Franzh

    Franzh Member

    Joined:
    Dec 16, 2004
    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    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!
     
    #5 Franzh, Jan 4, 2007
  6. teknowebworks

    teknowebworks Active Member

    Joined:
    Mar 9, 2004
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #6 teknowebworks, Jan 4, 2007
  7. manokiss

    manokiss Well-Known Member

    Joined:
    Mar 31, 2002
    Messages:
    575
    Likes Received:
    0
    Trophy Points:
    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!
     
    #7 manokiss, Feb 24, 2007
  8. talkrowing

    talkrowing Registered

    Joined:
    Jul 24, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    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?
     
    #8 talkrowing, Jul 24, 2007
  9. teknowebworks

    teknowebworks Active Member

    Joined:
    Mar 9, 2004
    Messages:
    30
    Likes Received:
    0
    Trophy Points:
    156
    It sounds like your host recompiled mysql again, check to see if they ran mysql_upgrade
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #9 teknowebworks, Jul 24, 2007
  10. talkrowing

    talkrowing Registered

    Joined:
    Jul 24, 2007
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    51
    Thanks for the suggestion - Is there a way to check this without asking them? I think they're getting fed up of me!
     
    #10 talkrowing, Jul 24, 2007
  11. mtindor

    mtindor Well-Known Member

    Joined:
    Sep 14, 2004
    Messages:
    1,338
    Likes Received:
    56
    Trophy Points:
    178
    Location:
    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

    root@mach1 [/home/oht/public_html/dev]# /scripts/restartsrv_mysql
    root@mach1 [/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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #11 mtindor, Jul 9, 2008
  12. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,561
    Likes Received:
    42
    Trophy Points:
    308
    cPanel Access Level:
    Root Administrator
    Do it from within the cPanel interface.

    Granting privileges requires administrative level privileges and thus is limited to the MySQL root account.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #12 cPanelKenneth, Jul 10, 2008
(You must log in or sign up to post here.)
Loading...
Similar Threads - mysql stored procedures
  1. RobNH

    MySQLi - Too Many Connections Error due to Amazon AWS

    RobNH, Jun 21, 2018 at 7:47 AM, in forum: Database Discussion
    Replies:
    5
    Views:
    35
    Infopro
    Jun 21, 2018 at 10:03 AM
  2. geekbaaz

    Multiple cPanel servers with a common MySQL server?

    geekbaaz, Jun 21, 2018 at 3:24 AM, in forum: Database Discussion
    Replies:
    2
    Views:
    53
    cPanelMichael
    Jun 21, 2018 at 11:32 AM
  3. cosmin

    MySQL InnoDB errors after upgrade to 5.7

    cosmin, Jun 20, 2018 at 11:46 PM, in forum: Database Discussion
    Replies:
    2
    Views:
    39
    cPanelMichael
    Jun 21, 2018 at 10:31 AM
  4. tyuuu

    mysql_pconnect can not work after change password/username

    tyuuu, Jun 19, 2018 at 9:14 AM, in forum: Database Discussion
    Replies:
    10
    Views:
    56
    cPanelLauren
    Jun 20, 2018 at 9:38 AM
  5. Skin

    Migration from VPS with MySQL 5.6 to a VPS with MySQL 5.7

    Skin, Jun 18, 2018 at 11:04 AM, in forum: Database Discussion
    Replies:
    2
    Views:
    32
    Skin
    Jun 19, 2018 at 12:09 PM

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice