Jonathan Gazeley

Yesterday at work I had the need to create a federated table in MySQL. I read about the federated engine and thought I had it sussed. I noted:

Beginning with MySQL 5.1.26, the FEDERATED storage engine is not enabled by default in the running server; to enable FEDERATED, you must start the MySQL server binary using the --federated option.

Turns out it’s also possible simply to add the line federated in the [mysqld] section of /etc/my.cnf

The version of MySQL currently installed on my CentOS box was an older one (5.0.45) but I added this line anyway. The server refused to start. It quickly became clear that the MySQL binary packaged with CentOS was not compiled with the federated engine.

Fedora is currently packaging MySQL 5.1.37 but it seems that this too is lacking the federated engine. That’s annoying – I had wanted to install a version of MySQL from some yum repo or other, so I don’t have to keep upgrading the package every time a new version is released.

Perhaps the lack of federated support is a Red Hat (and derivatives) issue. I downloaded the rpm from MySQL directly, and installed it. Guess what – no federated engine compiled in.

So I downloaded the source tarball. I explicitly configured it with the federated engine, like so:

./configure --with-plugins=federated

And then I built and installed it. Nothing worked properly out of the box, and I was annoyed to find that the make install command doesn’t do half of the things I would normally expect it to do. I found this information and followed the steps to get it working. I had to steal and tweak the /etc/init.d/mysqld script from a different box which was running the bog-standard CentOS package.

Woohoo! The federated engine was finally available.

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

But I can’t understand why none of the binary builds of MySQL include it. Fair enough that isn’t enabled by default in the running server – it’s no problem to add a line to my.cnf on a standard CentOS box. But it is a nuisance to have to build from source. It doesn’t break anything to have it enabled in a build, even if unused.

Of course CentOS won’t change the way they build their packages until Red Hat does. So I’m doing what I can, and I have filed a feature request with Fedora in the hope that in the next major release, there will be a version of MySQL built with the federated engine.