List:General Discussion« Previous MessageNext Message »
From:Dotan Cohen Date:March 24 2013 1:25pm
Subject:Show ROUTINE body, not PROCEDURE
View as plain text  
A MySQL database is using a stored function avg_sales in some SQL
queries. I am having a hard time finding the code for this function,
in order to track down a bug that I suspect is in the function. Here
is what I get when I try to query MySQL for the function code:

mysql> SELECT * FROM information_schema.ROUTINES\G
*************************** 1. row ***************************
       SPECIFIC_NAME: avg_sales
     ROUTINE_CATALOG: NULL
      ROUTINE_SCHEMA: ********
        ROUTINE_NAME: avg_sales
        ROUTINE_TYPE: FUNCTION
      DTD_IDENTIFIER: int(11)
        ROUTINE_BODY: SQL
  ROUTINE_DEFINITION: NULL
       EXTERNAL_NAME: NULL
   EXTERNAL_LANGUAGE: NULL
     PARAMETER_STYLE: SQL
    IS_DETERMINISTIC: NO
     SQL_DATA_ACCESS: CONTAINS SQL
            SQL_PATH: NULL
       SECURITY_TYPE: DEFINER
             CREATED: 2013-03-14 02:21:14
        LAST_ALTERED: 2013-03-14 02:21:14
            SQL_MODE:
     ROUTINE_COMMENT:
             DEFINER: admin@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
  DATABASE_COLLATION: utf8_general_ci
2 rows in set (0.00 sec)

mysql> SHOW CREATE FUNCTION avg_sales;
+-----------+----------+-----------------+----------------------+----------------------+--------------------+
| Function  | sql_mode | Create Function | character_set_client |
collation_connection | Database Collation |
+-----------+----------+-----------------+----------------------+----------------------+--------------------+
| avg_sales |          | NULL            | latin1               |
latin1_swedish_ci    | utf8_general_ci    |
+-----------+----------+-----------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE PROCEDURE avg_sales;
ERROR 1305 (42000): PROCEDURE avg_sales does not exist
mysql> SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES;
+--------------------+
| ROUTINE_DEFINITION |
+--------------------+
| NULL               |
| NULL               |
+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT param_list,returns,body FROM mysql.proc;
ERROR 1142 (42000): SELECT command denied to user
'********'@'localhost' for table 'proc'
mysql>



What else should I try to get the code? This is on MySQL 5.1.61
running on CentOS. Thanks.

-- 
Dotan Cohen

http://gibberish.co.il
http://what-is-what.com
Thread
Show ROUTINE body, not PROCEDUREDotan Cohen24 Mar
  • Re: Show ROUTINE body, not PROCEDUREPeter Brawley24 Mar
    • Re: Show ROUTINE body, not PROCEDUREDotan Cohen25 Mar
      • Re: Show ROUTINE body, not PROCEDUREPeter Brawley25 Mar
Re: Show ROUTINE body, not PROCEDUREhsv26 Mar
Re: Show ROUTINE body, not PROCEDUREhsv27 Mar
  • Re: Show ROUTINE body, not PROCEDUREDotan Cohen28 Mar
    • Re: Show ROUTINE body, not PROCEDUREPeter Brawley28 Mar
      • Re: Show ROUTINE body, not PROCEDUREDotan Cohen29 Mar
        • Re: Show ROUTINE body, not PROCEDUREPeter Brawley29 Mar