OurSQL Episode 181: MariaDB Goodies

This week we discuss more features of MariaDB 10. Ear Candy is how to avoid downtime when switching to GTIDs and At the Movies is Managing Hundreds of MySQL Servers Efficiently.

News
MySQL Community Awards 2014: the Winners

MariaDB 10
MariaDB 10.0.1 features
Other MariaDB podcasts:
OurSQL Episode 89, where we talk about an overview of MariaDB
OurSQL Episode 174, where we talk about the CONNECT storage engine
OurSQL Episode 175, where we talk about the CONNECT storage engine
OurSQL Episode 176, where we talk about GTIDs in MariaDB
OurSQL Episode `64, where we talk about the MariaDB Audit Plugin

SHOW EXPLAIN command to see EXPLAIN commands for currently-running queries. You can also export SHOW EXPLAIN to the slow query log.

Dynamic columns in MariaDB

CREATE TABLE `album` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `artist` varchar(128) NOT NULL,
  `details` blob,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB;

INSERT INTO album (name, artist, details) VALUES ('The snow goose', 'Camel', COLUMN_CREATE('year', '1984', 'style', 'progressive rock'));
INSERT INTO album (name, artist, details) VALUES ('Back in the U.S. Disc 1', 'Paul McCartney', COLUMN_CREATE('style','rock', 'art', '~/art_cover/back_in_the_us_disc_1.jpg'));

MariaDB [test]> select name, artist, column_get(details, 'year' as CHAR) AS year, column_get(details, 'style' as CHAR) AS style from album;
+-------------------------+----------------+------+------------------+
| name | artist | year | style |
+-------------------------+----------------+------+------------------+
| The snow goose | Camel | 1984 | progressive rock |
| Back in the U.S. Disc 1 | Paul McCartney | NULL | rock |
+-------------------------+----------------+------+------------------+
2 rows in set (0.00 sec)

<code>UPDATE album SET details=COLUMN_ADD(details, 'art', '/opt/media/art_cover/the_snow_goose.jpg') WHERE name = 'The snow goose';

COLUMN_EXISTS Example:

MariaDB [test]> select name, artist, column_exists(details, 'year') as year, column_exists(details, 'style') as style from album;
+-------------------------+----------------+------+-------+
| name                    | artist         | year | style |
+-------------------------+----------------+------+-------+
| The snow goose          | Camel          |    1 |     1 |
| Back in the U.S. Disc 1 | Paul McCartney |    0 |     1 |
+-------------------------+----------------+------+-------+
2 rows in set (0.00 sec)</code<

COLUMN_LIST example:
<code>MariaDB [test]> select name, artist, column_list(details) from album;
+-------------------------+----------------+----------------------+
| name                    | artist         | column_list(details) |
+-------------------------+----------------+----------------------+
| The snow goose          | Camel          | `art`,`year`,`style` |
| Back in the U.S. Disc 1 | Paul McCartney | `art`,`style`        |
+-------------------------+----------------+----------------------+
2 rows in set (0.00 sec)

Nesting dynamic columns:
set @members=COLUMN_CREATE('drummer', 'Allan White', 'guitar', 'Steve Howe', 'bass', 'Chris Squire', 'keyboard', 'Rick Wakeman', 'voice', 'Jon Anderson');

and then insert the artist as:
INSERT INTO album (name, artist, details) VALUES ('Yes', 'Yes', COLUMN_CREATE('guests', @members));

MariaDB [test]>  select name, artist, column_list(details) from album;
+-------------------------+----------------+----------------------+
| name                    | artist         | column_list(details) |
+-------------------------+----------------+----------------------+
| The snow goose          | Camel          | `art`,`year`,`style` |
| Back in the U.S. Disc 1 | Paul McCartney | `art`,`style`        |
| Yes                     | Yes            | `guests`             |
+-------------------------+----------------+----------------------+
3 rows in set (0.00 sec)

Allowed data types in dynamic columns

Storage engine independent table statistics

Ear Candy
Booking.com's MySQL 5.6 GTIDs: Evaluation and Online Migration on how to avoid needing downtime to use GTIDs in MySQL. The method on how to avoid downtime relies on bug 71527, which describes how 2 slaves from the same master can generate 2 different GTIDs for the same transaction, which can Managing Hundreds of MySQL Servers Efficiently, presented by Mozilla's Brandon Johnson and Sheeri Cabral at the MySQL Connect conference 6 months ago.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast