cPanel Question posted in
The official documentation can be seen here.

PHPMyAdmin: You might be lacking the necessary privileges to edit this routine – CPanel

Error in processing request: No routine with name ‘daily_difference’ found in database ‘chamelis_wp’. You might be lacking the necessary privileges to edit this routine.

enter image description here

Tags:

4

Answers


  1. 0

    It sounds like you don’t have permission to edit it, or you’re trying to edit it from the wrong DB (schema). Run the following to see which DB it is on:
    show function status like ‘%daily%’;
    show procedure status like ‘%daily%’;

    Are you trying to edit it by executing a statement or by using the gui? If by executing a statement, can you provide your statement? If by statement, make sure you’ve run this first:
    use db_name_here;

    Then try to edit your routine again.

    Did your user create the procedure, or another user created it? You may not have permission to modify an user’s procedure. You can see who created it by running:
    show create procedure daily_difference;

    Then see if you have permission to edit procedures by running the below statement – you need “ALTER ROUTINE” privilege:
    show grants for Your_username_here;

    If a user with super privilege created it, you won’t be able to edit it – you’ll need them to, or you will need to get super privilege.

    Login or Signup to reply.
  2. 0

    I’m replying partly as I have a work around and partly for the comedy, as GoDaddy sent me this page as a solution to this problem where, yes the problem is defined but no, there’s no solution.

    I had to rebuild my SPs in phpMyadmin but for the DEFINER where root@localhost was present, using an account I had created and could use – account@localhost. cPanel doesn’t expose the root account AFAIK.

    I didn’t have all my SPs’ definitions elsewhere. But I found that in cPanel files backup the files from there of the MySQL DBs had the SP definitions. That may not be in your hosting package. Backing up from phpMyAdmin didn’t help as I still lacked the privileges to make the back up with the definitions.

    Login or Signup to reply.
  3. 0

    If you are using phpMyAdMin (pma) under cPanel, this error results from the fact that cPanel creates a temporary user for every session to log into pma, and therefore the database. If you are working in a hosted environment where you cannot be granted SUPER privileges, and you are creating routines, you are in trouble. Read on.

    Stored routines (procedures and functions) in MySQL/Mariadb have a property called DEFINER. This stores the username of the the database user who "owns" that routine. If a db user opens phpMyAdmin (pma) and creates a new routine without explicitly declaring the DEFINER, DEFINER will be populated with the current username. Only a db user that has SUPER permissions can create a routine and declare another user as the DEFINER. Non-SUPER users can only declare themselves as the DEFINER, or leave it blank and the db does it for them. So the db user that you are logged in as gets to be the DEFINER for all routines you create during that session.

    The problem you have encountered arises because

    • Only the DEFINER of a routine (or a user with SUPER permissions) can
      edit or export a routine. If another (non-SUPER) user tries to edit
      or export an existing routine, they will get the error you are
      seeing, and will not get access to the routine.

    • cPanel logs you into pma using a temp username that it generates.
      Even if you have created database users in cPanel, it will not use
      any of these to launch pma. You don’t get to see the pma login page –
      the login happens off-screen – and you are taken straight into the
      pma home page. You have no control over the username/password used.

    • Whenever you start a new cPanel session, cPanel creates a new db user
      name to log you into pma.

    • If you create a routine during a cPanel/pma session, the cPanel
      username for that session becomes the DEFINER for that routine. If
      you close pma, log out of cPanel, log back into cPanel then reopen
      pma, the db user name will change. The new user cannot edit or export
      the routine. The only way the new user can get access to the routine
      is to recreate it using a script without a DEFINER statement (from a
      backup) in the current session so they now become the DEFINER. This
      must happen every time you start a new cPanel session.

    You can have a similar issue if you connect to a server-hosted database externally using say a local MySQL Workbench. You control the username, but it will have your IP address appended (e.g. [email protected]). You won’t have access to any routines created in cPanel/pma (unless you recreate them), but provided your IP address does not change, you will have ongoing access to any routines you create in the external environment. If you have a permanent IP, all is well indefinitely. But if your IP is dynamic, then you are back to the same problem encountered in cPanel/pma, and you have to recreate all routines each time your IP address changes.

    MY SOLUTIONS: I always use external access and a local copy of MySQL Workbench to access my databases and always have a current backup of my routines.

    I have developed a script based on mysqldump.exe that I run daily (sometimes more often) as a scheduled task on my local PC. It backs up my online database (structure and data into one .SQL file, routines into another). The filenames are datetime-stamped so no backups are ever overwritten. This is it – you should adapt the paths to match your setup, and enable remote access to your database for it to work.:

    @echo off
set X=
for /f "skip=1 delims=" %%x in ('wmic os get localdatetime') do if not defined X set X=%%x
set DATE.YEAR=%X:~0,4%
set DATE.MONTH=%X:~4,2%
set DATE.DAY=%X:~6,2%
set DATE.HOUR=%X:~8,2%
set DATE.MINUTE=%X:~10,2%
set DATE.SECOND=%X:~12,2%

@echo on
"C:...mysqldump.exe" --host=example.com --protocol=tcp --user=myusername --password=xxxxx --force=TRUE --port=3306 --default-character-set=utf8 --single-transaction=TRUE --result-file "X:...DAILY BACKUPDATASQLDATA_%DATE.YEAR%%DATE.MONTH%%DATE.DAY%_%DATE.HOUR%%DATE.MINUTE%%DATE.SECOND%.SQL"          --triggers  "mydatabasename"
"C:...mysqldump.exe" --host=example.com --protocol=tcp --user=myusername --password=xxxxx --force=TRUE --port=3306 --default-character-set=utf8 --single-transaction=TRUE --result-file "X:...DAILY BACKUPROUTINESSQLROUTINES__%DATE.YEAR%%DATE.MONTH%%DATE.DAY%_%DATE.HOUR%%DATE.MINUTE%%DATE.SECOND%.SQL" --routines --no-create-info --no-data --no-create-db --skip-opt  "mydatabasename"
pause

    ================= update April 2023 =================

    Subsequent to my posting this answer, I have discovered that you can use the cPanel’s Backup Wizard to export a complete database, including routines, regardless of the DEFINER. So routines that you can no longer access through pma can be exported using this wizard and reimported through pma, which re-establishes your ability to edit them.

    Hope that helps.

    Login or Signup to reply.
  4. 0

    I FIX THIS ISSUE IN CPANEL

    This shows that there is lack of routines ( Function, Procedures ) privilege for the current user. This will cause even the edit button of routines to be disabled.

    In CPanel there is default user that you can not manage their privileges, So, you can not add or remove their privileges.

    <<<<< HOW I FIX IT >>>>>:

    FIRST

    Drop all routines(Function, Procedure) that you have no privilege from your database

    SECOND ( import sql file again )

    Find the sql file used to import database and remove DEFINER=root@localhost everywhere.
    Then import Edited sql file after remove DEFINER=root@localhost .

    I think this solution can help someone.
    Thanks

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search