MySQL Stored routines at the Users Conference

The Users Conference and Expo is approaching fast. As a last minute assignment, I will be speaking about Stored routines for MySQL administration. The session will cover the basics of stored routines and their specifics for administration.

Coincidentally, today was published a review of an excellent book about MySQL Stored Procedures programming by Guy Harrison and Steven Feuerstein. I warmly recommend this book to anyone using stored routines with MySQL.

Mentoring a Summer of Code project

It's official.
I am now appointed mentor of a Google Summer of Code project.
Congratulations to Charlie Cahoon, who has submitted an intriguing proposal for improving our code coverage and testing tools. The abstract doesn't do justice to the project. The juicy part is in the details. More about it later.
More information on Kaj's announcement.

Thanks, Charlie, for proposing this project!

Thanks, Google, for promoting this great infrastructure!

Handling multiple data sets in stored procedures from Perl

MySQL stored procedures can use SELECT statements in their body, returning a data set to the caller. The manual also explains that multiple data sets can be returned from such a stored procedure.
For example, assume you have this stored procedure:

CREATE PROCEDURE p1(x INT, y INT)
    DETERMINISTIC
    BEGIN
        SELECT 
            x ;
        SELECT 
            x AS first_param, 
            y AS second_param;
        SELECT 
            x, 
            y, 
            x + y AS sum_xy, 
            x * y AS prod_xy; 
        SELECT *  FROM t1;
    END

There are four SELECT statements, with different headers, and a variable number of rows returned.
Early versions of DBD::mysql could not handle multiple data sets. Actually, prior to version 3.0004 they could not even handle one dataset from a stored procedure. Starting with version 4.001, single and multiple datasets from SPs are handled gracefully.
The trick is to call $sth->more_results after retrieving each dataset.
See the complete example at Multiple data sets in MySQL stored procedures


For those interested, the MySQL Users Conference and Expo includes a BoF about DBD::mysql on April 24^th at 7:30pm.

Logs on demand, a DBA's prayer come true

Several months ago I wrote about logs on demand in MySQL 5.1. Now that 5.1 is approaching its maturity stage, I am happy to report that this feature has proven itself very handy and useful. Petr Chardin will talk about this very feature at the MySQL Conference and Expo.

While reviewing the material for this session, I remembered a usability report that I wrote when the feature was announced. Among other things I wrote that you can create ad hoc log files for separate needs, but you can't do the same thing for tables. This reminds me of a general rule of technology: "If a respected scientist says a thing is possible, you can be almost certain he's right. If an established, respected scientist tells you that something is impossible, he's almost certainly wrong." I was wrong. It can be done. To know how, attend the aforementioned session, or wait until I post an a follow-up to this post after the conference.

When Community strikes

A few days ago a bug report was filed to the MySQL bugs system. For a few hours it was just one among the many, although it looked hard to reproduce.
Then, suddenly, two experienced contributors, Martin Friebe and Alexander Fomichev, joined the discussion, offering similar cases, explanations, a workaround, and even a patch.

The public intervention took place within 11 hours after the MySQL Engineer asked for clarifications, and within one day the solution was served!

Thanks guys!
We love it when bugs are solved this way.