DBA > Articles

Advanced Bazaar for MySQL developers

By: Guilhem Bichot
To read more DBA articles, visit http://dba.fyicenter.com/article/

The MySQL project switched from Bitkeeper to another revision control system, Bazaar, in June 2008. My colleague Daniel Fischer wrote an excellent article describing how to get MySQL's code by using Bazaar, and how to compile a MySQL server binary from this. Here I am going to build on this knowledge and take it further, to show you how to modify MySQL's code for your needs, and share your modifications.

I will show several Bazaar commands in action; but I will show neither all their options, nor all commands; make sure to read bzr help and bzr help [the_command_s_name] to get a feeling of all a command can do.

Say you need a feature that MySQL lacks, and you're even ready to modify MySQL's code to implement it. As an example, I often see people who would like their MySQL server to log all "access denied" errors; this is already achievable by turning on the General Query Log with the --log option but this would log all successful connections and queries too, which is too much logging for a production server. So, I'll put myself in the role of some development-inclined MySQL user who wants to implement logging of "access denied" errors without the overhead of the General Query Log. For a refresher course about MySQL's access privilege system, see here.

So, to start coding, we need the MySQL code, of course... but which version of it? 5.0? 5.1? 6.0? Well, if this feature is something you need now in your production servers (which should be using 5.0), then take 5.0. But if you wish your feature to be included in MySQL releases, that can happen only in the latest version (6.x right now), the older ones being feature-frozen.

In my example, MySQL already made progress: starting from 6.0, it does log "access denied" errors. Though only a subclass of them: connection failures due to bad user/password. Here I want to log errors also for successful connections when they fail to access tables, stored routines (procedures or functions), etc, because they don't have sufficient object-level privileges... So let's grab 6.0's code and improve it.

You have seen in Daniel Fischer's article above how to set up a shared repository, get and build the code (the $ character represents the prompt of a Unix shell or Windows command-line window):

$ mkdir bzr
$ cd bzr
$ bzr init-repo .
$ bzr branch lp:mysql-server/6.0
$ ... some build commands which depend on the platform ...

That's the 6.0 code which MySQL/Sun developers regularly modify and which gets into 6.0 releases. That's the "6.0 branch". Let's keep it clean on the side, and create another branch where we will do our feature development:

$ bzr branch 6.0 6.0-more-access-denied-logging
$ cd 6.0-more-access-denied-logging

In 6.0, existing "access denied" logging, which we'll use as a model, is in sql/sql_connect.cc, at end of function check_user() (by the way this was implemented by this patch) :

Log access denied messages to the error log when log-warnings = 2
so that the overhead of the general query log is not required to track
failed connections.
if (global_system_variables.log_warnings > 1)
passwd_len ? ER(ER_YES) : ER(ER_NO));

See: if the global MySQL variable log_warnings is 2 or more, this prints a message to the Error Log of the MySQL Server. And that's only for failed connections attempts (i.e. check_user() failure).

All "access denied" error codes in MySQL's code have names which start with ER_ and contain ACCESS_DENIED , like above, and they are defined in sql/share/errmsg.txt. We just need to find places where "access denied" errors of any sort are thrown to the client, and there, add an if() statement similar to the one above, to force the error message to some log.

Depending on your operating system, there is always a command to look for character strings in a file (grep under Unix, find or findstr under Windows): by searching in sql/share/errmsg.txt we find ER_DBACCESS_DENIED_ERROR, ER_ACCESS_DENIED_ERROR, ER_TABLEACCESS_DENIED_ERROR, ER_COLUMNACCESS_DENIED_ERROR, ER_SPECIFIC_ACCESS_DENIED_ERROR, ER_PROCACCESS_DENIED_ERROR, where we recognize at least database, table, column, stored routine "access denied" errors.

Then we can search the entire code tree for places where these errors are raised. This brings up some include files in include/, some testsuite files in mysql-test/, but what matters is in the sql/ directory:

item.cc:4144: my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0),
set_var.cc:921: my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER");
set_var.cc:3015: my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER");
set_var.cc:3053: my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER");
sp_head.cc:1504: my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0), priv_desc,
sql_acl.cc:3003: my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0),
sql_acl.cc:3983: my_error(ER_TABLEACCESS_DENIED_ERROR, MYF(0),
sql_acl.cc:4139: my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0),
sql_acl.cc:4294: my_error(ER_COLUMNACCESS_DENIED_ERROR, MYF(0),
sql_acl.cc:4424: my_error(ER_PROCACCESS_DENIED_ERROR, MYF(0),

I didn't show all matches; in total there are roughly thirty lines, in a dozen C++ files. And they all look similar: a call to my_error() sends the error to the client when a privilege is missing.If you search a little bit, you will find that my_error() is defined in mysys/my_error.c, that it calls a run-time defined error handler (pointer to function) error_handler_hook, which, for the MySQL Server, happens to be my_message_sql() (defined in sql/mysqld.cc), of signature:

int my_message_sql(uint error, const char *str, myf MyFlags)

where error is the error's code (ER_something, like ER_COLUMNACCESS_DENIED_ERROR), and str is the human-readable text. I could thus add to my_message_sql(), just below

DBUG_PRINT("error", ("error: %u message: '%s'", error, str));

this kind of logic:
if ((global_system_variables.log_warnings > 1) &>&>
{ // then this is an access-denied error, log it

Instead of sql_print_warning(), which will write to the MySQL server's existing Error Log, you could instead put a simple fprintf() to a file, or a call to operating system's facilities like syslog()under Unix or ReportEvent() under Windows. Or even send an email or SMS to the DBA, or phone to the police. What you prefer!

When I'm coding, I regularly look at all code that I changed, to see where I'm going: bzr diff shows the changes in so-called "diff"/"patch" form, like this:

=== modified file 'sql/mysqld.cc'
--- sql/mysqld.cc 2008-08-26 15:01:13 +0000
+++ sql/mysqld.cc 2008-09-09 09:02:23 +0000
@@ -2957,6 +2957,16 @@
sql_print_message_func func;
DBUG_PRINT("error", ("error: %u message: '%s'", error, str));
+ if ((global_system_variables.log_warnings > 1) &>&>
+ { // then this is an access-denied error, log it
+ sql_print_warning(str);
+ }
Put here following assertion when situation with EE_* error codes will be fixed

I'm not able to read long diffs without falling asleep, so I have installed the difftools plugin (which, like most Bazaar plugins, is available here) and kdiff3 (my favorite GUI for viewing differences between files), thus I can view my changes graphically and in colours with bzr diff --using=kdiff3:

Full article...

Other Related Articles

... to read more DBA articles, visit http://dba.fyicenter.com/article/