I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:
Although it is possible to create a view with a nonexistentHow can this be possible?
DEFINERaccount, an error occurs when the view is referenced if the
SQL SECURITYvalue is
DEFINERbut the definer account does not exist.
The ProblemFor a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the master). Our configuration files include the following line:
So if we create a user on the master, the user definition doesn't go through the replication chain.replicate-ignore-table=mysql.user
So a VIEW can be created in the master, but unless we run all the proper GRANT statements on the slave as well, the VIEWs won't be effective on the slaves. Example from our slave (output formatted for clarity):
show create view view3\G *************************** 1. row *************************** View: view3 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`app`@`192.168.0.1` SQL SECURITY DEFINER VIEW `view3` AS select [...] show grants for `app`@`192.168.0.1`; ERROR 1141 (42000): There is no such grant defined for user 'app' on host '192.168.0.1'
The SolutionOnce again, Maatkit's to the rescue with mk-show-grants on the master:
A simple copy from the master and paste onto the slave fixed it.mk-show-grants | grep 192.168.0.1 -- Grants for 'app'@'192.168.0.1' GRANT USAGE ON *.* TO 'app'@'192.168.0.1' IDENTIFIED BY PASSWORD '*password_hash'; GRANT DELETE, EXECUTE, INDEX, INSERT, SELECT, SHOW VIEW, UPDATE ON `pay`.* TO 'app'@'192.168.0.1';
ConclusionEvery now developers come to me with unusual questions. In this case it was: How come I can access only 2 out of 3 views?. In cases like these, it usually pays off to not overthink the issue and look into the details. A SHOW CREATE PROCEDURE on the 3 views quickly showed that one had a different host for the DEFINER. A quick read through the documentation and an easy test confirmed the mistake. That's why I have 3 mantras that I keep repeating to whomever wants to listen:
- Keep it simple
- Pay attention to details
- RTFM (F is for fine)