Bug #34189 ALTER TABLE t1 ENGINE=FEDERATED CONNECTION='connectionString' on MyISAM fails
Submitted: 31 Jan 2008 9:23 Modified: 31 Jan 2008 10:33
Reporter: Patrick Savelberg Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.0.54, 5.0.51 OS:Any
Assigned to: CPU Architecture:Any
Tags: ALTER TABLE, Connection, engine, federated

[31 Jan 2008 9:23] Patrick Savelberg
Description:
Altering a MyISAM table to a FEDERATED table does not work.

How to repeat:
I'm trying to execute the following two statements:

CREATE TABLE federated.fed_produkt
(
produkt_id Int NOT NULL AUTO_INCREMENT,
produkt_code Char(20) NOT NULL,
produkt_omschrijving Varchar(255),
PRIMARY KEY (produkt_id)
) ENGINE = MyISAM;

which works just fine, but

ALTER TABLE federated.fed_produkt
ENGINE = FEDERATED
CONNECTION='mysql://username:password@servername:3306/dbname/tblname';

generates the following error:

"Table storage engine for 'fed_produkt' doesn't have this option"
[31 Jan 2008 10:33] Valeriy Kravchuk
Thank you for a bug report. Verified just as described. If it is intended behaviour (as I assume) it should be clearly documented at:

http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

and 

http://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html
[4 Feb 2008 20:09] Omer Barnir
Workaround: Instead of an alter the table can be dropped and recreated
[2 Oct 2009 16:35] Nathan Zook
I think that the most likely reason to want to do this is if one is attempting to create the federated table programatically.  In that case, the solution is to alter the original create statement by appending ENGINE = FEDERATED CONNECT = 'connect_string' to the create statement of the target table.

I have added a tip in the 5.0 CREATE TABLE documentation.