Don’t Forget to Alter your Federated Tables!

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:

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:

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

1 thought on “Don’t Forget to Alter your Federated Tables!”

  1. In 5.1, you can use a federated server to do this:

    create server ‘server_one’ foreign data wrapper ‘mysql’ options
    (HOST ‘127.0.0.1’,
    DATABASE ‘first_db’,
    USER ‘root’,
    PASSWORD ”,
    PORT 3306,
    SOCKET ”,
    OWNER ‘root’);

    CREATE TABLE t1 (
    id int(20) NOT NULL,
    name varchar(64) NOT NULL default ”
    )
    ENGINE=”FEDERATED” DEFAULT CHARSET=latin1
    CONNECTION=’server_one’;

    then, change what t1 connects to by:

    ALTER SERVER ‘server_one’ options(DATABASE ‘second_db’);

    This makes it so you don’t have to alter the table t1, so if you have 100s of tables pointing to a given server/database, you just have to change the server and not all 100 tables !

    Yes, I will be improving federatedx 😉

Comments are closed.