Bug #20230 routine_definition is not null
Submitted: 2 Jun 2006 15:23 Modified: 2 Aug 2006 18:40
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.12-beta-debug/5.0BK OS:Linux (SUSE 10.0)
Assigned to: Tomash Brechko CPU Architecture:Any

[2 Jun 2006 15:23] Peter Gulutzan
Description:
In information_schema, in the routines table, the routine_definition column is NOT NULL.
It should be nullable.
If a user does not have privileges, routine_definition contains ''.
It should contain NULL.

How to repeat:
As user root, say:

mysql> create procedure p6 () set @a=5;
Query OK, 0 rows affected (0.00 sec)

mysql> grant execute on procedure p6 to john@localhost;
Query OK, 0 rows affected (0.00 sec)

As user john@localhost, say:

mysql> select count(*) from information_schema.routines where specific_name='p6' and routine_definition is null;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.routines where specific_name='p6' and routine_definition = '';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)
[2 Jun 2006 18:12] Miguel Solorzano
Thank you for the bug report.

miguel@hegel:~/dbs/5.1> bin/mysql -ujohn
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.12-beta-debug

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

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition is null;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition = '';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)

mysql> 
-----------------------------------------------------------------------------------
miguel@hegel:~/dbs/5.0> bin/mysql -ujohn
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23-debug

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

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition is null;
+----------+
| count(*) |
+----------+
|        0 | 
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from information_schema.routines where specific_name='p6'
    -> and routine_definition = '';
+----------+
| count(*) |
+----------+
|        1 | 
+----------+
1 row in set (0.01 sec)

mysql>
[25 Jun 2006 9:57] Bugs System
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/8199
[29 Jun 2006 16:15] Konstantin Osipov
Approved by email.
[29 Jun 2006 20:21] Bugs System
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/8513
[2 Aug 2006 14:30] Konstantin Osipov
Fixed in 5.0.24 and 5.1.12
[2 Aug 2006 18:40] Paul Dubois
Noted in 5.0.24, 5.1.12 changelogs.

In the INFORMATION_SCHEMA.ROUTINES table the ROUTINE_NAME column now
is defined NOT NULL rather than NULL. Also, NULL rather than the empty
string is returned as the column value if the user does not have
sufficient privileges to see the routine definition.
[2 Aug 2006 23:17] Paul Dubois
Previous comment is incorrect.  Should be:

In the INFORMATION_SCHEMA.ROUTINES table the ROUTINE_DEFINITION
column now is defined as NULL rather than NOT NULL. Also, NULL rather
than the empty string is returned as the column value if the user
does not have sufficient privileges to see the routine definition.