myDBR upgrade - problem with stored procedures

(6 posts) (2 voices)
  1. dbrb2, Member

    Hello,

    Having upgraded myDBR, I now have a permissions problem with my stored procedures:

    "
    Cannot determine report's parameters.
    In order for myDBR to read report's parameters, reports needs be be created under the same user account the myDBR is running (mydbr).
    "

    Does this mean that if the procedures were not created on the db when logged in as mydbr they will not run (they used to...)

    Cheers,

    Ben

  2. myDBR Team, Key Master

    Ben,
    in MySQL, only the root or user that created the procedure is allowed to see the procedure source code and hence the parameters. In order to allow myDBR to operate under non-root username, myDBR 1.2 uses new method of determining the procedure parameters. You might have a situation where the procedures are created under another username than what you are currently using to run myDBR (i.e 'mydbr').

    In order to fix the problem you have two options:

    1. Recreate the procedures under username 'mydbr'.
    2. Run myDBR under the same username which was used to create the procedures (perhaps 'root').

    If you created the procedures originally under username 'root', we'd recommend that you choose option 1. This requires dumping the procedures from the database (if you do not have them stored elsewhere), drop and recreate them under username 'mydbr'. You can do all this in MySQL Query Browser if you wish.

    --
    myDBR Team

  3. dbrb2, Member

    Hi - that was the first thing I tried. I logged into query browser as the user that created the procedures, took a copy of the procedure SQL, and then logged in using the mydbr login, and recreated them.

    They run fine from query browser, but I get the same error as before from the mydbr frontend. The only difference in the logins used is that mydbr is logging into mysql from localhost, wheras I am logging in remotely.

    Any ideas?

  4. myDBR Team, Key Master

    They run fine from query browser, but I get the same error as before from the mydbr frontend. The only difference in the logins used is that mydbr is logging into mysql from localhost, wheras I am logging in remotely.

    The permission issue is a design decision in current MySQL.

    If you have created mydbr user with connections allowed from localhost ('mydbr'@'localhost') and from your machine ('mydbr'@'mymachine'), MySQL treats them as separate users.

    The most common way around this is to operate MySQL client through a SSH tunnel. Open a SSH tunnel to the server (% ssh -L3309:localhost:3306 myaccount@myserver) and point your MySQL client (like MySQL Query Browser) through the opened port (3309) in your own machine. This way the connection to MySQL is done in the server and therefore it uses the same account as myDBR. This has also an advantage that you can operate your MySQL from any location and only need one MySQL account.

    Alternatively you can create the mysql user with '%' in host part ('mydbr'@'%') and use that account for all your actions. This has a disadvantage that it opens MySQL to all users and is therefore not recommended.

    Third options is simply to run the procedure creations from the server, but this might not be as convenient as operating from your own PC.

    --
    myDBR Team

  5. dbrb2, Member

    If you have created mydbr user with connections allowed from localhost ('mydbr'@'localhost') and from your machine ('mydbr'@'mymachine'), MySQL treats them as separate users.

    Ah...

    I tried putting a wildcard (%) into the mydbr config file in place of localhost, but it wouldn't accept it. In the end, I went for a ssh tunnel as you suggested, which is a nicer solution for the reasons you gave, and worked.

    Thanks for the help.

    Out of interest, do you know what the future face of this project is likely to be? Charge for the product? Charge for support? Presumably both will not be free forever...?

  6. myDBR Team, Key Master

    I tried putting a wildcard (%) into the mydbr config file in place of localhost, but it wouldn't accept it. In the end, I went for a ssh tunnel as you suggested, which is a nicer solution for the reasons you gave, and worked.

    In order to utilize 'user'@'%' login in MySQL, you need to point to the server with it's name instead of 'localhost'.

    Out of interest, do you know what the future face of this project is likely to be? Charge for the product? Charge for support? Presumably both will not be free forever...?

    We continue to improve the product based on our own roadmap and user feedback. There will be supports contracts available and we are planning on a "Pro" version with additional functionality.


Reply

You must log in to post.