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

Question

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.

Chiffon

Do you indeed have permissions to edit the routine? Can you try it when logged in as the root or some other administrative user? What are the permissions of the user you're logged in as? – Octopus

I am the one who created the database and I always use the same credentials. The user has all the permissions – Chiffon

Answer 1

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 BACKUP\DATA\SQLDATA_%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 BACKUP\ROUTINES\SQLROUTINES__%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.

Lycaon

Answer 2

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

Amero

Answer 3

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.

Cyn

Thank you for the response Jason. I am in the right DB and the routine exists. I have posted a screenshot in the question description for reference. – Chiffon

This isn't a solution, but might help to troubleshoot and reach the end goal at the same time. I would try to drop the procedure and then create it again. Does this work, or do you get a similar error? – Cyn

Answer 4

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.

Broken

I haven't gone back to the issue, but during the time, I had to drop all the procedures and write them again everytime I wanted to change anything – Chiffon