TIL: Lookout For DEFINER

The Issue

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 nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.

How can this be possible?

The Problem

For 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:

replicate-ignore-table=mysql.user

So if we create a user on the master, the user definition doesn't go through the replication chain.

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 Solution

Once again, Maatkit's to the rescue with mk-show-grants on the master:

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';

A simple copy from the master and paste onto the slave fixed it.

Conclusion

Every 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)

It constantly keeps me from grabbing some shears and going into yak shaving mode.

ZRM for MySQL Issues

I really like ZRM for MySQL Community Edition for backups. I find
it very simple to install and configure and it gets the job done. It's default settings suits most installations. The challenges arise once you need to adapt it to more complex organizations. Please note that all the issues described on this article refer to Linux installations.

What If DBAs Have No sudo?

This was the 1st challenge that I ever faced. ZRM requires full root access at least during installation and in some cases, it requires some sudo permissions to be able to manage its files as well. Some organizations don't grant these permissions to the DBAs complicating the installation. When I looked into the scripts, I didn't see any reason why they wouldn't run in the user space, other than a few hard coded paths. I posted the question in the Zmanda forums and I got an answer with some instructions on how to patch the scripts. The instructions are pretty straight forward, but I haven't had a chance to test them. May be someone else could try and post the results as a comment? Just follow the link to the original post and read through the thread.

Install From RPM or Tarball?

So far I have always used the tarball since it offers the most flexibility. However, this requires to properly set up permissions for the different directories and subdirectories. Although the locations of the main files are listed in the documentation, the tarball includes a script (run automatically if you use the deb or rpm packages) that sets the ownerships properly. Example:

sudo mysql-zrm-migrate-file-ownership --user mysql --group mysql

For multi-DBA organizations, you can easily modify the script to also grant group access to the directories using chmod as needed.

More on Permissions

Once the right access permissions have been set properly, the scripts will
run OK, however a new problem arises. No matter how you set the
permissions, the directory holding the backup files will only be created with user access. With the example above it means that only the user mysql will have access, effectively leaving the DBAs with no proper access to the files and reports even if they are included into the mysql group. I haven't been alble to locate where to patch the scripts properly to avoid this issue. For now, writing a wrapper that will include the lines:chmod
-R g+rx /path/to/backups after the backup works as an easy solution.

Crontab Entries

The most traditional method to run any process periodically is to use crontab, but modern distributions are favoring the use of files in the /etc/cron* subdirectories that will be run by run-parts. Chances are your system already has some daily tasks scheduled, which can be listed using:

run-parts --list /etc/cron.daily/

The security in many of these modern systems don't allow the use of crontab anymore. In these cases the mysql-zrm-scheduler utility can't be used. In it's place you'll need to create a file in /etc/cron.d (or a wrapper script in the /etc/cron.daily sub directory) containing the proper entry in a format similar to the traditional crontab. The main difference is that you need to specified the user it will use to execute. A typical file would look like:

# /etc/cron.d/zrm-backup: crontab entries for mysql-zrm

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
# add other environment variables you might need, like: PERL5LIB

30 1  * * *   mysql    mysql-zrm --action backup --backup-set backup

Conclusion

ZRM for MySQL continues to be one of my favorite MySQL tools. None of these issues are showstoppers, however the fact remains that Zmanda hasn't released any major update to these scripts in a long time and it's starting to show. If they don't do something about it, someone else will stealing their thunder. Such is the nature of Open Source.