FOREACH in MySQL/MariaDB stored procedures

One of the annoying limitations of MySQL/MariaDB stored procedures is the lack of a FOREACH construct, which loops on each row returned by a query.

In practice, this forces users to write a lot of code just to tell MySQL how to fetch rows and exit properly. Nesting 2 loops of this kind simply results in unmaintenable code (don’t trust me, just try).

Now, I’m writing a library of views and procedures that I’ll share as open source in the next days, and I decided to finally write my foreach. Well, sort of. It is impossible to use the current stored procedures language to write a flexible foreach, because to loop rows you need a cursor. And cursors are based on a hard-coded query. In this old post I proposed a verbose, ugly, but working solution, but it has a limitation: the number of columns returned by the query must still be fixed. So, I used that technique (based on views) in my procedure, but I had to write different procedures: foreach_1(), foreach_2(), foreach_3(). If you need to read more rows you can modify the code easily, but I think that for an open source library 3 columns is reasonable.

Here I decided to share an experimental prototype. If you have a better idea on how to achieve a similar result, I’ll be glad to trash this hack and use your idea instead.

Also note that for this procedure I used MariaDB 10.2 improved PREPARE statement. If you want to run it on MySQL or older MariaDB versions, you’ll have to make some easy changes and test the procedure.

The code

CREATE PROCEDURE foreach_2(IN in_sql TEXT, IN in_callback_type VARCHAR(9), IN in_callback_body TEXT)
    NOT DETERMINISTIC
    READS SQL DATA
BEGIN
    DECLARE p_sql TEXT DEFAULT NULL;
    DECLARE p_eof BOOL DEFAULT FALSE;
    DECLARE v_p1, v_p2 TEXT DEFAULT NULL;
 
    DECLARE crs_foreach CURSOR FOR
    SELECT p1, p2 FROM vw_foreach;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND
    BEGIN
        SET p_eof := TRUE;
    END;

    SET in_callback_type := UPPER(in_callback_type);
     IF NOT (in_callback_type IN ('SQL', 'PROCEDURE')) THEN
         SIGNAL SQLSTATE VALUE '45000'
        SET MESSAGE_TEXT = '[foreach_2] Invalid in_callback_type';
    END IF;

    DO GET_LOCK('crs_foreach', 1);

    SET p_sql := CONCAT('CREATE OR REPLACE VIEW vw_foreach AS ', in_sql, ';');
    PREPARE stmt_foreach_2 FROM p_sql;
    EXECUTE stmt_foreach_2;

    OPEN crs_foreach;

    DO RELEASE_LOCK('crs_foreach');
 
    lp_while: WHILE TRUE DO
        FETCH crs_foreach INTO v_p1, v_p2;
 
        IF p_eof THEN
            LEAVE lp_while;
        END IF;

        IF in_callback_type IN ('SQL') THEN
            SET @p1 := v_p1;
            SET @p2 := v_p2;
            PREPARE stmt_foreach_2 FROM in_callback_body;
        ELSE
            PREPARE stmt_foreach_2 FROM CONCAT('CALL `', in_callback_body, '`(', QUOTE(v_p1), ', ', QUOTE(v_p2), ');');
        END IF;
        EXECUTE stmt_foreach_2;
    END WHILE;

    CLOSE crs_foreach;
    DEALLOCATE PREPARE stmt_foreach_2;
END

Now, the usage. There are 2 ways to use this procedure.

Using a callback procedure

First, create a callback procedure which will handle the rows returned by your query:

CREATE PROCEDURE p_echo(IN in_p1 TEXT, IN in_p2 TEXT)
BEGIN
    SELECT CONCAT_WS('.', in_p1, in_p2) AS r;
END

As you can see, the procedure must accept 2 values (foreach_2). The names don’t really matter, but p1 and p2 are clear, at least in this example.

Now, invoke foreach_2():

CALL foreach_2(
    'SELECT TABLE_SCHEMA AS p1, TABLE_NAME AS p2 FROM information_schema.TABLES LIMIT 3;',
    'PROCEDURE',
    'p_echo'
);

We passed the query producing the results we want to loop. Then we told foeach_2() that we want to use a callback procedure, and specified its name.

Using a callback SQL statement

Writing a procedure to perform a simple tasks would be annoying. That’s why I decided to also support callback statements:

CALL foreach_2(
    'SELECT TABLE_SCHEMA AS p1, TABLE_NAME AS p2 FROM information_schema.TABLES LIMIT 3;',
    'SQL',
    'SELECT @p1 AS p1, @p2 AS p2;'
);

As you can see, the callback query can access the values using user variables: @p1 and @p2.

These variables exist at session level, so it is possible that we are overwriting something. But I think there are no reasonable ways to avoid these collisions.

Enjoy!

Advertisements

Prepared Statements in MariaDB 10.2

Prepared statements are particularly useful in stored procedures. In fact, they are the only way to execute dynamic SQL, which means that, for example, the list of ORDER BY columns is in a variable. You can do this by composing an SQL string, as you do in other languages.

However, in MySQL and up to MariaDB 10.1, there are some annoying limitations:

  • Prepared statements exist at a connection level. Even if you declare them in a stored procedure, they are global. If you are writing a stored procedure which should be usable in many contexts (maybe it’s part of an open source library), there is a risk that you overwrite existing prepared statements. There is no way to get a list of existing prepared statements.
  • A prepared statement is prepared from a literal string or a user variable. A literal string cannot contain variables. A user variable exists at a session level, so again, there is a risk to overwrite something. Especially if you always call the variable @sql, because a lot of people tends to do so.
  • Prepared statements are verbose. You need to run 3 different statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) just to execute a dynamic SQL statement.

First, let me say that the first problem is not solved. I hope it will be solved in a future version, so it will be easier to distribute stored procedures libraries. After all, MariaDB 10.3 will have several improvements to stored procedures.

The second problem is solved in 10.2. You can pass any SQL expression (or, at least, all expressions I tested) to PREPARE. Including a local variable, which means that there will be no conflicts with variables declared by the user. For example:

DECLARE v_query TEXT DEFAULT 'SELECT COUNT(DISTINCT user) FROM mysql.user';
PREPARE stmt FROM v_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

But this example is verbose. It demonstrates what I stated above: you need at least 3 statements just to execute a dynamic query. But MariaDB 10.2 solves this problem introducing EXECUTE IMMEDIATE, which is basically a shortcut:

DECLARE v_query TEXT DEFAULT 'SELECT COUNT(DISTINCT user) FROM mysql.user';
EXECUTE IMMEDIATE v_query;

As you can see, we don’t need PREPARE or DEALLOCATE PREPARE if we use EXECUTE IMMEDIATE. This is nice to have in stored procedures, but is not useful outside of procedures, because the prepared statement is always reprepared, even if we executed it before.

Enjoy!

 

MySQL/MariaDB cursors and temp tables

In MariaDB and MySQL, cursors create a temporary table.

Does this statement deserve a whole blog post? Apparently not. However, in some cases one does not expect a temporary table to be created:

  • SELECT ... FOR UPDATE: An exclusive lock is created, yes, but you still read data from a temporary table.
  • SELECT FROM a temporary table: you are reading from a temporary tables, yes, but an internal temporary table is created anyway.
  • Impossible WHERE and LIMIT 0.

A quick example:

CREATE TEMPORARY TABLE t
(
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
);

CREATE PROCEDURE p()
BEGIN
        DECLARE c CURSOR FOR
                SELECT id FROM t WHERE 0 LIMIT 0 FOR UPDATE;
        OPEN c;
        CLOSE c;
END;

MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 31    |
+--------------------+-------+
1 row in set (0.00 sec)

MySQL [test]> CALL p();
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> SHOW STATUS LIKE 'Created_tmp_tables';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 32    |
+--------------------+-------+
1 row in set (0.00 sec)

I am not complaining, and I don’t even know if this behavior can be changed. But one should certainly be aware of this behavior. For example, one could think that creating a temporary table one time and then loop on that table with cursors several times is an optimization – but that’s not the case.

Federico

Reusing Prepared Statements in MariaDB 10.1

I may be wrong, but I think that MariaDB has a strange characteristic: it has many good features, some of which were not implemented intentionally. Well, this sentence is weird too, I know. But I have some examples: I’m not sure that the SEQUENCE engine was designed to generate a sequence of dates and/or times; but it can. And I believe that the CONNECT author had no idea that someone would have used his engine to create cursors for CALL statements; but I do.

Now I have a new example. MariaDB 10.1 supports compound statements out of stored procedures. Which means that you can write IF or WHILE in your install.sql files to create your databases in a dynamic way. This is done via the BEGIN NOT ATOMIC construct.

I played with this feature, like I usually do with new features. And what I’ve found out is amazing for me: BEGIN NOT ATOMIC allows us to nest prepared statements!

Uh, wait… maybe what I’ve just written sounds weird to you. Maybe you’re thinking: “prepared statements can be nested since they were first implemented!”. Which is only true in the documentation. The docs doesn’t lie of course, but it doesn’t work out there, in the real world’s complexity. PREPARE, EXECUTE and DEALLOCATE PREPARE statements cannot be prepared, and this limitation can be very frustrating if you try to write a reusable (perhaps public) stored procedure library.

I tried to explain the reason in this post, but it was becoming way too long, so I had to delete that boring explanation. I’ll just mention an example. You can write a procedure that prepares and executes a statement; but the prepared statement cannot call the procedure itself, recursively. Why? Because you cannot reuse its name, and annot dynamically generate a name for the new prepared statement. If this explanation is too short, just code and you’ll find out.

How can BEGIN NOT ATOMIC possibly fix this problem? Well, for a reason that’s far beyond me, the following blocks can be prepared and executed:

  • BEGIN NOT ATOMIC PREPARE ... ; END;
  • BEGIN NOT ATOMIC EXECUTE ... ; END;
  • BEGIN NOT ATOMIC DEALLOCATE PREPARE ... ; END;

Now you may be thinking that this feature is totally useless. But it isn’t. Thanks to this change, I’ve written a procedure that:

  • Executes a specified SQL string.
  • Autogenerates a “free” name for that statement, or uses an id passed by the user.
  • Returns the autogenerated id, so you can reuse it, or deallocate the statement.

Writing this procedure has been a bit annoying, because after all it uses a dirty trick. But now the procedure is written, and the dirty trick is encapsulated in it. You can use it as if it was a native feature, and forget the trick. Here’s the code:

CREATE DATABASE IF NOT EXISTS _;

CREATE TABLE IF NOT EXISTS _.prepared_statement
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
)
    ENGINE = MEMORY
;

CREATE PROCEDURE _.exec(IN p_sql TEXT, INOUT p_stmt_id INTEGER UNSIGNED)
BEGIN
    IF p_stmt_id IS NULL THEN
        INSERT INTO _.prepared_statement (id) VALUES (DEFAULT);
        SET p_stmt_id := LAST_INSERT_ID();
    END IF;
    
    SET @_SQL_exec := CONCAT(
        'BEGIN NOT ATOMIC PREPARE stmt_dyn_', p_stmt_id, ' '
        , 'FROM ', QUOTE(p_sql), IF(RIGHT(p_sql, 1) = ';', ' ', '; ')
        , 'END;'
    );
    PREPARE _stmt_exec FROM @_SQL_exec;
    EXECUTE _stmt_exec;
    
    SET @_SQL_exec := CONCAT(
        'BEGIN NOT ATOMIC EXECUTE stmt_dyn_', p_stmt_id, '; END;'
    );
    PREPARE _stmt_exec FROM @_SQL_exec;
    EXECUTE _stmt_exec;
    DEALLOCATE PREPARE _stmt_exec;
    SET @_SQL_exec := NULL;
END;

How do I use it? Very simple:

MariaDB [_]> -- redundant: @id is not set, so it's NULL
MariaDB [_]> SET @id := NULL;
Query OK, 0 rows affected (0.00 sec)
MariaDB [_]> CALL _.exec('SELECT 42 AS answer', @id);
+--------+
| answer |
+--------+
| 42 |
+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
MariaDB [_]> -- reuse @id
MariaDB [_]> CALL _.exec('SHOW SCHEMAS LIKE \'info%\'', @id);
+--------------------+
| Database (info%) |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

I am writing a general purpose stored procedure library, that should make stored procedures developing more friendly. It will include this procedure, as well as a procedure for deallocating a specified statement, or all statements you prepared. As soon as the code is interesting and tested, I’ll make it public.

Enjoy!
Federico

MariaDB and Stored Procedures: errata & repository

In the article How MariaDB makes Stored Procedures usable I explained how to use the MariaDB CONNECT Storage Engine to workaround Stored Procedures limitations. Please read that article, if you didn’t, for details.

Since the technique to workaround such limitations is verbose and error-proof, I also proposed a procedure which makes it easy and convenient. Even thought this is a simple country blog, I have immediately received positive feedback after that article. But then, people started to write me: “Hey, doesn’t work!”. The reason is… MariaDB/MySQL bugs. While investigating these problems, I was able to report the following bugs:

I’m sure there are others, but isolating such bugs to produce decent test cases can be difficult. I will do, if I can.

Basically, while developing the procedures I needed, was that some combinations of (correct) statements could misteryously trigger bugs that are not triggered by other combinations of statements. Despite those procedures being trivial, testing their behavior was frustrating.

I was able to develop a version of these procedures which does not trigger any bug, and I put them in the materialize repository, on GitHub.

I can finally suggest people to try them. If you have any problems (becuase there are bugs in the procedures, on in MariaDB, or you don’t understand something) feel free to contact me at info [At, federico - razzoli /Dot, com (replace [At, with @ and /Dot, with .). I will help. But be very patient or pay me: remember that 3 days are not a long time for a free service. 🙂

Enjoy!

How MariaDB makes Stored Procedures usable

I already wrote how MariaDB makes the debug of stored procedures much easier via the SQL Error Log. But I recently found out that MariaDB provides a usable workaround for some big limitations of their procedural SQL.

First, SELECT is not the only SQL statement which returns a resultset. Other examples are DELETE RETURNING, CALL, SHOW, EXPLAIN and administrative commands like ANALYZE TABLE or CHECK TABLE. But these commands cannot be used in place of SELECT in the following contexts:

  • Subqueries, derived tables, JOINs, UNIONs
  • CREATE TABLE ... SELECT
  • INSERT/REPLACE INTO ... SELECT
  • CREATE VIEW ... AS SELECT
  • DECLARE CURSOR ... SELECT
  • SET @my_var := SELECT

This means that there is no way to read the output of these statements within a stored procedure or trigger.

Also, some statements cannot be executed in a stored procedures and/or in prepared statements. An example is CHECK TABLES: even if you don’t care about the resultset (which would be a serious limitation), you cannot execute it in a stored procedure or in a prepared statements.

MariaDB and MySQL support the DO statement, which is identical to SELECT except that it doesn’t return any result. However, stored procedures could return one or more resultsets too, and you cannot invoke them with DO. This means that in some contexts you may have to deal to annoying resultsets, or perhaps you cannot call those procedures (within triggers or functions).

I faced all these problems while developing STK/Unit and in other projects.

But MariaDB lets us workaround these limitations – and the funny thing is that probably its developers are not aware about this! Here is an example:

CREATE OR REPLACE TABLE show_master_status
	ENGINE = CONNECT
	TABLE_TYPE = MYSQL
	SRCDEF = 'SHOW MASTER STATUS'
	CONNECTION = '_'
;

DELIMITER ||
CREATE PROCEDURE test()
BEGIN
	DECLARE eof BOOL DEFAULT FALSE;
	DECLARE v_file, v_position TEXT;
	
	DECLARE cur CURSOR FOR
		SELECT `File`, `Position` FROM show_master_status;
	
	DECLARE CONTINUE HANDLER
		FOR NOT FOUND
	BEGIN
		SET eof := TRUE;
	END;
	
	OPEN cur;
	`lbl_loop`:
	LOOP
		FETCH cur INTO v_file, v_position;
		IF eof IS TRUE THEN
			LEAVE lbl_loop;
		END IF;
		
		SELECT CONCAT('I''ll do something with ', v_file, v_position);
	END LOOP;
	CLOSE cur;
END ||
DELIMITER ;

MariaDB [_]> CALL test(); 
+--------------------------------------------------------+ 
| CONCAT('I''ll do something with ', v_file, v_position) | 
+--------------------------------------------------------+ 
| I'll do something with binlog.00000242965              | 
+--------------------------------------------------------+ 
1 row in set (0.01 sec) 

Query OK, 0 rows affected (0.01 sec)

As you can see, with this trick I was able to use a cursor with SHOW MASTER STATUS.

How does the trick work? If you know CONNECT, probably you already guessed. CONNECT is a storage engine which allows the users to read data from several kind of data sources as if they were regular SQL tables. Supported data sources include several file formats, remote DBMS’s and more. In this case I defined a table which connects to a MariaDB/MySQL server (TABLE_TYPE=MYSQL). The server is… localhost. Since I specified a SRCDEF table option, the data source is not a table in the server, it is the resultset of the specified query (SHOW MASTER STATUS). This means that, when the table is queried, the CONNECT engine opens a connection to the local server, it executes SHOW MASTER STATUS, and it returns the results of that statement. But since this work is done by the CONNECT engine, from the MariaDB point of view we are just querying a table. So, we can use that query in all contexts where SELECT works.

Also note that we don’t have to specify the table structure. CONNECT supports table discovery, which means that it automatically knows which columns and types are needed.

Confused? I hope not. Look again at the example – everything will be clear.

Of course, having to CREATE a TABLE each time is not very comfortable, even with table discovery. So I wrote a stored procedure which does the boring work for us:

UPDATE:
I removed the code because it was buggy triggered MariaDB bugs. See this post and this GitHub repository for working code.

Enjoy!

Debugging MariaDB stored procedures with the SQL Error Log

A very old version of the MySQL site contained a nice header: Speed, Power, Ease of Use. I don’t think that stored programs (routines, triggers, events) were supported, at that time. Now they are, and… developing them is amazingly hard.

There are many problems, for example the language is not flexible and the execution is sloooow. But the biggest problem is that there is no debug API.

In details, one of the biggest problems is that, if you have complex procedures (accessing multiple tables, having error handling, and/or calling other procedures) in practice you have no idea of what warnings occur within your procedures.

MariaDB 10.0 makes things much easier by adding the SQL_ERROR_LOG plugin. Well, I don’t think that they had stored programs in mind when they developed it. But what’s important for us is that… it makes stored programs debug decent.

This article explains how. Its use in application debugging is not covered here, because it seems to me quite obvious and I want to focus on stored procedures.

Enabling SQL_ERROR_LOG

We will use the SQL_ERROR_LOG plugin. Its purpose is to log SQL errors into a text file. To enable it:

INSTALL SONAME 'sql_errlog';

Note that specifying the file extension is not necessary; this makes the statement platform-independent. The MySQL syntax is still supported.

When the plugin is installed, the logging is always active. To stop the logging, uninstall the plugin:

UNINSTALL PLUGIN SQL_ERROR_LOG;

Unfortunately, you might see a warning like this:

Warning (Code 1620): Plugin is busy and will be uninstalled on shutdown

I don’t know any way to avoid this. But I don’t think that SQL_ERROR_LOG is meant to be ran on a production server, I consider it as a development tool. However, if this is a problem for you, just ask the list or file a bug.

Errors are written in a file called sql_errors.log, in the data directory:

MariaDB [test]> SELECT CONCAT(@@global.datadir, @@global.sql_error_log_filename) AS sql_errlog;
+--------------------------------------+
| sql_errlog                           |
+--------------------------------------+
| /usr/local/mysql/data/sql_errors.log |
+--------------------------------------+
1 row in set (0.00 sec)


To make debug and life easier, I rotate the files very often:

SET @@global.sql_error_log_rotate = 1;

The format is the following:

2014-06-30  1:22:30 root[root] @ localhost [] ERROR 1051: Unknown table 'test.unknown_table' : DROP TABLE `unknown_table`

Limitations

  • The session id is not written to the log. The hostname and the user account are written, but they don't help us distinguishing one session from another. I suppose it is meant for being executed by application developers on their local machines.
  • In theory, only errors are logged - no warnings, no notes. But keep on reading.
  • The file format is not safely parseable. It's designed for humans.

Debug Messages

Look at the following procedure:

DROP PROCEDURE IF EXISTS do_something;
DELIMITER ||
CREATE PROCEDURE do_something(IN p TEXT)
    READS SQL DATA
BEGIN
    DECLARE CONTINUE HANDLER
        FOR SQLSTATE '45000'
    BEGIN END;
    
    IF @debug = TRUE THEN
        SET GLOBAL sql_error_log_rotate = 1;
    END IF;
    
    IF @debug = TRUE THEN
        SET @error = CONCAT('p = ', IF(p IS NULL, 'NULL', QUOTE(p)));
        SIGNAL SQLSTATE '45000' SET
            MYSQL_ERRNO = 9999,
            MESSAGE_TEXT = @error;
    END IF;
    
    -- why do something? let's do nothing!
    DO NULL;
END ||
DELIMITER ;
SET @debug = TRUE;
CALL do_something('x');

Got the trick? If the @debug variable is set to TRUE, an error is produced. The error is immediately suppressed by an empty CONTINUE HANDLER, so the procedure's execution continues and nothing happens. Well, almost nothing: the error is logged!

So:

  1. Write your CONTINUE HANDLERs.
  2. Set @debug to 1.
  3. CALL your procedure.
  4. cat the sql_errors.log.

Warning Logging

Look at this example:

DROP PROCEDURE IF EXISTS do_something;
DELIMITER ||
CREATE PROCEDURE do_something()
    READS SQL DATA
BEGIN
    DECLARE CONTINUE HANDLER
        FOR SQLWARNING
    BEGIN
        DECLARE CONTINUE HANDLER
            FOR SQLSTATE '45000'
        BEGIN END;
        
        GET DIAGNOSTICS CONDITION 1
              @errno = MYSQL_ERRNO
            , @error = MESSAGE_TEXT
            ;
        IF @debug = TRUE THEN
            SIGNAL SQLSTATE '45000' SET
                MYSQL_ERRNO = @errno,
                MESSAGE_TEXT = @error;
        END IF;
        RESIGNAL;
    END;
    
    IF @debug = TRUE THEN
        SET GLOBAL sql_error_log_rotate = 1;
    END IF;
    
    -- here goes the 'real' procedure.
    -- let's produce a warning to trigger the main HANDLER...
    DO 1/0;
END ||
DELIMITER ;

I know: this is tricky. Writing this has been hard. But don't worry: reusing this template will be quite easy.

The main HANDLER catches the warnings (in this case division by 0, if you have a good SQL_MODE). Then we get the warning properties (GET DIAGNOSTICS) and we use them to issue an error (SIGNAL). The error is suppressed by the internal empty HANDLER, but it is logged. Finally, the error is RESIGNAL'd, because we don't want the debug code to suppress warnings.

A final note. Do you want to log warnings only if they appear within a particular portion of your procedure? You can. Just enclose those lines in a BEGIN ... END construct, and define your handlers inside the construct.

Enjoy!