Debugging a stored program can be tricky in MariaDB. There are several reasons. The most obvious is that MariaDB does not have any native debug API. Some debuggers exist, but they use dirty techniques to emulate the debugger process. Some of them parse the stored programs code and transparently add some statements that emulate a checkpoint, or keep track of the call stack, or return information to the debugger. This technique heavily modifies the code, thus it is not always reliable. Other debuggers execute the code internally to be able to implement debugging features. However, they cannot reproduce the exact behavior of MariaDB and MySQL in all circumstances, especially if you consider that several versions exist, and that they necessarily have bugs in both the program and the documentation. Of course, this is true for all existing software, not only for MariaDB!

To debug stored programs, a developer needs to use some tricks, which consist of adding some informational statements in various parts of the stored program's code. When the debug process ends, these informational statements must be deleted or commented for future use.

If the developer just wants to know whether a branch of an IF statement is executed, a SELECT 1; is enough, like in the following example:

IF @a > 1 OR @b IS NOT NULL THEN
    SELECT 1; -- debug code
    …
END IF;

More often, a developer may want to SELECT a table row or a variable to check his/her values. Before executing a dynamic prepared statement, checking the text of the statement is often a good idea, as shown in the following example:

SET @sql := /* some string expression here */ ;
SELECT @sql; -- debug code
PREPARE stmt FROM @sql;

However, debugging based on SELECT statements only works in stored procedures because other stored programs (such as stored functions, triggers, and events) cannot return a result set. In the development stage, it is often a good idea to implement them as a stored procedure.

For events, this is simple: no modifications are needed to turn an event into a procedure. However, events should generally be written in a table whether they succeed or not. This is often important because if they encounter problems, the errors are not returned to a client.

For functions, it is easy. Functions have more limitations than procedures, so it is important to remember that some features cannot be used (for example, prepared statements). The only modification required is that a procedure cannot return a value. So the RETURN statements need to be turned into SELECT statements.

For triggers, the conversion can be hard. This is because triggers can access a table row's values—both OLD values (the values as they were before the statement execution) and the NEW values (the values modified by the statement). Turning the required values into procedure parameters can be a convenient solution in simple cases. However, in complex cases, this is an error-prone technique because the values must be passed manually. A better solution might be using the INSERT statement instead of the SELECT statement. The values will not be returned to the client; they will be written to a debug table. The drawback of this technique is that writing and reading such values will require more code.