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
  • 185,223
  • 33
  • 326
  • 536
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