Exporting Stored Procedures

[expired user #6273]'s profile image [expired user #6273] posted 12 years ago in Import/Export Permalink
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.
jfalch's profile image jfalch posted 12 years ago Permalink
what exactly happens when you "can't seem to export them successfully" ? error messages ?
[expired user #6273]'s profile image [expired user #6273] posted 12 years ago Permalink
The exported stored procedure will look like this in the dumped file:

DROP FUNCTION IF EXISTS `_$myprocedure`;
DELIMITER //
//
DELIMITER ;


As you can see there's no sql code in between the delimiters.
ansgar's profile image ansgar posted 12 years ago Permalink
What does
SHOW CREATE FUNCTION `_$myprocedure`;

give you in the "Create function" column? Could be that it's empty for some reason.
ansgar's profile image ansgar posted 12 years ago Permalink
Ah, and about which version of HeidiSQL are we talking?
[expired user #6273]'s profile image [expired user #6273] posted 12 years ago Permalink
If I do:

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
ansgar's profile image ansgar posted 12 years ago Permalink
That's the problem: "Create function" is NULL. Heidi uses the contents of that column to fetch the procedure code. If it's empty, Heidi fails to export it. So, why is it empty on your side? Not so here. Is it probably a function to which you have no privilege?
[expired user #6273]'s profile image [expired user #6273] posted 12 years ago Permalink
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.

ansgar's profile image ansgar posted 12 years ago Permalink
> I know for a fact that "Create Function" is not null

See your own result of SHOW CREATE FUNCTION above, please, it is NULL.
[expired user #6273]'s profile image [expired user #6273] posted 12 years ago Permalink
@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.
jfalch's profile image jfalch posted 12 years ago Permalink
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.
jfalch's profile image jfalch posted 12 years ago Permalink
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.
[expired user #6273]'s profile image [expired user #6273] posted 12 years ago Permalink
@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.
ansgar's profile image ansgar posted 12 years ago Permalink
See also this thread.
[expired user #6273]'s profile image [expired user #6273] posted 12 years ago Permalink
Fix: make sure the "SELECT" option is at least selected for the desired user under "Global Privileges" in HeidiSQL's User Manager screen. That did it!

@jfalch @anse Thank you for helping me figure this out.

Please login to leave a reply, or register at first.