patternsqlMinor
Update MySQL Stored Procedure Definers on shared environment
Viewed 0 times
storeddefinersupdatesharedprocedureenvironmentmysql
Problem
I am accessing a MYSQL database through PHPMyADMIN.
My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL Workbench. Routines created on Workbench are visible in PHPMyAdmin, but cannot be viewed or opened there or vice versa.
I believe the issue lies with the Definer that was generated upon the Routine's creation.
When using PHPMyAdmin, Routines are created with definer 'dbname@localhost'.
But when done through Workbench, this varies I assume based on the Network I am currently connected to. The format of the stored procedures created outside of PHPMyAdmin are 'dbname@ipaddress'.
I am trying to change all the Definers to 'dbname@localhost', in the hopes that I will be able to open and manipulate all Routines from PHPMyAdmin.
Running any queries on mysql.* is not an option because the database is hosted in a shared environment and I don't have root permissions on the database server.
I also tried updating INFORMATION_SCHEMA but this also proved fruitless.
I have contacted Support and was told I may be able to find more help here.
I hope this was descriptive enough.
Is there any other way I will be able to update these definers?
My issue is that throughout the course of a project, my Stored Procedures were created through two main places. PHPMyAdmin, and through MySQL Workbench. Routines created on Workbench are visible in PHPMyAdmin, but cannot be viewed or opened there or vice versa.
I believe the issue lies with the Definer that was generated upon the Routine's creation.
When using PHPMyAdmin, Routines are created with definer 'dbname@localhost'.
But when done through Workbench, this varies I assume based on the Network I am currently connected to. The format of the stored procedures created outside of PHPMyAdmin are 'dbname@ipaddress'.
I am trying to change all the Definers to 'dbname@localhost', in the hopes that I will be able to open and manipulate all Routines from PHPMyAdmin.
Running any queries on mysql.* is not an option because the database is hosted in a shared environment and I don't have root permissions on the database server.
I also tried updating INFORMATION_SCHEMA but this also proved fruitless.
I have contacted Support and was told I may be able to find more help here.
I hope this was descriptive enough.
Is there any other way I will be able to update these definers?
Solution
SUGGESTION #1
If you login as
In your case, you would do
I am not sure if the stored procedures load automatically into INFORMATION_SCHEMA.ROUTINES
If not, run these
SUGGESTION #2
Based on my old post Modify DEFINER on Many Views, you can do the following
GIVE IT A TRY !!!
If you login as
root@localhost, you can change the definer in mysql.procIn your case, you would do
UPDATE mysql.proc SET DEFINER = 'dbname@localhost';
SELECT name,type,definer FROM information_schema.routines;I am not sure if the stored procedures load automatically into INFORMATION_SCHEMA.ROUTINES
If not, run these
FLUSH PRIVILEGES;
FLUSH TABLES;
SELECT name,type,definer FROM information_schema.routines;SUGGESTION #2
Based on my old post Modify DEFINER on Many Views, you can do the following
- You may have to mysqldump the stored procedures into a test file
- Edit the definer user to 'dbname@localhost' in the text file
- Then, reload the stored procedures
GIVE IT A TRY !!!
Code Snippets
UPDATE mysql.proc SET DEFINER = 'dbname@localhost';
SELECT name,type,definer FROM information_schema.routines;FLUSH PRIVILEGES;
FLUSH TABLES;
SELECT name,type,definer FROM information_schema.routines;Context
StackExchange Database Administrators Q#91909, answer score: 3
Revisions (0)
No revisions yet.