I had some conversations with a few people at Oracle Open World about features that would be beneficial in InnoDB. They encouraged me to blog my thoughts, so I am.

Someday I’d like to have a clear mental list of features I want in MySQL in general, but that is a much harder list to organize and prioritize. InnoDB is an easier place to get started.

First, I’d like truly online, nonblocking DDL. I see two ways this could work: reading the old version of rows and writing the new version, or doing a shadow-copy and building the new table in the background. The first way has the advantage of being lazy, so the schema change is instantaneous, and you really never pay any additional cost. However, it has the disadvantage that it might be hard to implement multiple schema changes if a previous change isn’t fully finished, so to speak (I can see a lot of bugs if there are more than 2 versions of the schema at a time). This is a …

SkySQL today announced that it will begin reselling Severalnines' ClusterControl™ product, which enables customers to set-up a production-ready, MySQL® Cluster configuration. The latest addition to the SkySQL™ Reference Architecture - SkySQL's framework for reducing the cost and complexity of deploying some of the most common MySQL data infrastructure applications - ClusterControl™ provides a virtual DBA assistant that frees the developer from the complexity and typical learning curves associated with database clusters, and provides assistance throughout the database lifecycle.

Kaj Arnö, EVP of products at SkySQL, and Vinay Joosery, CEO of Severalnines, will give an overview of the ClusterControl™ solution in a live webinar, on November 8th at 4PM CET/10AM ET. Viewers can register for the webinar here.

To learn more about this partnership, read …

Just a heads-up that today, October 12, is my last official working day at Monty Program. The change is bittersweet. Bitter because I will very much miss working with a very talented team. Sweet because I leave with a real sense of accomplishment. Monty hired me 2.5 years ago to drive awareness of MariaDB and to start building a viable community around it. I think these goals have been met to a large degree.

While I have been using vacation days recently, I did follow the announcements by Oracle regarding new closed, commercial extensions. Almost universally in these discussions MariaDB is put forth as a fully Free, drop-in …

In response to http://forge.mysql.com/snippets/view.php?id=60 - adding support to count strings, not just characters

(
  character_length(<string1>)
  - character_length(REPLACE(<string1>, <searchString>, '')
) / character_length(<searchString>)

Finds the date for Easter (Western) given a year.
Sample usage: SELECT easter(YEAR(NOW()));

DELIMITER //
CREATE FUNCTION easter(inYear YEAR) RETURNS DATE DETERMINISTIC
BEGIN
    DECLARE a, b, c, d, e, k, m, n, p, q INT;

    DECLARE easter DATE;

    SET k = FLOOR(inYear / 100);
    SET a = MOD(inYear, 19);
    SET b = MOD(inYear, 4);
    SET c = MOD(inYear, 7);
    SET q = FLOOR(k / 4);
    SET p = FLOOR((13 + 8 * k) / 25);
    SET m = MOD((15-p+k-q), 30);
    SET d = MOD((19 * a + m), 30);
    SET n = MOD((4+k-q), 7);
    SET e = MOD((2*b+4*c+6*d+n), 7);

    SET easter = CASE
        WHEN d + e <= 9 THEN CONCAT_WS('-', inYear, '03', 22 + d + e)
        WHEN d = 29 AND e = 6 THEN CONCAT_WS('-', inYear, '04-19')
        WHEN d = 28 AND e = 6 AND a > 10 THEN CONCAT_WS('-', inYear, '04-18')
        ELSE CONCAT_WS('-', inYear, '04', LPAD(d + e - 9, 2, 0))
    END;

    RETURN easter;
END
//
DELIMITER ;

A stored procedure to show all grants in the database.

USE mysql;

DELIMITER //
CREATE PROCEDURE showAllGrants() BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE theUser CHAR(16);
    DECLARE theHost CHAR(60);
    DECLARE cur1 CURSOR FOR SELECT user, host FROM mysql.user;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur1;

    REPEAT
        FETCH cur1 INTO theUser, theHost;
        IF NOT done THEN
            SET @sql := CONCAT('SHOW GRANTS FOR ', QUOTE(theUser), '@', QUOTE(theHost));
            PREPARE grantStatement FROM @sql;
            EXECUTE grantStatement;
            DROP PREPARE grantStatement;
        END IF;
    UNTIL done END REPEAT;

    CLOSE cur1;
END//
DELIMITER ;

CALL showAllGrants();

Convert an integer in the range 0 to 3999 to Roman numerals.

CREATE FUNCTION `toRoman`(inArabic int unsigned) RETURNS varchar(15) CHARSET latin1 DETERMINISTIC
BEGIN
    DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM';

    DECLARE stringInUse CHAR(3);
    DECLARE position tinyint DEFAULT 1;
    DECLARE currentDigit tinyint;

    DECLARE returnValue VARCHAR(15) DEFAULT '';

    IF(inArabic > 3999) THEN RETURN 'overflow'; END IF;
    IF(inArabic = 0) THEN RETURN 'N'; END IF;

    WHILE position <= CEIL(LOG10(inArabic + .1)) DO
        SET currentDigit := MOD(FLOOR(inArabic / POW(10, position - 1)), 10);

        SET returnValue := CONCAT(
            CASE currentDigit
                WHEN 4 THEN CONCAT(SUBSTRING(numeral, position * 2 - 1, 1), SUBSTRING(numeral, position * 2, 1))
                WHEN 9 THEN CONCAT(SUBSTRING(numeral, position * 2 - 1, 1), SUBSTRING(numeral, position * 2 + 1, 1))
                ELSE CONCAT(
                    REPEAT(SUBSTRING(numeral, …

SHOW CREATE TRIGGER for 5.0 - 5.1.20

CREATE FUNCTION SHOW_CREATE_TRIGGER(in_trigger varchar(255))
RETURNS text READS SQL DATA
BEGIN
    DECLARE returnText text;
    SELECT CONCAT_WS(
        " ",
        "CREATE TRIGGER",
        TRIGGER_NAME,
        ACTION_TIMING,
        EVENT_MANIPULATION,
        "ON",
        EVENT_OBJECT_TABLE,
        "FOR EACH ROW",
        ACTION_STATEMENT) into returnText
    FROM information_schema.triggers;
RETURN returnText;
END

Translate from Roman Numerals back to decimal. Legal range is 0 to 3999 (that is, N to MMMCMXCIX) You can use IV or IIII for 4. You could even use a string of twenty I's for 20, but there's a string limit of 15, since that's the length of the biggest well-formed numeral below 3999.

CREATE FUNCTION fromRoman (inRoman varchar(15)) RETURNS integer DETERMINISTIC
BEGIN

    DECLARE numeral CHAR(7) DEFAULT 'IVXLCDM';

    DECLARE digit TINYINT;
    DECLARE previous INT DEFAULT 0;
    DECLARE current INT;
    DECLARE sum INT DEFAULT 0;

    SET inRoman = UPPER(inRoman);

    WHILE LENGTH(inRoman) > 0 DO
        SET digit := LOCATE(RIGHT(inRoman, 1), numeral) - 1;
        SET current := POW(10, FLOOR(digit / 2)) * POW(5, MOD(digit, 2));
        SET sum := sum + POW(-1, current < previous) * current;
        SET previous := current;
        SET inRoman = LEFT(inRoman, LENGTH(inRoman) - 1);
    END WHILE;

    RETURN sum;
END

Given a source string and a two-character range string, extracts all characters from the source that fall within the range. Sort of a simple version of the [A-Z] syntax from regular expressions.

Examples:
extractRange('123-ABC-456', 'AZ') returns 'ABC'
extractRange('123-ABC-456', '09') returns '123456'

CREATE FUNCTION extractRange(inString TEXT, inRange char(2))
RETURNS TEXT
DETERMINISTIC
BEGIN
DECLARE returnString TEXT DEFAULT '';
DECLARE pointer INT DEFAULT 1;
WHILE pointer <= CHAR_LENGTH(inString) DO
    IF MID(inString, pointer, 1) BETWEEN LEFT(inRange, 1) AND RIGHT(inRange, 1) THEN
        SET returnString := CONCAT(returnString, MID(inString, pointer, 1));
    END IF;
    SET pointer := pointer + 1;
END WHILE;
return returnString;
END