1z0-833 第三题

jikcheng

You inherit a legacy database system when the previous DBA, Bob, leaves the company. You are notified that users are getting the following error:
 
 
mysql> CALL film_in_stock (40, 2, @count);
 
ERROR 1449 (HY000): The user specified as a definer (‘bob’@’localhost’) does not exist 

How would you identify all stored procedures that pose the same problem?


A.  Execute SELECT * FROM mysql.routines WHERE DEFINER=’bob@localhost’;.
B.  Execute SHOW ROUTINES WHERE DEFINER=’bob@localhost’.
C.  Execute SELECT * FROM INFORMATION_SCHEMA. ROUTINES WHERE
DEFINER=’bob@localhost’;.
D.  Execute SELECT * FROM INFORMATION_SCHEMA. PROCESSLIST WHERE USER=’bob’
and HOST=’ localhost’;.

E.  Examine the Mysql error log for other ERROR 1449 messages.

[此贴子已经被作者于2014-12-22 11:37:02编辑过]

jikcheng

原文翻译：你接受了一个历史数据库，在bob 这个DBA离开公司后，你注意到出现了以下错误。mysql> CALL film_in_stock (40, 2, @count);
 
ERROR 1449 (HY000): The user specified as a definer (‘bon’@’localhost’) does not exist
你怎么识别出有着相同 问题的存储过程。

[此贴子已经被作者于2014-12-22 11:27:20编辑过]

jikcheng

答案选C ，

mysql> select * from information_schema.ROUTINES\G     

*************************** 1. row ***************************

           SPECIFIC_NAME: pay_check

         ROUTINE_CATALOG: def

          ROUTINE_SCHEMA: world

            ROUTINE_NAME: pay_check

            ROUTINE_TYPE: FUNCTION

               DATA_TYPE: float

CHARACTER_MAXIMUM_LENGTH: NULL

  CHARACTER_OCTET_LENGTH: NULL

       NUMERIC_PRECISION: 9

           NUMERIC_SCALE: 2

      DATETIME_PRECISION: NULL

      CHARACTER_SET_NAME: NULL

          COLLATION_NAME: NULL

          DTD_IDENTIFIER: float(9,2)

            ROUTINE_BODY: SQL

      ROUTINE_DEFINITION: BEGIN

DECLARE net_pay FLOAT(9,2)

DEFAULT 0;

SET net_pay=gross_pay - gross_pay *

tax_rate;

RETURN net_pay;

END

           EXTERNAL_NAME: NULL

       EXTERNAL_LANGUAGE: NULL

         PARAMETER_STYLE: SQL

        IS_DETERMINISTIC: NO

         SQL_DATA_ACCESS: NO SQL

                SQL_PATH: NULL

           SECURITY_TYPE: DEFINER

                 CREATED: 2014-12-05 09:54:59

            LAST_ALTERED: 2014-12-05 09:54:59

                SQL_MODE: NO_ENGINE_SUBSTITUTION

         ROUTINE_COMMENT: 

                 DEFINER: root@localhost

    CHARACTER_SET_CLIENT: utf8

    COLLATION_CONNECTION: utf8_general_ci

      DATABASE_COLLATION: latin1_swedish_ci

[此贴子已经被作者于2014-12-22 11:43:26编辑过]