Scary reading advice

Inspired by the Arctic Dolphin's scary movies, I dug out some reading advice that I made during the MySQL Developers meeting in Riga.

Jack Kerouac, On The RoadMap, a fictionalized autobiography of a senior engineer on a cross country bohemian odyssey after failing to deliver 5.1 GA for the fifth time.

Ken Kesey, One flew over the cuckoo's nested query. A story of love and madness involving a quiet Engineering department. When Jeffrey McMurphy, a convicted felon who's faking insanity to escape a prison sentence, is sent to the Database Group asylum, life changes instantly for the other guests.
A subtle tragedy involving a marketing planner, a virgin junior engineer, and a swordfish develops inexorably, leaving the main character with a headache, while the chief of engineering runs away with the bath sink.

Umberto Eco, The name of the release. Young novice Zack of Melk tells the story of his master, Friar Marten of Baskerville, faced with the sudden suicide of his beloved Five-One-Twenty-Two release. The mystery thickens when another release, Five-One-Twenty-Three, is found dead in a puddle of blood, and again when the scribe Five-One-Twenty-Four is poisoned. Three more releases are slaughtered before some light is finally shed on the story, and the murder is revealed, and the surviving release Five-One-Thirty is finally sent to preach in the outside world.

Partitions usability I - blues and a preview

Pop quiz. Look at the picture below. Quick: can you tell me to which year and month partition P001 refers?


I couldn't say it without asking the database itself to revert the output of to_days:

select from_days(723180);
+-------------------+
| from_days(723180) |
+-------------------+
| 1980-01-01        | 
+-------------------+

Just to make the pain more clear, you do something like this, clean and neat, and you think you have done a good job.

CREATE TABLE t1 ( d DATE ) 
PARTITION by range (to_days(d))
(
  partition p001 VALUES LESS THAN (to_days('2001-01-01')) 
, partition p002 VALUES LESS THAN (to_days('2001-02-01')) 
, partition p003 VALUES LESS THAN (to_days('2001-03-01'))
);

And then, what the database server is keeping, is really hard to use.

show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (to_days(d)) (PARTITION p001 VALUES LESS THAN (730851) ENGINE = MyISAM, PARTITION p002 VALUES LESS THAN (730882) ENGINE = MyISAM, PARTITION p003 VALUES LESS THAN (730910) ENGINE = MyISAM) */

If you made a mistake and you look at the code, you will need to convert all the values using FROM_DAYS to make sure that the dates were as intended.
An additional pain is caused by the unbroken long line of partition definition. (Bug#14326). A patch was created, and it should eventually be applied soon.

New features around the corner

Well, not really around the corner, but there is hope that this enhancement will be available soon.
The change is conceptually simple, but momentous for users. You will be allowed to use native data types, without need of converting them to integers.
Here's how the above DDL will be rewritten:

CREATE TABLE t1 ( d DATE ) 
PARTITION by range column_list(d)
(
  partition p001 VALUES LESS THAN (column_list('2001-01-01')) 
, partition p002 VALUES LESS THAN (column_list('2001-02-01')) 
, partition p003 VALUES LESS THAN (column_list('2001-03-01'))
);

SHOW CREATE Table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE  COLUMN_LIST(d) 
(PARTITION p001 VALUES LESS THAN ( COLUMN_LIST('2001-01-01')) ENGINE = MyISAM,
PARTITION p002 VALUES LESS THAN ( COLUMN_LIST('2001-02-01')) ENGINE = MyISAM,
PARTITION p003 VALUES LESS THAN ( COLUMN_LIST('2001-03-01')) ENGINE = MyISAM) */

Not only you will use native data types without conversion, but the partition definition will be human readable. If you made a mistake somewhere, you will find out without need for recalculations.
When is the above marvel going to be available? Now!, with a Launchpad tree. Although I can't say when it will be released. It won't be in 5.1, because it's almost GA. It could be in a quick point release (5.2) or a major release (6.0).
And what about the error prone procedure of creating partitions? If I need to make monthly partitions from 1985 to 2008, doing that manually is going to be a pain, and a source of errors.
Stay tuned. I have a solution handy.
Till next time.

MySQL University - Quick and easy testing with MySQL Sandbox

As announced by Stefan Hinz, our tireless documentation leader, we are using a new system for MySQL University. With DimDim, we can have more fluid and interactive presentations. Unlike the previous homegrown system, you don't need to download the slides, which will be shown to you by the presenter. There is no sound delay, and attendees can interact with the presentation by (integrated) chat and by graphical tools.

Today, October 30, 2008, at 15:00 CET, I will present a session on Quick and easy testing with MySQL Sandbox.
There may be some compatibility problems, depending on your browser and OS of choice. So, please join the presentation a few minutes earlier.
The session will be open at 14:45 CET [Rome] (13:45 UTC [London], 09:45 EDT [New York]).

MySQL 5.1.29 - Last RC - small changes

MySQL 5.1.29 is available. This is the last RC. Yes. You heard me right. The long wait is almost over. The next release will be GA. Sharpen your tools, and get ready to use partitions, row-based replication, and the event scheduler in production without that uneasy sense of guilt that you feel when using non-GA software.

There are a few small changes in MySQL 5.1.29.

SHOW PROFILES

It was already in a preview, but now SHOW PROFILES is available in 5.1 binaries. As you may recall, it is not active by default. It becomes active when the session variable profiling is set.
This feature is going to be very useful when measuring the impact of partitioning or RBR on your queries. I will do some testing soon.

Logging

Log on demand is one of the main features of MySQL 5.1. It means that you can enable and disable general logs and slow query logs without restarting the server. You can also change the log file names, again without a restart.
What was missing was the ability of setting a log file name in the options file, without actually starting the logging. The old option log, could set the general log file name, but it will also start the logging immediately. If you want to set the log file name without activating the logging, now you can use general_log_file=filename or slow_query_log_file=file_name in the options file. These features were already available as system variables, but not as startup options.

Row-base logging safety

From this version, setting the binlog format for a session will require the SUPER privilege.
This change was decided to make it consistent with similar events. If you want to stop or enable the binary log, you can use SET SQL_LOG_BIN=0, but only if you have the SUPER privilege. Both suspending the binary logging and changing its format are affecting replication, and as such, the SUPER privilege is required.

For the complete list of changes affecting MySQL 5.1.29, please look at the manual.

An odd spot in relay slaves

A relay slave is a slave that writes to its binary log the stream of commands received from the master, thus enabling other slaves to replicate. This is often used to relieve the master from heavy traffic created by many slaves asking for binlog events.

A typical scenario is when a master has a few dozen slaves, and instead of dealing with all of them directly, uses four relay slaves, each one dealing with 6 slaves.
So, where's the trick? The trouble comes if you change replication format after you start the slave.
Example. One master (M), two relay slaves (R1, R2), with four slaves each (S1,S2,S3,S4, S5, S6, S7, S8).
You started working in MIXED mode, which was set in the option file of each server.
Now you want to switch to STATEMENT mode for a while.
Then, you connect to each of the 11 servers, one by one, with a script that sends to each one the command SET GLOBAL binlog_format=STATEMENT.
You close all open connections from your clients, and before issuing your command, you again connect to all servers, and run SELECT @@binlog_format to make sure that all servers have the same setup.
Your special query is a tracking command, which inserts @@server_id in a table, and in each server the table should have the server_id of its host;
However, you notice that the propagation happens until the relay slaves. All the leaf nodes have the same values of their master.
What happened? It's something that is somehow documented, but results in an unexpected result. A SET GLOBAL statement is valid for every new connection. Meaning that, if you have an open connection, the global behavior doesn't apply. In our example, we closed all client connections. But there was one hidden connection that was not closed.
The slave SQL thread was already running, and was thus unaffected by the change.
To make the change stick, and thus propagate to all the slaves, we need to start and stop the slave.
I open Bug#40106 about this behavior because, although it's is logically explainable, it goes against the user's expectation. (If I change the binlog format, I expect to affect the binary log. Stopping the slave does not occur naturally. Perhaps this is a question for the certification exam).

Community at work - SIGNAL

Long time ago, I saw a blog post by Jorge Bernal, with a simple implementation of SIGNAL for MySQL stored procedures. If you have ever tried to write MySQL stored procedures, you know how dearly missed is this feature. I discussed this feature internally, and everyone told me "don't bother, we're going to implement SIGNAL in MySQL 6.1". And indeed, the full implementation for SIGNAL and RESIGNAL is in the roadmap.

What does that mean? Should we wait two or three more years to have a SIGNAL feature? Wouldn't it be nice to have a minimal SIGNAL working today, and get the full thing when it's ready?

A bird in the hand ...

I would like to have SIGNAL available today, and therefore I tested Jorge's patch in the latest 5.1.28 code. It needs some more twisting to get it working. If you want to try it, make sure you have the current patch.
Once you apply the patch and compile the code, you will have a server that accepts a simple "SIGNAL" command, without any parameter.
Here's a simple usage example:
First, you create two stored procedures:

delimiter //

drop procedure if exists less_than_10 //
create procedure less_than_10(i int )
deterministic
begin
    if ( i < 10)
    then
        set @comment = concat('a valid number was entered (', i,')');
    else
        set @comment = concat('number too high (', i,')') ;
        SIGNAL ;
    end if;
    select concat('everything fine [',@comment,']')  as comment;
end//

drop procedure if exists trapping //
create procedure trapping ()
deterministic
begin
    declare mystatus varchar(20) default 'ok';
    declare continue handler for SQLSTATE '38503'
        set mystatus = 'ERROR';
    call less_than_10(7);
    select mystatus, @comment;
    call less_than_10(17);
    select mystatus, @comment;
    select 'and this is after the error';
end //

delimiter ;

And then we'll try out the simple one, by calling directly the procedure with a SIGNAL.

call less_than_10(2);
+--------------------------------------------------+
| comment                                          |
+--------------------------------------------------+
| everything fine [a valid number was entered (2)] |
+--------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

select @comment;
+--------------------------------+
| @comment                       |
+--------------------------------+
| a valid number was entered (2) |
+--------------------------------+
1 row in set (0.00 sec)

call less_than_10(12);
ERROR 1340 (38503): Exception generated from user-defined function/procedure

select @comment;
+----------------------+
| @comment             |
+----------------------+
| number too high (12) |
+----------------------+
1 row in set (0.00 sec)

next, we call the procedure that traps the exception. This one has full control of the execution flow, with SIGNAL and HANDLER.

call trapping();
+--------------------------------------------------+
| comment                                          |
+--------------------------------------------------+
| everything fine [a valid number was entered (7)] |
+--------------------------------------------------+
1 row in set (0.00 sec)

+----------+--------------------------------+
| mystatus | @comment                       |
+----------+--------------------------------+
| ok       | a valid number was entered (7) |
+----------+--------------------------------+
1 row in set (0.00 sec)

+----------+----------------------+
| mystatus | @comment             |
+----------+----------------------+
| ERROR    | number too high (17) |
+----------+----------------------+
1 row in set (0.00 sec)

+-----------------------------+
| and this is after the error |
+-----------------------------+
| and this is after the error |
+-----------------------------+
1 row in set (0.00 sec)

This less than 30 lines patch is much better than many hacks that we've seen published for years.
What's your take? Would you like to have this feature in MySQL 5.1, while the full thing is being developed for 6.1?

Preparing for Open SQL Camp

The Open SQL Camp will take place in Charlottesville, VA, USA, on November 14, 15, and 16. Attendees are requested to register in the event's Wiki, and if you are interested in presenting something, there is a mailing list to discuss your intended topics.

I have proposed a topic about the MySQL community driven replication monitoring project, a hackish solution to the old problem of replication monitoring, implemented entirely on the server side, without using system applications.

Using the event scheduler to purge the process list

Two of the most common tasks for database administrators are cleaning the process list from unresponsive queries and remove idle connections that are filling the connection pool. Both tasks are related to poor usage of the database. In a perfect world, users would only run queries designed, tested, and benchmarked by the DBA or the project manager, and the application servers would never allocate more connections than planned.

But users are human, and an unpredictable amount of unplanned events can happen everywhere. When I was consulting, the above cases were quite common.
Before MySQL 5.1, the only method to clean up the process list was by hand, or using a cron job to do it from time to time.
MySQL 5.1 introduces the event scheduler, and now you can run scheduled stored routines without need of external applications.
I created two stored procedures that read the processlist and kill a process if the execution time is longer than 200 seconds or if a process is idle for longer than 200 seconds. The script with the stored routines and the associated events is in MySQL Forge.

drop procedure if exists purge_slow_queries;
drop procedure if exists purge_idle_connections;
drop event if exists auto_purge_slow_queries;
drop event if exists auto_purge_idle_connections;

delimiter //

create procedure purge_idle_connections()
deterministic
begin
   declare done boolean default false;
   declare max_time int default coalesce(@max_kill_time, 200);
   declare pid bigint;
   declare c cursor for
   SELECT id
   FROM information_schema.processlist
   WHERE command in ('Sleep')
        -- add more conditions here
        AND time > max_time;
   declare continue handler for not found
       set done = true;
   open c;
   set @q_kill = 'KILL ?';
   prepare q_kill from @q_kill;
   PURGELOOP: loop
       fetch c into pid;
       if done then
           leave PURGELOOP;
       end if;
       set @pid = pid;
       execute q_kill using @pid;
   end loop;
   deallocate prepare q_kill;
end//

create procedure purge_slow_queries()
deterministic
begin
   declare done boolean default false;
   declare max_time int default coalesce(@max_kill_time, 200);
   declare pid bigint;
   declare c cursor for
   SELECT id
   FROM information_schema.processlist
   WHERE state in ('executing')
        -- add more conditions here
        AND time > max_time;
   declare continue handler for not found
       set done = true;
   open c;
   set @q_kill = 'KILL ?';
   prepare q_kill from @q_kill;
   PURGELOOP: loop
       fetch c into pid;
       if done then
           leave PURGELOOP;
       end if;
       set @pid = pid;
       execute q_kill using @pid;
   end loop;
   deallocate prepare q_kill;
end//

delimiter ;

create event auto_purge_idle_connections
   on schedule every 10 second
   do call purge_idle_connections();

create event auto_purge_slow_queries
   on schedule every 10 second
   do call purge_slow_queries();

Notice that you can disable an idle connection by setting the variable interactive_timeout, but this method allows you to be more precise. For example you can state that only idle connections to a given database should be killed, or only the ones from a given user.

UPDATE It looks like there is prior art in the same department. My colleague Matthew Montgomery beat me to it loooong time ago.

Is the Federated engine useful to you?

Followers of my blogs and talks know that I am partial to the Federated engine. Not much as a normal way of storing data, but more as an auxiliary device to create innovative schemes. A few years ago I wrote an article, Federated: the missing manual where I listed most of the gotchas that you can meet when using the Federated engine. This article alone would be enough to discourage you from using Federated in production. If you attempt to use a Federated table like a normal one, your server will suffer, and possibly crash.

Add the sad fact that the Federated engine is riddled with bugs, and you will agree with the decision of disabling the engine by default in MySQL 5.1.
Indeed, using federated tables as normal ones is a recipe for disaster. Yet, a measured usage of Federated tables can greatly improve your database usage experience.
For example, one of my former customers has had a Federated table in production since 2005. The production database is 4.1. A data warehouse database used 5.0, with a Federated table linking to a production table that grows very quickly. Using a cron job, the federated table copies data regularly from the production table to an archive table, and deletes the records older than three months.
Another usage that I have been experimenting with for a few years is using Federated tables as monitoring devices, with small records being passed around between servers. You can use this scheme to get a quick statistics of what another server is doing, for example by linking the federated table to a well tuned view. Or you can use small federated tables to check the health of a master from a slave and vice versa. I used this system for my MySQL Replication Monitor.
In short, Federated is not to be discarded, but it must be used with extreme caution.
The best course of action would be refactoring the whole engine with a more robust architecture. Until that happen, caution and planning would suffice.

30 terabyte per night

The Large Synoptic Survey Telescope is making news once more. Its projected data inflow of 30 Terabyte per night has caught to the imagination of slashdot readers. Why is this interesting? Because it was not news to me.

You may recall that I was enthusiast about a Astronomy, Petabytes, and MySQL at the MySQL Users Conference 2008, and with reason. The engineers at Stanford have a plan of storing petabytes of data into a cluster of MySQL databases.
The technical session was complemented by an exciting keynote on The Science and Fiction of Petascale Analytics, which closed the conference with a bang.
Hey! that's innovation! Anyone has a massive data storage to tell at the next Users Conference?

Using partitions to create constraints

A devilish quiz by Scott Noyes has made me thinking about a side effect of partitioning.

The quiz

Given a table trickytable with a INT field, explain how these statements and results are possible:

INSERT IGNORE INTO trickyTable (id) VALUES (1);
Query OK, 0 rows affected (0.00 sec)

SELECT * FROM trickyTable;
Empty set (0.00 sec)

There are no blackhole, federated, triggers, proxy.
I initially tried with the event scheduler, with an event that empties the table every second, but the insert results in 1 row to be inserted, while the quiz asked for 0 rows.

After a few minutes, I got it. The right answer was to use partitioning.

Let me show off a bit. :)
Table trickytable was created with partitions by range or list , with a partition that does not include 1.

Either of the following will do the trick:

create table trickytable (id int)
partition by list (id) (partition p1 values in (2));

#or

create table trickytable (id int)
partition by range (id) (partition p1 values less than (1));

If you insert "1" in this table, without the IGNORE keyword, you get an error:

INSERT INTO trickytable (id) VALUES (1);
ERROR 1526 (HY000): Table has no partition for value 1

Using IGNORE means "don't react to errors". This is used, for example, when inserting duplicated values, to have them discarded without triggering errors.
Thus, adding the IGNORE keyword will discard the error, leaving no rows to be inserted. Thus the "0 rows affected" message and the resulting empty table.

Well spot, Scott!

Practical use

Using the same concept, we can use partitioning to enforce a constraint.
For example, let's assume that we have a table with part_number and category_id, and we want only products belonging to categories 10, 25, 40, and 53 to be in this table.
Using a partition by list does the trick nicely:

create table special_products (
 part_no int not null,
 category_id int not null,
 primary key (part_no, category_id)
) engine = InnoDB
partition by list (category_id)
(
 partition p1 values in (10, 25, 40, 53)
);
insert into special_products values (1, 53), (2, 25);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

insert into special_products values (1, 23);
ERROR 1526 (HY000): Table has no partition for value 23

If we want to include category 23 into the list, we can use REORGANIZE PARTITION

alter table special_products
reorganize partition p1 into
(
 partition p1 values in (10, 23, 25, 40, 53)
);

Or we can actually add more partitions

alter table special_products
reorganize partition p1 into
(
 partition p1 values in (10, 23, 25),
 partition p2 values in (40, 53)
);

This is an efficient way of adding a constraint (within the limits of partitioning, which accept only integer values and a fixed set of functions).

My proposals for the Users Conference 2009

In addition to participating to the reviewing committee for the Users Conference 2009, I have also ambitions of being there as a speaker. Thus I am thinking about the topics I could talk about. Being the theme of the conference "innovation everywhere", I will not propose any of my previous talks (as I hope everyone else will, since I will strike all the carbon copy proposals).

I have some ideas already.

A tutorial on partitions

Since I have done much testing and writing on partitioning, I feel the need of presenting something in public. Performance, administration, use cases, are all aspects of partitioning with which I am well acquainted.

Row-based replication

Another 5.1 feature that I have been testing at length. It is perhaps too narrow to deserve a tutorial, but I have material for a couple of good sessions.

The Community Replication Monitor

This is a project that has started several years ago as pure speculation, and it is now being shaped into something that can be used.

Testing with MySQL Sandbox

I have applied many changes to MySQL Sandbox since my last presentation, and it is now time to show some cookbook cases.

Community hacks

many interesting community contributions are entering my radar. While I don't know which ones would make their way to the official release, I would like to show how life could be if we put all these clever additions together.

using Bazaar and Launchpad to develop a small project

Unlike previous presentations about Launchpad and Bazar and MySQL or Drizzle, this is a small project oriented session, where I show how to use Launchpad to plan, develop, and maintain a moderately complex application, with blueprints, revision code system, bug tracking, and forums.

Your comments will be highly appreciated. Especially if you suggest something not in this list (and falls within my abilities, of course!)