MySQL Bugs: #7787: Stored procedures: improper warning for "grant execute" statement

Bug #7787	Stored procedures: improper warning for "grant execute" statement
Submitted:	10 Jan 2005 23:54	Modified:	27 Feb 2006 19:34
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.3-alpha-debug, 5.0.13-BK	OS:	Linux (SUSE 9.2)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
If I "grant execute on procedure_name to user_name", I get a warning. The warning applies 
to a statement within the procedure, so it's perhaps legitimate if I'm creating or executing 
the procedure. But not  when I'm granting a privilege on the procedure. 

How to repeat:
 
mysql> create procedure pk () show innodb status; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> grant execute on pk to pierre; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> show warnings; 
+---------+------+-----------------------------------------------------------------------------+ 
| Level   | Code | Message                                                                     | 
+---------+------+-----------------------------------------------------------------------------+ 
| Warning | 1287 | 'SHOW INNODB STATUS' is deprecated; use 'SHOW ENGINE INNODB 
STATUS' instead | 
+---------+------+-----------------------------------------------------------------------------+ 
1 row in set (0.00 sec)

The warning stems from the CREATE statement, it looks like GRANT just doesn't reset it.
Do the CREATE, disconnect, connect again and then do the GRANT and it does not show any warning at all.

The warning is different now ...

pgulutzan@d-142-59-78-116:~> /usr/local/mysql/bin/mysql --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.7-beta-debug

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

mysql> use db2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> grant execute on procedure fg4 to hartmut;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: 'root@localhost' for column 'Timestamp' at row 1 |
+---------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

I was able to repeat on the 5.0.13-BK on Linux:

mysql> create procedure pk2 () show innodb status;
Query OK, 0 rows affected, 1 warning (0,00 sec)

Yes, warning is added when creating procedure.

mysql> grant execute on procedure pk2 to hartmut;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------
---------------+
| Level   | Code | Message
               |
+---------+------+--------------------------------------------------------------
---------------+
| Warning | 1287 | 'SHOW INNODB STATUS' is deprecated; use 'SHOW ENGINE INNODB STATUS' instead |
+---------+------+--------------------------------------------------------------
---------------+
1 row in set (0,00 sec)

Warning remained from the previous statement. Isn't it a bug?

Yes, if I reconnect and try to execute GRANT statement after that, it shows no warnings. In any case, it is strange to get such a warning after executing GRANT statement. It has nothing to do with it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2437

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/2907

Pushed into 5.0 tree, currently tagged 5.0.19.

Merged into 5.1, currently tagged 5.1.8-beta.

Noted in 5.0.19 and 5.1.8 changelogs:

   <listitem>
        <para>
          Issuing <literal>GRANT EXECUTE</literal> on a procedure would
          display any warnings related to the creation of the procedure.
          (Bug #7787)
        </para>
      </listitem>