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!

GNU Affero GPL license: (trying to) debunking myths

There are a lot of misconceptions about the GNU AGPL license. I’m writing this brief article to try debunking them and explain what this license really is.

Let’s start from the GNU GPL. It makes a licensed piece of code (the covered work) forever free. If you download/receive a copy of a GPL’d software, you can use it in any way, study the code, modify the code, redistribute the software as is or a modified version. There are some limitations: if you redistribute the software, you can’t do things which limit the freedom of your users. For example, you can’t distribute the software as closed source.

This is considered as a strong limitations by non-free software producers and even by some free software producers. I completely disagree. Whoever can produce non-free software; but why should free software developers help them in doing so? Do non-free software developers help us in any way? No, they don’t.

However, GPL has a deficiency. The hey is the concept of distribution. If Jenny gives to Butch an USB stick containing a software, she’s distributing it. The same applies if she sends the software via email. In these cases, the GPL grants the basic rights of the end user. When the GPL was written, this was enough.

Nowadays, a software is often used via a network. No distribution takes place: Butch connects to Jessie’s server, and uses her copy of a software. He doesn’t receive the software itself, so the GPL cannot grant him any right. But… he is the end user! Most software users today don’t even know if the software they use is running on their hardware or somewhere else. In many cases, you just download a client. And no one even tells you it is just a client.

The AGPL addresses this problem. It is basically a GPL license with an additional clause: the section 13. This section is brief and very easy to understand (like most parts of the license) and the key part is: if you modify the Program, your modified version must prominently offer all users interacting with it remotely through a computer network (…) an opportunity to receive the Corresponding Source of your version by providing access to the Corresponding Source from a network server at no charge. This includes non-system libraries used by the software.

So, the AGPL simply does what the GPL cannot do anymore because we don’t run programs on our computers.

Of course you can use it on non-network software. It is generally a good idea: someday it might become network software, or a piece of your code could be used in a network software.

I can’t imagine possible reasons why one can honestly think that GPL is ethically right and AGPL is not. Of course there may be commercial reasons to pretend you think it. It depends on your company’s business model. If your company modifies GNU GPL software on your server, and you don’t want users to know this, then yes, you probably must pretend that AGPL violates your basic human rights.

License compatibility

There is however a caveat. Many people think that all free licenses can be used together. False: mixing incompatible licenses is just as illegal as copying Microsoft Windows for a friend. GPL is a great license which comes with a price: not all free licenses are compatible with it. AGPL is an even more free license, which comes with a greater price: even GPLv2 is not compatible with it.

Enjoy!