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!

Stored Routines to easily work with the SQL_MODE

Versione italiana

Working with the SQL_MODE can be tricky, because it’s a command separated list involving long keywords. To be honest, I hate to rewrite the flags, trying to read an unreadable list, etc. Of course some string functions can help (Justin Swanhart suggests to use REPLACE() to make comma-separated lists readable).

I made a small set of Stored Routines wich allow me to easily show SQL_MODE, add a flag, drop a flag and check if a flag is set. These routines work with the GLOBAL SQL_MODE; if you don’t like this, simply replace “@@global.” with “@@session.” in the SQL file before installing.

You can download (or improve) my routines using this SQL_MODER GitHub repo.

And now, some examples.

Show currently selected flags:

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
8 rows in set (0.46 sec)

(this is the SQL_MODE I suggest to use)

Check wether a flag is set:

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.38 sec)

Add one or more flags:

MariaDB [(none)]> CALL _.sql_mode_set('NO_UNSIGNED_SUBTRACTION,HIGH_NOT_PRECEDEN
CE');
Query OK, 0 rows affected (0.07 sec)

MariaDB [(none)]> CALL _.sql_mode_show();
+----------------------------+
| FLAG                       |
+----------------------------+
| ERROR_FOR_DIVISION_BY_ZERO |
| HIGH_NOT_PRECEDENCE        |
| NO_AUTO_CREATE_USER        |
| NO_ENGINE_SUBSTITUTION     |
| NO_UNSIGNED_SUBTRACTION    |
| NO_ZERO_DATE               |
| NO_ZERO_IN_DATE            |
| ONLY_FULL_GROUP_BY         |
| STRICT_ALL_TABLES          |
| STRICT_TRANS_TABLES        |
+----------------------------+
10 rows in set (0.13 sec)

Query OK, 0 rows affected (2.09 sec)

Drop a flag:

MariaDB [(none)]> CALL _.sql_mode_unset('HIGH_NOT_PRECEDENCE');
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT _.sql_mode_is_set('HIGH_NOT_PRECEDENCE');
+------------------------------------------+
| _.sql_mode_is_set('HIGH_NOT_PRECEDENCE') |
+------------------------------------------+
|                                        0 |
+------------------------------------------+
1 row in set (0.00 sec)

If you mistyped the flag (or it wasn’t set):

MariaDB [(none)]> CALL _.sql_mode_unset('hello world');
ERROR 1644 (45000): Flag 'hello world' was not set

Enjoy!

MariaDB/MySQL: Procedures to easily work with the Diagnostics Area

Versione italiana

UPDATE 2013-08-30: I fixed 2 bugs and create a GitHub repo called sql_diagnostix. Sorry for not doing this before. If I (or someone else) find new bugs, I’ll update the repo.

The problem

To quickly see information about the errors and warnings generated by the last executed statement, we can use SHOW WARNINGS. However this statement’s results cannot be accessed via SQL, so they cannot be used for error handling within a stored program.

For that purpose we can use GET DIAGNOSTICS, which has two problems:

  • It requires a lot of code
  • There is not a trivial way to quicly show all info from Diagnostics Area.

The first problem may discourage developers to use GET DIAGNOSTICS at all.

The second problem is not so important in most cases, because the missing info are SQLSTATE and the information which can only be set via SIGNAL and RESIGNAL, and cannot be read by an external program (SCHEMA_NAME, CURSOR_NAME, etc). However SQLSTATE can be important for you and setting/reading all SIGNAL‘s clauses could help debugging and problem solving… that’s why they exist 🙂

Oracle MySQL 5.7 has two Diagnostics Areas, and supports the STACKED keyword to access the second DA from an error HANDLER. However, this doesn’t help much: it just allows to execute another table-based statement before reading information about the error conditions which occurred. So, the STACKED DA doesn’t seem to solve any real life problem.

The solution

I created three Stored Procedures to address these problems:

  • _.materialize_diagnostics_area()
  • _.show_diagnostics_area()
  • _.show_full_diagnostics_area()

All the Procedures create a MEMORY temporary table called DIAGNOSTICS_AREA in a database called _. If the table already exists, it is DROPped and reCREATEd. Incidentally they also empty server’s Diagnostics Area, but the information will be avaible in that table until you call one of those routines again.

_.DIAGNOSTICS_AREA has a column called ID, which indicates each condition’s position in the Diagnostics Area. Other columns names and values are equal to the conditions properties in the diagnostics area. The only exception is the SQLSTATE column, which has the name that can be used with SIGNAL and RESIGNAL, not the name used in the diagnostics area (RETURNED_SQLSTATE).

Since both Stored Programs and external programs will be able to access _.DIAGNOSTICS_AREA, this technique exposes all the information that you can set with SIGNAL to external programs (MYSQL_ERRNO, etc).

Directly call materialize_diagnostics_area() if you want to easily copy the DA into a table and later run queries on it.

show_full_diagnostics_area() shows all the information, after populating the table. It’s a sort of SHOW WARNINGS with a lot of columns.

show_diagnostics_area() only shows the ID, SQLSTATE, MYSQL_ERRNO, MESSAGE_TEXT columns.

Example:

MariaDB [test]> INSERT INTO `t` VALUES (1/0), (1/0), (1/0), (-1);
Query OK, 4 rows affected, 4 warnings (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 4
 
MariaDB [test]> CALL _.show_diagnostics_area();
+----+----------+-------------+--------------------------------------------+
| ID | SQLSTATE | MYSQL_ERRNO | MESSAGE_TEXT                               |
+----+----------+-------------+--------------------------------------------+
|  1 | 22012    |        1365 | Division by 0                              |
|  2 | 22012    |        1365 | Division by 0                              |
|  3 | 22012    |        1365 | Division by 0                              |
|  4 | 22003    |        1264 | Out of range value for column 'c' at row 4 |
+----+----------+-------------+--------------------------------------------+
4 rows in set (0.09 sec)

Limitations

If your Stored Program needs to be fast, use these Procedures only for debug.

As mentioned above, each call will erase old data from _.DIAGNOSTICS_AREA. This is by design.

An information is present in SHOW WARNING‘s output but missing from _.DIAGNOSTICS_AREA: the Level column. The reason is that I wasn’t able to find in MySQL documentation how to distinguish a Warning from a Note, except using SHOW WARNINGS. If there is no way, or a way exists but is not documented, I can’t add that information.

5.7’s STACKED diagnostics area is not used.

The code

Okay, I wrote enough. Now, if you are interested:

Download the SQL code

I won’t explain the code, because it seems to me very simple and it’s commented. Just take a look to materialize_diagnostics_area() and you’ll understand what I did.

Enjoy!