the devmin quest

link / alias tables in mysql

---

• Author: Olivier El Mekki
• March 8, 2008
• Category: sysadmin

This article discusses the way to make a table point to another in mysql.

context

When you try to integrate a website in a other, you certainly don’t want to have to redesign each one, especially when you’re not the one that wrote the code in first place.

This is what happened to me when I tried to integrate a rails forum in a mediawiki website. It was easy to get the CSS and put it in the forum, but what about session authentication? In mediawiki, this is achieved by matching a token in a user cookie against a token from the mw_user table in the database. If you don’t want the user to have to register in the wiki and in the forum, you’ve got to use this table.

The first solution is to let the forum directly access on the table, ie granting the forum user access to it, but I wanted to keep the forum on a distinct database. There was also the problem of the naming convention of rails database tables. So far, the best solution I found was to make an alias table in the forum database, linked to the mediawiki’s one.

method

This is a simple task using the MERGE engine from mysql. MERGE table are used to get a persistent union table. If you link it to only one table, you’ve got an alias table.

There’s a little issue with MERGE engine : it only works with MyISAM powered tables, so if you want to link your alias table against an InnoDB one, you’ve got to change its engine first. I don’t know all of the implications of changing the engine of a table, so I suggest you run test before changing it on production databases (but as wise people, you always do so, don’t you?). I had to change the engine of the mw_user table on the wiki, and it was troubleless for mediawiki.

Enough speaking, here’s the trick. For the example, we’ll say that we want to make an authors table in the forum_development database that link to the mw_user table in the mediawiki database:

use forum_development ;

alter table mediawiki.`mw_user` ENGINE=MyISAM ;

create table `authors` select * from mediawiki.`mw_user` ;

delete from `authors` ;

alter table `authors` ENGINE=MERGE ;

alter table `authors` UNION=(mediawiki.`mw_user`) ;

The second line change the target table’s engine as we’ve said above.

The third and fourth lines create the new table by duplicating the target one, and then avoid it. The point is that an alias table (any MERGE table, actually) must have the same definition that the target one. So we just duplicate the table and kick out its data. We may simpler has made an “show create table mediawiki.`mw_user`”, but I keep this to remember how to duplicate tables.

The fifth and sixth lines bring the magic. One change to engine to MERGE, the other link the table to the target. You’ve got to keep the bracket around the target name, even if there’s only one target, or it won’t work.

And that’s all. The forum can now use the authors table transparently, without even needing to have any privilege on the target table. All changes made to one change the other, as any kind of regular link.

• 2 Responses
• Leave a comment...
• Comment Feeds