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!

Pop quiz (with prize): generate 4 billion records

My latest quiz was quite popular, and some interesting ideas were submitted.
There was an interesting development. A colleague called and asked me for advice on how to insert 4 billion rows in a table with a simple structure.

create table t1 (
id tinyint not null
);

Very simple. No primary key, no indexes. It is needed to perform some specific tests.
Actually, not 4 billion, but 2^32 records are needed, i.e. 4,294,967,296.
The classical method used in these cases is doubling the table contents:

insert into t1 values (1),(1),(1),(1),(1),(1),(1),(1);
insert into t1 select * from t1;
insert into t1 select * from t1; # and so on

My solution was similar to the one from my quiz.

CREATE VIEW `v4` AS
select NULL
union all select NULL
union all select NULL
union all select NULL;

CREATE VIEW v1024 AS
select null from v4 a, v4 b, v4 c, v4 d, v4 e;

INSERT INTO t1 select 1 from v1024 a, v1024 b, v1024 c, v4 ;

This one is faster than the doubling method, but it still requires from 40 to 65 minutes, depending on how fast is your server.
So, the challenge, for which we will give away 1 MySQL T-shirt to the winner, is as following:

• Generate 2^32 records for table t1;
• no limits to the length of code to use;
• you can use stored routines, temporary tables, views, events, whatever makes the insertion fast;
• the method must be portable across operating systems. If it is not portable, a Unix-only method may be accepted if it is exceptionally faster than SQL-only solutions;
• methods relying on external applications cannot be accepted;
  
• If an programming language is needed, for compatibility with the test suite we can only accept Bash shell or Perl scripts;
• If you devise a fast method to insert data using MySQL Proxy, a Lua script can be accepted.
• what matters is the final insertion speed and ease of use.
• If a method uses an external script, its speed must be more than 20% faster than the fastest method using only SQL.
• The speed will be calculated on my server, using MySQL 5.1.23.
  

Solutions so far

• Dpin. 20 minutes for a portable solution is very good.
• Jedy. Very nice, but not that fast. 32 minutes.
• Todd. Brilliant solution (10 minutes for 4 billion rows!), but really impractical. We need something that works for any engine. This one is a dirty trick that is fun to use once, but in the long run it won't stand.

Data from nothing - solution to pop quiz

My latest post on generating one million records received many comments, with interesting solutions.
The challenge required the insertion of 1 million records in a simple table, with a few constraints.

create table t1 (
dt datetime not null,
primary key (dt)
);

The solution

The official solution is straightforward:

create view v3 as select null union all select null union all select null;
create view v10 as select null from v3 a, v3 b union all select null;
create view v1000 as select null from v10 a, v10 b, v10 c;
set @n = 0;
insert into t1 select now()-interval @n:=@n+1 second from v1000 a,v1000 b;

You can appreciate the title here. Data from nothing. Generate 1 million valid records from a view made of NULLs!
The principle is easy.

1. First create a few values in a view, by means of UNION ALL queries.
2. Then, using Cartesian products, generate a larger view.
3. And a larger one, containing 1,000 self generating rows.
4. The final step is a simple Cartesian product in a self join.

This is something that they told you not to do in Database 101 classes, but it can be handy sometimes!

It can be reduced to 4 lines:

create view v3 as select 1 n union all select 1 union all select 1;
create view v as select 1 n from v3 a, v3 b union all select 1;
set @n = 0;
insert t1 select now()-interval @n:=@n+1 second from v a,v b,v c,v d,v e,v;

The other solutions

The solution that comes closer to the intended one comes from Shane Bester.

set @a:=1167602400,@b:=1168602400;
create view v as select 1 union select 2 union select 3 union select 4;
create view x as select 1 from v,v a,v b,v c,v d;
insert t1 select(from_unixtime(@a:=@a+1))from x,x a where @a<@b;  

Shane also produced the fastest solution.

create view v as select a.dt from t1,t1 a,t1 b,t1 c,t1 d,t1 e,t1 f,t1 g;
replace t1 values('990101'),('980101'),('970101'),(@a:=1);
replace t1 select (adddate(a.dt,@a:=@a+1)) from v,v a limit 999996;

B.Steinbrink produced the shortest one.

  INSERT t1 VALUES(@a:=72620211),(101),(102),(103),(104),(105),(106),(107);
 replace t1 select@a:=adddate(@a,1)from t1,t1 a,t1 b,t1 c,t1 d,t1 e,t1 f;

Roland Bouman produced the most wicked one. And a method that caught me by surprise.

# WARNING! DON'T RUN IF YOUR SERVER DOES NOT HAVE AT LEAST 4 GB RAM !!!
set max_allowed_packet := 1073741824;
set @s=concat('insert into t1 values(@d:=10000101+@i:=1)');
set @s=concat(@s,repeat(',(date_add(@d,interval @i:=@i+1 day))',999999));
prepare s from @s;
execute s;

In 5 lines, he is generating a 37 MB query containing 1 million records!
Not very efficient, but it works, if you have a huge amount of RAM. Otherwise, it can crash your server!
Kai Voigt came up with the same idea, with a faster execution.

set @a=concat(repeat(concat(@s:="select 1 ","union all "),99),@s),@x:=0;
set @c=concat(@s,"from (",@a,")a join(",@a,")b join(",@a,")c"),@i="inser";
set @d=concat(@i,"t into t1 select from_unixtime(@x:=@x+1) from(",@c,")c");
prepare s from @d;
execute s;

Jedy solution was the fastest until Shane's arrived.

insert into t1 values (now()-2),(now()-1),(now()),(now()+(@a:=1));
insert into t1 select adddate(now(),@a:=@a+1) from t1 a,t1 b,t1 c,t1 d,t1;
insert into t1 select adddate(now(),@a:=@a+1) from t1,t1 b limit 998972;

You can see all the other solutions as comments to the previous post.
This post comes earlier than promised, because of an unexpected development. A new quiz is coming. Stay tuned!

Pop quiz: generate 1 million records

This is a quiz that has a aha! solution. Not so trivial, though. It requires some thinking.

Given this table:

create table t1 (
dt datetime not null,
primary key (dt)
);

Task: insert exactly 1 million records in table t1, with the following constraints:

• Use a maximum of 5 (five) SQL statements;
• Use only the MySQL interactive command line client;
• No shell commands;
  
• No loading of scripts;
• No inserts from existing tables in your system. You must assume that t1 is the only table in your entire database;
  
• No MySQL Proxy;
• No stored routines, triggers or events;
• Each statement must be not longer than 75 characters;
• UPDATE. No modification of table t1;
• No LOAD DATA.
  

Prize: fame and fortune (i.e. your name quoted in these columns).
I will publish the solution at the end of the week.

To make sure that I am not cheating, here is the MD5 signature of the solution file that I will publish this week.

fc6d32faf19b5ac1064093a6d7969f7c  solution.txt

If you are paranoid and believe that I can create an arbitrary file and make its contents match with the above MD5 signature, you have until Friday to get the solution from that. :)

Update: To keep the challenge interesting, I won't publish the comments with the right solutions for a few days. If you have sent a comment, don't worry if it does not show up immediately. I will publish it soon before the final solution.

Solutions so far

• Shane. Three lines. Less than 6 seconds. And close to the original solution! (Your third solution is almost the same as the intended one)
  
• Jedy. Three lines! and less than 6 seconds to execute! on top.
• Dipin. Your latest solution (3 lines) tops the list.
• Kai Voigt. one solution with the crowd, and a wicked one like Roland's, but much faster!
  
• Roland Bouman. This is the wickedest solution so far. Not the shortest, but it's the one that is totally different from the intended one (and the other solutions). It will crash most weak servers, though.
• Carsten. In the same league as Kai and Roland for the wicked solution.
  
• Dirk1231. The challenge requires not to use other tables. (Also your second solution does). The third attempt put you finally in the list! Your fourth solution is nice, but not enough to climb to the top.
  
• Sergey Zhuravlev. Excellent! Can you do it without creating a table? (your second solution is very nice and imaginative)
  
• WSchwach. Your solution qualifies as cheating. Altering the given table to insert duplicate records is not a valid solution. Good shot, nonetheless!
• Morgan Tocker. Very creative! That's very good.
• Matthias. Good use of the allotted characters.
  
• Ephes. Not bad. But you are not assuming that t1 is the only table in your system. Will you try without creating tables?
• Hubert. Nice try. One of the elements you mentioned will lead you to the right track. Keep trying.
• Bill Karwin. I did not specify that the numbers should be contiguous, and you took advantage of it! Well done!
• Domas. Good solution. I expected more from you.
• Tobias. Your first solution runs forever. The second one is cheating (using a information_schema table)
• Erik. Nope. No other tables allowed. Not even information_schema tables.
  
• William: Nope. No stored routines allowed. And the instruction to create the routine is longer than 75 characters. Your second solution has a command longer than 75 chars, and it's using other tables, and it does not work either!
  

Some hints

Keep trying, and consider that my intended solution does the following:

• Inserts contiguous records;
• Uses all dates in this century;
  
• Does not insert from ANY table, not even t1;
• Does not use LIMIT;
  
• The total execution time is below 7 seconds.

UPDATE: Solution online!

MySQL University - Introducing Lua for Proxy scripting

Mark your calendars! On Thursday, December 13th at 15:00 CET (14:00 UTC, 09:00 EDT, 06:00 PST) I will host a session of MySQL University, on the topic of Introducing Lua for MySQL Proxy scripting.

For those who missed the previous announcements, MySQL University is a series of online expert lessons that you can join for free and attend from the comfort of your home or office. The slides are provided in either PDF of wiki pages, the audio is an ogg stream, and you can interact with the lecturer via IRC.
If you have heard of MySQL Proxy but haven't got the time of getting involved with it yet, this session is for you. If you were interested but you thought that another scripting language would be too difficult, give this session a chance. Enroll now!

Multiple scripts in MySQL Proxy

MySQL Proxy is being beefed up, to make it ready for prime time. New features are being added, and old ones are improved and tested.
Testing the Proxy is an adventure in its own right. It's a tool that was designed to get in the middle of the scene and change things. Testing its features is more challenging than testing a straightforward application, even more complex like a database server. So the test suite for MySQL Proxy is much more complex and feature rich than the corresponding suite for the database server.
While researching to create the next edition of the test suite, able to cope with more features and corresponding testing requests, I developed a Lua script that enhances the current Proxy features and allows you to load several scripts, and use them all at once.
The script (load-multi.lua) is available in the latest Subversion tree. The usage is not difficult.
From any client, you need to send a query

PLOAD script_name;

The script you indicate will then be available to all the clients.
If your script contains functions that are used at session start (connect_server, read_handshake, read_auth, read_auth_result), they will be available when the next client connects. If your script uses read_query and read_query_result, they will be available immediately.
The mechanics of how this works is simple. You load one or more script containing one or more of the above mentioned functions that hook to the Proxy, and the load-multi module will stack each function in a list. For each hook, the load-multi module loops through the loaded functions, executes each one and tests the result. If the function returns a non null value, then that value is passed back to the Proxy.

This means that if you have two scripts that can handle a particular query, only the first one that has been loaded will get a chance to evaluate the query.
When loading modules, you must check the order in which you are loading them. If you load first a script that handles every query, such as a logging application, subsequent scripts would be just filling memory.
There are more goodies.
Your scripts can use some global functions that load-multi prepares for you. Each of these routines is in the proxy.global namespace, so they are available to all clients and their loaded scripts.
One first useful application that can be plugged to this module is a script that lists the status of loaded functions:

-- show_handlers.lua
function read_query (packet)
if packet:byte() ~= proxy.COM_QUERY then
 return
end
local query = packet:sub(2)
  if query:match('select pload status') then
      local header = { 'module', 'handler' }
      local rows = {}
      for id, lmodule in pairs(proxy.global.handler_status) do
          for i,h in pairs(lmodule) do
              table.insert(rows, { id, h.func } )
          end
      end
      return proxy.global.make_dataset(header,rows)
  end
end

Load this script with PLOAD show_handlers, and any client will be able to get a list of modules, with the function that each one introduced.

pload show_handlers;
+-------------------------------+
| info                          |
+-------------------------------+
| module "show_handlers" loaded |
+-------------------------------+
1 row in set (0.01 sec)

pload auth1;
+-----------------------+
| info                  |
+-----------------------+
| module "auth1" loaded |
+-----------------------+
1 row in set (0.03 sec)

select pload status;
+---------------+-------------------+
| module        | handler           |
+---------------+-------------------+
| show_handlers | read_query        |
| auth1         | read_auth         |
| auth1         | disconnect_client |
| auth1         | read_handshake    |
| auth1         | connect_server    |
| auth1         | read_auth_result  |
+---------------+-------------------+
6 rows in set (0.01 sec)

This new module has the ability of extending its own behavior. If you like playing with new features, you should try this one!

Blocking specific queries

Imagine having a database with 100 tables and you want to allow a user to read from all of it, except one.

But of the table of the knowledge of good and evil, thou shalt not select of it

That's an old problem, with an ugly solution. You must either move the forbidden table to another database or to explicitly authorize the user to read each one of the other 99 tables.

A friend of mine had a similar problem. She has a huge database with thousand of tables, and she wants to prevent the users from issuing a "SHOW TABLES" command. Why? Because, with thousands of tables, the response time of MySQL can easily become very slow. It is a design problem, not easily solvable, and the best course of action here is to deny access to this command.

The bad news is that MySQL has no provision for this kind of restrictions.
The good news is that you can solve this problem with an easy MySQL Proxy script.
And you don't even need to write it. Just download the appropriate script from MySQL Forge and use it.
It's quite easy to customize. The interesting part is in the following lines:

local SHOW_REGEXP       = make_regexp_from_command('show')

queries_to_filter = {
  {
      prefix = SHOW_REGEXP,
      keywords = { 'SHOW', 'TABLES'} ,
  },
}

SHOW_REGEXP is a variable containing a regular expression built from the command you want to consider. For performance reasons, before tokenizing every query, a quick search is pergormed, and only if that search is positive the query is analyzed further. In this case, the script will first check if there is a 'SHOW' at the start of the query. Then it will tokenize the query, and check if all the elements (SHOW and TABLES) are present. The tokenizer can separate literal values from strings. Therefore, if your query contains something like SELECT "SHOW TABLES" as X, it won't block the query.
To make the restriction more robust, there are also rules for 'SHOW TABLE STATUS', 'SELECT FROM INFORMATION_SCHEMA.TABLES", and "EXECUTE" (to prevent smart users from using prepared statements as a workaround).

mysql> show tables;
ERROR 7777 (X7777): command <SHOW TABLES> is  not allowed
mysql> show table status;
ERROR 7777 (X7777): command <SHOW TABLE STATUS> is  not allowed
mysql> select * from information_schema.tables;
ERROR 7777 (X7777): command <SELECT FROM INFORMATION_SCHEMA TABLES> is  not allowed

mysql> select schema_name from information_schema.schemata;
+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

Now, if you want to prevent someone from accessing the good_and_evil table, you can do the following:

local SELECT_REGEXP       = make_regexp_from_command('select')

queries_to_filter = {
  {
      prefix = SELECT_REGEXP,
      keywords = { 'SELECT', 'FROM', 'GOOD_AND_EVIL'} ,
  },
}

And your beloved table becomes taboo.
Try it!

MySQL Users Conference 2008 - CFP is closing soon!

Hurry up!
The call for papers for the Users Conference 2008 is closing on Tuesday, October 30th.
If you haven't submitted a proposal, now it's the time to do it.
Read the unofficial but very effective guidelines offered Baron Schwartz and Colin Charles and write down your well thought abstract
NOW!

OpenFest 2007

The OpenFest 2007 is under way in Sofia, Bulgaria. The event is hosted in an elegant building in Sofia center and it's packed with enthusiastic open source lovers.
The star of the show was Georg Greve president of the EFF Europe. A passionate speaker, he gathered a huge audience just at the start of the event.
Then, during break, the organizers called for an impromptu press conference, where Georg, Jonas Öberg, Erik Josefsson, Domas Mituzas, snd yours truly enjoyed the spotlight for a hour, while asked about recipes to win the ongoing battle between good and evil in the software industry.
I had a session about getting started with MySQL Proxy, which got me a full hall and a great degree of interest. (slides)
After that, Domas had a packed room with standing audience when he presented his talk about Wikipedia.
The evening was closed at a local restaurant where we were treated with free beer, of which everyone took advantage during the ongoing discussion on good and evil.

A very enjoyable experience.

Speaking at the OpenFest - Sofia

I was invited to talk at the OpenFest in Sofia, Bulgaria, and I gladly accepted.
Despite having worked four years in Balkan states, I have never been to Sofia before, and I welcome the chance.
I will present a session about Getting started with MySQL Proxy. The Proxy has advanced a lot since I wrote the getting started article, not only in terms of development (it has!) but also in terms of acceptance and experimentation.
I will show some consolidated usage, and some magic, which is good for advertising.
Since this is my first visit to Bulgaria, I got to learn some words of the local language. And since I was at it, I taught some to the Proxy as well.
How?
I won't anticipate anything, but the Proxy interacts politely in half a dozen languages now. If you are near Sofia, come and see!

Introducing the 15 seconds rule

How fast do you want your installation? Check this.

$ time ./express_install.pl ~/downloads/mysql-5.0.45-osx10.4-i686.tar.gz --no_confirm
unpacking /Users/gmax/downloads/mysql-5.0.45-osx10.4-i686.tar.gz
Executing ./install.pl --basedir=/Users/gmax/downloads/5.0.45 \
[...]
Installing MySQL system tables...
OK
Filling help tables...
OK
[...]
loading grants
sandbox server started
installation options saved to current_options.conf.
To repeat this installation with the same options,
use ./install.pl --conf_file=current_options.conf
----------------------------------------
Your sandbox server was installed in /Users/gmax/msb_5_0_45

real    0m6.773s
user    0m0.245s
sys 0m0.235s

Old times

MySQL has a long established rule of going from downloading to up and running in less than 15 minutes, as stated in various sources, like this interview with David Axmark

So we worked hard to make the installation and first use as easy as possible. We came up with the 15 minutes rule: we wanted a user to be able to have MySQL up and running 15 minutes after he finished downloading it.

With advanced installers like apt and rpm, the installation time can go down to seconds.
All goes well when you want to install or replace the main (or the only) server in your host, but things may get hairy when you want to install a second server. Reasons for having a second server:

• You can't upgrade to a new version, but you need a feature available in a newer release, and you install it as a side server;
• You want to test a problem without affecting the production database;
• You want to test a new version.

For QA and Support engineers, consultants, developers, installing a side server is a common task, and usually a painful one.
To install cleanly a second server on a host, you need to:

1. unpack a binary tarball;
2. create a data directory (in a different location)
3. start the server with a different port and socket, to avoid conflicts with the main server.

If you have tried the above steps, you know that it is not a difficult task, but it is error prone, the commands to issue are long and full of options to remember. And even if you don't make mistakes, starting and stopping the server requires a great deal of attention, and using the right server is a challenge in itself.
Enters MySQL Sandbox a program that creates a side install of a MySQL server in seconds.

MySQL Sandbox

The latest version of MySQL Sandbox is designed for the maximum speed with the minimum preparation from the user. To go from nothing to a fully functional server, you need 3 steps:

1. download the binary tarball from MySQL site, and save it to a directory, say $HOME/downloads.
2. download and expand the Sandbox
3. run this command:

   ./express_install.pl $HOME/downloads/tarball_filename.tar.gz

You will get an output similar to the one seen at the beginning of this post, and within seconds, you will have a side server up and running.

Using the Sandbox

The Sandbox installation comes provided with essential goodies to start, stop, and use the server. By default, it installs in $HOME/msb_x_x_xx, where x_x_xx is the version numeral of the server you have just installed. If you were installing mysql 5.0.45, then you can run

$ ~/msb_5_0_45/use.sh
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45 MySQL Community Server (GPL)

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

mysql [localhost] {msandbox} ((none)) > show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.02 sec)

mysql [localhost] {msandbox} ((none)) >

When you are done, you can stop the server just as easily.

$ ~/msb_5_0_45/stop.sh
/Users/gmax/downloads/5.0.45/bin/mysqladmin --defaults-file=/Users/gmax/msb_5_0_45/my.sandbox.cnf shutdown

Replication

You have seen so far that installing a server took less than 7 seconds. Why I am talking about the 15 seconds rule?
That's what it takes to install a replication system of 1 master + 2 slaves, without any additional setup, no fiddling with configuration files. here goes.

$ time ./set_replication.pl ~/downloads/mysql-5.0.45-osx10.4-i686.tar.gz
installing and starting master
installing slave 1
installing slave 2
starting slave 1
sandbox server started
initializing slave 1
starting slave 2
sandbox server started
initializing slave 2
replication directory installed on /Users/gmax/rsandbox

real    0m14.820s
user    0m0.789s
sys 0m0.745s

That's slightly less than fifteen seconds. And you must consider that each "start" step contains a sleep 3 instruction, to let the server boot properly.

The replication sandbox has a few convenience scripts that let you manage the nodes without thinking of the details. m.sh is the master, s1.sh is the first slave, s2.sh is the second slave, start_all.sh starts all the nodes, and so on. Look. The replication is working!

$ ~/rsandbox/m.sh -e 'create table test.t1(i int)'
$ ~/rsandbox/s1.sh -e 'show tables from test'
+----------------+
| Tables_in_test |
+----------------+
| t1             |
+----------------+
$ ~/rsandbox/s1.sh -e 'show slave status \G' |grep Running
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

Now it's up to you. Try it!

Team exhibition - Baby talking MySQL

At the MySQL Developers Meeting in Heidelberg there were show off sessions where each team had a chance of showing their best to others.
As part of this shut out presentation, I demonstrated some MySQL Proxy magic.

This is a sample session from that presentation.

mysql> select 1 + 1;
+-------------------------------+
| ERROR                         |
+-------------------------------+
| Don't understand these digits |
+-------------------------------+
1 row in set (0.00 sec)

# Hmmm!
# Let's try to make it understand ...

mysql> set sql_mode='english_digits'; # You did not know we could do that. Did you?
Query OK, 42 rows affected (0.01 sec)

mysql> select 1 + 1;
+--------+
| result |
+--------+
| two    |
+--------+
1 row in set (0.00 sec)

# Clever!
# perhaps it understands something more

mysql> select 1 + two;
+--------+
| result |
+--------+
| three  |
+--------+
1 row in set (0.00 sec)

# Ok. What about two numbers?

mysql> select seven + two;
+--------+
| result |
+--------+
| nine   |
+--------+
1 row in set (0.00 sec)

# Very good. Now how about something other than adding numbers?

mysql> select seven * two;
+----------+
| result   |
+----------+
| fourteen |
+----------+
1 row in set (0.00 sec)

# Pushing it a bit ?

mysql> select seven * three;
+-----------------------------------------------+
| result                                        |
+-----------------------------------------------+
| Can't count beyond my fingers and toes. (21?) |
+-----------------------------------------------+
1 row in set (0.00 sec)

# Well, it has some limitations!

As an exercise for the reader, you can figure out the Lua script that made the above exchange possible.