4

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?

StuffJoy
  • 41
  • 1
  • 1
  • 3

2 Answers2

3

SUGGESTION #1

If you login as root@localhost, you can change the definer in mysql.proc

In 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 !!!

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • 1
    Hi @RolandoMySQLDBA, thanks for your response. Re your Suggestions.

    Suggestion #1 I am using a shared server space, and as such I will be unable to access an account with more proviledges than the one I currently have. Accessing mysql.* or updating anything in the information_schema will result in an "Accessed denied" message.

    Suggestion #2 I tried using PHOMyAdmin's export feature. This produced only stored procedures created in PHOMYADMIN. Those done through word bench only returned the drop command, and not the procedure itself. I will try the dump command and see if resuts differ.

     – StuffJoy Feb 16 '15 at 14:45
  • @StuffJoy did you find the solution ? same case here. – Tilak raj Oct 24 '16 at 22:12
2

In SQL Workbench, do not connect through Standard (TCP/IP), connect through Standard (TCP/IP) over SSH. This way, you'll be able to use the same user for the definer as you're using now in PhpMyAdmin (just set SSH Username and Username to the same user PhpMyAdmin is running under), and the same host (just set the MySQL Hostname in the SQL Workbench connection screen to localhost).

jchevali
  • 91
  • 3