Exporting Stored Procedures

User, date	Message
Written by adonix 1 year 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 jfalch 1 year ago 222 posts since Sat, 17 Oct 09	what exactly happens when you "can't seem to export them successfully" ? error messages ?
Written by adonix 1 year 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 1 year ago 3949 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 1 year ago 3949 posts since Fri, 07 Apr 06	Ah, and about which version of HeidiSQL are we talking?
Written by adonix 1 year 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 1 year ago 3949 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 1 year 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 1 year ago 3949 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 1 year 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 jfalch 1 year ago 222 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 jfalch 1 year ago 222 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 1 year 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 1 year ago 3949 posts since Fri, 07 Apr 06	See also this thread.
Written by adonix 1 year 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. Username: Password: Remember me