Introducing Maria, the new tough storage engine

Over the weekend, MySQL made available a new storage engine, codenamed Maria.
It is an improvement of MyISAM, the flagship engine, adding crash recovery to the already appreciated features of MyISAM. This release is a preview, based on MySQL 5.1 code base. The actual final implementation has not been announced yet, although it is safe to assume that it will happen in the 6.x series.
The source code is available as a bitkeeper tree.
There is some documentation, and more will follow.
What can we do with the new engine? The immediate answer is "use it as we use MyISAM, with crash recovery features".

Let's show an example, using MyISAM: (this demonstration was given live at the Linux Conference Australia during MySQL Mini-conf)

mysql [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (1.14 sec)

mysql [localhost] {msandbox} (test) > create  table t1 (id int, b longblob) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values (1, repeat('a',1000000));
Query OK, 1 row affected (0.02 sec)

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 8 rows affected (0.26 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 16 rows affected (1.17 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 32 rows affected (2.13 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 64 rows affected (4.77 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;

At this point, while the server is inserting data, from a different shell, we enter a vicious command:

$ kill -9 `pgrep mysqld`

which is as close as you can get to pulling your server plug. The result is disastrous.

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > exit
Bye

Let's restart the server and see what has happened.

[sandbox@dsl093-167-171 maria_5_1_23]$ ./start 
sandbox server started
[sandbox@dsl093-167-171 maria_5_1_23]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-maria-alpha Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {msandbox} (test) > check table t1;
+---------+-------+----------+-----------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                  |
+---------+-------+----------+-----------------------------------------------------------+
| test.t1 | check | warning  | 1 client is using or hasn't closed the table properly     | 
| test.t1 | check | warning  | Size of datafile is: 256004096       Should be: 128002048 | 
| test.t1 | check | error    | Record-count is not ok; is 256   Should be: 128           | 
| test.t1 | check | warning  | Found 256 parts                Should be: 128 parts       | 
| test.t1 | check | error    | Corrupt                                                   | 
+---------+-------+----------+-----------------------------------------------------------+
5 rows in set (0.62 sec)

As you can see, the table got corrupted, because we pulled the plug during an insertion.
Let's do the same operation with a Maria table.

mysql [localhost] {msandbox} (test) > drop table if exists t1;
Query OK, 0 rows affected (0.15 sec)

mysql [localhost] {msandbox} (test) > create  table t1 (id int, b longblob) engine=maria;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {msandbox} (test) > insert into t1 values (1, repeat('a',1000000));
Query OK, 1 row affected (0.03 sec)

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 4 rows affected (0.29 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 8 rows affected (0.82 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 16 rows affected (1.61 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > 
mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 32 rows affected (3.16 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
Query OK, 64 rows affected (6.54 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into t1 select * from t1;
#
# kill -9 from another terminal
#
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql [localhost] {msandbox} (test) > exit
Bye

Here we pull the plug as in the MyISAM case, and the server dies.
On restart, the situation looks much different:

[sandbox@dsl093-167-171 maria_5_1_23]$ ./start   
sandbox server started
[sandbox@dsl093-167-171 maria_5_1_23]$ ./use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.23-maria-alpha Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {msandbox} (test) > check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       | 
+---------+-------+----------+----------+
1 row in set (0.73 sec)

So, Maria survived the crash, while MyISAM was corrupted. There is still much to do to make this engine ready for production, but it looks like an excellent start.

Slides from MySQL Proxy presentation at LCA 2008

The MySQL Proxy presentation at Linux Conf Australia 2008 was done just before lunch.
As announced, here are the presentation slides
The penguin on the above image is actually painted on my room's window glass.

Attending Linux Conference Australia - Melbourne 2008

The world tour proceeds. After Sydney, I am now in Melbourne, to attend the Linux Conf Au, hosted at Melbourne University. I am actually lodged at the charming Trinity College.

I have already met several known people and many more new people. Currently, I am getting ready for my presentation on MySQL Proxy at the MySQL mini-conf, and I have also a surprise presentation that will happen during the lighting talks.
Curious? You should be! See you tomorrow afternoon for the surprise talk!

Session on MySQL Proxy at the Linux Conf Australia

It's getting closer.
On Tuesday late morning, I will hold a session on MySQL Proxy during the MySQL MiniConf at Linux Conf Australia.
It will be an introduction to MySQL Proxy with some live demo.
If you are in Melbourne, come along!

Multiple triggers for the same event - Bug or feature?

Greetings from the Southern Hemisphere.
The World tour proceeds as planned, although with some added tasks in the meantime. The solution to the quiz announced from North America is now given from Australia. (Boy! I love the global village!)

The task was to create a series of three triggers, all associated to the same table for the same event (BEFORE INSERT). If you had a look at the manual, you would know that you can't set multiple triggers for the same event. However, there is a workaround, using the Federated engine. The solution to the quiz was already available as a comment to a bug report. My intended solution, very similar to the one provided by the winner, is reported below. Notice that I use the CREATE SERVER statement instead of hardcoding username and password in the CREATE TABLE statement. The quiz required using MySQL 5.1.22 or later for two reasons:

• the ability of using the CREATE SERVER statement, instead of hardcoding the connection credentials. This feature was introduced in MySQL 5.1
• The ability of using a federated table pointing to a table in the same server. This behavior was introduced in MySQL 5.1.22.

(Hubert Roksor, send me your address if you want the T-shirt, or post the address as a comment. I won't publish it, but just get the address for the shipping).

DROP TABLE IF EXISTS t1, t1_f1a, t1_f1b, t1_f1c, t1_f2a, t1_f2b, t1_f2c;
DROP SERVER IF EXISts s1;

CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;

CREATE SERVER s1
FOREIGN DATA WRAPPER mysql
OPTIONS (
   host '127.0.0.1',
   port 5123,
   database 'test',
   user 'msandbox',
   password 'msandbox'
);

CREATE TABLE t1_f1c (id int, c char(100))
   ENGINE=FEDERATED CONNECTION='s1/t1';
CREATE TABLE t1_f1b (id int, c char(100))
   ENGINE=FEDERATED CONNECTION='s1/t1_f1c';
CREATE TABLE t1_f1a (id int, c char(100))
   ENGINE=FEDERATED CONNECTION='s1/t1_f1b';

CREATE TABLE t1_f2c (id int, c char(100))
   ENGINE=FEDERATED CONNECTION='s1/t1';
CREATE TABLE t1_f2b (id int, c char(100))
   ENGINE=FEDERATED CONNECTION='s1/t1_f2c';
CREATE TABLE t1_f2a (id int, c char(100))
   ENGINE=FEDERATED CONNECTION='s1/t1_f2b';

CREATE TRIGGER bi_t1_fed_1 BEFORE INSERT ON t1_f1a
FOR EACH ROW
SET NEW.c = 'something';

CREATE TRIGGER bi_t1_fed_1b BEFORE INSERT ON t1_f1b
FOR EACH ROW
SET NEW.c = CONCAT(NEW.c, ' more');

CREATE TRIGGER bi_t1_fed_1c BEFORE INSERT ON t1_f1c
FOR EACH ROW
SET NEW.c = CONCAT(NEW.c, ' !!');

CREATE TRIGGER bi_t2_fed_1 BEFORE INSERT ON t1_f2a
FOR EACH ROW
SET NEW.id = NEW.id + 10;

CREATE TRIGGER bi_t2_fed_1b BEFORE INSERT ON t1_f2b
FOR EACH ROW
SET NEW.id = NEW.id + 100;

CREATE TRIGGER bi_t2_fed_1c BEFORE INSERT ON t1_f2c
FOR EACH ROW
SET NEW.id = NEW.id + 1000;

INSERT INTO t1_f1a VALUES (1, 'nothing');
INSERT INTO t1_f2a VALUES (1, 'nothing');

SELECT * FROM t1;

This works as expected. However, the above behavior is not in the manual. Is it a bug or a feature? Since it is not in the documentation, nor was in the original specifications, this behavior is a useful side effect. Strictly speaking, it's a bug. Pragmatically, it can be consider a feature, depending on your needs.

Mobile Quiz - many triggers on a single event on the same table

A few weeks after the latest useful quiz, I want to offer a funny one.
Given the following table, on a MySQL server 5.1.22 or newer,

CREATE TABLE t1 (id int, c char(100)) engine=MyISAM;

1. Create one set of three triggers that must all be activated BEFORE INSERT.
   
   • Upon insertion of the values (1, 'nothing'), the first trigger should replace 'nothing' with 'something', leaving the number unchanged;
   • the second trigger should add ' more ';
   • the third trigger should add '!!'.
2. Create one set of three more triggers, all activated BEFORE INSERT.
   
   • Upon insertion of the values (1,'nothing') the first trigger should add 10 to the number, leaving the text unchanged.
   • the second trigger should add 100 to the number;
   • the third trigger should add 1000 to the number;

To explain it further, it must be possible to enter a distinct INSERT command that will fire the first set of triggers, and a different INSERT command that will fire the second set of triggers.

Constraints:

• You can only use one MySQL server. No replication, no cluster;
• You can't use MySQL Proxy;
• The only routines you can create are TRIGGERs. No PROCEDUREs, FUNCTIONs, or EVENTs.
• The solution must work with a standard binary. No code changes;
• Each insert statement must result in the creation of one and only one record in table t1.
• No intermediate records should be created in t1 or other tables.
• All three triggers must fire automatically as a result of a single INSERT statement.
  

The first one to submit the correct solution will win a T-shirt. (Note: Dipin, the winner of the previous quiz should contact me by email ( my_first_name at mysql dot com ) and give me a complete address, so I can send him/her a T-shirt).

Since I am traveling (I am about to board a plane from L.A. to Sydney), don't expect a quick reaction from me. I will eventually acknowledge your comments. Just be patient and don't assume I ran away.

The above instructions are accurate, but kind of devious (of course, or else you would not enjoy the quiz). Good luck!

New step in the world tour and new role in a new company

What a week!
It started with a gift for all employees. A book that Lenz Grimmer has produced with great professionalism.

It is not for the general public. I am showing it here to give would-be employees one more reason to join the company. (Yes. It's business as usual. We are still hiring!)
Then the unexpected but very exciting news arrived, and perhaps a part 2 of this book won't be necessary.
(Update. Marten pointed out that this is not necessarily true, and the second part of the book will only be more exciting. Maybe so. The second part may just need a different sub-title.)
As part of the interesting changes in MySQL, there has been some side effects on me. Since Kaj is now busy being ambassador (my wife, when I explained what he'll be doing, asked if he is going to spend time with Angelina Jolie as goodwill ambassador, but it doesn't seem to be the case!) the community team needed new guidance, and the choice fell on me. So I will be coordinating the community team from now on until the smoke clears on the future of the company roles.
My world tour continues. I am now in Los Angeles, to assume my new responsibilities while on the road, as expected from a virtual company employee.

Munich-Orlando with special outfit

My world tour includes mostly warm places (Orlando, Los Angeles, Australia, Singapore), but to cover the first leg, Munich, I needed warm clothes (cover is the operative word here).
Thus, when I saw a skilled craftsman creating customized hats and scarfs, I couldn't resist.
The weather in Orlando does not require such gear. This is a view form my hotel room.
. We are going to have MySQL company meeting in this beautiful place. For now, I am just nursing my jet lag and preparing to meet a few hundred colleagues.

World tour - step one

I am about to embark for my longest trip ever.
I will board a plane on Saturday in my hometown in Sardinia, Italy, heading to Munich. A comfortable two hours flight, to be ended with a business meeting and a traditional Munich dinner, both provided by Kaj Arnö.
The easy part of the trip ends here. From Munich, I will go to Orlando, USA, to the Company Meeting.
After the meeting, a short stopover in Los Angeles, and then the loooong jump to Sydney, which I will be wisiting for the first time. And another first time will be in Melbourne, a few days later, where I will attend the Linux Conf Au, and also present a session about MySQL Proxy during the MySQL MiniConf.

That is not the end of it, of course. I will be just half world away from home. Then I will go to Singapore for a vacation, and then back th Europe.

More travel news to come in the next days!