CODEWITHSUNDEEP
mysqlmysql-workbench
Tamar E. Granor
Tamar E. Granor

Reputation: 3937

Can't open stored procedures with MySQL Workbench

I have a MySQL database hosted on Dreamhost. I've defined a single user for that database, with full rights.

I've just started using MySQL Workbench. I had no trouble connecting to the database. But when I try to view the contents of stored procedures that I created using the web client provided by Dreamhost, I get nothing. By nothing, I mean that right-click | Send to SQL Editor | Create statement does nothing. No new tab, no error message, nothing. Ditto for right-click | Send to SQL Editor | Procedure Call. Ditto when clicking the wrench or lightning icons.

Based on something I found online, it appears to be a rights issue. But when I choose Server | Users and Privileges to try to fix it, I get this message:

"The account you are currently using does not have sufficient privileges to make changes to MySQL users and privileges."

How can that be when there's only one user and that user has all rights?

Upvotes: 5

Views: 8342

Answers (3)

Sarel Botha
Sarel Botha

Reputation: 12710

On mysql 8.0.20+ run this to give a user permission to view the procedure:

grant show_routine on *.* to <MYUSER>;

Run this to give the user permission to edit and run procedures:

grant execute, create routine, alter routine on <MYDB>.* to <MYUSER>;

Upvotes: 1

Quan Nguyen
Quan Nguyen

Reputation: 5134

For me although I granted full privilege on that database, I still cannot view it's store procedure.

So I have to run this:

GRANT SELECT ON mysql.proc TO yourusername@'%';

Problem solved !!

Upvotes: 4

Tamar E. Granor
Tamar E. Granor

Reputation: 3937

I was able to solve the immediate problem by opening the SP in phpAdmin on the Dreamhost site and cutting and pasting the code into MySQL Workbench, recreating the SP from there. That does seem, however, to keep me from editing the SP on Dreamhost. Clearly something's still wrong, but I can move forward.

Upvotes: 0

Related Questions