Sunday, November 28, 2010

Dispelling some unintentional MySQL FUD

Sakila dispelling FUD There are three types of FUD: the first and more genuine is (#1) the intentional spreading of falsehood, mostly to gain some marketing advantage over a competing product. While I despise this practice, I understand it.
Then there is (#2) FUD spread by ignorance, when the originators are so blindly enraged by their hatred for a product that they don't care about getting the facts straight.
And finally, there is a third kind, not less dangerous, which is (#3) the spreading of FUD with good intentions, when the authors believe that they have the facts straight and they want to help.
I have recently come across two examples of unintentional FUD. For different reasons, my comments to these public cases did not get through, and then I have to say something about that here in my blog.

MySQL is not ACID complaint

This surprising piece of news came in the blog of a company that calls itself the remote DBA experts.
The claim is this: if I insert a record in a table and then issue a ROLLBACK command, the record is not rolled back.
Anyone who has a minimal knowledge of MySQL knows about InnoDB tables (luckily for the poster, InnoDB is default in MySQL 5.5.6, which he was testing) and autocommit.
Reading through the example, one sees that the poster did not know about this piece of information. In MySQL, autocommit is ON by default. So if you want to rollback a record, you need to deactivate it. This is not optimal, and it can be debated, but if you read the docs, you don't claim something that is simply the result of your lack of knowledge. MySQL has shortcomings, but being unable to rollback a record is not one of them. Hence, this is FUD type #2.
Why I am writing all this here and not as a comment in that blog? Because I did post a comment, on November 23rd, but as of today, it has not been approved yet. The same is true for comments posted by other more knowledgeable people.

MySQL licenses. When it's free and when you need to pay for one.

This article is well intentioned. MySQL Licenses: The Do's and Don'ts of Open Source, or What's All the Fuss About? is a well thought piece, with practical examples, to help users decide what to do with MySQL licensing, i.e. when they need to pay and when they don't. Unfortunately, the article contains some unintentional confusion, and therefore leaves the readers with more wrong ideas than they had before.
I left a long comment on that blog, but for some unfathomable reason it was reduced to a tiny piece, and thus the need for explaining the matter here again.
The poster says this:
I make commercial software, which needs to have MySQL installed. My customers can use my commercial software, for which they do need to buy a license, in combination with the MySQL database engine, for which they don't need to pay. Because the MySQL engine is not embedded in my commercial software and I don't redistribute MySQL together with my software, I don't need a commercial license for MySQL and neither do my customers.
I am afraid that this wishful information is not correct. The GPL FAQ states it clearly:
If a library is released under the GPL (not the LGPL), does that mean that any program which uses it has to be under the GPL or a GPL-compatible license?
Yes, because the program as it is actually run includes the library.

Another quote:
However... as long as I have no desire to sell the embedded MySQL source code commercially, I can let the GPL license apply.
Also this is not true. The GPL does not regulate commercial transactions. It only deals with distribution of software. If I want to distribute a public domain but GPL-incompatible software linked to a GPL application or library, I am violating the GPL, even if I don't charge anything.

Another source of disinformation is "If you decide to pay for a MySQL license, you don't actually pay for the software."
This is also incorrect. Oracle sells two kind of things with MySQL. One thing is a subscription to services (MySQL Enterprise). If you buy this, you are not getting a license (unless you ask for it explicitly) but an agreement about services for a given periods.
The other thing that Oracle sells is licenses. They can do it because they own the source code, and they can decide to release it either as GPL (which is what you download from the MySQL site) or with a commercial license. If you ask for a license, you will most definitely get one. You can also get a license together with a subscription, if you are so inclined, but that doesn't mean that you aren't buying a license.
The important thing to understand to put the matter in perspective, is that the above information about licensing was still true before 2008, when MySQL was owned by MySQL AB, and it is still true today. Oracle, despite all the preemptive accusations of being ill intentioned, has not changed the rules of the game.

A world of ebooks

ebooks I am a bibliophile, or, to say it in plain English, a book lover. I have been collecting books since I was in first grade. I read books at high speed, which is both a blessing and a curse. A blessing, because I can squeeze useful information out of a book very quickly, and that's useful for my job, and for some of my hobbies. A curse, because when I travel one book is usually not enough to keep me busy for the whole travel, and I need to carry or buy more, with negative effects on the weight of my luggage and my on my back. Ten years ago I had a brief but intense experience with electronic books in a Palm hand held device. It didn't last long, though. The quality of ebooks and readers in that period was less than optimal, and I have left the matter rest for a while.
In the meantime, I kept collecting electronic books, mostly PDF editions of technical books that I keep in my laptop for quick reference. Reading them from cover to cover, though, is not a pleasant experience in a laptop. Ditto for reading fiction or essays. The laptop screen is not comfortable for such exercise.
Then, last year, I bough an ebook reader.
That changed the whole business. Reading ebooks became very similar to reading paper books. The size of the screen and the ability of increasing font size makes your reading a pleasure. As for my back and luggage problems, that's solved hands down. The weight of the device is the same, no matter if I carry one ebook or one hundred.
Suddenly, the dozen of ebooks that I had kept idle in my laptop sprang to life, and I was able to read them like a paper book, easily, comfortably, and with pleasure.
I started buying more ebooks, both of fiction and of technical matters. The latter are especially welcome. Whenever I travel to conferences, I am tempted to buy some useful book, and then I regret when it burdens my backpack during the trip home, and fights for room on my overcrowded book shelves. No more of this. Now, when I visit a book booth at a conference, I simply take note of the interesting titles, and then I buy the ebooks at the publisher's site directly. If there is no ebook, I can easily convince me that the book is not really needed.
A few months ago there was some new development. My ebook reader's screen was faulty. It was showing a few unwanted lines at the bottom and the top of the screen, making it difficult to read menus. No big deal. I sent it to the manufacturer, which replaced the screen for free. The only trouble was that the replacement took three months! During that period, I experienced reading ebooks (to which I was by then addicted) with my Android phone, using a wonderful application named Aldiko. The user friendliness of this app more than compensated for the smaller screen size, and I was able to read technical and fiction books with little problem. But I was missing the big screen. So the delay of the back shipment was partially responsible for the lowering of my defenses, when I entered an Apple store and I couldn't leave without a new iPad.
I felt guilty for a while, but the guilt disappeared in a matter of hours, when I loaded all my ebooks in the iPad, and saw what a difference a bigger and colorful screen does. Compared to the six inches of my ebook reader, the iPad is huge, and the reading is even easier and more pleasurable. I was hooked.
Since then, my personal library of ebooks has grown rapidly. I have bought 90 (yes, ninety) books from O'Reilly, including many that I had already bought on paper, and now I am giving away to friends and libraries.
I need to spend a few words of praise for O'Reilly. In the jungle of book publishing, O'Reilly is the best and more user friendly publisher available. The quality of its books is excellent, the choice of catalog vast and modern, the service impeccable. There are other publishers that offer comparable quality (e.g. the Pragmatic bookshelf or Manning) but not the same rich catalog, or a similarly vast catalog (e.g. Packt Publishing) but not the same quality.
If I have to note any negative points about O'Reilly, is that there is no wish list in their shop. So, for now, I am restricting my wishes to my list on Amazon.

Sunday, November 21, 2010

How MySQL Workbench breaks itself

Once upon a time, there was a policy in MySQL not to add new features after the beta stage.
To my surprise, MySQL Workbench 5.2.30 introduces a new feature, the query formatter. I gave it a try. The results are not extremely encouraging. Granted, it's a plugin and not a feature in the core application, but nonetheless one would expect something more stable in a GA release, especially since the plugin features are displayed in the main menu, and unless you have read the announcement, you couldn't easily tell the core from the plugins.
This is what I have got in just a few minutes:

Bug #58356: beautify function fails on CREATE TABLE
Bug #58357: beutify function erases statement on CREATE INDEX
Bug #58358: query formatter fails on selected query
Bug #58359: query formatter indentation fails on partially selected query
Bug #58360 query formatter converts non-keywords to uppercase
Bug #58361 Query formatter mangles query when CASE operator is used

MySQL Workbench is a great product. I would like it to be more solid. New features, even as a plugin, should be more carefully released that this one.

Update. 4 days after my submission, the WB team has fixed all 6 of them. Kudos!

Friday, November 19, 2010

How to create a private cloud in your laptop

private cloud laptopEverybody is moving to cloud architectures, although not all agree on what cloud computing is. In my limited understanding, and for the purpose of my work, cloud computing is a quick and versatile availability of virtual machines.
Now, if my purpose was deploying these machines, a private cloud in one host (namely, my laptop) would not make sense. But to create a flexible testing environment, it works very well.
Users of virtual machines software such as VMWare or VirtualBox may ask what's new here. You can create many virtual machines and use them within the same host.
True, but creating a new virtual machine in one minute without duplication of resources is not so easy. This is what this article covers. More specifically, it covers how to clone virtual machines efficiently with VMWare Fusion on a Mac OSX laptop.
I have been a VMWare user for more than 10 years, mostly on Linux, where I used VMWare Workstation. One of the features that I liked on that application was the ability of cloning virtual machines, where the clones share the disk with the original VM. VMWare Fusion does not have a cloning feature, but there are ways of achieving the same result.
If you copy a virtual machine and then open it with Fusion, it will ask you if you have copied it, and after your answer, it will use the new virtual machine independently from the first one. The only problem is that you will have twice as much disk space occupied. So, in addition to the space that will eventually got eaten up in your limited laptop, the duplication process is slow (copying 10 GB does not happen instantly). The method shown here will instead allow you to clone a virtual machine in less than one minute.

Part I - How to clone a virtual machine

I have got this recipe from HOWTO: Manual Linked Cloning in Fusion, a year old tutorial that I have integrated with updated images.

Do it once - Create a virtual machine with everything you need.

This is a once only step. Install a virtual machine as usual. In my case, I used Ubuntu server 10.10. When I said with everything you need I mean making sure that everything you think it will be necessary should be installed in this base VM. If you don't, you may end up installing the same thing in every cloned virtual machine.
What I did was updating the software, then installing the build-essential package and the VMWare tools, and then enabling the "partner" repository to install the original Sun Java packages instead of the default OpenJDK. Next, I downloaded the source code for MySQL 5.5 and made sure that I had all the software necessary to compile and build the database.
Finally, I updated the CPAN with my favorite modules, stopped the virtual machine, and I was ready for the next step.
What I had in my hands was a working virtual machine. In order to make it clonable, I will go through some basic steps.

Step 1. Copy the VM disks

I create a new directory under the same path where the normal virtual machines are. I called it ub_srv_10_base, and I copied the .vmdk files into this new directory.

Step 2. Modify the base disk to refer to fixed paths

In each virtual machine, the disk that has the same name as the VM (without numerals) is a sort of index of the real files containing the data. This file needs to be edited for further usage. Originally, it looked like this:
# Disk DescriptorFile
version=1
encoding="UTF-8"
CID=f88ac433
parentCID=ffffffff
isNativeSnapshot="no"
createType="twoGbMaxExtentSparse"

# Extent description
RW 4192256 SPARSE "ubuntu_server_10-s001.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s002.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s003.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s004.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s005.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s006.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s007.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s008.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s009.vmdk"
RW 4192256 SPARSE "ubuntu_server_10-s010.vmdk"
RW 20480 SPARSE   "ubuntu_server_10-s011.vmdk"

# The Disk Data Base 
#DDB

ddb.toolsVersion = "8323"
ddb.virtualHWVersion = "7"
ddb.longContentID = "d14a2f23de35287969b8eaebf88ac433"
ddb.uuid = "60 00 C2 98 72 3b 6e 76-ff 05 b7 ff 8a 07 6e 92"
ddb.geometry.cylinders = "2610"
ddb.geometry.heads = "255"
ddb.geometry.sectors = "63"
ddb.adapterType = "lsilogic"
To make it usable, I added a relative path to the file names, and removed the UUID.
# Disk DescriptorFile
version=1
encoding="UTF-8"
CID=f88ac433
parentCID=ffffffff
isNativeSnapshot="no"
createType="twoGbMaxExtentSparse"

# Extent description
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s001.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s002.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s003.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s004.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s005.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s006.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s007.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s008.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s009.vmdk"
RW 4192256 SPARSE "../ub_srv_10_base/ubuntu_server_10-s010.vmdk"
RW 20480 SPARSE   "../ub_srv_10_base/ubuntu_server_10-s011.vmdk"

# The Disk Data Base 
#DDB

ddb.toolsVersion = "8323"
ddb.virtualHWVersion = "7"
ddb.longContentID = "d14a2f23de35287969b8eaebf88ac433"
# ddb.uuid = "60 00 C2 98 72 3b 6e 76-ff 05 b7 ff 8a 07 6e 92"
ddb.geometry.cylinders = "2610"
ddb.geometry.heads = "255"
ddb.geometry.sectors = "63"
ddb.adapterType = "lsilogic"

Now the file is ready to be used by other virtual machines. Notice that the files in this directory do not make a virtual machine. Only the storage part is here.

Step 3. Make the disks read-only

We need to make sure that the base disks are not modified. So, we remove the "w" attribute from all the files in the base directory.

$ chmod a-w ub_srv_base/*s0*

Step 4. Remove the original virtual machine

Make a copy if you want. And actually having a backup of the base disk directory is a very good idea. Compress it and save it to a good location. But remove the virtual machine from Fusion. This will avoid confusion later on.

Do it many times - Create a clone

This process looks frightfully long, especially if you count the images below, but in reality it's very quick and painless. Once you get familiar with it, you will be cloning virtual machines in no time at all.
vm1
Back in VMWare Fusion, select "create a new Virtual Machine".
Make sure that you don't have the Ubuntu CD in your read DVD player. Click on "continue without disk".

vm2
Ignore the top options, and select "create a custom virtual machine".

vm3
Select the same operating system that was used for your base VM.

vm4
When the summary shows up, choose "Customize settings".

vm5
Save the VM with a sensible name. Since you will be creating many of the same kind, choose a pattern that you can recognize. In this case, I used the base name with an additional letter at the end to identify the server.

vm6
The disk is your main concern. The default settings want to create a 20 GB disk. Simply remove it.

vm7
When asked if you want to get rid of the disk completely, say yes, "move it to the trash".

Now, Very important, before you go to the next step. Use the command line, or a GUI, and copy the index .vmdk file from the base directory to the new virtual machine. I call it nd.vmdk (nd=new disk) but you can call it whatever you want. make sure that this file is not write protected.
vm8
Add another disk. Here's the first trick part. By default, Fusion wants to recreate the same disk that you have just removed. But notice that there is a drop down menu on the right side of the panel. Click on "choose existing disk".

vm9
Here you select the file that you have copied from the base directory. And now another tricky point. Make sure that you click on "Share this virtual disk ...", otherwise VMWare will make a copy of your original disk files.

vm11
You can now change other parameters in the virtual machine, such as the amount of RAM and how many processors you want to use for it. I recommend unchecking the "connected" box next to the CDROM, to avoid a warning when more than a cloned VM work at the same time. You can always enable the CD later if you need it.

vm12
Now Don't switch on your cloned virtual machine just yet. If you do, you get an incomprehensible message, where the VM complains about not being able to access the new disk file, while in reality it can't access the disks referred by that index file. This is where the precaution of write protecting the files comes handy. If you hadn't done that, the VM would access (and eventually modify) the virtual disk files, and possibly corrupt them. Instead, you need another step before having a functional clone.

vm13
You need to create a snapshot. Once you have done that, the VM will write to the snapshot files all the deltas between your read-only disks and your VM final status.

vm14
You can call the snapshot whatever you like.

vm15
Finally, you can run the virtual machine. If other virtual machines are running, it may warn you that some devices might not be available. Ignore this warning unless you know for sure that there is a unique resource that should not be shared (usually, there isn't).

Your virtual machine is ready to run. If you need to create three identical servers to simulate a cluster, and the original VM has 4GB of occupied storage, the operation won't cost you 16 GB, but just a few dozen MB:
$ du -sh ub_*/
 21M ub_srv_10_10a.vmwarevm/
 22M ub_srv_10_10b.vmwarevm/
 21M ub_srv_10_10c.vmwarevm/
3.9G ub_srv_10_base/

Part II - Use your virtual machines from the command line

Although VMWare Fusion is handy for creating virtual machines, and for using GUI-based operating systems, it is less than desirable when you have several virtual machines with only text interface, and you want to use them from the command line, the same way you would do in most real life administration or QA operations.

No need for further hacking, in this case. You can manage your virtual machines from the command line, using an utility called vmrun, which is located under /Library/Application Support/VMware Fusion/.
You can read the full manual in a lengthy PDF document that is available online ( Using vmrun to Control Virtual Machines), but here's the short story.
vmrun can start and stop a virtual machine. You just need to use it when the VMWare Fusion application is closed. For example:
$ vmrun start $HOME/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx nogui
2010-11-19 14:58:49 no printer configured or none available
2010-11-19 14:58:49 adaptor daemon booted
2010-11-19 14:58:49 connector "vmlocal" booted

As part of my preparation of the virtual machine, I created a simple script that runs ifconfig and filters out the virtual machine IP to a file in the user's home.
Using this information, I can then run the following commands:
$ vmrun -gu qa -gp SECRETPWD runProgramInGuest \
   $HOME/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx \
   /home/qa/bin/get_ip

$ vmrun -gu qa -gp SECRETPWD copyFileFromGuestToHost \
   $HOME/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx \
   /home/qa/myip ./server_a_ip

$ cat server_a_ip 
192.168.235.144

This is just a simple example of what you can do. Once you have the IP address (which the DHCP server could change), you can connect to your virtual machine via ssh and do what you need. When you have finished, you can switch off the VM, again without need of using the GUI:
$ vmrun stop ~/vmware/vm/ub_srv_10_10a.vmwarevm/ub_srv_10_10a.vmx 2010-11-19 15:11:25 adaptor daemon shut down
2010-11-19 15:11:25 connector "vmlocal" shut down

Happy (cloud) hacking!

Friday, November 12, 2010

Filtering binary logs with MySQL Sandbox and replication

A few days ago, a friend of mine asked me if I knew of a way of filtering a bunch of binary logs, to extract only statements related to a single table. The task was about filtering a few hundred binary log files.

It's a tricky problem. Even with my experience with regular expressions, I knew that using a script to extract statements related to a single table was going to be a nasty business.
However, I know of an entity that can filter by table name efficiently, and that's the MySQL replication system. So I suggested using replication to a sandbox with a replicate-wild-do-table statement to get the job done.
My friend was skeptical and did not want to go that way. I was busy writing an article for an Italian magazine and did not follow up immediately. But today, with the article safely in the editor's hands, I did a quick test, and guess what? It works!
binary log filter
Here is a step-by-step procedure to do it. I started with a server built with MySQL Sandbox, using MySQL 5.5.7. I used the employees test database to create a large enough binlog, and soon I had a database containing 160 MB of data and a binary log of about the same size.
Then I decided that I wanted to filter the binlog, to get only statements about the employees table. Thus, I issued this command:

$ make_sandbox 5.5.7 --sandbox_port=6000 \
  --sandbox_directory=trans_repl \
  -c log-slave-update \
  -c replicate-wild-do-table=employees.employees \
  -c log-bin=mysql-bin \
  -c server-id=101
The "-c" option transfers its argument to the sandbox configuration file.
At the end of this operation, I had one server with the same version of the server that I had filled with the employee database. The server is ready to filter replicated streams, accepting only commands that affect the table 'employees' within the database 'employees'.

The second step was to create an empty database in the second server, with the Innodb tables converted to BlackHole (to avoid wasting unnecessary space).

Inside the first sandbox, I did this:

$ ./my sqldump -B --no-data employees \
  | perl -pe 's/=innodb/=blackhole/i' \
  | ~/sandboxes/trans_repl/use
Combining the flexibility of the sandbox with some command line skills, the operation requires just one command.
Before starting the replication, I needed to avoid re-creating the tables, or my blackhole trick would have been useless. So I looked at the binary log, and found where the CREATE TABLE statements ended:

$ ./my sqlbinlog ./data/mysql-bin.000001 |less
BEGIN
/*!*/;
# at 3057
#101112 9:48:45 server id 1 end_log_pos 3364 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1289551725/*!*/;
INSERT INTO `departments` VALUES ('d001','Marketing'),('d002','Finance'),('d003','Human Resources'),('d004','Production'),('d005','Development'),('d006','Quality Management'),('d007','Sales'),('d008','Research'),('d009','Customer Service')/*!*/;

Armed with this knowledge, I logged in the second server and did the following:

FLUSH BINARY LOGS; # to get a binlog with only the statements that I need

CHANGE MASTER TO
master_host='127.0.0.1',
master_port=5570,
master_user='msandbox',
master_password='msandbox',
master_log_file='mysql-bin.000001',
master_log_pos=3057;    # this is the position after all the CREATE TABLE
                        # statements in the master
START SLAVE;
After a few seconds, I issued a "SHOW SLAVE STATUS". All was OK.
I flushed the logs again and inspected the second binary log. As expected, it contained only the statements related to the employees table.
Total cost of the operation: 5 minutes. Way quicker than writing this report!

Thursday, November 11, 2010

Speaking at the UK Oracle User Group conference - Birmingham

UKOUG Conference Back to the conference circuit after some rest.
On December 1st I will be speaking under my new affiliation at Continuent in the MySQL track at the UKOUG conference. My topic is MySQL - Features for the enterprise and it will basically cover the main features of MySQL 5.5.
This conference is the largest Oracle related event in Europe, and it is organized by users for other users. This year for the first time the conference hosts a MySQL dedicated track.
It is a sort of epidemic. Most of the important Oracle events have now some MySQL content, or a full track. I see this as a great opportunity for both classic MySQL and Oracle users to meet each other and find common business ground for the future.

Thursday, November 04, 2010

Testing MySQL 5.5 semi-synchronous replication

A few days ago I saw an article about Semi-Synchronous Replication in MySQL 5.5. It asks questions, and doesn't give answers beyond gut feeling. So I thought that I would do some practical testing of this new feature.
Before we go that way, though, let's revisit the theory.

How semi-synchronous replication works


Figure 1. A transaction with regular replication
With regular replication, you send a transaction to the master (1). When the COMMIT is received, the master executes it (2), and if successful it logs the transaction to the binary log (3). The the master answers the client request (4) with a successful result. In the meantime, the slaves replicate the record (5).
What happens if the master crashes after point #4 and before a slave has had a chance of getting the data in point #5?
The client will have a result for that transaction, but that data is lost, because it has never reached one slave.


Figure 2. A transaction with semi-synchronous replication

Let's see the same scenario with semi-synchronous replication. All is the same until point #3. Then, things change. The master does not return to the client. Instead, it alerts the slave that a transaction is available (4). The slave gets it and stores it to the relay log (5). Without further action, the slave tells the master that the transaction was received (6) and only then the master returns the result to the client (7).
What is the benefit? If the master crashes, the transaction is lost. but the client does not get the false information that the transaction was stored. Instead, if the master crashes before it returns the result to the client, the client gets an error, and knows that that transaction needs to be reworked when a new master is back.

Semi-synchronous replication in practice

Now, the practicalities.
How do you tell if semi-synchronous replication is working? If you leave the default timeout of 10 seconds, you have an immediate clue that something is wrong when a query takes too long to return. Investigation is possible by looking at the GLOBAL STATUS variables.
Rpl_semi_sync_master_status tells you if the master is ready for semi-synchronous replication.
Rpl_semi_sync_master_yes_tx is the number of positive transactions that were delivered using semi-synchronous replication.
Rpl_semi_sync_master_no_tx is the number of failed attempts at delivering a transaction via semi-synchronous replication. When that happens, Rpl_semi_sync_master_status becomes "OFF", and you need investigating.

The important thing to understand about this feature is that semi-synchronous replication does not guarantee that your transaction is executed in the slave. It will only tell you that the data has been transferred to the slave relay log. It can still happen that the transaction fails to execute on the slave (which could be either a bug in your application or a bug in MySQL replication). But this is not a cluster. Don't expect a two-phase commit.

Testing semi-synchronous replication

If you want to test this feature without suffering too much, you can use a tarball binary and MySQL Sandbox. Once you have installed MySQL Sandbox and have downloaded the server tarball, you can install a test replication system with
make_replication_sandbox --how_many_slaves=4 /path/to/mysql-5.5.6-yourOS.tar.gz
This will create a system with 1 master and 4 slaves.
The Sandbox has a shortcut to install the plugin quickly and painlessly:
sbtool -o plugin --plugin=semisynch -s $HOME/sandboxes/rsandbox_5_5_6
Now you will have the semi-synchronous plugin installed in the master and all the slaves. For our tests, we will make a shell script, an easy task thanks to the sandbox utilities.

#!/bin/sh

echo "disabling semi-synch replication in all slaves except 1"
./s1 -e 'set global rpl_semi_sync_slave_enabled=1'
./s1 -e 'slave stop io_thread; slave start'

for E in 2 3 4
do
    ./s$E -e 'set global rpl_semi_sync_slave_enabled=0'
    ./s$E -e 'slave stop io_thread; slave start'
done

#
# this query will show the main variables that tell
# if semi-synch replication is working
#
Q1='select variable_name, variable_value'
Q2='from information_schema.global_status'
Q3='where variable_name in'
Q4='("RPL_SEMI_SYNC_MASTER_YES_TX", "RPL_SEMI_SYNC_MASTER_NO_TX")'
I_S_Q="$Q1 $Q2 $Q3 $Q4"

echo ""
echo "creating a table. it should replicate through the semi-synch"
./m -vvv -e 'create table test.t1( i int)'
./m -e "$I_S_Q"

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (1)'
./m -e "$I_S_Q"

echo ""
echo "disabling semi-synch replication in slave 1"
./s1 -e 'set global rpl_semi_sync_slave_enabled=0'
./s1 -e 'slave stop io_thread; slave start'

echo ""
echo "enabling semi-synch replication in slave 3"
./s3 -e 'set global rpl_semi_sync_slave_enabled=1'
./s3 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (2)'
./m -e "$I_S_Q"

echo ""
echo "disabling semi-synch replication in slave 3"
./s3 -e 'set global rpl_semi_sync_slave_enabled=0'
./s3 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'NO' should increase"
./m -vvv -e 'insert into test.t1 values (3)'
./m -e "$I_S_Q"

echo ""
echo "enabling semi-synch replication in slave 2"
./s2 -e 'set global rpl_semi_sync_slave_enabled=1'
./s2 -e 'slave stop io_thread; slave start'

echo ""
echo "inserting a record. The number of 'YES' should increase"
./m -e 'insert into test.t1 values (4)'
./m -e "$I_S_Q"
This script will first disable semi-synchronous replication in all the slaves except one. Then it will create a table, and check for the telling status variables.
This should work quickly and without problems. Then it will disable the plugin on the only slave that was active, and enable another slave instead.
Inserting a record on the master will work again quickly, as the newly enabled slave will get the record immediately.
Then the slave gets disabled, and we can witness what happens. The query takes a bit longer than 10 seconds, and the status variable tells us that semi-synchronous replication has failed.
We finally enable yet another slave, and when we try a further insertion, we can see that the semi-synchronous replication has resumed.

Very important:
To enable or disable semi-synchronous replication on a slave it is not enough to set the appropriate variable. You need also to restart the slave by issuing a STOP SLAVE IO_THREAD followed by a START SLAVE commands.

Here is a sample run:
disabling semi-synch replication in all slaves except 1

creating a table. it should replicate through the semi-synch
--------------
create table test.t1( i int)
--------------

Query OK, 0 rows affected (0.87 sec)

Bye
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 1              |
+-----------------------------+----------------+

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 2              |
+-----------------------------+----------------+

disabling semi-synch replication in slave 1

enabling semi-synch replication in slave 3

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 0              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 3              |
+-----------------------------+----------------+

disabling semi-synch replication in slave 3

inserting a record. The number of 'NO' should increase
--------------
insert into test.t1 values (3)
--------------

Query OK, 1 row affected (10.12 sec)

Bye
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 1              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 3              |
+-----------------------------+----------------+

enabling semi-synch replication in slave 2

inserting a record. The number of 'YES' should increase
+-----------------------------+----------------+
| variable_name               | variable_value |
+-----------------------------+----------------+
| RPL_SEMI_SYNC_MASTER_NO_TX  | 1              |
| RPL_SEMI_SYNC_MASTER_YES_TX | 4              |
+-----------------------------+----------------+
Using the above steps, you should be able to use semi-synchronous replication and do some basic monitoring to make sure that it works as expected.

Tuesday, November 02, 2010

QA at Continuent. A serendipitous job.

Unexpected road A few days ago, I left the MySQL team at Oracle, after more than 4 years of work dedicated mostly to the MySQL community.
Someone will probably remember that, when I joined MySQL in 2006, I started my work in the QA team. It was no coincidence. My previous work as a consultant was very much focused on database development quality, even when my customers had approached me for different reasons.
Let's be frank. I am a minority. It's not common to find someone who is passionate about QA. I am aware of being a rare bird, who likes testing and bug searching, and doing all the little steps that all together improve the overall quality of a software system.
I had been thinking about my passion lately, and I often caught myself thinking why in my job at MySQL I let things slip away so much from my ambition of tackling some hard technical problems.
I was more or less in this frame of mind when I met by chance an old acquaintance, Robert Hodges, CEO of Continuent and a fine technologist whom I respect and admire. And I was astonished to hear him asking me if I knew a good candidate for a position of QA manager for Continuent.
You can guess the rest of the story. I told him that I indeed had a candidate, and the next day we went to dinner together, and talked the most hackish and refreshing talk that I have had in years with Ed Archibald, the CTO (oh, and the steaks were great too!)
A few days after that, Robert and Ed organized a demo of Continuent technology for me, and I was hooked.

There was, indeed, an uncommon problem to solve. I had received offers for very good positions at three other companies, and one more had asked me if I wanted to consider working for them. As all the offering parts are friends, this fact did put me in a difficult position. Every offer was very intriguing and exciting, and in the end I had to take a choice, leaving one happy and three disappointed friends in the field. After careful choice, I chose Continuent, where I will start as Director of Quality Assurance on November 15th.
Fortunately for me, the three other friends are real friends, and they took the refusal graciously. They knew from the beginning that they had competition, and they knew that they could win or lose for reasons that they could not fully control. And indeed the main reason for choosing Continuent is the challenge of developing a beautiful technology that will eventually take the world by storm. (Or so it's the general plan. It's too early to make claims, as I haven't started getting acquainted with the technology yet, but it feels good).

What now? I have some tentative plans that I need to consolidate in the near future:
  • Taking some vacation. I am already one day into this first part;
  • Getting familiar with the technology that I will help improving;
  • Getting acquainted with my new colleagues, for which I will have a splendid occasion at the company meeting that will happen soon in Stuttgart;
  • Learning some new technology, both in development and in a more strict QA field;
  • Setting up my new laptop, which will hopefully come before I start the new job;
  • Talking at conferences. I have already committed to talking at the UKOUG in Birmingham, on December 1st, and more will come.
  • Resuming my blogging at full speed. I have a mental drawer full of ideas, and I only need to transfer them to the keyboard.
And probably I will also start playing with the new technologies that I have been reading about in the last months but I haven't had time to pursue. Either way, I feel refreshed and ready for the next battle.
Thanks, Continuent, for this great opportunity!

Monday, November 01, 2010

Book review: MySQL 5.1 plugin development

MySQL 5.1 Plugin Development MySQL 5.1 Plugin Development,
by Sergei Golubchik and Andrew Hutchings.
Packt Publishing, 2010.
Executive summary: Highly recommended. If you want to develop MySQL extensions, buy this book. It's a must, written by two expert professionals who probably know more than anyone else on this matter. The book is full of practical examples explained with the theoretical information necessary to make it stick.

This book fills a gap in the world of MySQL documentation. Although the MySQL docs are extensive and thorough, to the point that sometimes you wished that the writers were less verbose and stick more to the topic, when it comes to the plugin architecture, there are still more some unanswered questions. I guess that the ones who have implemented MySQL extensions so far have read the code, more than the documentation.
But, back to the point. The void is filled now, and in such a wonderful way! I have to disclose that I was the reviewer of this book, and while this fact puts me in a conflicting position when it comes to a review, it has also given me inner reason for praise, beyond the experience of a regular reader. The people who have worked with me will know that I am hard to please, and so my review was peppered with suggestions for improvements that, I admit it, made the authors' life quite difficult. I mostly complained that some of the examples proposed for each chapter were not enough useful and interesting. In short, there was not enough incentive for the reader to start coding immediately. I am glad to report that the authors were very responsive, and, rather than being annoyed by my demands, did work enthusiastically at the examples in the book until they became a true gem in this category. This book will surprise you for its practical approach, when it guides you through the examples, until you end up with a working extension.
The first chapter is probably the part that will save many hours of attempts to everyone who wants to build a plugin. In that chapter the authors explain how to build the different types of plugins in several operating systems. If you have ever tried to build a plugin, you will appreciate this chapter.
The book's body covers every type of plugin that you can create in MySQL, from the classic UDF to the more recent storage engine. For each chapter, you will have code ready to compile and try on your own, and the line-by-line explanation of what that code does, and why it was written the way it is.
Whenever the code is less than intuitive, the authors take care of it with additional theoretical and practical explanation, making sure that the reader is fully informed on the topic, and can complete the task effectively.
Summing up, this is not a book for the idle reader. It's a a practical help for the doers, the ones who want to get the job done, and need guidance through the maze of the MySQL plugin protocol.

Update. There is also a review at Slashdot.

Vote on Planet MySQL