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.