Tag Archives: development

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
   SET @str = IF (@have_csv = ‘YES’,
   ‘CREATE TABLE IF NOT EXISTS general_log (
   event_time TIMESTAMP(6) NOT NULL,
   user_host MEDIUMTEXT NOT NULL,
   thread_id BIGINT(21) UNSIGNED NOT NULL,
   server_id INTEGER UNSIGNED NOT NULL,
   command_type VARCHAR(64) NOT NULL,
   argument MEDIUMTEXT NOT NULL
   ) engine=CSV CHARACTER SET utf8 comment=”General log”‘,
   ‘SET @dummy = 0’);

…

Continue reading “MariaDB 10.1.1: Compound statements”

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

Continue reading “MariaDB 10.1.1: no more .frm’s for performance_schema tables”

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
SET ROLE foobar;
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). …

Continue reading “MariaDB 10.1.1: default roles”