MariaDB 10.1.1: Compound statements

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’);

Read more

MariaDB 10.1.1: no more .frm’s for performance_schema tables

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. Read more

MariaDB 10.1.1: default roles

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). Read more

Three new MariaDB Releases

The MariaDB project is pleased to announce the immediate availability of the following:



MariaDB 10.1.1 is an Alpha release.

Download MariaDB 10.1.1

Release Notes Changelog What is MariaDB 10.1?

MariaDB APT and YUM Repository Configuration Generator

Don’t use alpha releases on production systems!

See the Release Notes and Changelog for detailed information on this release and the What is MariaDB 10.1? Read more

Performance evaluation of MariaDB 10.1 and MySQL 5.7.4-labs-tplc

Introduction

Evaluating the performance of database systems is a very demanding task. There are a lot of hard choices to be made, e.g.:

  • What operating system and operating system version is to be used
  • What configuration setup is to be used
  • What benchmarks are to be used and how long are the warm-up and measure times
  • What test setups are to be used
  • What version of the database management system is used
  • What storage engine is used

While performance evaluation is mostly machine time, there is still a lot of hard work for the human monitoring the tests. Read more

MariaDB moves development to Github

Today marks a milestone in terms of the MariaDB project – going forward, the MariaDB project plans to use Github and git for source code management. The migration happens from Launchpad and the bzr tool.

The 10.1 server development (under heavy development now) will happen on Github. You can check it out here: https://github.com/MariaDB/server. Feel free to watch, star or even fork the code, and send us contributions!

Previous maria-captains should now provide their Github IDs so that they can be accorded similar status. Send the IDs to the maria-developers mailing list. Read more

What do you want to see in MariaDB 10.1?

Last night, after my previous blog post, everyone in attendance at the SkySQL developer meeting in Barcelona gathered for dinner at El Cangrejo Loco, which, if my High School Spanish is working, translates as The Crazy Crab. After the excellent food, the tradition of singing at MySQL/MariaDB developer meetings was preserved.

Today the MariaDB developers in attendance at the SkySQL developer meeting in Barcelona got together to work on plans for MariaDB 10.1. We also paused for a group photo:

Many tasks have been identified for possible inclusion in 10.1. Some have already been marked as such in JIRA.

Read more