A few hacks to simulate mysqldump --ignore-database

A few days ago, Ronald Bradford asked for a mysqldump –ignore-database option.

As a workaround, he proposes:

mysqldump --databases `mysql --skip-column-names \
   -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') \
   FROM information_schema.schemata WHERE schema_name \
   NOT IN ('mysql','performance_schema','information_schema');" \
   >` >/mysql/backup/rds2.sql

It's a clever solution, but unfortunately it only works if you have a handful of schemas. If your databases happens to have several dozens (or hundreds or thousands) of schemas (which is where you need this option more), then the output will be truncated to the length of group_concat_max_len (by default, 1024.)

There are two alternative methods.

The all-shell method

This method lets shell commands filter the wanted databases. In its simplest way, it goes

DATABASE_LIST=$(mysql -NBe 'show schemas' | grep -wv 'mysql\|personnel\|buildings')
mysqldump --all-databases $DATABASE_LIST

Notice that, when you use --all-databases, information_schema and performance_schema are filtered off by default.

This method works, because the default length of the command line in Unix is much longer than group_concat_max_len:

$ getconf ARG_MAX
131072  # Linux

$ getconf ARG_MAX
262144  # Mac OSX

(in Windows it's much shorter: 8191, but since I haven't used Windows for ages, I don't really care).

A more elaborate method would require a scripting wrapper around the above instructions, but I think that as it is, it's simple enough to be remembered.

The options file method

If the length of all your database names combined is more than the maximum allowed by the shell, and all you want is filtering a few databases off a huge list, there is still hope.

Let's assume that you want to ignore mysql, personnel, and buildings from your backup.

echo '[mysqldump]' > mydump.cnf
mysql -NBe "select concat('ignore-table=', table_schema, '.', table_name) \
  from information_schema.tables \
  where table_schema in ('mysql', 'personnel', 'buildings')" \
  >> mydump.cnf

Now the options file looks like this:

[mysqldump]
ignore-table=mysql.db
ignore-table=mysql.host
ignore-table=mysql.user
[...]

What we need to do is tell mysqldump to get its information from this options file, and it will duly skip all the tables that are listed in there.

mysqldump --defaults-file=./mydump.cnf  -u $DBUSER -p$DBPWD --all-databases

There are two drawbacks with this approach:

• There will be a DROP DATABASE IF EXISTS and CREATE DATABASE for each of the excluded schemas, although no tables will be dumped.
• This method only works with --default-options-file. Theoretically, it should also work with --default-extra-file, but it doesn't. Therefore, if you are relying on an options file for connection parameters, they should be added to this file or listed in the command line.

Summing up, there is no perfect solution, but there are enough hacks available that you probably would find what suits you.

Some lessons from MySQL Conference 2012

The Percona Live MySQL Conference and Expo 2012 is over. Together with the SkySQL solutions day, it has kept me occupied for 4 full days, from early morning to late at night.

I have to say that I am pleased. The quality of the organization was very high, with a very good lineup of speakers and an excellent technical support.

As usual, I have learned a lot during this week, either directly, by attending talks, or indirectly, by meeting people who told me what was juicy at the talks that I had missed. And I have met new interesting people, and caught up with the people that I know already.

This conference was particularly intense also because I got myself involved in 5 talks, which was probably more than I should have. How did I end up with such a task? It's a long story.

It all started when the CfP opened. In the review committee, we all knew that Oracle was not eager to participate, but we hoped that it would change its mind and send someone in the end. So we planned ahead, and some of us proposed talks aimed at beginner and intermediate users, with topics that are usually best covered by the people who work at the MySQL team. I proposed Replication 101 and What's new in MySQL 5.5 and 5.6 replication, with the idea that I would hand them over to a couple of Oracle engineers, or have them as co-speakers. That, however, didn't happen. So I had to prepare and present these two talks, in addition to the one that I wanted to do on my own (Testing MySQL creatively in a sandbox).

That makes 3 talks. Then I got tasked with organizing the lightning Talks, which is not a big deal per se, but it adds to the global effort. 4 talks.

And finally, SkySQL organized another beautiful conference on Friday, and I got to present a fifth talk. I enjoyed every bit of them, but boy! the conference was intense!.

I have learned not only from the talks that I have attended, but also from the preparation of my own talks. The biggest source of surprises was my talk about MySQL 5.6 replication. I was expecting a mature release, but I found a collection of features that don't play very well together, and can sometimes lead to an unstable server. Since I was trying to get my demos working, rather than isolating the bugs, I didn't submit any reports, but I will come back to that version and do a more thorough analysis as soon as I catch up with my day-by-day work.

Speaking about demos, it's quite common for me to include a demo in a technical talk. First, because getting a demo done will make me better acquainted with the features that I am presenting, and also because a presentation with a demo conveys the idea of a mature and reliable product (or the idea that I, as the speaker, know what I am talking about). Either way, I know prepare a demo for every talk where I have sufficient time to show one, and sometimes even for a lightning talk. So it was surprising to hear comments that praised my talks because they contain demos. Is this practice so unusual? I should start taking count of how often this is done.

My most satisfactory demo (and the one that almost got me in trouble) happened at the last talk, on Friday, when I had to show features from three different Tungsten topologies, using three separate remote clusters. For these demos to be successful, I needed good internet connection, a solid confidence in the product and the strength of its tests, and to remember the sequence of operations for each demo. To my surprise, everything went so smoothly, that someone in the audience thought that I was running a simulation in my laptop, instead of interacting with servers that were 10,000 Km away. So much for my rehearsals! I must remember to add at least a tiny mistake in an otherwise perfect sequence of tasks, to make the audience aware that I am playing live.

The slides for my presentations are available at Slideshare.

21st century presentation technology at Percona Live

After 15 years of slide show technology, I thought that we need to change the way we do presentations. And since I am advocating radical changes, I will eat my own dog food and be the first to present a MySQL session using 3D technology.

Since watching Avatar a few years ago, I thought that using this technology would make my presentations truly amazing. However, two years ago a 3d projector was prohibitively expensive. Now, instead, it is affordable, and fits in my briefcase!

What I needed, though, was a compelling reason for using 3d vs. traditional presentations. And I found it. As I have mentioned recently, I am working with the coolest replication technology on earth. Explaining this technology is often challenging. While regular replication is easy to represent in slides, star and fan-in topologies are hard to grasp for the average attendees. But with the help of 3d technology, the concept looks easy and reachable.

For this reason, I have convinced my company to invest a few thousand dollars in this technology and I am now ready to replace the regular projector in ballroom "C" with the new machine. Sure I will need to drill a few holes in the floor (BTW, thanks to the San Francisco MySQL User Group for lending me the tools), but the result will be fantastic!

I don't want to spoil the surprise, so no more details will be available until you see the result on Tutorial Day.

Now, let's talk about the logistics. In order to follow a 3d presentation, you need special glasses. Since this is a talk about open source stuff, it seems just right that I tell you How to Make Your Own 3D Glasses, so you won't have to buy them. If you are in a hurry, you can get the quick model (Make Your Own 3D Glasses in 10 Seconds).

For those of you who want the enterprise edition, you can buy very fancy 3D glasses at a friendly price (just $14), following the QR link below.

April talks at Percona and SkySQL events

The second week of April will be quite a busy one

Tuesday, April 10

April 10th is Tutorial day at the Percona Live MySQL Conference and Expo.

On that day, I will present a classic: MySQL Replication 101. This is a topic traditionally presented by a MySQL engineer. However, since Oracle seems not to be eager to send anyone to the conference, I volunteered to the task, and I have let everyone know that, if Oracle change its mind and sends some engineers at the conference, I will happily have one of my former colleagues from the replication team as co-speaker.

Wednesday, April 11

The conference will be in full swing when the regular sessions (and the keynotes!) start. From my side, it is noteworthy the talk about Continuent crown jewels, which I have mentioned recently.

• Edward Archibald : Building a multi-master, multi-region database infrastructure in Amazon EC2

Next on the same day, two of my colleagues will take the podium before it's my turn again.

• Robert Hodges: Be a Data Management Hero with Good Backups!
• Jeff Mace : Build simple and complex replication clusters with Tungsten Replicator
• Giuseppe Maxia: What's new in MySQL 5.5 and 5.6 Replication (another talk where an Oracle engineer co-speaker is welcome!)

Unfortunately, at the same time, there will be a talk that I will miss, but I would love to see:

• Jeff Malek: Building a High-Volume Reporting System on Amazon using MySQL, Tungsten, and Vertica

After my own talk, I will instead go to see

• Tim Callaghan: Creating a benchmark infrastructure that just works

It is not over yet. After the regular schedule, there will be Lightning Talks during the Community Networking Reception.

Thursday, April 12

We will start with two interesting keynotes:

• Mårten Mickos: Making LAMP a Cloud
• Mark Callaghen: What Comes Next

The sessions will start with

• Marco Tusa: Oracle to MySQL migration

Another tough choice in the afternoon. I will be on stage while my colleagues will present on yet another cool technology that I have tested extensively in the past months.

• Giuseppe Maxia: Testing MySQL creatively in a sandbox
• Robert Hodges and Stephane Giron: Testing MySQL creatively in a sandbox Boost Your Replication Throughput with Parallel Apply, Prefetch, and Batching

I will then try to learn something new with

• Jeremy Cole: Gizzard: Scale with Twitter's MySQL sharding framework

Friday, April 13th

This day brings us the MySQL Solutions day sponsored and organized by SkySQL. I will be on stage with Robert Hodges to talk and demo some of the solutions offered by Continuent.

Lightning Talks at Percona Live MySQL Conference and Expo 2012

Several months ago I suggested having lightning talks at Percona Live MySQL Conference and Expo 2012, and I also offered to help.

Then I forgot about that for a while, until I saw the announcement that there was a call for Lightning Talks. Great! I submitted two proposals, and asked my colleagues to do the same, and also encouraged many good speakers I know to submit something.

The deadline for lightning talks submission passed, and I was told that my offer to help had been accepted, and I was in charge of lightning talks! OK. I would have preferred being told before the CfP, but an offer to help is an offer to help, and thus I went through the motions of evaluating the talks, sending notices to the winners, consoling the losers, and giving hope to the few brave ones who will replace the winners if they don't show up.

The talks that you will see at the conference are in the Lightning Talks page.

Lightning talks are fun and instructional micro events. Their official purpose is to give the audience a chance to learn something in a very limited amount of time. The real purpose is for the speaker to be as entertaining and memorable as possible within the allocated time.

Here are the official rules:

1. All slides will be loaded into a single computer, to minimize delays between talks
2. All speakers will meet 15 minutes before the start, and be given the presentation order. Missing speakers will be replaced by reserve speakers
3. The speaker will have 5 minutes to deliver the talk.
4. When one minute is left, there will be a light sound to remind of the remaining time.
5. When 10 seconds are left, most likely the audience will start chanting the countdown.
6. when the time is finished, the speaker must leave the place to the next one.

For this to be real fun, there must be some cooperation from the audience. Rule #5 is often a spontaneous behavior from the crowd. It's very effective to make the speaker hurry up and close.

If rule #6 were to be enforced in style, there would be a tele-transporter that is triggered at the last second, and the too-slow speaker is instantly moved to the parking lot. My contact at the Star Trek labs tells me that the appliance is not available yet. We'll see if there is an app for that …

MySQL 5.6 too verbose when creating data directory

When I install a MySQL package using MySQL Sandbox, if everything goes smoothly, I get an informative message on standard output, and I keep working.

This is OK

$HOME/opt/mysql/5.5.15/scripts/mysql_install_db --no-defaults \
  --user=$USER --basedir=$HOME/opt/mysql/5.5.15 \
  --datadir=$HOME/sandboxes/msb_5_5_15/data \
  --lower_case_table_names=2
Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/Users/gmax/opt/mysql/5.5.15/bin/mysqladmin -u root password 'new-password'
/Users/gmax/opt/mysql/5.5.15/bin/mysqladmin -u root -h gmac4.local password 'new-password'

Alternatively you can run:
/Users/gmax/opt/mysql/5.5.15/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /Users/gmax/opt/mysql/5.5.15 ; /Users/gmax/opt/mysql/5.5.15/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /Users/gmax/opt/mysql/5.5.15/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /Users/gmax/opt/mysql/5.5.15/scripts/mysqlbug script!

I can actually suppress this output, confident that, if something goes wrong, the error comes to my screen loud and clear. For example, if I try to install to a data directory that is write protected, I get this:

chmod 444 $HOME/sandboxes/msb_5_5_15/data
$HOME/opt/mysql/5.5.15/scripts/mysql_install_db --no-defaults \
  --user=$USER --basedir=$HOME/opt/mysql/5.5.15 \
  --datadir=$HOME/sandboxes/msb_5_5_15/data \
  --lower_case_table_names=2  > /dev/null

mkdir: /Users/gmax/sandboxes/msb_5_5_15/data: Permission denied
chmod: /Users/gmax/sandboxes/msb_5_5_15/data: Permission denied
chown: /Users/gmax/sandboxes/msb_5_5_15/data: Permission denied

This, way, I know that there was an error, it is very clear and readable. I don't need to hunt it down within the regular messages. The standard error is a separate file descriptor, which can be read independently from the standard input.

After fixing permissions:

chmod 755 ~/sandboxes/msb_5_5_15/
$HOME/opt/mysql/5.5.15/scripts/mysql_install_db --no-defaults \
  --user=$USER --basedir=$HOME/opt/mysql/5.5.15 \
  --datadir=$HOME/sandboxes/msb_5_5_15/data \
  --lower_case_table_names=2  > /dev/null
# empty line: means all OK

This is very convenient, and it is the Unix way.

This is not OK

Now, let's try the same with MySQL 5.6

$BASEDIR/scripts/mysql_install_db --no-defaults --user=tungsten \
  --basedir=$BASEDIR --datadir=/home/tungsten/sandboxes/msb_5_6_4/data \
  --tmpdir=/home/tungsten/sandboxes/msb_5_6_4/tmp  > /dev/null
120318 10:10:44 InnoDB: The InnoDB memory heap is disabled
120318 10:10:44 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120318 10:10:44 InnoDB: Compressed tables use zlib 1.2.3
120318 10:10:44 InnoDB: Using Linux native AIO
120318 10:10:44 InnoDB: CPU supports crc32 instructions
120318 10:10:44 InnoDB: Initializing buffer pool, size = 128.0M
120318 10:10:44 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
120318 10:10:44 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
120318 10:10:44 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
120318 10:10:44 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
120318 10:10:44 InnoDB: 128 rollback segment(s) are active.
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
120318 10:10:44 InnoDB: 1.2.4 started; log sequence number 0
120318 10:10:44 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
120318 10:10:44 [Warning] Error while checking replication metadata. Setting the requested repository in order to give users the chance to fix the problem and restart the server. If this is a live upgrade please consider using mysql_upgrade to fix the problem.
120318 10:10:44 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
120318 10:10:44 [Warning] Error while checking replication metadata. Setting the requested repository in order to give users the chance to fix the problem and restart the server. If this is a live upgrade please consider using mysql_upgrade to fix the problem.
120318 10:10:44 [Note] Binlog end
120318 10:10:44 [Note] Shutting down plugin 'partition'
120318 10:10:44 [Note] Shutting down plugin 'ARCHIVE'
120318 10:10:44 [Note] Shutting down plugin 'BLACKHOLE'
120318 10:10:44 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_DELETED'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_METRICS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_CMPMEM'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_CMP_RESET'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_CMP'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_LOCKS'
120318 10:10:44 [Note] Shutting down plugin 'INNODB_TRX'
120318 10:10:44 [Note] Shutting down plugin 'InnoDB'
120318 10:10:44  InnoDB: FTS optimize thread exiting.
120318 10:10:44  InnoDB: Starting shutdown...
120318 10:10:46 InnoDB: Shutdown completed; log sequence number 1602841
120318 10:10:46 [Note] Shutting down plugin 'CSV'
120318 10:10:46 [Note] Shutting down plugin 'MEMORY'
120318 10:10:46 [Note] Shutting down plugin 'MyISAM'
120318 10:10:46 [Note] Shutting down plugin 'MRG_MYISAM'
120318 10:10:46 [Note] Shutting down plugin 'mysql_old_password'
120318 10:10:46 [Note] Shutting down plugin 'mysql_native_password'
120318 10:10:46 [Note] Shutting down plugin 'binlog'
120318 10:10:46 InnoDB: The InnoDB memory heap is disabled
120318 10:10:46 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120318 10:10:46 InnoDB: Compressed tables use zlib 1.2.3
120318 10:10:46 InnoDB: Using Linux native AIO
120318 10:10:46 InnoDB: CPU supports crc32 instructions
120318 10:10:46 InnoDB: Initializing buffer pool, size = 128.0M
120318 10:10:46 InnoDB: Completed initialization of buffer pool
120318 10:10:46 InnoDB: highest supported file format is Barracuda.
120318 10:10:46 InnoDB: 128 rollback segment(s) are active.
120318 10:10:46 InnoDB: Waiting for the background threads to start
120318 10:10:47 InnoDB: 1.2.4 started; log sequence number 1602841
120318 10:10:47 [Note] Binlog end
120318 10:10:47 [Note] Shutting down plugin 'partition'
120318 10:10:47 [Note] Shutting down plugin 'ARCHIVE'
120318 10:10:47 [Note] Shutting down plugin 'BLACKHOLE'
120318 10:10:47 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_FIELDS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_INDEXES'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_SYS_TABLES'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_CONFIG'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_DELETED'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_INSERTED'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_METRICS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_CMPMEM'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_CMP_RESET'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_CMP'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_LOCK_WAITS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_LOCKS'
120318 10:10:47 [Note] Shutting down plugin 'INNODB_TRX'
120318 10:10:47 [Note] Shutting down plugin 'InnoDB'
120318 10:10:47  InnoDB: FTS optimize thread exiting.
120318 10:10:47  InnoDB: Starting shutdown...
120318 10:10:49 InnoDB: Shutdown completed; log sequence number 1602851
120318 10:10:49 [Note] Shutting down plugin 'CSV'
120318 10:10:49 [Note] Shutting down plugin 'MEMORY'
120318 10:10:49 [Note] Shutting down plugin 'MyISAM'
120318 10:10:49 [Note] Shutting down plugin 'MRG_MYISAM'
120318 10:10:49 [Note] Shutting down plugin 'mysql_old_password'
120318 10:10:49 [Note] Shutting down plugin 'mysql_native_password'
120318 10:10:49 [Note] Shutting down plugin 'binlog'

Why is this bad? Because you don't see at a glance what is right and what is wrong. All the above messages are printed to the standard error, the kind of output that should be reserved for, well, errors! If the standard error is used for regular messages, you may miss the important error messages that are instead mixed with the "all is OK" messages.

There is Bug#60934 filed about this issue, but it has been considered a feature request, and as such unlikely to be fixed.

In the above text there is something more. There are warnings, mixed with the standard text, telling me of errors that the bootstrap operation is not in a position to fix, like replication metadata and slave_master_info.

MySQL developers, please fix this issue. Users need error messages when there is something wrong, and warning or error messages about something that can actually be fixed. When MySQL 5.6 goes GA, this issue will hit most everybody.

MySQL Sandbox at the OTN MySQL Developers day in Paris, March 21st

On March 21st I will be in Paris, to attend the OTN MySQL Developers Day. Oracle is organizing these events all over the world, and although the majority are in the US, some of them are touching the good old European continent. Previous events were an all-Oracle show. Recently, the MySQL Community team has been asking for cooperation from the community, and in such capacity I am also presenting at the event, on the topic of testing early releases of MySQL in a sandbox. Of course, this is one of my favorite topics, but it is quite appropriate in this period, when Oracle has released a whole lot of preview features in its MySQL Labs. Which is another favorite topic of mine, since I was the one who insisted for having the Labs when I was working in the community team. It's nice to see that the labs are still in place, and being put to good use.

Speaking of sandboxes, I was making some quick tests yesterday, and I installed 15 sandboxes at once (all different versions, from 5.0.91 to 5.6.5). Installing a single sandbox, depending on the version, takes from 5 to 19 seconds. Do you know how long it takes to install 15 sandboxes, completely, from tarball to working conditions? It takes 19 seconds. How's so? It's because I have been working at a large project where we are dealing with many replicated clusters spread across three continents. Administering these clusters is a problem in itself, and so we are using tools to do our work in parallel. At the same time, using a host with a fast 16 core CPU I can install many sandboxes at once. It's a real joy to see software behaving efficiently the way it should! It works so fast, in fact, that I found a race condition bug. If you install more than one sandbox at once, the MySQL bootstrap process may try to open the same temporary file from two different servers. That's because I did not indicate a dedicated temporary directory for the bootstrap (I was using one only for the installed sandbox). When this happens, you may find that instead of 15 sandboxes you have installed only 9 or 11. So I fixed the bug, by adding --tmpdir to mysql_install_db, and now you can install more than one sandbox in parallel.

Bringing fresh blood to the MySQL Council

The MySQL Council has been operational for more than one year, with mixed fortunes. As an early member, I am glad to be a member, and I feel that I have done something useful by participating in this organism. The council is young, and the MySQL community is variegated and sparse. The enthusiasm of the first volunteers must be reinforced by the injection of new members, who can contribute fresh views and keep the council on their toes. I am glad to see more names coming up, and, as some of my fellow council members, I volunteered to serve for another year. But this doesn't need to be my decision. I will stay unless we get feedback on the contrary, and the same will be for the other people in the list below. ⁽¹⁾ Please use the feedback form to voice your ideas and suggestions about the new council. Anything goes: who should be or not be there, what you would like the council to do, and how the next council should be created.

First Name	Last Name	Title	Company	Country
Alexandre	Almeida	CTO	HTI Tecnologia	Brazil
Sheeri	Cabral	DBA/Architect	Mozilla	United States
Laine	Campbell	Principal and CEO	PalominoDB	United States
Patrick	Galbraith	Senior Engineer Cloud Data Services	Hewlett Packard	United States
Bradley	Kuszmaul	Chief Architect	Tokutek	United States
Giuseppe	Maxia	Director of Quality Assurance	Continuent	Italy
Sarah	Novotny	CIO	Meteor Entertainment	United States
Marco	Tusa	Cluster Technical Leader	Pythian	Canada
Daniel	van Eeden	Consultant	Snow B.V.	Netherlands

The deadline for feedback is March 23, 2012. Let your voice be heard!

⁽¹⁾ Just for the record: all the people in this list have volunteered, in answer to a public call for candidates.

Cool technology and usability in Tungsten Enterprise

When I joined Continuent, at the end of 2010, I was fascinated by the technology of its core products. Readers of this blog know that I have had my hands full with Tungsten Replicator, but what really turned me on was the flagship management suite, Tungsten Enterprise. After hammering at it for several months, and always marveling at the beauty of its technology, let me give a tour of the suite, so that you'll understand what's so exciting about it. First off, Tungsten Enterprise is not simply a replication tool. It is based on replication, but it is mostly a data management suite. Its aim is to reduce complexity for the user and to show a database cluster to the user as if it were a single server, always on, no matter what happens. The most amazing things that you will see in Tungsten Enterprise are

• Automatic failover
• Cluster console, and One-Command-Operations
• transparent connections
• No VIPs !!!
• Multi site switch and failover

Automatic failover

This is probably the most amazing feature of all. It is a combination of the same efficient replication technology seen in Tungsten Replicator, which uses a global transaction ID to allow a seamless failover, and a management system, made of components that communicate to each other and can replace a failed master within seconds, even under heavy load. All this, without the application having more trouble than a few seconds delay (see transparent connections below). This feature is customizable. If the manager is in "automatic" mode, it will replace a failed master without manual intervention, and it will try to put online every element that goes offline. In "manual" mode, however, it will let the user take control of operations as needed.

Cluster Console, and One-Command-Operations

Tungsten Enterprise comes with a text-based console that gives immediate access to the cluster information, and lets the users perform maintenance without troubling them with the inner knowledge necessary to perform the tasks. Promoting a slave to master (a planned "switch", as opposed to an unplanned "failover") is just one command, even though behind the scenes the Tungsten Manager runs a dozen commands to complete the task safely. Backup and restore are also one command. And so are all the dozens of administrative tasks that the Tungsten Manager allows the user. The console comes with a comprehensive help that explains all commands in detail. The console allows the DBA to perform operations in any text terminal, without additional components such as a desktop application or a web interface.

Transparent connections

The suite includes a component called Tungsten Connector, which is a sort of high performance proxy between the application and the database. Instead of connecting your applications to the DBMS, you connect it to a Tungsten Connector, which looks and feels as a MySQl (or PostgreSQL) database. The difference is that, when the master changes, the connector will get notified by the Tungsten Manager and immediately re-routes the underlying connections to the appropriate server. Depending on how smart is your application, you can use the Tungsten Connector in two ways:

• Static routing mode: You create one (or more) connector that will always bring you to the master, and use that connection whenever your application needs to write. And you also create one or more connectors that will always give you access to a slave, and use this one whenever your application needs to read.
• Smart mode: you ask the connector to detect what you are doing and direct your queries to the appropriate server. This mode sends all transactions and updates to the master, and every read query that is not inside a transaction to the slaves. This mode can also guarantee data consistency, by directing the reading of a just saved record to a slave that has already received that record.

The connector can also do more interesting and even amazing things, such as showing you its status as a SQL query (what fun in being a proxy if you don't take advantage of it?) and allowing on-the-fly changes of policy for an existing connector using command line or SQL parameters. The connector plays well with well designed applications, such as the ones that retry a failed transaction rather than failing, and the ones that are replication-aware and can split reads and writes between connections. But it also plays well with applications that have been designed for a single server, without scalability in mind. In most cases, you replace a single server with a cluster of Tungsten Enterprise, and you are in business.

No VIPs !!!

The failover and switch features are not new in the replication arena. There are tools that do something similar and keep an application connected to the same IP using virtual IPs. I don't like virtual IPs, as they are dumb stateless components between two stateful elements, and I am not the only one who dislikes them (See Virtual IP Addresses and Their Discontents for Database Availability). Using Tungsten Connector instead of a dumb virtual IP makes life so much easier. When you do a failover with a VIP, quite often the application hangs, as the client doesn't detect that the server on the other side has gone away, and thus your failover technology has to somehow identify the hanging connections and cut them: a very painful experience. Instead, the Tungsten Connector will either kill the connection immediately or reroute your query, depending on the needs, and your application doesn't get more than a hiccup.

Multi site switch and failover

A recent addition to the suite is the ability of handling whole sites as single servers. The suite can create and maintain a so called composite data service, which is a cluster that is seen and treated as a single server. in a disaster recovery scenario, you want to have a functioning site in one location and a relay site in another location, ready to take over when sudden disaster strikes. Here's an example of what you can get:

cctrl -multi -expert 
Tungsten Enterprise 1.5.0 build 426
sjc: session established
[LOGICAL:EXPERT] / > ls
+----------------------------------------------------------------------------+
|DATA SERVICES:                                                              |
+----------------------------------------------------------------------------+
great_company
nyc
sjc

[LOGICAL:EXPERT] / > use great_company
[LOGICAL:EXPERT] /great_company > ls

COORDINATOR[qa.tx6.continuent.com:AUTOMATIC:ONLINE]

DATASOURCES:
+----------------------------------------------------------------------------+
|nyc(composite master:ONLINE)                                                |
|STATUS [OK] [2012/03/05 10:59:28 PM CET]                                    |
+----------------------------------------------------------------------------+

+----------------------------------------------------------------------------+
|sjc(composite slave:ONLINE)                                                 |
|STATUS [OK] [2012/03/05 10:59:30 PM CET]                                    |
+----------------------------------------------------------------------------+

In this scenario, there is a data service called "great_company", which contains two sites that loom like regular servers. Inside each site, there is a cluster, which we can examine at will:

[LOGICAL:EXPERT] /great_company > use nyc
nyc: session established
[LOGICAL:EXPERT] /nyc > ls

COORDINATOR[qa.tx2.continuent.com:AUTOMATIC:ONLINE]

DATASOURCES:
+----------------------------------------------------------------------------+
|qa.tx1.continuent.com(master:ONLINE, progress=1397, THL latency=0.857)      |
|STATUS [OK] [2012/03/05 10:58:42 PM CET]                                    |
+----------------------------------------------------------------------------+
|  MANAGER(state=ONLINE)                                                     |
|  REPLICATOR(role=master, state=ONLINE)                                     |
|  DATASERVER(state=ONLINE)                                                  |
|  CONNECTIONS(created=54, active=0)                                         |
+----------------------------------------------------------------------------+

+----------------------------------------------------------------------------+
|qa.tx2.continuent.com(slave:ONLINE, progress=1397, latency=0.000)           |
|STATUS [OK] [2012/03/05 11:02:19 PM CET]                                    |
+----------------------------------------------------------------------------+
|  MANAGER(state=ONLINE)                                                     |
|  REPLICATOR(role=slave, master=qa.tx1.continuent.com, state=ONLINE)        |
|  DATASERVER(state=ONLINE)                                                  |
|  CONNECTIONS(created=0, active=0)                                          |
+----------------------------------------------------------------------------+

+----------------------------------------------------------------------------+
|qa.tx3.continuent.com(slave:ONLINE, progress=1397, latency=0.000)           |
|STATUS [OK] [2012/03/05 10:58:31 PM CET]                                    |
+----------------------------------------------------------------------------+
|  MANAGER(state=ONLINE)                                                     |
|  REPLICATOR(role=slave, master=qa.tx1.continuent.com, state=ONLINE)        |
|  DATASERVER(state=ONLINE)                                                  |
|  CONNECTIONS(created=0, active=0)                                          |
+----------------------------------------------------------------------------+

There is a master and two slaves. For each server, we can see the vitals at a glance. The relay site offers a similar view, with the distinction that, instead of a master, there is a relay server. All changes coming from the master in the main site will also go to the relay server, and from that to the slaves in the second site.

[LOGICAL:EXPERT] /nyc > use sjc 
[LOGICAL:EXPERT] /sjc > ls

COORDINATOR[qa.tx6.continuent.com:AUTOMATIC:ONLINE]

DATASOURCES:
+----------------------------------------------------------------------------+
|qa.tx6.continuent.com(relay:ONLINE, progress=1397, THL latency=4.456)       |
|STATUS [OK] [2012/03/05 10:58:32 PM CET]                                    |
+----------------------------------------------------------------------------+
|  MANAGER(state=ONLINE)                                                     |
|  REPLICATOR(role=relay, master=qa.tx1.continuent.com, state=ONLINE)        |
|  DATASERVER(state=ONLINE)                                                  |
|  CONNECTIONS(created=0, active=0)                                          |
+----------------------------------------------------------------------------+

+----------------------------------------------------------------------------+
|qa.tx7.continuent.com(slave:ONLINE, progress=1397, latency=0.000)           |
|STATUS [OK] [2012/03/05 10:59:03 PM CET]                                    |
+----------------------------------------------------------------------------+
|  MANAGER(state=ONLINE)                                                     |
|  REPLICATOR(role=slave, master=qa.tx6.continuent.com, state=ONLINE)        |
|  DATASERVER(state=ONLINE)                                                  |
|  CONNECTIONS(created=0, active=0)                                          |
+----------------------------------------------------------------------------+

+----------------------------------------------------------------------------+
|qa.tx8.continuent.com(slave:ONLINE, progress=1397, latency=0.000)           |
|STATUS [OK] [2012/03/05 10:58:30 PM CET]                                    |
+----------------------------------------------------------------------------+
|  MANAGER(state=ONLINE)                                                     |
|  REPLICATOR(role=slave, master=qa.tx6.continuent.com, state=ONLINE)        |
|  DATASERVER(state=ONLINE)                                                  |
|  CONNECTIONS(created=0, active=0)                                          |
+----------------------------------------------------------------------------+

If you need to bring the relay site as the main one, all you need to do is to run a switch command:

[LOGICAL:EXPERT] /sjc > use great_company 
[LOGICAL:EXPERT] /great_company > switch
SELECTED SLAVE: 'sjc@great_company'
FLUSHING TRANSACTIONS THROUGH 'qa.tx1.continuent.com@nyc'
PUT THE NEW MASTER 'sjc@great_company' ONLINE
PUT THE PRIOR MASTER 'nyc@great_company' ONLINE AS A SLAVE
SWITCH TO 'sjc@great_company' WAS SUCCESSFUL
[LOGICAL:EXPERT] /great_company > ls

COORDINATOR[qa.tx6.continuent.com:AUTOMATIC:ONLINE]

DATASOURCES:
+----------------------------------------------------------------------------+
|nyc(composite slave:ONLINE)                                                 |
|STATUS [OK] [2012/03/06 09:44:48 AM CET]                                    |
+----------------------------------------------------------------------------+

+----------------------------------------------------------------------------+
|sjc(composite master:ONLINE)                                                |
|STATUS [OK] [2012/03/06 09:44:47 AM CET]                                    |
+----------------------------------------------------------------------------+

If disaster strikes, instead of "switch" you say failover, and then use the relay site transparently. Did I mention that Tungsten Connector can be configured to use a composite data service transparently? It can, and if you switch your operations from one Coast to another, the applications will follow suit without any manual intervention. It's so cool! I am sure any geek must love it! BTW: this is not a toy application. This suite is handling data centers that are huge by any standard you care to use, with 100+ terabyte moved through this technology.

Usability

The experience gained with the installer for Tungsten Replicator has been very useful for the whole team. Using the same technology, we have now created a more advanced and simpler installation tool, which is summarized in the Tungsten Enterprise Cookbook. Installing a complex cluster has never been easier!

Tungsten 2.0.5 with more power and ease of use

Tungsten Replicator 2.0.5 was released this week-end. The release notes have quite a long list of bug fixes. Thanks to all the ones who have submitted bug reports, and fixes! There are a couple of new features as well. The replicator includes now a slave prefetch service. Unlike parallel replication, this feature works fine with a single database, and provides performance improvements that in many cases solve the slave lagging problems. This was a bitch of a feature to get right. Many have tried it, many have experienced various degrees of success, and several failures. We started with the bold assertiveness of the brave after an exciting talk at Percona Live in October, and I was sorry to report one bad performance result after the other for a few months, until finally the tide turned, and the good results started showing up, and improving! The key to success was the realization that the prefetch is hard to set up and tune right, but also the need for multiple threads that do the pre-fetching efficiently. Since we had already an efficient engine that we use for parallel replication, the final design started bearing fruits at the end of January, and became definitely good and reliable in February. The other noteworthy improvements were made in the installer. Thanks to the many users who have tried it and reported usability issues, we have made the Tungsten Replicator installation a much better experience, and a powerful tool. The best proof of the installer maturity is that the prefetch installation required little work to be implemented and it worked flawlessly at the first attempt! Other improvements in the tools include trepctl and thl better understanding of their environment. They no longer require a service name if there is only one installed in a given host, and they provide more instrumentation for parallel replication, pre-fetching, and for the processing of huge transactions (quite common when dealing with RBR). This version was also the first with Oracle to MySQL support. This is not open source, however. As this feature requires substantial investments, it is not possible to release it as the rest of the replicator. But the list of goodies is not over yet. The feature that probably more than anything else has been used in the past months has been the star schema topology, which is something that was probably possible in 2.0.4, but nobody had tried it before.

We are not stopping here, however. The investment in the installer has given us the know-how necessary to improve and simplify the installation of our flagship product (Tungsten Enterprise) which is about to ship with similar usability enhancements. We have plans to enhance multiple master replication and management, we are developing powerful parallel processing administration tools, and we are also trying to simplify the powerful filters that Tungsten provides. There are more open source releases to discuss, but these will require more than one article to be described conveniently. We have released more tools in the Tungsten Toolbox project. A better Tungsten Sandbox, capable of installing every technology, and some more ancillary tools for Tungsten. I will come back to those in the near future. Much as I like coding, I also like talking about the cool things that we have made. And, another thing that kept me busy and happy: Continuent and SkySQl are now partners. This has given me quite a lot of work, since we had deliver training to SkySQL field operatives. It was a beautiful experience (teaching to a class of advanced users always is) also because most of the attendees were my former colleagues at MySQL AB. The future looks good. More to come.

More Oracle ACEs for MySQL

As Keith announced today, there are two more Oracle ACE Directors for MySQL expertise. In case you are wondering how an ACE Director compares to a regular ACE, here is an overview and some FAQ.

MySQL and Friends schedule at FOSDEM 2012

The MySQL DevRoom at FOSDEM is ready. The schedule has been voted. Thanks to all who have participated. Now, let's make sure that the event is successful. The schedule is juicy, and not only because I have three talks in it!

Sunday 2012-02-05
Event	Speaker	Room	When
All you need to know about migrations and you never dared to ask	Ralf Gebhardt	H.1309	09:05-09:30
Sphinx User stories	Stéphane Varoqui	H.1309	09:35-10:00
MySQL HA reloaded - old tricks and cool new tools to guarantee high availability to your MySQL Servers	Ivan Zoratti	H.1309	10:00-10:25
MariaDB 5.3's query optimizer: taking the dolphin to where he's never been before	Sergey Petrunya	H.1309	10:30-10:55
How to offload MySQL server with Sphinx	Vladimir Fedorkov	H.1309	11:00-11:25
** Build simple and complex replication clusters with Tungsten Replicator	Giuseppe Maxia	H.1309	11:30-11:55
Cluster internals	Ralf Gebhardt	H.1309	12:00-12:25
Optimising SQL applications by using client side tools	Mark Riddoch	H.1309	12:30-12:55
** MySQL Replication 101	Giuseppe Maxia	H.1309	13:00-13:25
Choosing Hardware for MySQL	Kenny Gryp	H.1309	13:30-13:55
Replication features of 2011: what they were, how to get and how to use them	Sergey Petrunya	H.1309	14:00-14:25
** MySQL creatively in a sandbox	Giuseppe Maxia	H.1309	14:30-14:55
Case Study: La Poste - Real Time, High Volume Data Warehousing Using MySQL & InfiniDB	Stéphane Varoqui	H.1309	15:00-15:25
Sphinx performance top secret	Vladimir Fedorkov	H.1309	15:30-15:55
Managing MySQL with Percona Toolkit	Frédéric Descamps	H.1309	16:00-16:25
Data Warehousing with MySQL	Ivan Zoratti	H.1309	16:30-16:55

UPDATE The schedule has changed. Speakers with more than one talk have been asked to give up one. Now I have two talks instead of three.

Time to vote for MySQL sessions at FOSDEM

There is a room dedicated to MySQL at FOSDEM 2012. (Thanks to @lefred for organizing). The CfP has received 37 submissions, but there will be time slots only for 12 to 15 talks. So now it's up to the community. If you want to attend a particular talk, you should vote for it. Like in previous years, the selection of the talks is public. You can see the list of the proposals, with the instructions, which I repeat here. You can vote either publicly, using Twitter, or privately, by sending an email. Each talk proposal will be referred by the number immediately after the title in this page. This number indicates the order in which the proposals were received. In public, you should send a tweet to @opensqlcamp, indicating a maximum of 12 talks that you would like to see, in the order you like them. e.g. "@opensqlcamp #FOSDEM2012 1,2,3,4,5,6,7,8,9,10,11,12 http://bit.ly/mysql_fosdem_2012" (adding the link will help others to find the page. In private, by email at mysqlfriends AT gmail DOT com, using the same method used for Twitter. Maximum 12 talks, in the order of your preference. In both cases, votes for your preferences will result in 1 point for each talk. In case of equal voting, we will assign 12 points to the first in the list, 11 to the second, and so on. We'll do the tally, and choose the most popular ones. Anonymous votes either by Twitter or email won't be counted. If you want your vote to count, make sure your twitter account has a recognized name (or known nick) on it. If your email address doesn't spell your name, please sign the message with your real one. DEADLINE: Your votes must be entered by January 8th, 2011.

Testing new builds with MySQL-Sandbox 3.0.24

MySQL::Sandbox 3.0.24 was released yesterday, with many new features.

More than vanilla MySQL

If you have missed my previous announcement, here's the gist of it. MySQL Sandbox can now deal with tarballs from either Percona Server or MariaDB. The main difference after this change is that you can now create a directory called <PREFIX>5.5.16 and make_sandbox will recognize it as well as the plain 5.5.16.

$ make_sandbox --export_binaries --add_prefix=ps \
   Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz  \
   -- --sandbox_directory=msb_ps5_5_11
unpacking Percona-Server-5.5.11-rel20.2-114.Darwin.i386.tar.gz
[…]
installing with the following parameters:
upper_directory                = /Users/gmax/sandboxes
sandbox_directory              = msb_ps5_5_11
[…]
basedir                        = $HOME/opt/mysql/ps5.5.11
tmpdir                         = 
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_ps5_5_11

After the binary export, subsequent installations will be easier:

$ make_sandbox ps5.5.11

The same commands can be used for MariaDB. At the moment, make_sandbox does not recognize other packages, but adding them should not be a big deal, provided that such packages look like MySQL. It wouldn't work with Drizzle, because it lacks the main ingredients for MySQL installation.

High Performance sandboxes

While testing parallel replication and prefetch slaves with Tungsten Replicator, I realized that I was doing too much manual fiddling with my scripts. Since I need more performant servers, I added the basic items that I need to modify to enable a faster server. Now, using the '--high_performance' option with make_sandbox, you get a server that is much better than out-of-the-box MySQL. To avoid problems with too much RAM, I am using a default of 512 MB for InnoDB, which is not enough for really demanding tests, but at least it is a good placeholder in the sandbox configuration file, should you need to modify it.

$ make_sandbox 5.1.60 -- --high_performance
[…]
innodb-flush-method=O_DIRECT ; \
innodb-log-file-size=50M ; \
innodb_buffer_pool_size=512M ; \
max_allowed_packet=48M ; \
max-connections=350 ; \
innodb-additional-mem-pool-size=50M ; \
innodb-log-buffer-size=50M ; sync_binlog=0 ; \
innodb-thread-concurrency=0 ; log-error=msandbox.err
[…]

Standalone masters and slaves

MySQL Sandbox has had the ability of creating replicated systems for years. Yet, sometimes you need a stand-alone master server that you want to use for some odd experiment. Similarly, you may want to create a slave of a specific master without having a full replication system. One case where you would like this ability is when you want to try replicating between servers of different versions.

$ make_sandbox 5.1.57 -- --master
[…]
my_clause                      = server-id=5157 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_1_57

$ make_sandbox 5.5.10 -- --slaveof='master_port=5157' 
[…]
my_clause                      = server-id=5510 ; log-bin=mysql-bin ; log-error=msandbox.err
[…]
Your sandbox server was installed in $HOME/sandboxes/msb_5_5_10


$ ~/sandboxes/msb_5_1_57/use -e 'show master status'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+

$ ~/sandboxes/msb_5_5_10/use -e 'show slave status\G'
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 5157
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysql_sandbox5510-relay-bin.000002
                Relay_Log_Pos: 252
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 420
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5157

You can download MySQL::Sandbox from either launchpad or CPAN.

How to submit a good database bug report

When an open source project becomes popular, bug reports start flocking in. This is both good and bad news for the project developers. The good news is that someone is using the product, and they are finding ways of breaking it that we didn't think of. The bad news is that most of the times the reporters assume that the developers have super human powers, and that they will find what's wrong by the simple mentioning that a given feature is not working as expected. Unfortunately, it doesn't work that way. An effective bug report should have enough information that the ones in charge will be able to reproduce it and examine in lab conditions to find the problem. When dealing with databases and database tools, there are several cases, from simple to complex. Let's cover them in order.

Installation issues

This is often a straightforward case of lack of functionality. When a tool does not install what it is supposed to, it is a show stopper, and a solution must be found. In this case, it's often enough to state the environment where the installation is happening (operating system, version of the tool, version of core components such as the MySQL database used) and the command used to start the installation. The error message could be an expected failure, when the installation procedure checks for requirements and fails if they are not met. For example: "Missing Ruby interpreter". The message tells (or suggests) you what to do. Filing a bug report on an expected failure is a waste of time. You should install the missing part and try again. Even if the message is about an unexpected failure (e.g. you get a stack trace from Ruby or Java), usually the first message tells you enough to be able to find a workaround. For example, if you get an exception from Ruby complaining about a missing 'curl' command, you can file a bug report to ask for the installer to check for 'curl' in the requirements, but if you install 'curl' yourself, the installation should continue.

Simple database issues

Reporting a database bug means complaining that the DBMS is not behaving as advertising by the documentation, or as common usage dictates. If it is a missing or misbehaving functionality, the best way of showing the problem is by starting with an empty DBMS, followed by the creation of the objects needed to reproduce the issue (CREATE SCHEMA, TABLE, INDEX) and by a minimal amount of data that triggers the problem. Some information about what operating system and database version was used is probably necessary to reproduce the problem consistently.

Simple database replication issues

By simple replication we mean a vanilla master/slave topology. In this scheme, data inserted in the master will eventually end up in the slave. Bugs in this category may fail to replicate the data totally or partially, or they may cause a break in the replication flow. Reproducing them is almost as easy as with simple database bugs. If you start with an empty system and manage to reproduce the error with a short sequence of commands, it should probably reproducible by a third party. Sometimes, settings in the master and the slave are essential to reproduce the problem. In MySQL, the format of binary logs, the default database engine and SQL modes can affect replication and produce different results with the same stream of SQL commands.

Complex database replication issues

The most difficult bugs to report are the ones where the error shows up only in a given topology. While MySQL native replication offers only few options to pipe data around (single, circular, hierarchical), Tungsten replicator allows a rich set of combined pipelines that can change the outcome of a data change event, depending on the originating node and the direction it took. In these case, information o how the cluster was installed becomes essential.

Concurrency issues

This is one of the most difficult bugs to report. When an error happens only because of the contemporary action of two or more threads, there is no easy way of reporting it in a way that it can be easily reproduced. Three methods are possible:

• Describe the action of the first thread, then mark the change of thread and describe the actions of the second thread, continuing in this way until you reach the error point.
• If you are a developer and feel comfortable with multi thread applications, write a script that reproduces the error by running several threads (Perl, Python, and Ruby offer the best environment for this kind of tests).
• If the database offers a tool to write such multi-threading tests, consider using it.

Heavy load issues

This is a more complex case of the above one. Not only you need concurrency, but a lot of it happening at the same time. Reproducing this kind or error is challenging. If you have a support agreement with the provider of the database or the tool, you may let the support engineer have a look at your running environment, to find some clues. But even in this case, the support engineers or yourself need to ultimately reproduce the case in such a way that a developer can fix the problem and test the fix. There are two methods to report this problem:

• Simplification: if you can reduce the concurrency to the elements that are misbehaving, the methods for concurrency issues will apply also in this case.
• Enabling a query log could lead to identifying the sequence of events that have generated the error. The log should be integrated by the DDL of the objects involved in the action (schemas, tables, triggers, views, etc).

Large data issues

If your error only shows itself with large data, there is often a logistical problem, as you can't easily provide gigabytes of data, even if there privacy and security issues weren't in the way (which usually are). There are three strategies that you can use to report such bugs:

• If only the size matters, then describing the kind of data used could be enough. E.g. When a table with such fields and such indexes grows beyond X GB, then the optimizer warp drive explodes. (Don't try this at home)
• Create a script to generate the data that will ultimately trigger the error. This method requires both skills and an understanding of what the error is.
• You may use a publicly available database to reproduce the error (for example, the Sample database with test suite.) Just mention in the bug report where to find the database, eventually how to load it if it is not simple, and then describe the steps needed to reproduce the bug after loading it.

DOs and DONTs

• DO
  • Search the bug repository and the mailing lists (or forums) before submitting yours. Someone may have had the same problem before you did. (Thanks, Robert Hodges, for this important reminder.)
  • Put yourself in the receiver's position, and try to reproduce the problem from a clean initial state.
  • If there is a workaround, mention it: it might give a good clue to the developers.
  • More information is better. Anything that can improve the identification of the bug root cause will be welcome. (But don't overdo: see below)
  • If you feel that a missing feature should be useful, report it as a feature request. (Even better: suggest a patch)

• DON'T
  • Don't report a missing feature as a bug, unless the docs say that the feature should be there.
  • Don't just send the error message without the events that generated it.
  • Don't include SQL commands embedded in code.
  • Don't say "my application doesn't work anymore," assuming it's the database's fault. Remember The First Rule of Programming: It's Always Your Fault
  • Less is better. If there is a long way and a short way of reproducing the bug, the shorter one is better. Don't send more info just for the sake of it.
  • Don't tell the developers that they are retarded. This will not increase the priority given to your bug, or your credibility.

Call for participation for MySQL events - MySQL conference and FOSDEM

It's that time of the year where MySQL would be speakers are called to action. As usual, the main event is the MySQL spring conference, this year hosted by Percona. The call for participation to the MySQL Conference And Expo 2012 is still open until December 5th. To submit a proposal, you should register as a speaker and then fill in the form.
There is a conference committee which is already busy evaluating the proposals that have been submitted so far. The committee is demanding (I know for a fact, since I am in it!) and therefore, if you want to submit something, be very critical with yourself and polish your proposal as if your job depended on it!
Please read an update on Percona Live MySQL Conference & Expo 2012.

Speakers in Europe have some more duty, though. This is also the time to submit talks for FOSDEM MySQL DevRoom. Thanks to Frédéric Descamps and Sergey Petrunia, we have Room H.1309 with 150 seats on Sunday 5th February 2012, all day. The deadline to submit a talk proposal is December 26th. There is no review committee. Like we did on past editions, as soon as the talks are submitted, we will ask everyone to vote on the talks via Twitter or email. More updates will come soon. Of course, participation to FOSDEM DevRoom is not limited to European speakers. There have been several brave speakers who have willingly crossed the pond to offer their services at European conferences before, and they are welcome to repeat the experience. Submit your talk proposals now.

Never say "there is no way"

Reading a recent MySQL book, I saw an example of SHOW CREATE TABLE that comes with backticks (`) around the table and column names, and a comment:

Unfortunately, there is no way to remove this from generated syntax with this command.

(Emphasis mine).
Here's how it goes:

mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Of course, there is a way!

mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE mytest (
  id int(11) NOT NULL,
  description varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Never say "there is no way!"

Instead of "tr -d '`'", you can use "sed -e 's/`//g'", which does the same thing.

If you are running the query at the command line, you may use the pipe directly:

$ mysql -e 'show create table test.mytest\G' | tr -d '`'
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE mytest (
  id int(11) NOT NULL,
  description varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Replication stars

Working with replication, you come across many topologies, some of them sound and established, some of them less so, and some of them still in the realm of the hopeless wishes. I have been working with replication for almost 10 years now, and my wish list grew quite big during this time. In the last 12 months, though, while working at Continuent, some of the topologies that I wanted to work with have moved from the cloud of wishful thinking to the firm land of things that happen. My quest for star replication starts with the most common topology. One master, many slaves.

Fig 1. Master/Slave topology

Legend

It looks like a star, with the rays extending from the master to the slaves. This is the basis of most of the replication going on mostly everywhere nowadays, and it has few surprises. Setting aside the problems related to failing over and switching between nodes, which I will examine in another post, let's move to another star.

Fig 2. Fan-in slave, or multiple sources

The multiple source replication, also known as fan-in topology, has several masters that replicate to the same slave. For years, this has been forbidden territory for me. But Tungsten Replicator allows you to create multiple source topologies easily. This is kind of uni-directional, though. I am also interested in topologies where I have more than one master, and I can retrieve data from multiple points.

Fig 3. all-to-all three nodes

Fig 4. All-to-all four nodes

Tungsten Multi-Master Installation solves this problem. It allows me to create topologies where every node replicates to every other node. Looking at the three-node scheme, it appears a straightforward solution. When we add one node, though, we see that the amount of network traffic grows quite a lot. The double sided arrows mean that there is a replication service at each end of the line, and two open data channels. When we move from three nodes to four, we double the replication services and the channels needed to sustain the scheme. For several months, I was content with this. I thought: it is heavy, but it works, and it's way more than what you can do with native replication, especially if you consider that you can have a practical way of preventing conflicts using Shard Filters. But that was not enough. Something kept gnawing at me, and from time to time I experimented with Tungsten Replicator huge flexibility to create new topologies. But the star kept eluding me. Until … Until, guess what? a customer asked for it. The problem suddenly ceased to be a personal whim, and it became a business opportunity. Instead of looking at the issue in the idle way I often think about technology, I went at it with practical determination. What failed when I was experimenting in my free time was that either the pieces did not glue together the way I wanted, or I got an endless loop. Tungsten Replicator has a set of components that are conceptually simple. You deploy a pipeline between two points, open the tap, and data starts flowing in one direction. Even with multiple masters replication, the principle is the same. You deploy many pipes, and each one has one purpose only.

Fig 5. All-masters star topology

In the star topology, however, you need to open more taps, but not too many, as you need to avoid the data looping around. The recipe, as it turned out, is to create a set of bi-directional replication systems, where you enable the central node slave services to get changes only from a specific master, and the slave services on the peripheral nodes to accept changes from any master. It was as simple as that. There are, of course, benefits and drawbacks with a star topology, compared to a all-replicate-to-all design. In the star topology, we create a single point of failure. If the central node fails, replication stops, and the central node needs to be replaced. Instead, the all-to-all design has no weaknesses. Its abundance of connections makes sure that, if a node fails, the system continues working without any intervention. There is no need for fail-over.

Fig 6. extending an all-to-all topology

Fig 7. Extending a star topology

However, there is a huge benefit in the node management. If you need to add a new node, it costs two services and two connections, while the same operation in the all-to-all replication costs 8 services and 8 connections. With the implementation of this topology, a new challenge has arisen. While conflict prevention by sharding is still possible, this is not the kind of scenario where you want to apply it. We have another conflict prevention mechanism in mind, and this new topology is a good occasion make it happen. YMMV. I like the additional choice. There are cases where a all-replicate-to-all topology is still the best option, and there are cases where a star topology is more advisable.

Tungsten Replicator and MySQL Sandbox at Percona Live London 2011

I will be a speaker at Percona Live - London 2011, and I am looking forward to the event, which is packed with great content. A whopping 40 session of MySQL content, plus 3 keynotes and 14 tutorials. It's enough to keep every MySQL enthusiast busy. Continuent speakers will be particularly busy, as between me and Robert Hodges, we will be on stage four times on Tuesday, October 25th. Robert, at 10:15am: Teaching an Old Dog New Tricks: Tungsten Enterprise Clusters for MySQL, Bishopsgate Suite Robert, at 1:30pm: MySQL Parallel Replication in 5 Minutes or Less, Newgate Suite me, at 2:30pm: MySQL Replication outside the box : multiple masters, fan-in, parallel apply, Ludgate Suite me, at 4:30pm: MySQL Sandbox: a framework for productive laziness,Ludgate Suite This event feels good from the beginning. There are plenty of participants, many names from all over the MySQL community, covering large and small companies, experienced speakers, well known names in the MySQL engineering arena, and a wealth of topics that will make me feel sorry for not being able to attend them all. It's the usual dilemma that attendees have at this kind of conferences. Not so much at Oracle Open World 2011, where there weren't that many MySQL sessions to choose from, although it was great for networking.

Our talks

Robert will open the dances with Teaching an Old Dog New Tricks: Tungsten Enterprise Clusters for MySQL, a talk about Tungsten Enterprise, my company's commercial product, which is a professional managing tool for demanding companies. Robert, again in the afternoon, with one of the most amazing features of our open source product, Tungsten Replicator: MySQL Parallel Replication in 5 Minutes or Less. This is a feature for large replication systems where the slave can't cope with large data streams, due to the singled-thread MySQL slave. This talk will show how easy is it to plug Tungsten Replicator to a lagging slave, start parallel replication until the lag has been zeroed, and then hand over the control to the native replication again. Then it will be my turn, with a general presentation about Tungsten Replicator, the open source product. I like the idea of calling it MySQL Replication outside the box : multiple masters, fan-in, parallel apply. The reasoning is that MySQL replication, although wildly successful in the web economy of the last decade, it is also constrained by several limits, which Tungsten, acting outside the boundaries, sets free. This will be a quick intro to Tungsten and its new user-friendly installation, with a few demos. Finally, a classic presentation with some new content, on MySQL Sandbox: a framework for productive laziness. The news is that MySQL Sandbox now supports Percona and MariaDB builds. Again, some demos will be shown, with old and new features mixed together.

Upgrading Tungsten Replicator: as easy as ...

When I talked about the usability improvements of Tungsten Replicator, I did not mention the procedure for upgrading. I was reminded about it by a question in the TR mailing list, and since the question was very relevant, I updated the Tungsten Cookbook with some quick upgrading instructions. A quick upgrading procedure is as important as the installer. Since we release software quite often, either because we have scheduled features to release or because of bug fixes, users want to apply a new release to an existing installation without much fuss. You can do the upgrade with a very quick and painless procedure. Let's suppose that you have installed one Tungsten Replicator cluster using this command:

#
# using tungsten-replicator 2.0.4
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
./tools/tungsten-installer \
  --master-slave \
  --master-host=r1 \
  --datasource-user=tungsten \
  --datasource-password=secret \
  --service-name=dragon \
  --home-directory=$TUNGSTEN_HOME \
  --cluster-hosts=r1,r2,r3,r4 \
  --start-and-report

If you want to upgrade to the very latest Tungsten Replicator 2.0.5, build 321, this is what you need to do.

• Get the latest tarball, and expand it;
• Stop the replicator;
• Run the update command (this will also restart the replicator)
• Check that the replicator is running again.

The actual upgrade command is in bold in the following script.

#
# using tungsten-replicator 2.0.5-321 (get it from bit.ly/tr20_builds) 
#
TUNGSTEN_HOME=/home/tungsten/installs/master_slave
HOSTS=(r1 r2 r3 r4)
for HOST in ${HOSTS[*]} 
do 
   ssh $HOST $TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/replicator stop 
   ./tools/update --host=$HOST --user=tungsten --release-directory=$TUNGSTEN_HOME -q 
   $$TUNGSTEN_HOME/tungsten/tungsten-replicator/bin/trepctl -host $HOST services
done

One benefit of this procedure, in addition to being brief and effective, is that the previous binaries are preserved. Before the upgrade, you will see:

$ ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.4
/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4

The 'tungsten' directory is a symlink to the actual binaries inside the 'releases' directory. After the upgrade, the same directory looks like this:

ls -lh ~/installs/master_slave/ ~/installs/master_slave/releases
/home/tungsten/installs/master_slave/:
total 32K
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 backups
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 configs
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 relay
drwxrwxr-x 4 tungsten tungsten 4.0K Sep 22 22:06 releases
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 service-logs
drwxrwxr-x 2 tungsten tungsten 4.0K Sep 22 22:03 share
drwxrwxr-x 3 tungsten tungsten 4.0K Sep 22 22:03 thl
lrwxrwxrwx 1 tungsten tungsten   75 Sep 22 22:06 tungsten -> /home/tungsten/installs/master_slave/releases/tungsten-replicator-2.0.5-321

/home/tungsten/installs/master_slave/releases:
total 8.0K
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:03 tungsten-replicator-2.0.4
drwxr-xr-x 6 tungsten tungsten 4.0K Sep 22 22:06 tungsten-replicator-2.0.5-321

If you did some manual change to the files in 2.0.4, you will be able to retrieve them. Upgrading from earlier versions of Tungsten Replicator is not as smooth. Since we changed the installation format, it has become incompatible from previous versions. Clusters running TR 2.0.3 need to be reinstalled manually. The next upgrade, though, will be much faster!