distal-attribute
distal-attribute
distal-attribute
distal-attribute

Exporting Stored Procedures

User, date Message
Written by adonix
2 years ago
Category: Import/Export
7 posts since Wed, 07 Mar 12
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.
Written by jfalchMoney, Euro
2 years ago
383 posts since Sat, 17 Oct 09
what exactly happens when you "can't seem to export them successfully" ? error messages ?
Written by adonix
2 years ago
7 posts since Wed, 07 Mar 12
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.
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
What does

SHOW CREATE FUNCTION `_$myprocedure`;


give you in the "Create function" column? Could be that it's empty for some reason.
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
Ah, and about which version of HeidiSQL are we talking?
Written by adonix
2 years ago
7 posts since Wed, 07 Mar 12
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
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
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?
Written by adonix
2 years ago
7 posts since Wed, 07 Mar 12
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.

Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
> I know for a fact that "Create Function" is not null

See your own result of SHOW CREATE FUNCTION above, please, it is NULL.
Written by adonix
2 years ago
7 posts since Wed, 07 Mar 12
@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.
Written by jfalchMoney, Euro
2 years ago
383 posts since Sat, 17 Oct 09
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.
Written by jfalchMoney, Euro
2 years ago
383 posts since Sat, 17 Oct 09
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.
Written by adonix
2 years ago
7 posts since Wed, 07 Mar 12
@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.
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
See also this thread.
Written by adonix
2 years ago
7 posts since Wed, 07 Mar 12
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.