What settings or user privileges does someone need to export or dump stored procedures to a file?
I have some stored procedures in my local machine running MySql server and I'm able to export my stored procedures. However, we have a box at work, which I use HeidiSQL to connect to of course, but when I try to export the stored procedures stored in that box, I can't seem to export them successfully. So what again, what settings or user privileges does one need to export stored procedures?
Thank you in advanced.
Exporting Stored Procedures
If I do:
I get a row result with the following column names and values under them:
Function = _$myprocedure
sql_mode =
Create Function = NULL
character_set_client = utf8
collation_connection = utf8_general_ci
Database_collation = utf8_general_ci
note*: sql_mode is blank.
I'm using HeidiSQL 7.0.0.4081
SHOW CREATE FUNCTION `_$myprocedure`;
I get a row result with the following column names and values under them:
Function = _$myprocedure
sql_mode =
Create Function = NULL
character_set_client = utf8
collation_connection = utf8_general_ci
Database_collation = utf8_general_ci
note*: sql_mode is blank.
I'm using HeidiSQL 7.0.0.4081
Well, I know for a fact that "Create Function" is not null because I can call the stored procedure from our code and it returns the appropriate results.
The stored procedure was created under a different user and it seems that HeidiSQL enters the current user as the "Definer". So basically what I'm seeing is that any stored procedure that was created by some else, I can't seem to export or see its code definition.
Is there a way to tell HeidiSQL not to include a Definer when creating a stored procedure?
As far was user privileges, I have all the privileges except to create new users.
The stored procedure was created under a different user and it seems that HeidiSQL enters the current user as the "Definer". So basically what I'm seeing is that any stored procedure that was created by some else, I can't seem to export or see its code definition.
Is there a way to tell HeidiSQL not to include a Definer when creating a stored procedure?
As far was user privileges, I have all the privileges except to create new users.
@anse I know what you mean but, my speculation, I think it is coming out as NULL because of some security setting, maybe, so in turn the show command can't retrieve the creation code. Same thing as when I try to view the stored procedures that were created by someone else and HeidiSQL can't show me any of the code. All the stored procedures have code, but for some reason I can't view them.
The mysql docs say that SHOW CREATE PROCEDURE|FUNCTION "require that you be the owner of the routine, or have SELECT access to the mysql.proc table. If you do not have privileges for the routine itself, the value displayed for the Create Procedure or Create Function field will be NULL.".
It seems to me that that is what is happening in your case; ie you do not have sufficient privileges on the production machine to view (SELECT from) the mysql.proc table.
Adding SELECT privilege for the mysql.proc table to your mysql user by a db admin should probably remove the problem.
It seems to me that that is what is happening in your case; ie you do not have sufficient privileges on the production machine to view (SELECT from) the mysql.proc table.
Adding SELECT privilege for the mysql.proc table to your mysql user by a db admin should probably remove the problem.
NB: even if you cant view the procedure, another privilege EXECUTE enables you to execute it nonetheless; ie if EXECUTE is set for your mysql user on mysql.view (?) but SELECT is not, the situation should be as you describe it: can execute routine, but SHOW CREATE PROCEDURE (and thus heidisql) shows its code as NULL.
@jfalch that makes more sense. It's probably most likely that I don't have SELECT access to the mysql.proc table...hmm, but then again I can select any other table and view table contents.
Thanks for your input. I will play around locally with user privileges and see if I can create the problem.
Thanks for your input. I will play around locally with user privileges and see if I can create the problem.
See also this thread.
Please login to leave a reply, or register at first.