Lenz Grimmer's blog

This year's Systems trade fair will take place from October 21st-24th in Munich, Germany. Sun will be present there with a stand in the exhibition area (Hall B2, Stand 329) and we also sponsor a conference themed "Perspective Open Source" that will provide half-day sessions (in German) about opensource-related topics throughout the week.

Some highlights of what we'll present on our stand:

• MySQL
• Sun Fire Servers (with Intel Xeon Processors)
• Open Storage
• VirtualBox
• StarOffice/OpenOffice
• Virtual Desktop Infrastructure (VDI) for SMBs

By the way, we have a limited contingent of free day passes for the fair - see this page if you're interested to get one!

I will be there to help manning the MySQL demo pod on all days. See you there!

I am happy to announce that mylvmbackup version 0.10 has been released.

You can download the updated package from the project home page or via the openSUSE Build Service.

This version fixes some bugs and includes new functionality:

• Applied patch from Marc Haber: added option --keep_snapshot that will skip the removal of the backup snapshot before terminating the script. Providing the option --backuptype=none will now skip creating a backup using the builtin backup modules. Both options provide more flexibility when using hooks for performing the actual backup tasks or when the snapshot is considered to be the actual backup.
• Added two new hooks: "backupsuccess" and "backupfailure" which are called respectively upon success of failure of the backup operation (Bug #264089)
• Make sure that binaries are being found ($PATH may not include /sbin when called from cron), added missing entry for "lvs" to mylvmbackup.conf (Bug #255703)
• Updated documentation

Greetings from the University of Latvia in Riga, where the local activities related to Software Freedom Day 2008 are in full swing! We've just finished the introductionary talk "Software freedom in Latvia"  by Evijs Taube (LATA) and Leo Trukšāns (Linux Centre). Currently, Mark Callaghan from Google Inc. is talking about "Running a database when your business depends on it". We've more talks scheduled for the rest of the day and there also is an ongoing workshop about MySQL Performance tuning by Jay Pipes, which takes place in the University's Linux Lab. The full agenda is published on the MySQL Forge Wiki and I just published a first batch of pictures on my gallery. Enjoy!

This is more of an "behind the scenes" update and I hope that you won't see any (negative) changes on the PlanetMySQL front page or the RSS feeds: I just finished and commited the conversion of the backend script that performs the parsing and aggregation of feeds from requiring MagpieRSS to SimplePie.

This will provide better support for a wider range of feed types and should also fix a few quirks, e.g. that some postings (for example the one from Kevin Burton) only showed up as an "A" in the Planet's RSS feed. It hopefully also fixes a weirdness with time zones that some people were reporting, but this requires further investigation.

The size and complexity of the script was reduced significantly because of this change - SimplePie is a breeze to use in comparison to MagpieRSS and it's very well-documented. The developers provide some more reasons and a comparison on why you should also make this switch!

And in case you notice anything broken or weird on PlanetMySQL that might be related to the change, please let me know! This change was an important step for future improvements of the site.

In case you are processing and working with geospatial data on MySQL, you may be interested in the following UDF (plugin) for MySQL 5.1: Koji Okumura from Oki Labs Japan has ported two functions from PostGIS into a MySQL UDF:

• distance_sphere(point, point): Returns linear distance in meters between two lat/lon points. Uses a spherical earth and radius of 6370986 meters. Faster than distance_spheroid(), but less accurate. Only implemented for points
• distance_spheroid(point, point, spheroid): Returns linear distance between two lat/lon points given a particular spheroid. Currently only implemented for points.

Since it's an UDF, it can be easily added to an already installed server. These two functions actually complement the improved precise GIS functions nicely (which provide a 2-dimensional DISTANCE() function).

You can download the UDF source tarball from here. Compiling it is pretty straightforward and requires a local copy of the MySQL 5.1 sources and the ususal build environment. For my testing, I used the 5.1 source tree that includes the additional precise geospatial functions. Below I just list the commands required to build and create the binary tarball and omit the lengthy output in between some of these commands:

$ bzr branch lp:~mysql/mysql-server/mysql-5.1-wl1326
$ cd mysql-5.1-wl1326
$ BUILD/compile-pentium-max
$ make bin-dist

You should now have a binary tarball mysql-5.1.26-rc-linux-i686.tar.gz that you can install or deploy by using the MySQL Sandbox, which I used for my further testing:

$ tar zxvf mysql-5.1.26-rc-linux-i686.tar.gz -C ~/opt/mysql
$ cd ~/opt/mysql
$ mv mysql-5.1.26-rc-linux-i686 5.1.26
$ make_sandbox 5.1.26
$ cd ~/sandboxes/msb_5_1_26
$ ./use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.26-rc Source distribution

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

mysql [localhost] {msandbox} ((none)) >

Success! Let's see if the new GIS functions work:

mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE (GEOMFROMTEXT('POINT(0 0)'),GEOMFROMTEXT('POINT(1000 1000)')) AS distance;
+-----------------+
| distance        |
+-----------------+
| 1414.2135623731 |
+-----------------+
1 row in set (0.00 sec)

Now let's compile and install the UDF that provides the new spherical distance functions. Download the sources and extract the tarball in the same directory in which you branched the MySQL server sources:

$ tar zxvf mysql-udf-distance_spheroid-1.0.tar.gz
$ cp distance_spheroid/*.{cc,h} mysql-5.1-wl1326/sql
$ cd !$
$ g++ -DMYSQL_SERVER -shared -o udf_distance_spheroid.so -I../regex -I../sql -I../include spatial.cc calc_distance_spheroid.cc udf_distance_spheroid.cc

This will build the shared object udf_distance_spheroid.so that you now have to copy into the plugins directory of your server:

$ install -D udf_distance_spheroid.so ~/opt/mysql/5.1.26/lib/mysql/plugin

Now we have to load the UDF and make the new functions known to the server. Get back into your sandbox or fire up the commandline client again:

mysql [localhost] {msandbox} ((none)) > CREATE FUNCTION distance_sphere RETURNS REAL SONAME "udf_distance_spheroid.so";
mysql [localhost] {msandbox} ((none)) > CREATE FUNCTION distance_spheroid RETURNS REAL SONAME "udf_distance_spheroid.so";

Now you're ready to experiment with these new functions! For my test case, I calculated the distance between Hamburg, Germany (where I currently live) and Heidelberg, Germany (where I grew up) by providing the Lat/Lon coordinates as points:

mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE_SPHERE(GEOMFROMTEXT('POINT(53.583333 9.983333)'),GEOMFROMTEXT('POINT(49.412222 8.71)')) AS 'Distance (m)';
+-------------------+
| Distance (m)      |
+-------------------+
| 479037.4799112912 |
+-------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} ((none)) > SELECT DISTANCE_SPHEROID(GEOMFROMTEXT('POINT(53.583333 9.983333)'),GEOMFROMTEXT('POINT(49.412222 8.71)'),'SPHEROID["GRS_1980",6378137,298.257222101]') AS 'Distance (m)';
+-------------------+
| Distance (m)      |
+-------------------+
| 479345.1884839106 |
+-------------------+
1 row in set (0.00 sec)

Interestingly, using the virtual ruler on Google Earth gave me a distance of 471881.44 meters for the same coordinates. Not sure where the difference comes from, I assume they are using a slighly different projection system? It would be interesting to see, if these results match what you would get out of PostGIS using the same queries.

In any case, these two functions may come in handy, if your application needs to calculate distances between two points on a map. So give it a try! If you have tested this functionality and want to give feedback to Koji, please reply to his post on the MySQL GIS Forum.

As I mentioned some time ago, Software Freedom Day 08 will take place on Saturday, 20th of September 2008.

Coincidentally, the a large number of Sun/MySQL Engineers and other Sun folks will be in Riga, Latvia for an internal developer meeting around this day. To make use of this opportunity, we plan to give a number of sessions and presentations (in english) about various topics and to contribute to this global celebration of Open Source Software.

We've set up a Team Page on the Software Freedom Day web site for this event - the venue will be the Cafeteria Conference room in the basement of the University of Latvia, Riga, which can accomodate 60-80 people:

Raiņa bulvāris 19
Rīga, LV-1586

There is no entrance fee and you don't have to register - just come by and meet with us! There will be free coffee, refreshments and cake during the breaks.

In the evening, Sun will host a social event (incl. free drinks and food) in the SAS Radisson Daugava hotel, starting at 19:30:

Radisson SAS Daugava Hotel
Kugu 24, Rīga, LV-1048, Latvia
Tel.:+371 6706 1147; Fax: +371 6706 1101

We've set up a tentative schedule (45 minutes per session plus 15 minutes of Q&A), please check the Wiki for eventual last-minute changes!

11:00-12:00: MySQL/Open Source in Latvia (Evijs Taube, Sun Microsystems)
12:00-13:00: Open Source Business Models: how to build a business around free software (Speaker TBD)
13:00-13:30: Lunch Break / Ask the Guru your tech questions
13:30-14:30: MySQL in the Enterprise: Customer references, commercial offerings (Rob Young/Robin Schumacher, Sun Microsystems)
15:00-16:00: MySQL Community Overview: How to engage and contribute (Giuseppe Maxia/Jay Pipes/Lenz Grimmer, Sun Microsystems)
16:15-17:15: MySQL Performance tuning best practices (Jay Pipes, Sun Microsystems)
17:15-18:15: Maintaining your Open source project with Bazaar and Launchpad (Lenz Grimmer/Giuseppe Maxia, Sun Microsystems)
19.30: Social event: Software demonstration, buffet and free beer in the SAS Radisson Daugava hotel

We'd like to thank Leo Trukšāns, Michael Dexter and Georg Richter for their help and support in getting this event arranged and organized! I look forward to being there and help to spread the word about the stuff that keeps me occupied for more than 13 years now

While Colin beat me in blogging about Project Kenai, I think I can still provide some additional background information about this new project hosting service from Sun.

If you are a maintainer of an Open Source project, you currently have plenty of choice when it comes to getting your project hosted for free. One criterion could be your software configuration management system (SCM) of choice.

Some of the hosting services that I am currently aware of and the choice of SCM they offer include:

• BitBucket (Mercurial)
• Collab.net (Subversion)
• github (Git)
• GoogleCode (Subversion)
• Launchpad (Bazaar)
• Sourceforge.net (CVS, Subversion)

As disclosed by Tim Bray some days ago, there now is another option - Kenai is open for project hosting (currently by invitation only)! In his blog post, he interviews Nick Sieger, one of the developers behind this project about their motivation and intentions:

We need to demonstrate credibility in building on top of more traditional LAMP/SAMP web stacks (not just Java EE); and we need to show viability of Sun technologies and hardware for next-generation web applications.

In a nutshell, Kenai is a platform for:

• Developer collaboration
• Communities of connected developers
• Integrated collaboration services stack

Some of the features that are currently available include:

• SCM services using Subversion and Mercurial
• Bug Tracking (Bugzilla)
• Forums
• Wikis
• Mailing Lists (using Sympa)

Reading the interview with Nick and looking at some presentations slides for RailsConf from Fernando Castano (a jRuby and Database performance engineer at Sun and another member of the project team),  I was able to gather a list of the tools and technologies they used to build Kenai:

• Apache2, mod_proxy_balancer
• Ruby on Rails (running on jRuby)
• Glassfish Java application server
• MySQL 5.0
• Apache Solr for search
• memcached

I found it interesting that they decided to deploy and run the Rails application as a war file within the Glassfish application server (using Warbler). By the way, the fabolous OpenSUSE Build Service is a Rails application, too! So far, the entire site is powered by a single MySQL instance with query cache enabled.

The project is hosted on the following infrastructure:

• Sun Fire T2000 for (for web and application serving)
• Sun Fire X4500 for Storage
• OpenSolaris Nevada (Zones/Containers, SMF)
• ZFS (9.7 TB RAIDz, Snapshots, NFS)
• Coolstack & Blastwave packages

You should check out Fernando's presentation for more technical details, tuning info and how they benchmarked the setup - it contains a number of useful tuning hints and performance graphs.

Last time I checked, 27 Projects have joined so far (e.g. jRuby, xVM Server). Kenai itself is developed on Kenai. It's going to be interesting what other projects will find their home there.

Nick also talked a bit about their future near term plans: to improve the usability and feature set, incrementally improve the site navigation and layout and adding support for hosting files/release downloads. They also consider offering Jira as an option to Bugzilla for bug tracking and Git as another SCM option.

There is an IRC channel #projectkenai on freenode.net, to get in touch with the developers directly. The mailing list for the Project Kenai site itself, is users@help.kenai.com - you can subscribe to this list here.

Tomorrow (Thursday, 11th of September) at 9:00 PST/16:00 UTC/17:00 GMT/18:00 CET, there will be an new free MySQL University Session. MySQL University started as an internal training program for MySQL engineers, to share and spread knowledge about their areas of expertise and has been available to the public for quite some time now. It covers a wide range of technical topics around the MySQL Server and usually takes place once per week.

For the first time, the presentation will not be performed by (former) MySQL employees/developers, but by two of our "Sun Classic" colleagues: Jyri Virkki (OpenSolaris Web Stack community lead) and Murthy Chintalapati (Sr Engineering Manager, Web Stack development) will talk about the OpenSolaris Web Stack:

OpenSolaris Web Stack is an OpenSolaris project and community building an integrated stack of popular open source web tier infrastructure technologies such as Apache HTTP server, MySQL, memcached, PHP and Ruby On Rails optimized for Solaris platform. This session introduces OpenSolaris Web Stack, its status and future development including addition of newer technologies such as lighttpd, Varnish etc., as well as the ease of use features for developers and deployers. We will also be discussing an experimental web stack IPS package repository and it could be leveraged to build and make available popular end user applications such as Drupal.

MySQL University sessions are free to attend - all you need is an IRC client (to post your questions and comments) and an audio player capable of playing back an OGG audio stream, so you can listen to what is being said. See the Instructions for Attendees on the MySQL University pages for more information on how to log in and attend. The audio stream will be recorded and published on the MySQL University pages for later consumption, in case you can't make it or want to listen to a previous session.

One of the sessions at DrupalCon I attended was Larry Garfield's talk about "Drupal Databases: The Next Generation", which gave me a good insight into the current state of the Drupal database layer and how they plan to overhaul it for Drupal 7. The key points that I took away:

• A new API based on PDO
• Object-oriented, requiring PHP5
• Support for using prepared statements
• A unified access API
• A query builder
• More support for other database systems (currently Drupal supports MySQL and PostgreSQL only). In particular, they are keen on adding SQLite support, to ease local development.
• Support for master-slave replication (by randomly distributing reads among the hosts)
• Support for using different database types in parallel (e.g. using SQLite for read-only tables, MySQL for everything else)

The slides and a video of the presentation are available, if you want to check it out. There is a task list on the Drupal.org web site that keeps track of the ongoing activities.

The MySQL 5.1 Use Case Competition is in full swing - we've already received a number of cool and interesting submissions, which we will turn into articles that will be published on the MySQL Developer Zone over the course of the coming weeks. Today we received a note from Jakub Vrána from the Prague, Czech republic. He's the author of phpMinAdmin, a MySQL management tool written in PHP. Here's what he wrote:

In the beginning of September 2008, I have implemented MySQL 5.1 Events to the database management tool phpMinAdmin. I've used the Windows version of MySQL 5.1.26 for the development.

As phpMinAdmin tries to be full-featured, yet compact management tool, I have implemented Events to allow users of MySQL 5.1 manage it.

The implementation was quite straightforward, it took only 4.5 kB of PHP code. Events management is well described in MySQL documentation and easy to understand.

During the development, I've reported three bugs: Bug#39163, Bug#39165, Bug#39173. I have been positively surprised by the speed of reaction to these reports

Thank you very much for your submission and the support, Jakub! We appreciate it.

If you're reading this and are using MySQL 5.1 and any of it's new features: have you considered telling your story yet? You may even win something when doing so!

I had a nice chat with Kieran from Acquia at DrupalCon last week - we discussed how people running local Drupal user groups could expand their outreach into other communities, in particular into the MySQL User Groups. Scott Mattoon captured our conversation on video, which is now available on blip.tv:

Click To Play

The gist of what we talked about: if you are organizing a local Drupal User Group Meetup, check out http://mysql.meetup.com to find out if there is a local MySQL user group nearby. Chances are high that there is! And if not, you may find at least people in the area that would be interested in meeting about this subject. We also maintain list of user groups on the MySQL Forge Wiki. Consider extending your invitation for your next meetup to these folks as well! It's very likely that someone would be interested to learn more about Drupal. The same applies to other user groups, e.g. from the PHP community.

I personally run a MySQL User Group here in Hamburg, and I usually extend my invitations to a number of channels and mailinglists, including the local PHP, Perl and Linux User Groups. Every once in a while, a new member from these communities shows up.

So this thing works the other way around, too: if you are the organizer of a MySQL Meetup, have you thought about looking at http://groups.drupal.org/ yet? Maybe you will find a Drupal User Group in your very own town that you could invite to learn more about MySQL and exchange contacts? If you are looking for more tips on how to run and expand your User Group, I've created a page with useful hints about this topic on the MySQL Forge Wiki. Your feedback and additions are very welcome!

I'm back home from DrupalCon 2008 now - it has been a great event! I met a lot of nice people from the Drupal Community and learned a lot about this CMS. I've been very busy in uploading the remaining pictures from the event to my gallery - so here's for your viewing pleasure:

• Impressions from Szeged
• Day One - 2008-08-27
• Day Two - 2008-08-28
• Day Three - 2008-08-29
• Day Four - 2008-08-30

I also gave two talks and held a BoF there - the slides have now been attached to the session nodes, one of them (the HA session) even includes a video recording:

• bzr - The Bazaar source revision control system
• MySQL Backup and Security - Best practices
• High availability solutions for MySQL: An Overview and practical demo (Video)

I've also uploaded some pictures from FrOSCon to my Gallery now, hope you enjoy them! The slides of my FrOSCon talks are now uploaded to the conference system as well:

• OpenSolaris für Linuxer
• bzr - The Bazaar source revision control system

Hello and greetings from DrupalCon 2008 in Szeged, Hungary!

We (Thierry Manfé, Scott Mattoon and myself) are having a great time manning our booth and talking about Drupal, MySQL and Open Source@Sun with the nice crowd of Drupal Users and Developers here. Sun is a gold sponsor of the event and we're giving a number of sessions as well.

Today I gave my first presentation about MySQL Backup and Security - Best practices - unfortunately I ran a tad bit out of time at the end... The slides have already been attached to the session page, so you can read up on the last few things I was going to talk about. Feel free to contact me, if you have further questions!

Tomorrow I'll be talking about High availability solutions for MySQL: An Overview and practical demo, which will also include a practical demonstration of a two-Node Linux Cluster, performed by Jakub Suchy. In the afternoon, I will also hold a BoF about bzr - The Bazaar source revision control system

I've also uploaded some pictures from the event (and some impressions from the city) on my gallery (more will follow later). Enjoy!

Being able to use an Open Source DBMS to manage your data is nice, but what good would it be if you can't easily access it from your applications? One key factor to the popularity of MySQL is probably its wide range of available language bindings, which started with support for C, PHP and Perl from early on.

I've tried to gather a list of languages and their respective MySQL drivers/modules below. It's by no means complete or exhaustive, but I think I covered quite a lot of popular as well as exotic programming languages.

There is a number of connectors which are actually developed by the Sun Database Group (aka MySQL) itself and that are ready to use:

• Connector/ODBC - Standardized database driver Windows, Linux, Mac OS X, and Unix platforms.
• Connector/J - Standardized database driver for Java platforms and development.
• Connector/Net - Standardized database driver for .NET platforms and development.
• Connector/MXJ - MBean for embedding the MySQL server in Java applications.
• MySQL native driver for PHP - mysqlnd - The MySQL native driver for PHP is an additional, alternative way to connect from PHP 6 to the MySQL Server 4.1 or newer.
• libmysql - The original implementation of the MySQL Client/Server protocol (in C). This library is the basis for a large number of client libraries for other languages.

In addition to the above, there are several other connectors developed by Sun/MySQL, which are still under development:

• MySQL Connector/OpenOffice.org - a driver that allows you to connect to MySQL with OpenOffice.org natively, without having to use JDBC/ODBC.
• PHP PDO mysqlnd - a PHP/PDO driver based on the mysqlnd driver
• Connector C++: a driver for C++ applications that uses an API similar to the JDBC 3.0 API.

But it's not only us who develop language bindings for the MySQL server. There is an abundance of drivers that are developed and maintained by the Community, independently from Sun/MySQL (but sometimes with support or guidance from MySQL engineers). The list below is not sorted in any particular order other than the sequence in how I found them over time:

• DBD::mysql - a MySQL driver for the DBI database abstraction layer of Perl scripting language. There used to be a a native driver named "Msql-Mysql-modules", but it does not seem to be maintained anymore.
• Net::MySQL - an implementation of the MySQL Client/Server Protocol in Perl (does not require the MySQL client library). There also is MySQL::Packet, a Perl module that can encode and decode the binary protocol.
• MySQLdb - a MySQL driver for the Python scripting language.
• mysqli, ext/mysqli - MySQL Improved Extension for PHP
• mysql, ext/mysql - MySQL Extension for PHP
• PDO_MYSQL - MySQL PDO driver for PHP
• MySQL/Ruby - the MySQL API module for Ruby, based on the libmysql C library.
• Ruby/MySQL - the MySQL Ruby API, implemented in Ruby.
• MySQL++ - a C++ wrapper for MySQL's C API. It is built around the same principles as the Standard C++ Library, to make dealing with the database as easy as dealing with STL containers.
• MySQL wrapped - another C++ wrapper for the MySQL C API.
• MySQLtcl - a simple API for accessing a MySQL database server from the Tcl programming language.
• MySQL-Cocoa - An Objective-C Cocoa API for MySQL (based on libmysql).
• Eiffel MySQL - an interface to the MySQL database server using the Eiffel programming language.
• Bryan O'Sullivan's pure Haskell MySQL bindings
• hsql-mysql - A MySQL driver for Haskell
• MySQL Driver for the programming language D
• MyDB module for the Kaya programming language (included in the distribution)
• MySQL bindings for the Objective Caml language
• MySQL bindings for GNU Ada
• The SPL programming language ships with a MySQL module included in the distribution
• Database bindings (incl. MySQL) for GNU Octave
• LuaSQL is a simple database interface for the Lua programming language
• erlang-mysql-driver - a revamped MySQL driver for Erlang
• Myscsh - a Scheme implementation of the MySQL protocol

I probably forgot some other drivers/bindings - if you have any more to add, please let me know!

And if you'd like to create your own implementation for your favourite language: the protocol is documented here and here. Jan's additional notes may also be helpful to get you started.

Just to remind you that Packt Publishing is running their Open Source CMS Award again:

The Packt Open Source Content Management System Award is designed to encourage, support, recognize and reward Open Source Content Management Systems (CMS) that have been selected by a panel of judges and visitors to www.PacktPub.com. Now entering its third year, the Award has established itself as an important measure for quality and the popularity of Open Source Content Management Systems.

You have two more weeks to submit your favourite CMS in the following categories:

• Overall Winner
• Most Promising Open Source CMS
• Best Open Source PHP CMS
• Best Other Open Source CMS

As for the last two years, I'll be a member of the team of judges that have to choose from the finalists that received the most nominations during the nomination stage.

I look forward to the list of finalists - it's always interesting to find out about new developments in this area and how the established projects in this market have developed over the course of the year!