myDBR Forums » General discussion

Unable to run reports while migrating to new server

(2 posts) (2 voices)

  1. cpasqu1n1, Member

    I am in the process of migrating MyDBR to a new server. The setup of the new server has worked fine and I am able to access MyDBR on my new server.

    I can also see all of my reports, however I am running into a problem executing reports on the NEW server due to the MySQL stored procedure's being inaccessible. The error I am receiving is this:

    ###
    Permission problem in MySQL reading report's parameters!

    In order for myDBR to read report's parameters, reports needs to be created under the same user account the myDBR is running (mydbr@10.24.148.26).
    The report 'sp_DBR_pricing_report' is now created under username mydbr@10.24.148.46.
    ###

    I am using the latest version of the MyDBR software on both servers (myDBR 7.1.0 (build 5265)) and we are using MySQL 8.0.34.

    I am not able to find anything in the documentation that details how to migrate from one server to another when running into this issue. We have over 450 stored procedures and I am looking for steps on how to resolve this issue.

    In prior versions of MySQL we were able update the Definer in the `mysql.proc` table but that no longer exists in MySQL 8.

  2. myDBR Team, Key Master

    You've correctly identified that the issue is related to the DEFINER setting. When a stored procedure is created, the login that creates the routine is assigned as the DEFINER (owner). The DEFINER account has permission to read the source code of the routine, including its parameters.

    When you create a backup of the database using mysqldump, the backup includes the original DEFINER in the format login@host. If this no longer matches the current environment, MySQL treats it as a different login. This is just how MySQL works.

    The easiest way to fix this is to make a backup of the database, remove/replace the DEFINER statements from the backup file, and reload the backup using the correct login. Routines without a DEFINER clause will be automatically assigned to the user running the load command.

    --
    myDBR Team


Reply

You must log in to post.