List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 24 2013 9:08pm
Subject:Re: Show ROUTINE body, not PROCEDURE
View as plain text  
On 2013-03-24 8:25 AM, Dotan Cohen wrote:
> 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>

Log in as admin@localhost.

PB

-----

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

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