MySQL Proxies

While searching for a MySQL proxy solution, I found the following four products:

• MySQL-proxy
• Spock proxy
• DPM (Dormando's proxy module)
• Proximo

At least two of them share the same codebase, MySQL-proxy being the forefather of Spock proxy. Spock proxy tries to increase performance by eliminating the scripting layer. The goal of Spock is to provide efficient sharding, not fault-tolerance. Dormando's proxy positions itself as an alternative to the official MySQL-proxy, and it tries to retain compatibility. It even supports Lua scripting. I'm not sure, though, if the API is the same. And, finally, Proximo is written in Perl, which means its performance is lower than that of the other competitors. But Proximo is in very early stage of development and has a promising architecture. It may have good future.

Slightly disappointed with Percona products

A large part of my to-do list was devoted to the products by Percona, company well-known in the MySQL world. I installed XtraDB engine on our servers and began using Xtrabackup to backup our databases. Not bad, I have to admit. I mean, the servers still work and the backups are made. But I had to solve so many problems that I kept recalling my first days with Linux almost fifteen years ago, when you had to compile everything from sources, manually track dependencies and even then there was only one chance in two that the program will run.

First, I installed XtraDB, MySQL engine which is supposd to cure the long-standing deficiencies of InnoDB. It was not too difficult. I downloaded the sources of XtraDB-1.0.6, tried to compile, failed, found a description of a non-standard (easily explainable, though) installation procedure, copied the sources to the MySQL source tree, replacing the InnoDB engine, tried to compile, found an error, googled for a solution, fixed a bug in handler/i_s.cc, tried to compile, found an error, googled for a solution, fixed a bug in Makefile.in, tried to compile, succeeded, installed and there it is. Easy, right? :)

Next, I wanted to have a look at another product by Percona, Xtrabackup, version 1.0 of which was announced in December 2009. Xtrabackup is to become the main backup solution for MySQL, being the only free tool able to perform online backups. There is more than one link at Percona.com leading to the sources. Or should I say misleading? Here's one page saying you have to use Bazaar to get the sources. So I did. There were some screens chockfull of error messages, which I tried to quench with a bunch of header files stolen from MySQL source tree, but to no avail. I checked the Percona web-site once again and found another link. This tarball included a whole distribution of MySQL. I tried to compile it as it is. Then, I tried to copy the sources to MySQL sources, strictly following the recommended procedure. Then, I tried to copy the sources to my source tree (with XtraDB substituted for InnoDB, as described above). I admit I almost gave up. In the end, I downloaded a binary version of Xtrabackup, compiled by the authors. It just worked. Well, to a degree...

This package contained Xtrabackup itself and a Perl script called Innobackupex. The problem with Xtrabackup is that it does not support anything but InnoDB (and XtraDB). Fortunately, MyISAM tables can be just copied as files, and Innobackupex does exactly this. Unfortunately, Innobackupex does this only when making full backups. Incremental backups only include InnoDB tables.

This problem was easy to fix, but there was another. Innobackupex does not support the option --incremental-basedir specifying the last full backup. The reference point for the incremental backup is defined as the earliest directory. So, I had to add support for this option to the script manually.

So, to sum it up, the number of problems I met in Percona products is unusually high. Or is it normal for a company that makes money solving users' problems? :)

Ubuntu: Sudo vulnerability

Not too dangerous, unless you grant sudo rights to too many people, but worrying enough: Ubuntu Security Notice USN-905-1:

sudo did not properly validate the path for the 'sudoedit' pseudo-command. A local attacker could exploit this to execute arbitrary code as root if sudo was configured to allow the attacker to use sudoedit. The sudoedit pseudo-command is not used in the default installation of Ubuntu.

And another one, only a little bit more unnerving:

sudo did not reset group permissions when the 'runas_default' configuration option was used. A local attacker could exploit this to escalate group privileges if sudo was configured to allow the attacker to run commands under the runas_default account. The runas_default configuration option is not used in the default installation of Ubuntu.

Testing new MySQL on production server

Even when your MySQL works on a mission-critical server, there is a way to make downtime as short as possible after you have compiled a new version of the DBMS. MySQL documentation contains a chapter called 5.6. Running Multiple MySQL Servers on the Same Machine. But the reality is a bit more complicated.

First of all, you have to create a test database with the mysql_install_db command. Create an empty directory called, for example, test-db in your home directory and give full permissions to all users, to avoid problems with mysql user trying to create files:

$ mkdir test-db $ chmod 777 test-db

Now, let's populate the directory with a test database:

$ mysql_install_db --user=mysql --datadir=/DIR/db

Note that we assume that your database runs under the username mysql, as it should. Now, you can try to run the test database. It must listen on a different IP port, use different data directory, different files for socket and pid, different log and error log. Hence, the following command:

$ PATH-TO-NEW/mysqld --port=12345 --datadir=/DIR/db --socket=/DIR/db/sock --pid-file=/DIR/db/pid --log=/DIR/db/log --log-error=/DIR/db/log-err

Now, you can check whether the new, fresh copy of MySQL works. If it does, you can stop the running version and then run make install for the new one.

Note that we did not grant any permissions to any users, so you might not be able to connect to the new database with mysql client.

MySQL+XtraDB: fixing compilation errors

Trying to launch MySQL with the new XtraDB engine. First, I downloaded MySQL sources with patches by Percona and untarred them. They compile without errors and MySQL starts without problems. However, this tarball uses InnoDB, while we wanted to test XtraDB.

Next, I downloaded XtraDB 5.1.42-1.0.6-9 sources from Percona web-site. According to the installation instructions, to use XtraDB, you have to replace the contents of storage/innobase directory with the contents of the XtraDB archive. This time, I received two error messages during the compilation phase, but both of them had already been addressed by the developers and described at Percona's Launchpad.net. In the first case, the file handler/i_s.cc contained a minor error at line 801: error: invalid conversion from ‘const char*’ to ‘char*’

To fix this,

if((p = strchr(index->table_name, '/')))

in line 801 in handler/i_s.cc has to be replaced with:

if((p = strchr((char *) index->table_name, '/')))

Then I got another error message: ha_innodb.cc:2622: undefined reference to `active_mi'. To solve this second issue, I had to add disable compilation of MySQL embedded server by adding the following option to the ./configure script: --without-embedded-server.

So, finally, make command succeeded and I tried to install MySQL, but then there was the third error:/bin/sh: @MKDIR_P@: command not found. Fortunately, someone else had seen this message before and the solution is available here. In the file /storage/innobase/Makefile.in line MKDIR_P = @MKDIR_P@ must be replaced with MKDIR_P = @mkdir_p@.

So, to save your time, do these three modifications before you run ./configure. So, the session transcript would look like this:

$ wget http://www.mysqlperformanceblog.com/mysql/5.1/\
source/mysql-5.1.26-percona.tar.bz2
$ wget http://www.percona.com/percona-builds/Percona-XtraDB/\
Percona-XtraDB-5.1.43-9.1/source/percona-xtradb-1.0.6-9.1.tar.gz
$ tar -xjf mysql-5.1.26-percona.tar.bz2
$ tar -xzf percona-xtradb-1.0.6-9.1.tar.gz
$ rm -r mysql-5.1.26-percona/storage/innobase/*
$ mv percona-xtradb-1.0.6-9.1/* mysql-5.1.26-percona/storage/innobase
$ cd mysql-5.1.26-percona/
mysql-5.1.26-percona$ sed -i 's/strchr(index/strchr((char *) index/' storage/innobase/handler/i_s.cc
mysql-5.1.26-percona$ sed -i 's/@MKDIR_P@/@mkdir_p@/' storage/innobase/Makefile.in
mysql-5.1.26-percona$ ./configure '--build=x86_64-linux-gnu' \
'--host=x86_64-linux-gnu' '--prefix=/usr' '--exec-prefix=/usr' \
'--libexecdir=/usr/sbin' '--datadir=/usr/share' \
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include' \
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--with-server-suffix=-1ubuntu2' \
'--with-comment=(Ubuntu)' '--with-system-type=debian-linux-gnu' \
'--enable-shared' '--enable-static' '--enable-thread-safe-client' \
'--enable-assembler' '--enable-local-infile' '--with-pic' \
'--with-lib-ccflags=-fPIC' '--with-pstack' '--with-fast-mutexes' \
'--with-big-tables' '--with-unix-socket-path=/var/run/mysqld/mysqld.sock' \
'--with-mysqld-user=mysql' '--with-libwrap' '--with-ssl' \
'--without-docs' '--with-extra-charsets=all' '--with-plugins=max' \
'--without-ndbcluster' '--without-embedded-server' '--with-embedded-privilege-control' \
'build_alias=x86_64-linux-gnu' 'host_alias=x86_64-linux-gnu' \
'CC=gcc' 'CFLAGS=-O3 -DBIG_JOINS=1 -fPIC -fno-strict-aliasing' \
'LDFLAGS=-Wl,-Bsymbolic-functions' 'CPPFLAGS=' 'CXX=g++' \
'CXXFLAGS=-O3 -DBIG_JOINS=1 -felide-constructors -fno-exceptions \
-fno-rtti -fPIC -fno-strict-aliasing' 'FFLAGS=-g -O2'
mysql-5.1.26-percona$ make
mysql-5.1.26-percona$ sudo make install

Google Chromium

I've installed Google Chromium today to have a look at the browser features. I think I will put it aside for now. First, I could not find a place where I could define the minimum font size. Chromium's default fonts were painfully small. Second, in Firefox I set accessibility.tabfocus to 3, so that when I press TAB, the cursor moves from one entry field to another, skipping hyperlinks. I could not find something similar in Chromium. Third, Google Wave is not really much faster in Chromium than in Firefox. Fourth, there is no Zotero plugin for Chromium. On the other hand, Google Reader works like a charm in Chromium. Besides, there's a great extension for Chromium, called CrossFire, that imitates Opera "spatial keyboard navigation". So, I'll wait for a while till the numbers one to four above are sorted out...

Problems with Lotus Notes 8.5 under Ubuntu Karmic Koala

Lotus Notes does not work under the new Ubuntu. The application starts, but the internal window area contains nothing and does not redraw. Click a menu and if it overlaps with the main window, a part of the menu stays there after you close it. The main window contains nothing but garbage. Actually, nothing strange, since 99% of Notes databases contain nothing but garbage, but you can't even pretend you're working!
The problem is deep inside Notes (and Ubuntu). Go here: Ubuntu 9.10 Lotus Notes 8.5 grab a TGZ archive with four shared libraries and copy the libraries to /opt/ibm/lotus/notes, start Notes and do something useless there.