Tuesday, May 19, 2009

Beware: myqldump and FEDERATED tables

mysqldump in 5.1 is broken in my opinion with respect to FEDERATED tables.

mysqldump insists on dumping all the rows of a FEDERATED table. That's bad when you go to restore from the dump. The normal case is that the remote table our FEDERATED table points to is fine and needs nothing done to it. The restore will try to re-INSERT all the rows into the remote table. But they are already there.

A solution to this problem is to isolate all FEDERATED tables within a single database, say "my_federated" for example. Don't put anything but FEDERATED tables in "my_federated".