Yet Another Blog About Programming (YABAP)

Menu

Tag Archives: stored_routines

Post navigation

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.

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:

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:

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.

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.

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:

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.

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 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. 🙂

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:

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:

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:

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.

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!

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.