Lenz Grimmer's blog

FOSDEM, the Free and Open Source Software Developers' European Meeting will again take place in Brussels, Belgium on February, 24th & 25th. I went there last year and really had a great time. This year, I submitted a proposal for a lightning talk, which has been accepted! I've just been informed that the lightning talk schedule is now published. My talk "What's new at MySQL AB" will be on Saturday, at 17:00. I look forward to being there!

Sorry for the short outage, due to a small coding glitch the script that updates the planet feeds got stuck and it took us a bit to find the problem... But thanks to Jay we now spotted the bug and it was fixed quickly!

Congratulations to Warren Young and the other developers that maintain the MySQL++ C++ wrapper around the MySQL Client libraries: version 2.2.0 has just been announced and boasts a wide range of changes. Here's a description of MySQL++ from the project web site:

MySQL++ is a C++ wrapper for MySQL's C API. It is built around STL principles, to make dealing with the database as easy as dealing with an STL container. MySQL++ relieves the programmer of dealing with cumbersome C data structures, generation of repetitive SQL statements, and manual creation of C++ data structures to mirror the database schema

I just learned from my colleagues in our Dublin office that Mårten Mickos, CEO of MySQL AB will be visiting them this coming weekend. They would like to take this opportunity to arrange a MySQL User Group Meeting, which will take place on Saturday, 27th of January, 18:00 o'clock in their offices:

MySQL Ireland,
Ground Floor, Misys Building,
Eastpoint Business Park,
Dublin 3
Ireland

This is your chance to meet and talk with Mårten and other MySQLers. There will also be free food and drinks and most likely a tech talk about MySQL (details will follow). So if you are located somewhere around Dublin, make sure to attend this event!

I've already sent out the invitation and announcements to various channels, but let me repeat it once more: the 5th Hamburg MySQL User Group Meetup is coming up on Monday, 5th of Februrary. So far, 15 people have already RSVPed, so it's going to be a fun evening again. If you have not done so yet, please register via meetup.com or Xing soon! I will give a 15 minute lightning talk about "What's new at MySQL AB", but we're still looking for another MySQL-related talk. If you want to share your experiences with MySQL or talk about a certain application or tool that has a relation to MySQL, please let me know! As usual, we'll meet a 19:00 at the Chinese Restaurant "Ni Hao", Wandsbeker Zollstraße 25-29, 22041 Hamburg.

Some days ago I had the pleasure to interview Andrey Hristov, who is a developer at MySQL AB. He joined the team after working on a new feature for the MySQL Server as part of his master's thesis. You can read the full interview on the MySQL Developer Zone. Enjoy!

I am happy to announce version 0.4 of mylvmbackup, a tool to perform consistent backups of a MySQL server's tables using Linux LVM snapshots.

For this release I'd like to especially thank Robin H. Johnson from the Gentoo project, who contributed another batch of useful changes and informed me that mylvmbackup is now in productive use to perform backups of the MySQL databases that power the project's Bugzilla bug tracking system. I am always glad to read about such use cases - how do you utilize mylvmbackup in your environment?

• The option handling has been improved. mylvmbackup now starts by using the builtin defaults, followed by the default configuration file (/etc/mylvmbackup.conf, followed by an alternative configuration file (specified via CLI arguments), followed by the remainer of the CLI arguments.
• Changed the capture of the position file and tarball creation to not use absolute path names, and instead use them relative to $mountdir. This means that the content of the backup tar archives has changed with regards to the directory structure, please keep this in mind when restoring from backups!
• Now utilizes a bindmount to get the position directory near the $mountdir for the tarball creation.
• Directory names are sanitized for excessive whitespace and trailing slashes
• Code cleanup: use my $variable instead of use vars

In addition to Robin's improvements, the following changes were made by me:

• Fixed DSN string handling (thanks to Peter Zaitsev for spotting this)
• Fixed logging to the console (log levels were not printed)
• Removed a few currently unused configuration options from the config file

Please note that I have updated my site - I now use S9Y exclusively as the content management system for this site. Therefore I have moved it from the /blog subdirectory into the toplevel directory. Please update your bookmarks and feed readers and remove the /blog part from any URL you might be using! I have added a HTTP redirect rule for URLs that still access to the old /blog subdirectory. Some other stuff might still be broken while I am in the process of moving the static pages from the file system into the database - please bear with me. Thanks!

After I published my article on how to build MySQL with the new Falcon storage engine from source, the free BitKeeper client was updated to version 2.0. I have now updated the instructions accordingly, our manual has been updated to reflect these changes, too.

However, it is currently still not possible to clone the Falcon tree with this client, some kind of obscure locking error message appears, even though the admin console on bkbits.net does not reveal any active locks in the repository:

$ bkf clone bk://mysql.bkbits.net/mysql-5.2-falcon mysql-5.2-falcon
ERROR-unable to lock repo for export, try later.

We're aware of this problem and have already contacted BitKeeper support about this. I hope it can be resolved quickly...

Now that the source tree for the new Falcon Storage Engine is finally public, here's a quick HOWTO on how to compile the server from source. This procedure is described in more detail in the MySQL Manual. I assume you use Linux and have the required development toolchain installed.

You first should get the free BK client from http://www.bitmover.com/bk-client2.0.shar, unpack and install it:

$ wget http://www.bitmover.com/bk-client2.0.shar
--17:34:34--  http://www.bitmover.com/bk-client2.0.shar
           => `bk-client2.0.shar'
Resolving www.bitmover.com... 192.132.92.2
Connecting to www.bitmover.com|192.132.92.2|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 17,676 (17K) [application/x-shar]

100%[========================================================>] 17,676        33.56K/s

17:34:35 (33.50 KB/s) - `bk-client2.0.shar' saved [17676/17676]

$ sh ./bk-client2.0.shar
x - creating lock directory
x - creating directory bk-client2.0
x - extracting bk-client2.0/Makefile (text)
x - extracting bk-client2.0/bkf.c (text)
x - extracting bk-client2.0/demo.sh (text)

$ cd bk-client2.0
$ make
cc -O2 -Wall -Wno-parentheses    bkf.c   -o bkf
$ sudo install -m755 bkf /usr/local/bin

Now that the BK client is installed, we can create a local clone of the Falcon development tree:

$ bkf clone bk://mysql.bkbits.net/mysql-5.2-falcon mysql-5.2-falcon

This will now perform a check out the most recent source tree from the mysql-5.2-falcon repository (~83 MB of source code). You can now enter the newly created directory and start the build:

$ cd mysql-5.2-falcon
$ ./BUILD/compile-pentium-debug-falcon

If you have a x86_64 Linux system, use compile-amd64-debug-falcon instead. Depending on the performance of your system, this step will take a while, as it performs a full compile of the MySQL server and all related tools. Once the build completed with no errors, you can create a binary tarball distribution:

./scripts/make_binary_distribution

This should leave you with a tarball mysql-5.2.0-falcon-alpha-linux-i686.tar.gz that you can now install to, say, /usr/local:

$ tar zxvf mysql-5.2.0-falcon-alpha-linux-i686.tar.gz -C /usr/local
$ cd /usr/local
$ ln -s mysql-5.2.0-falcon-alpha-linux-i686 mysql
$ cd mysql
$ ./configure

The last command will install the privilege tables and start up the mysql server. You can now fire up the mysql client:

$ ./bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.2.0-falcon-alpha-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Now make sure that falcon has indeed been included:

mysql> SHOW VARIABLES LIKE 'have_falcon';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_falcon   | YES   |
+---------------+-------+
1 row in set (0.01 sec)

Looks good! Now it's time to actually create a table that uses the Falcon engine:

mysql> use test;
Database changed
mysql> CREATE TABLE names (id INT, fname VARCHAR (20), lname VARCHAR (20)) ENGINE=Falcon;
Query OK, 0 rows affected (2.84 sec)

mysql> describe names;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| fname | varchar(20) | YES  |     | NULL    |       |
| lname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> INSERT INTO names VALUES (0, 'Lenz', 'Grimmer');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO names VALUES (1, 'Monty', 'Widenius');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM names WHERE lname='Widenius';
+------+-------+----------+
| id   | fname | lname    |
+------+-------+----------+
|    1 | Monty | Widenius |
+------+-------+----------+
1 row in set (0.00 sec)

Congratulations, you are ready to further test and explore this new storage engine! Have fun with testing it - we would appreciate your feedback and experiences! Please share your impressions via the Falcon Forum or report bugs via our Bug Database. Make sure to read the bug reporting guidelines on the MySQL Forge Wiki first!

UPDATE: The people at Bitkeeper updated the free BK client to version 2.0 after I wrote this. I updated the described procedure above accordingly. Currently, the repository seems to have a locking problem that only appears when using the free BK client. We are working with the BitKeeper team on resolving this issue.

Some time ago, our GUI tools development team started a contest to shake out the bugs in the MySQL GUI applications, especially the MySQL Administrator and the MySQL Migration Toolkit.

The contest ran for about 3 months and resulted in more than 170 bug reports, of which the team fixed all the significant ones (over 120) by now. This is quite impressive, especially considering the relatively small size of the GUI development team. I am very happy to see that the quality of these tools has been improved, they are quite useful, especially for users not keen on using the command line.

The new MySQL Administrator 1.2.8 and MySQL Migration Toolkit 1.1.8 have now been declared production quality (GA) are included in the MySQL GUI Tools Bundle 5.0 Release 8, which is now available from our download pages.

Today we announced the top 5 bug-reporters, which we will reward with an Apple iPod for their good bug reports:

• Heinz Schweitzer
• Andreas Påhlsson
• "Yahoo Serious"
• Cezary Okupski
• Mick Francis

Congratulations to you and thank you very much for the excellent support! You and all other bug reporters did a great job in helping us to improve the quality of these tools.

After a longer pause I finally had a moment to work on my packages in the openSUSE Build Service. I now added RPMs for the Google Performance Tools and enabled building on openSUSE 10.2. From the perftools project page:

Perf Tools is a collection of a high-performance multi-threaded malloc() implementation, plus some pretty nifty performance analysis tools.

I hope you will find them useful. It would be interesting to see, if a KDE desktop linked against tcmalloc() is significantly faster than using the default glibc malloc()...

With great pleasure I today noticed that we finally have daily snapshot builds of all current versions of the MySQL server source trees (4.0-5.1) available on http://snaps.mysql.com/ again. Unfortunately this has not been the case for quite some time - a few issues that caused the 5.0 and 5.1 snapshot builds/tests to fail have now finally been resolved. I also made some changes to the build script that performs the snapshot builds, e.g. to use the Perl-based test suite where available instead of the Shell-based one, which is going to phased out in 5.1 anyway. Let's hope that from now on the snapshot builds will now be available more frequently again, I'll keep a close eye on that.

Sorry for the long delay, I know that some of you depend on receiving updated source tarball snapshots on a regular basis instead of having to use the free BK client...

Recently I stumbled over a posting on the German MySQL Forum from a user that accidentally removed all table files from a MySQL Server's data directory with a misbehaving shell script. He was surprised to find out that the server happily continued to serve requests and his web site was still fully operational, even though /var/lib/mysql/<database> was completely emtpy! The reason for this in a nutshell: the rm command only removed the reference to the table files from the database directory, the files itself were not removed from the file system yet as the mysqld process still had the files opened. So as long as a process keeps a file open, the kernel will not release the disk space occupied by the file and it will remain intact, albeit no longer visible.

Of course, the user was now desperate to recover the deleted tables files and was asking for help. Fortunately the recovery in this case is pretty simple. You should first shut down your application to avoid further activitiy on the affected database. Important: you must not shut down the MySQL Server, as this would close the last open reference to the table files! Now you can simply use mysqldump --opt <database> > database.sql to perform an SQL dump of the deleted tables. As the MySQL server still can access the open table files, the dump will contain the entire content and can then be used to restore the database again. Now you should restart the MySQL server so it closes the still open file descriptors of the deleted tables files. Alternatively, you could use DROP TABLE <table> or DROP DATABASE <database> to properly remove the references, in case you don't want to shut down the entire server. Now you can restore your missing tables from the SQL dump as usual and can restart your application!

Note that this trick only works on table files that were removed on the file system level, not after you used DROP TABLE/DATABASE, so it's not a magic undo function for these commands - only restoring from a recent backup (e.g. performed with mylvmbackup, hint, hint) will help in this case. In addition to that the MySQL server must have had opened the tables before. A freshly started MySQL server has not opened any table files apart from the ones in the mysql system database.

By the way, there is a related article "Bring back deleted files with lsof" on Linux.com that covers the subject of recovering deleted (but still open) files on a more general level and also provides some more background information about the Linux internals. Worth a read!