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.
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 😉