The title pretty much says it all. I was wondering how changes to the remote or local table (e.g. adding a column) would affect the connection between them and could not find any resources about it. So does this work? (I assume it does not, because otherwise there would not be the constraint that both must have the same schema in the first place) But if it works, is it bi-directional and what steps have to be done?
I would appreciate any help and especially links to resources about this problem.
If you alter the base table on the remote system, you would have to DROP and then re-CREATE the federated table that connects to it.
https://dev.mysql.com/doc/refman/8.0/en/federated-usagenotes.html says:
The FEDERATED storage engine supports SELECT, INSERT, UPDATE, DELETE, TRUNCATE TABLE, and indexes. It does not support ALTER TABLE...
There is no way for the FEDERATED engine to know if the remote table has changed.
https://dev.mysql.com/doc/refman/8.0/en/federated-create.html says:
When you create the local table it must have an identical field definition to the remote table.
So maintaining a federated table is a somewhat manual process, and it's not supported to have continuous access to it if you ALTER TABLE on the remote end.
Frankly, I've never found a good use for federated tables. I'd rather code my application to connect to multiple database instances and query the tables directly.