Don’t Forget to Alter your Federated Tables!

May 5, 2009 · Posted in MySQL · 1 Comment 

If you’re using the Federated engine, here’s something important to remember (apart from the usual advice of “please don’t”). If you need to change the structure of the remote table, always remember to update the Federated table. If not, when you try to use the table, you’ll get this error:

mysql> SELECT * FROM foo;
ERROR 1030 (HY000): Got error 1 from storage engine

This error isn’t really helpful. The problem is, the Federated engine only checks that the remote table structure is correct when it initially connects. Once it has connected, no more checks. When you restart the server, you get a much more helpful message:

mysql SELECT * FROM foo;
ERROR 1431 (HY000): The foreign data source you are trying to reference does not exist. Data source error:  error: 1054  'Unknown column 'b' in 'field list''

Also, keep your eye on the FederatedX project. It’s still under development, but will hopefully upgrade the Federated engine to being useful again.

Tags: <, >