【dbdao.com MySQL OCP认证专题】- MySQL 5.6 - OCP 考题讲解 - 3

biotwang

【dbdao.com MySQL OCP认证专题】- MySQL 5.6 - OCP 考题讲解

3.

You inherit a legacy database system when the previous DBA Bob, leaves the company. You are notified that users are getting the following error:

1. mysql> CALL film_in_stock (40, 2, @count);
   
2. 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.

----------------------------------------------------------------------------------
答案：C

分析：
routines表在库INFORMATION_SCHEMA下，因此A错。
可以登陆MySQL后，使用? show命令查看show语法。可知show无routine语句，B错。

可使用以下命令来查看routines:

1. pager less;
   
2. select * from information_schema.routines\G

复制代码

可知C正确.
INFORMATION_SCHEMA.PROCESSLIST表中仅显示了当前正在运行的线程信息,D错。
Mysql error log是对报错信息的记录，并不会有所有存储过程的记录，E错。