MariaDB’s Sequence Storage Engine

Versione italiana

MariaDB 10.0.3 introduces a new Storage Engine: Sequence. It isn’t in MySQL or in older MariaDBs versions. Sequence is a special engine, which does not create or read any table. It only generates on the fly a sequence of integers which is returned to your query, and then dies. The sequence’s bounds and increment depend from the table name.

Very quick start

To install:

INSTALL PLUGIN sequence SONAME 'ha_sequence';

NOTE: On MariaDB (unlike Oracle MySQL) there is no need to add ‘.dll’ to the command on Windows, so the command is platform-independent. Thanks Vladislav Vaintroub for this information!

To create a sequence, you must first select a default database, using the USE command. Then issue this query:

SELECT * FROM seq_1_to_100;

To create a sequence specifying an increment different from 1:

SELECT * FROM seq_1_to_100_step_2;

A sequence can’t involve negative numbers, but can be descending:

SELECT * FROM seq_10_to_1;

Please, note that descending sequences always generate the same number as ascending sequence, in reverse order. This means that the highest number of a descending sequence sometimes is not the one you typed:

MariaDB [test]> SELECT * FROM seq_8_to_1_step_3;
+-----+
| seq |
+-----+
|   7 |
|   4 |
|   1 |
+-----+
3 rows in set (0.00 sec)

What sequence is not

Sequence Storage Engine is not an SQL SEQUENCE like the ones you can use in PostgreSQL or IBM, or FirebirdSQL’s generators. This means that a sequence exists only during the execution of a query. You cannot use a value from a sequence in a query and then use the next value in another query.

Also, sequence can’t generate negative numbers and can’t rotate when it reaches the maximum/minimum value (like they do in PostgreSQL).

Some sequence use cases

Other people don’t believe it, but developers have a lot of fantasy. It is needed to solve problems when there isn’t an obvious solution, and can’t find an algorithm designed by someone else. So, I’m sure that creative uses of the sequence Storage Engine will be found in the future. But for now, I’d like to share some ideas.

Almost all the following techniques can be used to populate tables with generated data. This can be useful, because when it can be done using some queries, you avoid network communications – which can be slow or error prone.

Find holes in a column

Sometimes a column should contain a sequence of integer values and holes shouldn’t be there. A common example is a column used to sort some elements in a web page: while holes shouldn’t be a problem for queries, they can make re-sorting operations buggy. In the following example we’ll create a table with a lot of holes, and then we’ll list the missing values using a sequence:

CREATE TABLE t1 (c TINYINT UNSIGNED) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1), (2), (3), (5), (10);
SELECT s.seq FROM seq_1_to_10 s LEFT JOIN t1 t ON s.seq = t.c WHERE t.c IS NULL;

Build a combination of numbers

SELECT s1.seq, s2.seq FROM seq_1_to_3 s1 JOIN seq_1_to_3 s2 ORDER BY 1, 2;

But you can also combinate numbers and characters (see below).

Find multiples of 3, minor than 100

SELECT seq FROM seq_3_to_100_step_3;

Find multiples of both 3 and 5

SELECT s1.seq FROM seq_5_to_100_step_5 s1 INNER JOIN seq_3_to_100_step_3 s2 ON s1.seq = s2.seq;

Optimized: find multiples of (2 or one of its powers)

SELECT seq FROM seq_1_to_100 WHERE NOT seq & 1; -- ...of 2
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 3; -- ...of 4
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 7; -- ...of 8
SELECT seq FROM seq_1_to_100 WHERE NOT seq & 15; -- ...of 16
...

Build a sequence of 1-char strings

The following query includes lowercase chars, uppercase chars and digits. But I’m sure that anyone can modify it to exlude one of these groups, or include more groups (for example, accented vowels).

-- Sequence of 1-char strings
SELECT CHAR(seq) AS ch
    FROM (
                -- lowercase
                (SELECT seq FROM seq_97_to_122 l)
            UNION
                -- uppercase
                (SELECT seq FROM seq_65_to_90 u)
            UNION
                -- digits
                (SELECT seq FROM seq_48_to_57 d)
        ) ch;

Build a sequence of 2-char strings

Ouch… this UNION is tricky. But someone could still use it.

SELECT CONCAT(ch1.ch1, ch2.ch2) AS ch
    FROM (
        (SELECT CHAR(seq) AS ch1
            FROM (
                        -- lowercase
                        (SELECT seq FROM seq_97_to_122 l1)
                    UNION
                        -- uppercase
                        (SELECT seq FROM seq_65_to_90 u1)
                    UNION
                        -- digits
                        (SELECT seq FROM seq_48_to_57 d1)
                ) s1
        )
    ) ch1
    CROSS JOIN (
        (SELECT CHAR(seq) AS ch2
            FROM (
                        -- lowercase
                        (SELECT seq FROM seq_97_to_122 l2)
                    UNION
                        -- uppercase
                        (SELECT seq FROM seq_65_to_90 u2)
                    UNION
                        -- digits
                        (SELECT seq FROM seq_48_to_57 d2)
                ) s2
        )
    ) ch2
    ORDER BY ch1, ch2;

Sorry for the aliases confusion (particularly ch1 and ch2). I ran out of fantasy before finishing the query.

Sequence of dates

This is interesting, in my opinion. Many booking applications, during the installation, populate a table with days or hours when a reservation could start. This example shows a simple way to fill a table with all days in January:

SELECT DATE ('2014.01.01' + INTERVAL (s.seq - 1) DAY) AS d
    FROM (SELECT seq FROM seq_1_to_30) s;

Sequence of hours, halfes of an hour, etc

-- Hours in a day
SELECT CAST('00:00:00' AS TIME) + INTERVAL (s.seq - 1) HOUR AS t
    FROM (SELECT seq FROM seq_1_to_24) s;
-- Halfes of an hour in a day
SELECT CAST('00:00:00' AS TIME) + INTERVAL (30 * s.seq) MINUTE AS t
    FROM (SELECT seq FROM seq_1_to_48) s;

Working days

The last two examples are not so useful, alone. Usually you don't need to fill a table with all days in a month/year, or all hours in a day. Instead, you only need to write working days and working hours. To find working days, you can use DAYOFWEEK() function to exclude saturdays and sundays. Of course this doesn't automatically exclude christmas or other holidays - but I think that the best solution is deleting them after filling the table.

SELECT DATE ('2014-01-01' + INTERVAL (s.seq - 1) DAY) AS d
    FROM (SELECT seq FROM seq_1_to_30) s
    -- exclude sunday (1) and saturday (7)
    WHERE DAYOFWEEK(DATE ('2014-01-01' + INTERVAL (s.seq - 1) DAY)) BETWEEN 2 AND 6;

Just a note on implementation. Why do I start dates from day 1 and then I perform a - 1, rather than directly starting from day 0? For two reasons. First, in Setting a strict SQL_MODE article, I explain that the NO_ZERO_DATE and NO_ZERO_IN_DATE flags in SQL_MODE should be set. Second: '2014-01-00' + INTERVAL 1 DAY returns NULL, and it is the correct behavior, not a bug. If you don't understand the reason (but also if you understand it), please set those flags. Permissive SQL_MODEs pretend to be your friends, but one of these days they will kill you!

Working hours

SELECT {dt '2013-01-01 01:00:00'} + INTERVAL (wd.d - 1) DAY + INTERVAL (dh.h - 1) HOUR AS wh
    FROM
        (
            -- working days in a month
            SELECT seq AS d FROM seq_1_to_30
        ) wd
    CROSS JOIN
        (
            -- daily working hours
            (SELECT seq AS h FROM seq_9_to_12)
            UNION
            (SELECT seq AS h FROM seq_14_to_17)
        ) dh
    ORDER BY 1;

Here we have one subquery returning the working days, JOINed with a subquery returning working hours. Note that working hours have a hole (workers lunch), so the second subquery is a UNION of 2 sequences.

And... yes, carthesian product is evil, but here it's exactly what we need. The CROSS keyword makes clearer that if you modify the query and make a mistake, you could create a SELECT returning a big number of rows.

To-Do and suggestions

In a future article, I will show a Stored Function which dynamicall composes a query to populate a table with DATETIME values, in a given time range, with a given granularity.

Do you think that one of these query is not well-optimized? Do you have other interesting ideas which involve the sequence Storage Engine? Please, comment this article.

Enjoy!

Advertisements

MariaDB/MySQL Cursors: a brief Tutorial

Versione italiana

In MariaDB and MySQL, Cursors can only be used within a Stored Program, are slow, and have very limited functionalities. That said, they can still be useful in some cases. This page explains how to use them in action, with a trivial example.

Here is our example Stored Procedure which uses a Cursor:

DELIMITER ||
    
DROP TABLE IF EXISTS `test`.`tab1`;
CREATE TABLE `test`.`tab1` (`c` TINYINT UNSIGNED);
INSERT INTO `test`.`tab1` (`c`) VALUES
    (1),
    (2),
    (3);
    
DROP PROCEDURE IF EXISTS `test`.`demo`;
CREATE PROCEDURE `test`.`demo`()
    READS SQL DATA
BEGIN
    DECLARE `eof` BOOL;
    DECLARE `val` INT;
    
    DECLARE `crs` CURSOR FOR
        SELECT `c`
            FROM `test`.`tab1`;
    
    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
        SET eof = TRUE;
    
    OPEN `crs`;
    `lp`: LOOP
        FETCH `crs` INTO `val`;
        IF `eof` IS TRUE THEN
            LEAVE `lp`;
        END IF;
        
        SELECT `val`;
    END LOOP;
    CLOSE `crs`;
    
    -- workaround for server bug
    DO (SELECT `c` FROM `test`.`tab1` WHERE FALSE);
END;
    
||
DELIMITER ;


This Procedure is totally useless in the Real World, but serves us as an example: it returns a different ResultSet for each row in test.tab1 table. Now, I’m going to explain how I implemented it.

First, I DECLARE some variables. eof is very important, becuase it indicates if the Cursor has read all rows from its query; for now, it’s FALSE. We’ll use it later. BOOL doesn’t really exist in Maria and My, and is mapper to TINYINT, while TRUE is just a synonym for 1, and FALSE is a synonym for 0.But using the BOOL, TRUE and FALSE keywords makes code more readable. val is the variable which contains the value that we’re going to read from the table.

Then, I DECLARE the Cursor. In this statement there are the Cursor’s name (crs) and its query. The query must be hardcoded here, because dynamic SQL is not allowed for Cursors. However, there is a workaround for this: see my post Cursors for Dynamic SQL.

Then, I declare an Error HANDLER for the Errors which are in the NOT FOUND class. Here we are interested in the 1329 error, which occurs when the Cursor has read all the rows from the query, and tries to read another row. I’ll explain this better later.

Now I OPEN the Cursor. Its query is executed at this point.

And then, there is a LOOP on the rows. What happens here? Well, first we try to FETCH a new row. If this operation succedes the value of the only field we’re SELECTing is copied into val. We check the value of eof, and since it is FALSE, we skip the IF part. And then we produce a ResultSet with the value we have read, and it is sent to the client. This happens three times, because we created a table with three rows.

The fourth time, the FETCH statement fails, because there are no more rows. An Error raises, so we go into the HANDLER which we defined before. Here, the eof variable is set to TRUE. Since it is a CONTINUE HANDLER, after that statement the Routine execution is resumed and finds again the IF. This time eof is TRUE, so we step into the IF. And we find a LEAVE statement which refers to the lp label. lp is the LOOP, so we step out of the LOOP.

Then, we CLOSE the Cursor.

Now, we have a little problem. Versions of MySQL older than 5.6, and MariaDB, have a bug (which MariaDB is solving, however) that makes the 1329 Error survive after the HANDLER. If we don’t do something to remove that error, it will be sent to the client. But if we run a query against a physical table, the Error is forgotten (the exact reason is beyond the purpose of this post). We don’t want another ResultSet to be returned, so we use the SELECT as a subquery of the DO statement (which doesn’t return anything). WHERE FALSE is used to make sure that we only add an acceptable overhead to the Procedure.

Exercises

This time I’ll give you some simple excercises. If you solve them, you can post your code in a comment, if you want. If you can’t solve them, you can post some questions so I’ll help you. I suggest you try these excercises, because you don’t really learn much without practice.

1) Modify the demo Procedure so that it terminates if it finds more than one NULL value.

2) Write a new Procedure which reads rows from the PROCESSLIST table, in the information_schema database, and KILLs all connections that are idle and exist since more than N seconds – where N is a Procedure’s parameter.

Enjoy!

MariaDB/MySQL: “Commands out of sync” error (2014)

Versione italiana

If you execute mysqli_multi() and later you try to execute mysqli_query() or mysqli_result(), you may get the infamous error 2014: Commands out of sync; you can't run this command now (SQLSTATE: HY000). This happens because you didn’t free all resultsets before executing another query. But probably, you didn’t even know that any resultset exists, because you executed statements like INSERT, DELETE, or DDL.

Well, here is a function which frees all resultsets, and an usage example:

<?php
    
/**
 *	Free all resultsets from $dbCon.
 *	@param		mysqli		$dbCon	mysqli object.
 *	@return		void
 */
function free_all_results(mysqli $dbCon)
{
    do {
        if ($res = $dbCon->store_result()) {
            $res->fetch_all(MYSQLI_ASSOC);
            $res->free();
        }
    } while ($dbCon->more_results() && $dbCon->next_result());
}
    
$db = new mysqli('...', '...', '...', '...');
    
$sql = <<<SQL
    DROP TABLE IF EXISTS `test`.`tab1`;
    CREATE TABLE `test`.`tab1` (`col` INT);
    INSERT INTO `test`.`tab1` VALUE (1);
SQL;
$db->multi_query($sql);
    
free_all_results($db);
    
$sql = 'SELECT * FROM `test`.`tab1`;';
$res = $db->query($sql);
    
if ($db->sqlstate !== '00000') {
    echo 'SQLSTATE: ' . $db->sqlstate . '; Error: ' . $db->errno . ' - ' . $db->error;
} else {
    echo $res->num_rows;
}
    
?>

If you comment the free_all_results($db); line, you will see the error.

Enjoy!