Every now and then there is a need to execute certain SQL statements conditionally. Easy, if you do it from your PHP (or Java or whatever) application. But if all you have is pure SQL? There are two techniques that MariaDB and MySQL use in the mysql_fix_privilege_tables.sql script (applied by mysql_upgrade tool).

  1. Create a stored procedure with IF statements inside, call it once and drop it. This requires the user to have the CREATE ROUTINE privilege and mysql.proc table must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
  2. Use dynamic SQL, like

    which is not very readable and doesn’t work well if you need to execute many statements conditionally.

This may be standard, but I never understood why one cannot simply use SQL control statements (besides CALL that is) directly from the mysql command line tool prompt. Imagine a bash variant that only supports if and while in scripts, but not in the interactive mode from the command line — how would you like it?

May be Antony Curtis was asking himself similar questions when he contributed a patch for compound statements in MDEV-5317. Either way, we thought it’s a great idea and implemented this feature, based on the Antony’s contribution.

Now one can use BEGIN, IF, CASE, WHILE, LOOP, REPEAT statements directly in SQL scripts and from the mysql command line prompt — outside of stored programs. For example, one can rewrite the above as

One can use BEGIN ... END blocks and loops without having CREATE ROUTINE privilege or with corrupted (or missing mysql.proc table). This all works as you would expect it to, with no artificial “standard says so” limitations.

Still, there are some limitations to keep in mind:

  • You cannot use a simple BEGIN to start a block, this is historically used to start a transaction. Use the standard syntax BEGIN NOT ATOMIC.
  • Compound statements from the mysql command line prompt cannot start with a label.
  • Not all statements that can be used in the stored program are supported from the mysql command line prompt, only those listed above are.

These limitations, though, only apply to the top-level statement. For example, if you need a labeled loop or SIGNAL, you start a block and put your statement inside it:

Enjoy!

Yes! In MariaDB 10.1.1 tables in PERFORMANCE_SCHEMA do not use .frm files. These files are not created, not read — in fact, PERFORMANCE_SCHEMA tables never touch the disk at all.

This became possible due to a lesser-known feature of MariaDB — new table discovery (“old table discovery” was implemented in MySQL for NDB Cluster in 2004), implemented in MariaDB 10.0.2. Instead of reading and parsing .frm files, MariaDB simply asks PERFORMANCE_SCHEMA table, what structure it has, and because these tables always have a fixed structure, the table directly returns it to MariaDB with no need for any external data dictionary.

It also means, you never need to upgrade PERFORMANCE_SCHEMA tables, they always have the correct structure corresponding to the MariaDB version you’re running. And PERFORMANCE_SCHEMA never needs to deal with outdated .frm files, with old table structures. This alone allowed us to remove ~5500 lines (five and a half thousand lines!) of PERFORMANCE_SCHEMA code.

Currently PERFORMANCE_SCHEMA tables, INFORMATION_SCHEMA tables, Sequence, and Archive tables can work without .frm files. Connect and FederatedX tables also support discovery (a variant of it, called assisted discovery).

In the future we plan to extend other engines to support discovery too.

Table discovery for PERFORMANCE_SCHEMA tables was implemented by Sergey Vojtovich.

As you all know MariaDB supported roles since the MariaDB release 10.0.5. They were implemented almost exactly as specified in the SQL Standard 2003, features T331 “Basic roles” and T332 “Extended Roles”.

But we were often hearing complains, users were not satisfied with purely standard set of features. In particular, the standard specified that one had to do

to be able to use privileges, granted to the role foobar. This was not always convenient and sometimes not even possible (imagine, you need to grant role privileges to an account used by a closed-source application). There had to be some way to enable a given role automatically, when a user connects.

To solve this issue we have introduced the concept of a default role. A default role for given user is automatically enabled when this user connects. Problem solved!

To set foobar as a default role you use, quite logically,

This stores your default role in the mysq.user table, and next time you connect the role foobar will be enabled for you automatically.

To remove a default role use

this works similarly to the standard SET ROLE statement.

You can also set a default role for another user (remember that use case with a closed-source application?):

Privilege-wise, if you can enable a role (using SET ROLE statement), you can make it a default (using SET DEFAULT ROLE statement). But to change a default role for someone else, you need insert privilege for the mysq.user table — same as when you change a password for someone else.

And don’t forget to run mysql_upgrade before using default roles — as they are stored in privilege tables, these tables have to be updated to the latest version to include the necessary columns. Otherwise SET DEFAULT ROLE statement will fail.

The implementation for this feature was contributed by Vicenţiu Ciorbaru.