MySQL Bugs: #19969: Editing Stored Procedure or Function generates code that can not be executed

Bug #19969	Editing Stored Procedure or Function generates code that can not be executed
Submitted:	20 May 2006 14:57	Modified:	12 Jul 2006 16:55
Reporter:	Daniel Fiske	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.20	OS:	Windows (Win XP Home)
Assigned to:		CPU Architecture:	Any

Description:
The code created when editing a procedure or stored procedure fails to run because DEFINER= is added.

How to repeat:
1. Create Stored Procedure sp_foo using MySQL QB.

Should generate something like this

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_foo` $$
CREATE PROCEDURE `test`.`sp_foo` ()
BEGIN

END $$

DELIMITER ;

2. Edit Stored Procedure using MySQL QB

Should generate this

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_foo` $$
CREATE DEFINER=`daniel`@`green` PROCEDURE `sp_foo`()
BEGIN

END $$

DELIMITER ;

3. Executing throws the following error

Error # 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$

DELIMITER' at line 4

Suggested fix:
Removing the DEFINER= allows it to run i.e.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_foo` $$
CREATE PROCEDURE `sp_foo`()
BEGIN

END $$

DELIMITER ;

The net result of this is that you can delete a procedure by mistake because the DROP is executed but the CREATE isn't. As a result if you close the query window you will have lost your procedure.

Actually DEFINER is accepted by the server in 'create view'.
Inconsistent!

this works:
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `testview` AS (select _latin1 'test' AS `test`)  ..

what is what is returned by 'show create table testview;'

This

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_foo` $$
CREATE DEFINER=`daniel`@`green` PROCEDURE `sp_foo`()
BEGIN
END $$
DELIMITER ;

... works on 5.1.9.  I think it should be classified as a bug with server 5.0.x and not MA !!

QB and not MA of course.
... one more 'error 40' :-(

What exact version of MySQL server are you working with?

For my part:  5.0.21 and 5.1.9 on WinXP

5.1.9 accepts a 'create procedure ... DEFINER ...'.
5.0.21 does not.

And if you want it more detailed it is the same as I wrote here:
http://bugs.mysql.com/bug.php?id=19971

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Now maybe Daniel did not reply but I did!
Maybe Daniel thought that my replies were sufficient?

This should not have been closed or neglected!

I have the same problem with 5.0.22. Query browser automatically adds DEFINER ... and I have to manually remove it to be able to store the procedure (With the execute button).

Please download the latest release of QB 1.2.1 from http://dev.mysql.com/downloads/gui-tools/5.0.html

There was a bug in the SQL parser that got confused by the @ symbol. The syntax DEFINER=`root`@`localhost` is correct according to the manual. Actually it is the server itself that returns this routine code.

Thanks a lot.

@Mike

"Actually it is the server itself that returns this routine
code"

I never was in doubt about that.  That is why I requested that this is verified as a bug with ther 5.0 server, and not QB (and there are similar ones reported with MA/restore that also should be classified as a bug with the server!)

It returns DEFINER in the CREATE statement but does not accept it in a CREATE PROCEDURE statement. Server 5.1 does accept DEFINER in a CREATE PROCEDURE statement.

That was my point!

The 5.0 documentation states:

"The optional DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic. The DEFINER clause was added in MySQL 5.0.20."

Taken from http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

It may have been added in 5.0.20, but it was buggy!

This:

CREATE DEFINER=`root`@`localhost` PROCEDURE `pr1`()
BEGIN
-- nothing here
END;

would not execute on 5.0.21! You got infamous MySQL error 1064. 
 
However I just checked that it does with 5.0.23, so the issue seems to have been fixed (without noting anything about it in the server changelogs as far as I can see :-(  )

DEFINER works also in latest 5.0.25-BK on Linux. So, now all is correct: manual, server and QB.

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `pr1`()
    -> BEGIN
    -> select 1;
    -> END;
    -> //
Query OK, 0 rows affected (0.12 sec)

Agreed!

Since this report was created, things seem to have been fixed.

Just a hint to @Mike:
I think there a several bug reports created on 'MySQL Administrator' backup functionality where issue is the same: Server did not accept the CREATE PROCEDURE statement that it returned itself!

Let me add that we at Webyog have some similar bug reports with our SQLyog program - that also come back to the same server issue with server 5.0.20, 5.0.21 and possibly 5.0.22.

Most 'common' users do not understand the relationship between the server and the client - and blame the client even when the server should be blamed!