Sample employees database migrated to GitHub

It's migration time. There was another project that I use often and was still in Launchpad. The Sample Employees Database is now on GitHub, under the same license it had before (CC A-SA 3).

Figure 1 - Employees database
This database is interesting because it is not too small (like Sakila) and not too big. It has enough data to allow you to test in a non trivial way.

MySQL Sandbox 3.0 is released - looking at the future

It's done. MySQL Sandbox 3.0 is now available. New features include: Installation as a Perl module; Copying, moving, deleting, and preserving sandboxes; Port checking before installing; A test suite with 200 tests; User defined modular tests; A rich cookbook

If you get the source code through the Bazaar repository, you will see something new. There is now a directory script_templates, containing the re-engineered scripts that go inside the Sandbox. These scripts are not in version 3.0.0, but they are the first step to the changes that will go into version 3.1. The main new feature is modularity. These scripts are now easier to maintain and modify.
The new start script is also more powerful. You will be able to start a sandboxed server without the mysqld_safe container, sending the output to a user defined place, choosing between running it in background or foreground.
All in due time. The new features for now pass all the existing tests, but I didn't want to include them in the current release before designing some rigorous tests for them.
Looking forward to your comments!

New version of employees test DB

The Employees Test database has been updated. There was a subtle bug in the data. One employee was assigned to two departments with the same start and end date. And one of the sample procedures fell into the trap of assuming that the data was clean, thus reporting incorrect statistics. Now the bug is fixed, the test suite is updated, and I can wait for the next bug report.

Launchpad improvements

Launchpad, the development framework created by Canonical, is under constant development. If you have never used it, have a look at Jay's getting started and code management articles about it. If you know it already, you may be pleased to know that Launchpad has a countinuous edge beta testing program that anyone can try.

If you are getting curious, then let me tell you that there are actually three testing environments:
edge, which is the beta testing environment. You get into if by adding edge before launchpad.net in every URL. If you subscribe to the beta testing program and are accepted, every time you connect to launchpad you are redirected to the edge environment, which uses the latest beta interface and the production database. Every change you make with the edge interface is permanent.
staging, which is a non permanent testing environment. It uses the beta interface, and a copy of the production environment, which is replaced at regular intervals. Here you can try daring operations, without risking your valuable production data.
dogfood has rules similar to staging, but the level of experimental is even higher. Its contents may change without notice.
Using the edge environment, you will notice an enhanced interface (a detail here) and some hidden improvements.

One of the recent improvements is a bug that I asked the Canonical developers to fix, because it was affecting the usability of Launchpad as a development framework. Bug#66344 is about the graphical display of a blueprint dependency tree. In the old application, when you had more than a dozen dependencies, you got a cramped display like this one.

Absolutely unreadable. But if you get the same page through edge, you get this one:

The resolution of this bug is very important for my team campaign of promoting Launchpad. At MySQL developers meeting, which will start in Riga in the next days, we will show the goodies of Launchpad to anyone willing to listen, and since blueprint management are a vital component in every development effort, this recent improvement will make things look better for Launchpad beginners. Thanks to Joey Stanford and Christian Reis for speeding up matters with this bug!
Happy coding!

MySQL Sandbox 2.0 has been released

Taking advantage of Launchpad excellent development services, I completed the tasks necessary to release MySQL Sandbox 2.0. So, you may ask, what's new? Quite a lot, actually: more intuitive script names master sandbox directory faster supports circular replication more error checking includes a test suite uses a default options file

more intuitive script names

One of the problem in the old sandbox was that the names of the scripts suggested some installation of the sandbox itself. This has been addressed in version 2.0. You now have the very clear names:

 make_sandbox                  creates a single sandbox
make_replication_sandbox      creates a replication sandbox
make_multiple_sandbox         creates sandbox containing many servers
                                of the same version
make_multiple_custom_sandbox  creates a sandbox containing many servers
                                of different versions

master sandbox directory

By default, MySQL Sandbox will create a directory under your home, named "sandboxes", and all sandboxes will be created under that directory. In addition of making your sandboxes more organized, the master directory provides some handy scripts to deal with many sandboxes at once. You can start, stop, or send a SQL command to all the sandboxes with just one command.

faster

The Sandbox had a few "sleep" instructions, to wait for the server being installed to come online. This solution was slowing down fast machines, where the server can start much faster than the standard 3 seconds I added to the sandbox, but was sometimes not enough for slower machines, where the server may take more than 3 seconds to start up, especially with replication. The new sandbox uses a loop coupled with a timeout limit. Thus, quick machines are faster, and slow machines don't fail.

supports circular replication

Now you can create a replication ring.

./make_replication_sandbox --topology=circular --how_many_nodes=5 VERSION
# or
./make_replication_sandbox --master_master VERSION

includes a test suite

If you have resources and MySQL binary tarballs at your disposal, you can stress test MySQL Sandbox (and your box!) with this test suite. For example, assuming that your 5.0.51 and 5.1.25 binaries are in $HOME/opt/mysql

./test/test_sandbox.pl --versions=5.1.51,5.1.25

This will create 22 sandboxes

more error checking

This is a direct consequence of the above item. Having a test suite has made the sandbox much more robust.

uses a default options file

There is also a default option file, where you can keep the options that you need to be executed always.

Try it!

Go get the 2.0 tarball from launchpad. If you find any bugs, there is a very convenient bugs reporting system. Please use it!

MySQL Sandbox - planning for 2.0

Taking advantage of Launchpad environment, I started paving the way for version 2.0 of MySQL Sandbox.
I opened a new branch for version 2.0, which currently holds 1.99.1, and I listed the necessary tasks using the built-in blueprints feature.

So now I have a list of tasks to keep track of the development.

• implement test suite (have a good prototype - I am getting to it)
• replace sleep with check loop (thinking - some analysis required)
• create function library (good progress)
• script to kill unresponsive servers (good progress)
• remote sandbox installer (thinking - hard to implement without being intrusive)
• allow url for tarball (thinking - needs external components)
• define circular replication (thinking - easily doable)
• refactoring for internationalization (thinking - easily doable - long task)

The above features should all go to version 2, although the list is still open. No feature freeze has been called. It's still alpha.
I am unsure about the remote installer. I have implemented such a feature a few years ago, and it turned out to be quite intrusive (the choice was between being intrusive and fragile). I may consider making it a plugin or moving it to a later version. Still thinking.

The development model is open. If you use the Sandbox and want to submit a patch or have comments on its development plans, please contribute!

From Bazaar to Sandbox in 5 moves

Now that MySQL has switched its development to Bazaar, we can play a bit with the new tools. One thing that I have tried immediately upon adoption of the new RCS is to create a test bench for multiple MySQL versions by combining Bazaar and MySQL Sandbox.

If you are into bleeding edge testing, you may like to know how to get a new MySQL server up and running in a sandbox from a source tree.

First move - make the repository

This is a zero time consuming move, and you will have to do this only once. But this move will save you heaps of time in future. By creating a shared repository, you are telling Bazaar to avoid redundant copies of files (actually Bazaar is much smarter than this, but as a simple explanation it will do).

$ bzr init-repo shared
$ cd shared/

Second move - get the source

Now we're ready to get the first copy of our source. The first branch we get is the current tree, 5.1. Depending on the speed of your network connection, it will take between 20 and 40 minutes.

$ time bzr branch lp:mysql-server/5.1  51

lp stands for launchpad. mysql-server is the project name, and 5.1 is the version. 51 is the directory we are creating on the local disk. You can call it whatever you want. I prefer short names.

This operation may be tricky. The current Bazaar client (1.5) is not verbose. It may actually appear to be doing nothing for very long time. It is actually importing code. Don't be discouraged and bear with me on this. It will pay off in the end. After a while you will see a progress bar and eventually it will display the result.

Branched 2655 revision(s).                                                                                                   

real 19m14.110s
user 7m22.883s
sys 1m21.372s

In my laptop, it took less than 20 minutes, but it could be much slower, depending on connection type and intensity of net traffic.

Let's have a look at the size of what we have downloaded. The hidden directory .bzr contains the biggest chunk.

$ du -sh .bzr/ */
498M .bzr/
129M 51/

Now for the juicy part. We want to branch also MySQL 5.0. You may be ready for yet another 20 minutes and 600 more MB of data to be added, but you are in for a good surprise:

$ time bzr branch lp:mysql-server/5.0 50
Branched 2634 revision(s).                                                                                                   

real 0m42.205s
user 0m28.100s
sys 0m4.757s

Less than one minute! And what about storage?

$ du -sh .bzr/ */
498M .bzr/
100M 50/
129M 51/

As you see, the new code added just the difference between 5.1 and 5.0, so it did not even had to get it from the net because it mostly got the previous version from the internal history (we'll come back to this point in a later post).
A similar experience awaits you when branching the newest version, 6.0. A bit slower than 5.0 (because it has to get something more from the net), but still quite fast compared to the full tree we got in the first run.

$ time bzr branch lp:mysql-server/6.0 60
Branched 2664 revision(s).                                                                                                   

real 4m45.383s
user 1m39.727s
sys 0m20.776s

$ du -sh .bzr/ */
566M .bzr/
100M 50/
129M 51/
146M 60/

Third move - export the code

We have now on disk the code for three versions. We could just go inside each directory and build it, but that would not be clean. So in this move we just export the code to a build directory that we create for this task.

$ mkdir ~/install/build

$ ls
50    51    60

Using a shell loop, we export each tree to a build directory. It takes about 30 seconds for each tree.

$ for R in 50 51 60 ; do echo $R ; cd $R ; bzr export ~/install/build/$R ; cd .. ; done
50
51
60

Fourth move - build the code

We are finally ready to build. Each build can take quite long, depending on your box. In my laptop, it takes about 40 minutes per build.

In the ./BUILD directory in each source tree there are several building scripts. Choose the one that suits your purposes, eventually disabling the parts that you don't need (e.g. cluster).

$ cd ~/install/build

$ for R in 50 51 60 ; do \
  cd $R ; \
  ./BUILD/your_favorite_script && ./scripts/make_binary_distribution ; \
  cd .. ; \
  done

After each build, this loop runs make_binary_distribution, which creates a binary tarball, right what you need for MySQL Sandbox.

Fifth move - sandbox it!

Now, let's use our favorite installation tool to use these new binaries.

Download the sandbox. Make sure that you got at least version 1.21.

$ export SANDBOX_HOME=$HOME/sandboxes
$ cd ~/install/mysql_sandbox-1.21

For each version, you invoke the sandbox installer as

./express_install full/path/to/tarball_name.tar.gz

Here we go

$ ./express_install.pl ~/install/build/50/mysql-5.0.66-darwin9.3.0-i386.tar.gz
unpacking /Users/gmax/install/build/50/mysql-5.0.66-darwin9.3.0-i386.tar.gz
Executing ./install.pl --basedir=/Users/gmax/install/build/50/5.0.66 \
 --sandbox_directory=msb_5_0_66 \
 --install_version=5.0 \
 --sandbox_port=5066 \
 --no_ver_after_name
  The MySQL Sandbox,  version 1.21 09-Jun-2008
  (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
installing with the following parameters:
home_directory                 = /Users/gmax/sandboxes
sandbox_directory              = msb_5_0_66
sandbox_port                   = 5066
datadir_from                   = script
install_version                = 5.0
basedir                        = /Users/gmax/install/build/50/5.0.66
[...]
loading grants
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/sandboxes/msb_5_0_66

$ ./express_install.pl ~/install/build/51/mysql-5.1.26-rc-darwin9.3.0-i386.tar.gz
unpacking /Users/gmax/install/build/51/mysql-5.1.26-rc-darwin9.3.0-i386.tar.gz
Executing ./install.pl --basedir=/Users/gmax/install/build/51/5.1.26 \
 --sandbox_directory=msb_5_1_26 \
 --install_version=5.1 \
 --sandbox_port=5126 \
 --no_ver_after_name
  The MySQL Sandbox,  version 1.21 09-Jun-2008
  (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
installing with the following parameters:
home_directory                 = /Users/gmax/sandboxes
sandbox_directory              = msb_5_1_26
sandbox_port                   = 5126
datadir_from                   = script
install_version                = 5.1
basedir                        = /Users/gmax/install/build/51/5.1.26
my_file                        =
[ ... ]
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/sandboxes/msb_5_1_26

$ ./express_install.pl ~/install/build/60/mysql-6.0.6-alpha-darwin9.3.0-i386.tar.gz
unpacking /Users/gmax/install/build/60/mysql-6.0.6-alpha-darwin9.3.0-i386.tar.gz
Executing ./install.pl --basedir=/Users/gmax/install/build/60/6.0.6 \
 --sandbox_directory=msb_6_0_6 \
 --install_version=6.0 \
 --sandbox_port=6060 \
 --no_ver_after_name
  The MySQL Sandbox,  version 1.21 09-Jun-2008
  (C) 2006,2007,2008 Giuseppe Maxia, MySQL AB
installing with the following parameters:
home_directory                 = /Users/gmax/sandboxes
sandbox_directory              = msb_6_0_6
sandbox_port                   = 6060
datadir_from                   = script
install_version                = 6.0
basedir                        = /Users/gmax/install/build/60/6.0.6
[ ... ]
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/sandboxes/msb_6_0_6

At the end of the exercise, you have three sandboxes with the three major MySQL versions, ready to use. Enjoy!

MySQL Sandbox 1.22 and the joys of developing on launchpad

I have recently moved MySQL Sandbox to launchpad and Bazaar.

Working with Launchpad is really a joy. It is much more than providing a web interface to Bazaar version control. It also includes provisions for bug reporting and handling, writing blueprints for your roadmap, creating distribution files, and manage your packages if you want to release them in Ubuntu.
Using the above tools, I implemented a couple of new features to MySQL Sandbox, and I started to work on a roadmap for the next version, which is now taking shape.
The latest version (1.22) includes the ability of using a default option file ($HOME/.msandboxrc), a safer usage of the "--force" option, and several bug fixes.

MySQL Sandbox 1.21 - matrioska and launchpad

MySQL Sandbox has moved to a new home. It is now hosted at Launchpad, a friendly environment where developers can work together.

What's new with MySQL Sandbox? The matrioskas in the image are a clue. Version 1.21 introduces the concept of SANDBOX_HOME, which previously was only a path under which store the sandboxes. With time, when I was using more and more sandboxes, I realized that I wanted to take control of all the sandboxes at once, and issue global commands to all of them.
Starting with this version, the sandbox installer recognizes an environmental variable named $SANDBOX_HOME, and builds the sandboxes under that path.
In addition to grouping all the sandboxes in a place, the installer creates a few scripts that allow you to take control of all the active sandboxes.
Let's say that you have a single sandbox of MySQL 5.0.62, a replication system of 7 5.1.24 servers, and a multiple installation of 5 6.0.5 servers.
Prior to this version, if you wanted to stop each sandbox, you should have to enter each sandbox directory and run "./stop" or "./stop_all". With the sandbox outer container, you just have to run "./stop_all" from one place.
You can also execute the same command in each server in all your sandboxes, by means of the "./use_all" script.
If you want to learn more, on Thursday, June 12, 2008 at 15:00 CEST, I will host a MySQL University session on this very subject.