MySQL 5.2

I installed MySQL 5.2 today a la Markus Popp although I note his comment that it may not in fact be any different from 5.1 as yet. I have installed MySQL from source before, in the form of the 5.1 source download package. I did BK pulls of both the 5.1 and 5.2 sources, noticing that they required me to upgrade my Bison compiler, where previous builds of 5.1 worked okay with the version of Bison bundled with Slackware 10.2.

Then I went to install it on my aging Thinkpad only to have the hard drive choose this particular moment to give up the ghost. I happen to like this particular system because when people ask about what sort of footprint to expect, I point out my Thinkpad with the 300 MHz Pentium II, 64MB ram and 10GB harddrive running Slackware, Apache, MySQL and PHP. I've been using it steadily for five years and it was refurbished at that, so I can't be too hard on it. I plan to buy a 60GB replacement drive and possibly some more RAM since it has a slot open, and thereby breathe new life into it.

Federated Tables

A number of people have been recently been inquiring about MySQL FEDERATED tables or are having requirements that seem ideally met using FEDERATED tables, so I thought I would perform some testing and better acquaint myself with the behavior of this storage engine. The first thing I noticed was the spot in the documentation that said:

The structure of this table must be exactly the same as that of the remote table, except that the ENGINE table option should be FEDERATED and the CONNECTION table option is a connection string that indicates to the FEDERATED engine how to connect to the remote server.

So I wrote a little PHP script that does a SHOW CREATE TABLE on the base table, alters the ENGINE clause, and adds the appropriate CONNECTION clause. After all if it has to be exactly the same, I may as well automate it and eliminate typos as a source of error.

Then I wrote another PHP script which created a series of base tables, and connected to each of these a series of FEDERATED tables with obvious differences. Lo and behold, it works! The only actual requirements I have been able to discern are:

The base table has to exist. The FEDERATED engine checks this at table creation time.

Columns in the FEDERATED table have to have columns by the same name in the base table.

Column types don't have to match. I haven't tried all the combinations, but I have gotten it to convert dates to strings, numbers to strings, strings to numbers, etc. I have also successfully connected to views.

The question was posed in the forums as to what connections are made and how long they persist. A connection is made briefly, obviously, during table creation since the base table is checked for existence. A persistent connection is made when the table is queried, and hangs about for a period of time controlled by wait_timeout on the remote server. On my system this is set to 28,800 or exactly 8 hours.

So all in all this engine seems like a fairly flexible and useful tool, and beyond the obvious function of making tables availabe to remote servers, can be used to do some data type-casting as well.

Switching to RSS

I have finally decided to start using an RSS reader. Recent changes in the MySQL forums' behavior, layout and features have made it hard for me to see what forums have new posts have been made— so I will give the reader a try for a while and see if I like it better. I have subscribed to most of the forums, not that I am knowledgeable in all areas, but I find it interesting to read posts on subjects outside of my direct bailiwick.

So far so good, though I notice the feeds for a couple of MySQL forums aren't working, Database Adminstration and .Net. None of the foreign language feeds are working either, but I expect that is because they are brand new and haven't been posted in yet. I like the format of my chosen RSS client, similar to that of an email program for example the one I am using to peruse the news groups. Ultimately it would be nice to have one client that pulls in everything, but this works for me right now.

I can also follow Planet MySQL in the reader too, though that has been working well enough from the web page.