Mysql – Update MySQL Stored Procedure Definers on shared environment

MySQLstored-procedures

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?

Best Answer

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

Related Solutions

Mysql – I cannot access local database on terminal (MAC)

It could be your admin user is connecting from admin@127.0.0.1.

Assuming admin is the actual user you have privileges on, try connecting through:

> mysql -h 127.0.0.1 -u admin -p

The way MySQL handles users, the host is very important, and in this case 127.0.0.1 is different than localhost.

If your phpMyAdmin user has access to the mysql database, you can the following statement to see what user/host combinations exist on your install.

 SELECT User, Host FROM mysql.user

Mysql – Access Drizzle databases through MySQL

Noting that you didn't ask whether it was a good idea, but only whether it is possible :) I would venture to say it seems like it should be possible, since Drizzle speaks the MySQL protocol over TCP -- as evidenced by the fact that you can use the "mysql" CLI client and other MySQL client libraries to connect to a drizzle server.

The "federated" storage engine in MySQL allows your "local" server to connect to a "remote" MySQL (Drizzle?) server using the standard MySQL C-client library (aha!), and remotely access one or more tables on the remote server as if they were tables on the local server... the "local" server essentially spoofs a client connection to the remote server... one connection per table, from what I've observed.

CREATE TABLE (
-- column definitions on the local server 
-- should be identical to the remote server
-- although the engine is more forgiving than
-- the documentation will lead you to believe 
) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://user:pass@remote_server:port/schema/table';

-- this is one of two ways to configure the actual connection

I believe this will work because the remote server doesn't know anything unusual is going on -- it thinks it's being accessed by a client, issuing ordinary INSERT/DELETE/SELECT/UPDATE statements, and queries on the local server interact with the federated tables just as they would with any other table.

If the "local" server were MySQL with your stored procedures, and federated tables configured to connect to the "remote" server -- the Drizzle server -- where your data lives, this should accomplish what you want, if I understand the question correctly. I use "local" and "remote" to describe their roles, of course, not their placement. For this application, they would most likely be on the same machine, or a very fast LAN.

Your application would connect to the MySQL server to call the stored procedures, which would then manipulate data on the Drizzle server via the federated connections.

How efficient will it be? Not nearly as efficient as having the stored procedures and data on the same DBMS.

Is it a good idea? I'm not too sure about that. But in principle, it's probably worth a shot.

The federated engine has gotten some bad press, but at least in MySQL 5.5, I find the criticism to be unmerited. I have numerous federated connections from 5.5 → 5.5 and 5.5 → 5.1 in my production systems.

There are a number of caveats that go along with the federated engine, such as no support for transactions, and queries involving federated tables aren't cached on the local server (there's no way to invalidate them, after all). They don't directly support foreign keys, but the foreign keys are still enforced by the remote server, which will throw an error that propagates back to you locally if you violate them (though it may not be the same MySQL error or SQLSTATE).

You also want be sure you define at least the same indexes on the "local" server as exist on the "remote" server -- and sometimes it may even make sense to declare indexes on columns that aren't really indexed on the far-end -- because if the optimizer decides a full table scan is needed, it actually requests the entire contents of the remote table over the wire from the far-end... usually not what you want. The indexes don't actually "exist" locally or take up any space on the local server -- but the query sent to the far end includes a WHERE clause to get only the appropriate rows, only if the optimizer realizes this is a possibility. (This fact does not seem quite as strange when you remember that federated is implemented as a storage engine and the core of MySQL server interacts with it as though it were indeed a storage engine.)

This document is several years old but provides some very good background on the federated storage engine.

Final note: yes, the federated engine is one of the things that got stripped out of Drizzle, but that's fine -- we only need it on the MySQL side for this application.