About the Author

Guilhem Bichot, born 1978, had the luck of being hired by MySQL AB six years ago. After working on replication and online backup, he currently co-leads with Monty Widenius the team developing the Maria storage engine. Outside of work, he helps raising his four children in the French countryside near Bordeaux.

We are always looking for interesting articles about MySQL! Have you written something and would like to it published here? Please contact us via feedback form »

Advanced Bazaar for MySQL developers

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)
 {
   sql_print_warning(ER(ER_ACCESS_DENIED_ERROR),
                     thd->main_security_ctx.user,
                     thd->main_security_ctx.host_or_ip,
                     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) &&
     (error == ER_DBACCESS_DENIED_ERROR ||
      error == ER_ACCESS_DENIED_ERROR ||
      error == ER_TABLEACCESS_DENIED_ERROR ||
      error == ER_COLUMNACCESS_DENIED_ERROR ||
      error == ER_SPECIFIC_ACCESS_DENIED_ERROR ||
      error == ER_PROCACCESS_DENIED_ERROR))
 { // then this is an access-denied error, log it
   sql_print_warning(str);
 }

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_ENTER("my_message_sql");
   DBUG_PRINT("error", ("error: %u  message: '%s'", error, str));
+  if ((global_system_variables.log_warnings > 1) &&
+      (error == ER_DBACCESS_DENIED_ERROR ||
+       error == ER_ACCESS_DENIED_ERROR ||
+       error == ER_TABLEACCESS_DENIED_ERROR ||
+       error == ER_COLUMNACCESS_DENIED_ERROR ||
+       error == ER_SPECIFIC_ACCESS_DENIED_ERROR ||
+       error == ER_PROCACCESS_DENIED_ERROR))
+  { // 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:

(Click on image to enlarge)

Off to testing. As we saw earlier, quite few tests in mysql-test/t test privilege problems, for example grant.test. Let's run it

$ cd mysql-test
$ perl mysql-test-run.pl grant --mysqld=--log-warnings=2

When I look into the error log, mysql-test/var/log/master.err, miracle:

080909 12:02:41 [Warning] UPDATE command denied to user 'mysqltest_3'@'localhost' for column 'q' in table 't1'
080909 12:02:41 [Warning] SELECT command denied to user 'mysqltest_3'@'localhost' for column 'd' in table 't2'
080909 12:02:41 [Warning] SELECT command denied to user 'mysqltest_3'@'localhost' for table 't1'
080909 12:02:41 [Warning] UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
080909 12:02:41 [Warning] SELECT command denied to user 'mysqltest_3'@'localhost' for column 's' in table 't1'
080909 12:02:41 [Warning] UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
080909 12:02:41 [Warning] UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
080909 12:02:41 [Warning] UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't2'
080909 12:02:41 [Warning] UPDATE command denied to user 'mysqltest_3'@'localhost' for table 't1'
080909 12:02:41 [Warning] INSERT,CREATE command denied to user 'mysqltest_1'@'localhost' for table 't2'

and many such lines. "Access denied" errors for table and column access are there - it works!

Here I checked for the presence of these messages with my eyes. Unfortunately, there is no easy way to automate such checking inside the .test itself. Ok, it is possible: in the .test file we could run a LOAD DATA INFILE of the Error Log's file into a temporary table, then use SELECT on this table to make sure that the expected "access denied" error messages are there; but that's too tricky and long to cover here. For many other features though, it's simple to write a .test file covering them, and unless solid excuse like here, lack of a .test file makes it likely that a reviewer would reject the implementation.

But anyway, it works. Great! Now what? You may want to share your work within your organization, with other MySQL users in the world, or with Sun for inclusion in MySQL 6.0 releases. Here are the old methods: you can generate a view of your work with bzr diff, and send that around (email it to people, put it on a website...). Or put the entire source package of 6.0-more-access-denied-logging on a website.

But there is a better way - publishing it on Launchpad: people all around the world will be able to download your 6.0-more-access-denied-logging source from Launchpad's machines at any time of the day. They will be able to communicate in ways which help you: for example, by filing reports for any bug they would find in your work - they may even propose fixes to these bugs.

First thing for that, commit your work. In Bazaar terms, committing stores your changes (what you did to my_message_sql()) persistently into the branch, creating what's called a revision; they are stored together with informative comments which you type at commit time, and which will help people understand your code in the future (and these people can simply be you in six months!). Do that with bzr commit -m[some_informative_description] or, if you installed the gtk plugin, with the GUI commit tool bzr gcommit - that's what I use; the gtk plugin makes things much easier than the command line.

Now go to launchpad.net, get yourself an account there, and publish your branch, as is all explained in simple steps here.

When done, you will have a home page there, in the likes of mine, where you can see that there are a few branches which I created. You will see your branch listed in your home page, and by clicking on this branch, the revisions which it contains. That branch is an exact copy of the 6.0-more-access-denied-logging branch on your computer.

If tomorrow you find that there is another code change to make in your branch, well do it on your machine, and commit it. Then propagate this new work to Launchpad, which means, update Launchpad's branch, dating of yesterday, with your new work just committed today: this is done with bzr push. Immediately after that, your new work is available to others.

Available... in particular to MySQL/Sun developers... maybe you wish them to review your code and include it in the next MySQL 6.0 version? Send an email to internals@lists.mysql.com saying that you have created a branch (please give its exact URL), implementing what feature, based on MySQL version X, and ask for review/inclusion. You can attach a complete description of the code changes which you made, generated by Bazaar: in Bazaar terms this is called a merge request and is produced by bzr send: do

$ bzr send -o some_tmp_file

then look at some_tmp_file: it contains a diff describing your committed changes. It's actually a bit more than a diff, because it contains, embedded, your commit comments, in fact it's a package of your committed revisions. Perfect for reviewers!

Now what if you don't want to share your work? There can be good reasons for that. At least you will want to maintain it, which means, to update your 6.0-more-access-denied-logging with the latest bugfixes applied to 6.0 over time. In other words, you will want to regularly merge the latest 6.0 into your 6.0-more-access-denied-logging. Do it this way:

$ cd 6.0
$ bzr pull

which pulls the latest updates made to 6.0 by MySQL/Sun developers, into your copy of 6.0; and then

$ cd ../6.0-more-access-denied-logging
$ bzr merge ../6.0

The last command above pulls those latest updates into your 6.0-more-access-denied-logging. It's however called a merge and not a pull, because it's between diverged branches: 6.0 and 6.0-more-access-denied-logging have a common ancestor but they both have distinct recent changes, none is a superset of the other. Once bzr merge has finished, it will list any found conflicts. Conflicts are when, since the common ancestor, the two branches received changes to the same portion of a file. Say, since you changed the code of my_message_sql(), somebody else also changed it in 6.0, around the same place in the file, but in a different way - that needs to be reconciled by hand, by you. For example, someone added an assertion below the DBUG_PRINT() - argh, the place where you added your if(). Conflicts can always be listed with bzr conflicts. A conflict in my_message_sql() is indicated by Bazaar like this, inside file sql/mysqld.cc:

void my_message_sql(uint error, const char *str, myf MyFlags)
{
  THD *thd;
  MYSQL_ERROR::enum_warning_level level;
  sql_print_message_func func;
  DBUG_ENTER("my_message_sql");
  DBUG_PRINT("error", ("error: %u  message: '%s'", error, str));
<<<<<<< TREE
  if ((global_system_variables.log_warnings > 1) &&
      (error == ER_DBACCESS_DENIED_ERROR ||
       error == ER_ACCESS_DENIED_ERROR ||
       error == ER_TABLEACCESS_DENIED_ERROR ||
       error == ER_COLUMNACCESS_DENIED_ERROR ||
       error == ER_SPECIFIC_ACCESS_DENIED_ERROR ||
       error == ER_PROCACCESS_DENIED_ERROR))
  { // then this is an access-denied error, log it
    sql_print_warning(str);
  }
=======
   DBUG_ASSERT(str != NULL);
>>>>>>> MERGE-SOURCE
  /*
    Put here following assertion when situation with EE_* error codes
    will be fixed
    DBUG_ASSERT(error != 0);
  */

See: it shows your work, as well as the conflicting pulled other people's work, enclosed in special markers. And Bazaar needs you to fix that.

First way to do so: edit the file, fix the function (here I would just move the DBUG_ASSERT(str != NULL) before the if(), because we should always check a variable before using it), remove markers.

Second way, more graphical: in case of conflict, Bazaar creates additional files; in our case it creates sql/mysqld.cc.BASE, sql/mysqld.cc.THIS, sql/mysqld.cc.OTHER. The BASE is the common ancestor. THIS is your version. OTHER is what we're pulling from 6.0. That's fit for using a typical three-way file merge graphical tool (I use kdiff3), and that's exactly what the "extmerge" plugin does: if you install it and do

$ bzr extmerge --all

then it calls such tool on the BASE/THIS/OTHER files:

(Click on image to enlarge)

Above, kdiff3 tells that it cannot choose between my if() (THIS file) or the DBUG_ASSERT() (OTHER file), and calls that a <Merge Conflict>. I just have to pick, using the "B" and "C" buttons in the top bar: I'll first click on "C", to stick DBUG_ASSERT() in sql/mysqld.cc, then click on "B", to add my if() right after.

Whatever the method, I end up with

void my_message_sql(uint error, const char *str, myf MyFlags)
{
  THD *thd;
  MYSQL_ERROR::enum_warning_level level;
  sql_print_message_func func;
  DBUG_ENTER("my_message_sql");
  DBUG_PRINT("error", ("error: %u  message: '%s'", error, str));
  DBUG_ASSERT(str != NULL);
  if ((global_system_variables.log_warnings > 1) &&
      (error == ER_DBACCESS_DENIED_ERROR ||
       error == ER_ACCESS_DENIED_ERROR ||
       error == ER_TABLEACCESS_DENIED_ERROR ||
       error == ER_COLUMNACCESS_DENIED_ERROR ||
       error == ER_SPECIFIC_ACCESS_DENIED_ERROR ||
       error == ER_PROCACCESS_DENIED_ERROR))
  { // 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
    DBUG_ASSERT(error != 0);
  */

Sometimes when I follow any of those methods, I'm unable to decide how to resolve the conflict, and need to dig into history, to read why the other developers changed this line. I do it with bzr gannotate, provided by the gtk plugin:

$ bzr gannotate sql/mysqld.cc

shows me information about each code line, and I can get even more information by clicking and double-clicking on any line: I can see the last revision which changed it, all changes made by that revision, the previous version of this line, etc.

(Click on image to enlarge)

Once all conflicts in a file are resolved, use

$ bzr resolve the_file_name

to tell Bazaar that it's ok now. Once all conflicts in all files are resolved, it's time to compile and test. If your feature still works as intended after that, congratulations, the merge was successful, you can commit it and push it to your Launchpad branch. You are staying ahead of 6.0!

All good things come to an end... and that's true for this article too. I showed you how to use Bazaar to get, modify MySQL's code, publish your changes, and ensure that your work always catches up with the latest developments in MySQL. One last thing before saying good bye: there is a very complete guide about all possibilities with Bazaar; and here are a few more commands which you may find useful (remember bzr help and bzr help [the_command_s_name]):

bzr log shows revision history (and in the "gtk" plugin it has a GUI counterpart bzr visualise)
bzr branch -r[some_revision] gets a branch as it was at revision X: useful to scroll back in the past like here where Giuseppe extracts MySQL 3.23.22 out of MySQL 5.1!
bzr diff -r[some_revision]: shows changes between two revisions of a branch
bzr annotate: shows who changed what line of a file, when and for what reason (and in the "gtk" plugin it has a GUI counterpart bzr gannotate).
bzr missing: lists all revisions which are missing between a branch and another.

Happy development!

Comments?

Read and post comments on this article in the MySQL Forums. There are currently 0 comments.