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 (‘bon’@’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.
Explanation:
Should be C.
Should be c
C and D is running…
D is not the right answer. I’ve seen this question asked a few times, and for some reason D (processlist) is presented as the answer. The processlist shows ONLY currently running processes, and you will almost NEVER be able to see the INSTANT when a query is submitted before it fails through the processlist of mysql.
C is correct
A is wrong – no such table – mysql.routines
B – doesnt work
mysql> show routines;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘routines’ at line 1
D – process list only shows current processes/sessions.
E – error log will not show ALL routines only the routines that have been called and produced error
C
C is the right answer.
select definer from information_schema.definer from routines;
+—————-+
| definer |
+—————-+
| root@localhost |
| root@localhost |
——-