MySQL user groups in Dubai and Sydney, on my way to NZ

In January 2010 I will attend Linux.Conf.Au, which this year is held in Wellington, New Zealand. It's a long way from Europe to New Zealand, and so I will take a few stops. On January 13 I will be in Dubai, UAE. If you are around, I would love to organize a MySQL meeting. I haven heard back from the local user group and it seems that a meeting will take place. Stay tuned for more. On January 15th I will be in Sydney. The organizers are already at work. We will definitely have an user group meeting. I am open to suggestions about the topics.

From Sydney, I will continue to Wellington, where I will attend LCA2009 and then DrupalSouth before coming back to my usual time zone.

Gearman: distributed computing and Codebits pictures

The first Codebits> day lasted until long past midnight. So the attendees were a bit sleepy today, but they were brave and got up early enough for my session. The presentation covered the basics of Gearman, some advanced magic to install remote MySQL servers, and more magic to enable MySQL users to shoot themselves in the foot repeatedly by combining a gearman/MySQL UDF and some clever scripts. As usual, the slides are available on slideshare. Some pictures from codebits 2009 are on Flickr.

Worshiping Technology.

Cool technology to worship.

José, the mastermind	Josette, the culture provider
Gonçalo, the friendly face who met me at the airport	&nbs; Pedro, magnificent guide of town and technology.

MySQL schema maintenance

At CodeBits I had my first session about MySQL schema maintenance. I covered the basic command line possibilities before coming to the recommended tool, MySQL Workbench. The slides are available at slideshare.

Interesting questions: ([updated] with answers from the development team

• [Q] Are there plans to administer MySQL Cluster with Workbench?
  [A] Not that we know of.


• [Q] Can Workbench deal with user permission maintenance across servers? (especially in cases where development and production users can't have the same privileges)
  [A] YES. It's in the roadmap


• [Q] Can MySQL Workbench help editing stored routines? Apparently, you can't change the routine code with ALTER PROCEDURE/FUNCTION, but you need to drop it, recreate it, and eventually change privileges.
  [A] Not that we know of, although something can be done with Proxy to ease this problem.


• [Q] Is the code for the plugin documented for contributors?
  [A] Not yet, but it's going to be soon.

Poor man's schema comparison

Comparing database schemas is one of those DBA tasks that occur all the time. If you can afford the luxury of having a GUI, then MySQL Workbench is the tool for you. It will help you compare structures and eventually synchronizing the differences. If the only thing at your disposal is a command line interface, without the possibility of installing anything (a common occurrence when you are consulting), then this Poor man's schema comparison tool may come handy.

It's a Perl script that doesn't require module installations beyond the most common ones, and produces a simple output, fit for further examination with the tools that you have at your disposal when working at the command line.
By default, it invokes vimdiff, to let you know visually what the differences are. If you change the command to diff, you get a simple output that can tell you at a glance what differences you are dealing with.
For example, after executing

schema_diff mysql://root:mysql@127.0.0.1:3306/crosstab \
mysql://msandbox:msandbox@127.0.0.1:5139/crosstab

I got the difference (a column that I introduced in the first database but not in the second one) clearly visible on screen.

I will be talking about this and other resources for the DBA during my session on schema maintenance at CodeBits in Lisbon (December 3-5, 2009).

Codebits 2009, coders conference and competition in Lisbon

Codebits is approaching. Form December 3rd to 5th, this gathering of 600 developers for a conference, which is also and foremost a competition, will occupy the mind of the best coders in Europe. I will be a speaker, with two sessions: Schema maintenance in MySQL, focusing mainly on MySQL Workbench, but with an introduction to the other choices. Introduction to Gearman - a distributed, language and OS independent system for dynamic programming.

Also Lenz will be there, and quite busy. He will also have two sessions:

• Why you should be using a distributed version control system (DVCS) for your project, with advice that will benefit every developer.
• MySQL High Availability Solutions - Best practices and commonly used HA setups for MySQL

The event is hardly like any other conference. It will be a momentous show, with a part that start like a conference but goes on as a competition.
If you like coding, you must show up!

Gearman for MySQL

If you haven't yet heard about Gearman, it's time to have a look at it. Its distributed client server architecture are a perfect match for today's cloud oriented applications. When talking about Gearman, much stress is often given on its scalability features, such as map/reduce and distributed loads. But Gearman has also a distinctive advantage in the feature department, because of its design that I like to describe as cooperation of the fittest.

While a traditional application is written entirely in a given language, or it must find ways of integrating multiple languages parts, Gearman encourages easy cooperation between parts written in different languages, eliminating the need for library translation, and allowing the best implementation of a feature to be used by another part of the application.
Today, November 12, at 14:00 UTC, there will be a MySQL University presentation on Gearman for MySQL.
One of the components created by Gearman enthusiasts is a MySQL Gearman UDF. Using this extension, you can easily define functions in any language you are familiar with, and then call that function from MySQL. Some examples shown in the presentation include shell access, regular expressions enhancements, storing results incrementally in a file, using language-specific library extensions, and your imagination can do the rest.
Using this sample worker code, you can then call the UDF function from MySQL with something like thge following:

select gman_do('reverse','abcd') as test;
+------+
| test |
+------+
| dcba |
+------+

SELECT gman_do('shell',
concat(' ls -lh ', 
  (select variable_value from information_schema.global_variables 
  where variable_name = "datadir" )))\G 

total 40976
-rw-rw----  1 gmax  staff   5.0M Nov 11 13:34 ib_logfile0
-rw-rw----  1 gmax  staff   5.0M Nov 11 13:34 ib_logfile1
-rw-rw----  1 gmax  staff    10M Nov 11 13:34 ibdata1
-rw-rw----  1 gmax  staff   1.2K Nov 11 13:34 msandbox.err
drwx------  2 gmax  staff   2.4K Nov 11 13:34 mysql
-rw-rw----  1 gmax  staff     6B Nov 11 13:34 mysql_sandbox5140.pid
drwx------  2 gmax  staff    68B Nov 11 13:34 test


select gman_do('eval','2 * 3') ;
+-------------------------+
| gman_do('eval','2 * 3') |
+-------------------------+
| 6                       |
+-------------------------+


select gman_do('eval',
  concat('$_="',host,'";tr/a-z/b-za/; $_')) 
as test from mysql.user;
+-------------+
| test        |
+-------------+
| %           |
| mpdbmiptu   |
+-------------+

Spider and vertical partition engines with new goodies

The Spider storage engine should be already known to the community. Its version 2.5 has recently been released, with new features, the most important of which is that you can execute remote SQL statements in the backend servers. The method is quite simple. Together with Spider, you also get an UDF that executes SQL code in a remote server. You send a query with parameters saying how to connect to the server, and check the result (1 for success, 0 for failure). If the SQL involves a SELECT, the result can be sent to a temporary table. Simple and effective.

In addition to the Spider engine, Kentoku SHIBA has also created the vertical partitioning engine. Instead of splitting tables by record, you split them by columns. You can define a table with column A and column B, with primary key K, and another table with column C and column D, with primary key K. The vertical partition engine allows you to define a table with columns K, A, B, C, D, which looks to the user like a regular column. The backend tables can be of any engine.
There is a MySQL University session about the Spider and VP engines on November 26th at 15:00 CEST. Free attendance!
The slides are online: Sharding for the masses

Introduction to Gearman at the Italian Research Council

I was invited to contribute some technological views at the Italian National Research Center, during the Internet Governance Forum. My contribution was ahigh level introduction to Gearman, which sparked a debate about the impact of the cloud on the future of open source. Indeed, cloud computing technologies have the potential of harming open source adoption. If this is a threat and how much it can affect the future of open source depends on the business model behind the cloud.

More interesting topics were discussed both during the scheduled sessions and in open gathering. During dinner, for example, I got some disturbing statistics on database teaching in Italian universities. It's very common to require MS Access and .NET as supporting technologies for most of the IT related exams, with little or no room for open alternatives like MySQL and PostgreSQL.
Seeing a newsstand inside the compound of the NRC makes me think that progress slow in the institution that is supposed to be the guide of the Italian technology. The same thought crossed my mind seeing that the conference hall, a beautiful 200 seat hall well equipped for multimedia has only one power socket, but it was off limits for users.

Jeremy's article on MySQL Sandbox in Linux Magazine

Jeremy Zawodny of Craiglist has written a kind article about MySQL Sandbox. The article, MySQL Sandbox: Treat MySQL Instances like Virtual Machines, is a practical test of MySQL Sandbox with usage examples and warm appreciation. Thanks, Jeremy!

The article was published in July but I noticed it only today. I guess I should pay more attention to my favorite topics when I travel.

Partitioning with non integer values using triggers

Looking at Bug#47310, which is a feature request that I hear frequently when I talk about partitions, I wrote a comment, suggesting triggers to work around the limitation.
The reason for the limitation is that allowing arbitrary functions for partitioning was too complex and it was provoking crashes and other unpleasant side effects (see the discussion under bug#18198).
But if you use a trigger, the resulting column is a plain integer, and many of the side effects disappear. The drawback is that you need to add a column to your table, and you need to use that column when searching for data. With that in mind, you can implement the workaround quite easily.

USE test;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
        user_id int(10) NOT NULL,
        username varchar(25) DEFAULT NULL,
        dummy INT not null,
        PRIMARY KEY (user_id, dummy),
        UNIQUE KEY username(username,dummy)
) ;

CREATE TRIGGER users_bi
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.dummy = ASCII(LOWER(LEFT(NEW.username,1))); 

ALTER TABLE users PARTITION BY RANGE (dummy) (
        PARTITION p0 VALUES LESS THAN  (96),  #being f
        PARTITION p1 VALUES LESS THAN (109),  #being m
        PARTITION p2 VALUES LESS THAN (115),  #being s
        PARTITION p3 VALUES LESS THAN (122)   #being z
); 

INSERT INTO users (user_id, username)
VALUES (1,'Joe'), (2,'Sam'),(3,'Abe'),(4,'Rich');

EXPLAIN PARTITIONS SELECT * FROM users 
where username = 'Abe'; 
# This simple query doesn't use partition pruning. 
# This is to be expected.

EXPLAIN PARTITIONS SELECT * FROM users 
where dummy = ASCII('a') and username = 'Abe'; 
# Here, the partition pruning kicks in, at the price of an extra
# condition in the query.

Snow Leopard blues

On Friday afternoon, I went to give a presentation about MySQL advanced features at the Sardegna Ricerche technology park. The presentation included a quick introduction to MySQL Sandbox, something that I have been doing for years, and I thought I could do blindfold, if required. However, something didn't go as expected.

Just when I was showing off how easy is it to install a MySQL sandbox from a tarball, I was faced by an unexpected error. The tar application was not among the recognized ones. As soon as I saw the error, I immediately knew what had happened. That morning I upgraded my Mac OSX to Snow Leopard. And, unknown to me, Apple has changed the default tar, which is not a symlink to the GNU tar, but points at bsdtar. Not a difficult fix (I released a new version of MySQL Sandbox this morning) but not something that you want to deal with during a live demo either.
Apart from that, Snow Leopard seems to behave nicely. Somebody else had nasty compatibility problems but so far I have been spared.
One annoying problem is that Safari crashes with Java applications, because of the faulty Java 1.6 package released with Snow Leopard. Apparently, Java 1.5 and 1.6 both point at the same binaries, on the grounds that 1.6 is backward compatible. Well, it is, but it crashes the browser, so I looked around for a remedy, and I found out that someone else has fixed the issue and shared the recipe. Thanks, folks. It worded for me as well.

The Open Source Events Calendar

Kudos to Lenz, who, acting on an idea launched by Ronald Bradford some time ago, has put together a comprehensive calendar of open source events. Most of them are somehow related to the MySQL ecosystem, but there is no limitation to what the calendar contains. Here is the announcement, with the instructions to use and contribute to the calendar.

In addition to informing you about the events, this calendar does also tell you when a deadline is approaching. Using this tool, you won't miss a call for participation anymore.
You can simply subscribe to the iCal feed (it's a Google calendar) or see it online.
And of course, we want to improve the calendar. Feel free to submit new events using the event submission form.

We're looking into ways of improving the service. It would be nice to have a widget to show on your blog. Using Google APIs, it's easy to create such a widget, but the events are shown in insertion order, rather than chronological order. If anyone knows how to fix this issue, please contact me or Lenz.

Sponsoring OpenSQLCamp

The next OpenSQLCamp will be held in Portland, Oregon, USA. It is being organized by Eric Day, well known to the open source community for his active and productive participation to several projects (especially Drizzle and Gearman).

The event is public and free. Therefore, it needs public sponsoring. I don't know yet if I can attend, but I have already donated something to the organizers, and I am officially a sponsor. You can be one too. Simply go to the sponsors page and donate a minimum of $100 as in individual or $250 as an organization.
And of course, if you plan to participate, register yourselfand eventually propose a session.
OpenSQLCamp is a fun, equal level event. If you have something to say, write a proposal, and the other participants will tell you if they want to hear it or not. Either way, you will learn something.

First Kosovo Free Software Conference

The first Free Software Conference in Kosovo is being held on August 29-30 in Prishtina, Kosovo. The newborn state, which declared independence in 2008, is growing in several ways. Besides economic and political growth, also technology is expanding. In this fresh environment, free and open source software can have an important role. The conference schedule includes local and international speakers.

I have a strong professional and personal relationship with Kosovo. Before and after the war, from 1998 to 2002, I was part of the OSCE-UN mission that helped the country in its first steps towards independence. And there I found my wife. Thus, it's with great pleasure that I will do a keynote on the theme of Freedom beyond free of charge.
And although the talk won't be about MySQL, it will nonetheless have a role in the story. Some of the freedom that was achieved during the UN Mission in Kosovo days is also due a combination of Linux, FreeBSD, MySQL, and many more free software projects.

MySQL Sandbox and Spider at FrOSCon and OpenSQLCamp

FrOSCon and the OpenSQLCamp are about to start. I am packing for Sankt Augustin, where I will attend the fourth edition of FrOSCon and the second OpenSQLCamp. I will have two sessions, Sharding for the masses, about the Spider storage engine and MySQL Sandbox 3, about one of my favorite tools.

The program is very rich. There will be several tracks in the main event and in the associated conferences. If you have any involvement or simply some curiority in open source matters, You will find something interesting at FrOSCon.

Testing the InnoDB plugin with MySQL snapshots

The cat is out of the bag. MySQL 5.1 will include the InnoDB plugin, and thanks to labs.mysql.com you can try the new version right away. Here is a step-by-step guide to testing the InnoDB plugin with MySQL snapshot 5.1.39 and MySQL Sandbox.

1. Install MySQL::Sandbox

This is a straightforward part. Please refer to the manual for the details.

2. get the binaries

Check the list of available binaries and download the one that matches your architecture and operating system.

3. Install the sandbox

Since we want to use the InnoDB plugin, we need to start the Sandbox with the builtin innodb engine disabled.

make_sandbox \
/path/to/mysql-5.1.39-snapshot20090812-osx10.5-i386.tar.gz \
-c ignore-builtin-innodb

The option passed with "-c" will be written to the options file.
Make sure that the sandbox is installed and the server starts. If it doesn't, check the error log at $HOME/sandboxes/msb_5_1_39/data/msandbox.err and try to figure out what happened.

4. Check the available engines

~/sandboxes/msb_5_1_39/use
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.39-snapshot20090812 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

select engine, support from information_schema.engines;
+------------+---------+
| engine     | support |
+------------+---------+
| MyISAM     | DEFAULT |
| MRG_MYISAM | YES     |
| BLACKHOLE  | YES     |
| CSV        | YES     |
| MEMORY     | YES     |
| FEDERATED  | NO      |
| ARCHIVE    | YES     |
+------------+---------+

As you can see, InnoDB is not in the list.

5. Install the innodb plugin

install plugin innodb soname 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.85 sec)

select @@innodb_version;
+------------------+
| @@innodb_version |
+------------------+
| 1.0.4            |
+------------------+

6. Install the additional INFORMATION SCHEMA tables

INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_LOCK_WAITS SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMP SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMP_RESET SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMPMEM SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

INSTALL PLUGIN INNODB_CMPMEM_RESET SONAME 'ha_innodb_plugin.so';
Query OK, 0 rows affected (0.00 sec)

7. Finally, check the results

select plugin_name, plugin_type, plugin_status from information_schema.plugins;
+---------------------+--------------------+---------------+
| plugin_name         | plugin_type        | plugin_status |
+---------------------+--------------------+---------------+
| binlog              | STORAGE ENGINE     | ACTIVE        |
| partition           | STORAGE ENGINE     | ACTIVE        |
| ARCHIVE             | STORAGE ENGINE     | ACTIVE        |
| BLACKHOLE           | STORAGE ENGINE     | ACTIVE        |
| CSV                 | STORAGE ENGINE     | ACTIVE        |
| FEDERATED           | STORAGE ENGINE     | DISABLED      |
| MEMORY              | STORAGE ENGINE     | ACTIVE        |
| MyISAM              | STORAGE ENGINE     | ACTIVE        |
| MRG_MYISAM          | STORAGE ENGINE     | ACTIVE        |
| InnoDB              | STORAGE ENGINE     | ACTIVE        |
| INNODB_TRX          | INFORMATION SCHEMA | ACTIVE        |
| INNODB_LOCKS        | INFORMATION SCHEMA | ACTIVE        |
| INNODB_LOCK_WAITS   | INFORMATION SCHEMA | ACTIVE        |
| INNODB_CMP          | INFORMATION SCHEMA | ACTIVE        |
| INNODB_CMP_RESET    | INFORMATION SCHEMA | ACTIVE        |
| INNODB_CMPMEM       | INFORMATION SCHEMA | ACTIVE        |
| INNODB_CMPMEM_RESET | INFORMATION SCHEMA | ACTIVE        |
+---------------------+--------------------+---------------+

Now you can read the InnoDB plugin manual and have as much fun as you can.

Beefing up community feeds

If you weren't paying attention, you may have missed the latest news in Planet MySQL. A few months ago, the site has added the ability of voting for posts (in addition to searching and tagging, which make it much more useful than it was before). However, if you, like many, read Planet MySQL using a feed reader, the recent benefits are not easily usable. To allow everyone to vote on the posts they like, even from a feed reader, the feeds now include two links at the end of for each post, to vote on a deserving post even from a feed reader.

The feed may look like this one:

Since I liked this post, I can click on Vote UP, and the link opens in my default browser, showing the result of the voting.

The same happens for the MySQL Librarian feed. At the end of each post, you can click on vote UP or vote DOWN directly from your feed reader.
In both cases, the vote works only if you are already logged in. If you aren't, you will be taken to the login page.
This is not the end of the story. More goodies will come. Stay tuned.

Automating MySQL Librarian tasks

The MySQL Librarian is a collection of community generated content, easy to search and to extend. If you have missed the announcement, you may look at Introducing the MySQL Librarian.. To add a new link, you have several ways. You can just use the Add A Link feature inside the Librarian. If the link to be added comes from Planet MySQL, every post has a link to facilitate the insertion to the Librarian. For everything else, adding a link is a manual task. Until today.

Adding easily to the Librarian

Diego Medina, who should be well known in the community as a very active promoter of MySQL Proxy, and one of the most creative bug finders, has made two additions that have already been incorporated into the Librarian. If you go to the MySQL Librarian now, you will find a "bookmarklet" that you can drag and drop to your browser toolbar (if your browser is either FireFox or Safari, that is). After that, you have a button in your toolbar, which you can use to quickly add a new link.

For example, let's consider an article that we may want to add to the Librarian. Something like MockLoad on Launchpad - MySQL Proxy.

If you have already dragged and dropped the bookmarklet, you can click on your new button, and get to the Librarian page, with almost all the detailed already filled in for you.

Search engines

Diego didn't limit his contribution to adding links. He has also created a new search engine plugin for FireFox, which lets you use the MySQ Librarian as your search engine.

Adding the Librarian engine	Using the Librarian engine

If your default search engine is the Librarian, your search will be sent to MySQL Librarian, as easily as you search in Google or in any other engine.

Thanks, Diego!

OpenSQLCamp democracy

We have seen this before. Actually, we got the idea from Drupal, where talk proposals are public, and the most voted ones get in the schedule. Nonetheless, it's a pleasure to see that a transparent voting system is accepted and used.

The OpenSQLCamp 2009 European edition, is under scrutiny. There are 27 session proposals, from which we will need to get 12 in the schedule.
The open voting is done via Twitter or the mailing list.
I have a good feeling about it. Since I am proposing a public talk, I must be prepared for public scrutiny. It's only fair that my proposal is evaluated by the same audience that will later decide to come see my presentation. It's an incentive to write a clear and compelling proposal. I can't get away with "I am well known. I will say something about replication."
If you are attending OpenSQLCamp at FrOSCon, let us know what you would like to see. Let the best proposals win.

Sharding for the masses: the spider storage engine

In my previous article about the Spider storage engine, I made some tests and I saw that the engine has potential. I did also identify some problems, which were promptly addressed by the author. I have looked at the latest version (0.12), and the results are very much encouraging.

Installing the Spider storage engine is not trivial but not extremely difficult either. My previous article has a step-by-step explanation. Before installing, though, you need to apply one patch to the server, to enable condition pushdown to the partitions engine. What is it about? In short, when you issue a query from the Spider table, with a WHERE clause, the query is passed to a backend database without the WHERE clause. True, it's filtered by partition, but each partition may have a large data set, and this means a full table scan on the backend server. In a few cases, this is not a big deal, because the general performance of the Spider engine is better than a non-partitioned table, but it can be improved.
The lack of conditions pushdown is a problem already known in the Federated engine. With the above patch applied, the Spider engine pushes the condition down to the back end, and the query is executed with the WHERE clause intact.
Let's consider this simple query:

select * from salaries where from_date='1996-01-10' limit 10;

Before applying the patch, the backend database would execute this:

select `emp_no`,`salary`,`from_date`,`to_date`
from `employees`.`salaries` limit 0,9223372036854775807

After the patch, the backend receives and execute the more sensible

select `emp_no`,`salary`,`from_date`,`to_date`
from `employees`.`salaries`
where (`from_date` = '1996-01-10')
limit 0,9223372036854775807

A final observation. While I was testing the latest version, I noticed something that should have been clear from the beginning, i.e. that a spider engine set of backends can have multiple front-ends. Meaning that you can create a Spider table from a different host, and access the same backends that another host is already using. In theory, it means more flexibility when dealing with large amount of data. In practice, we'll have to test it more and see what happens.