如何通过Federate远程创建MySQL表？ALTER表操作报错求助

1. Creating a Federated test_table Remotely From Your Local Server #

Since you already know the target table must exist on the remote Federated server first, here's how to set up the local Federated table that links to it:

• First, confirm the Federated engine is enabled on your local MySQL server. Run this query to check:

  SHOW ENGINES;
  

  Look for Federated in the list with Support set to YES. If not, you'll need to enable it in your my.cnf/my.ini file by adding federated under the [mysqld] section, then restart the server.

• Next, create the local Federated table that maps to the remote test_table. The key here is the CONNECTION clause, which defines how to reach the remote server. Use this syntax:

  CREATE TABLE test_table (
    -- Match the exact column structure of the remote test_table here
    id INT NOT NULL AUTO_INCREMENT,
    data VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id)
  )
  ENGINE=FEDERATED
  CONNECTION='mysql://remote_user:remote_password@remote_host:remote_port/remote_database/test_table';
  

  Replace the placeholders (remote_user, remote_password, etc.) with your actual remote server credentials and details. Once this runs, your local test_table will act as a proxy to the remote one—any queries you run locally will be routed to the remote server.

2. Fixing the ALTER TABLE Error #

The error Table storage engine for 'levermanneasypivot' doesn't have this option happens because the Federated storage engine does not support ALTER TABLE operations to modify table structure. Here's why and how to fix it:

• Federated tables are just read/write proxies to the remote table—their structure is tied directly to the remote table. You can't add, remove, or modify columns from the local side; all schema changes must be done on the remote server first.
• The secondary error about the ACA.PA field not existing is likely a side effect of the first issue, but also note: when using a field name with a dot, you need to wrap it fully in backticks to avoid MySQL interpreting the dot as a database-table separator. For example, use `ACA.PA` instead of just ACA.PA.

To resolve this:

1. Log into the remote MySQL server where levermanneasypivot resides.
2. Run the ALTER TABLE command there:

   ALTER TABLE `levermanneasypivot` ADD `ACA.PA` INT(9) DEFAULT NULL;
   

3. Back on your local server, you can either:
   • Run FLUSH TABLES; to refresh the Federated table's schema cache, or
   • Drop and re-create the local Federated table (though flushing is usually sufficient).

That should get your schema updated and eliminate the error.

内容的提问来源于stack exchange，提问作者Walter Schrabmair