Bug #13724 federated tables structure not replicable
Submitted: 3 Oct 2005 18:51 Modified: 24 Nov 2005 0:21
Reporter: Giuseppe Maxia Email Updates:
Status: Closed Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.13/BK source 5.0 OS:Any (all)
Assigned to: Eric Herman CPU Architecture:Any

[3 Oct 2005 18:51] Giuseppe Maxia
In MySQL 5.0.13 a change for federated tables was made,
to move the connection string from COMMENT to CONNECTION.
While this works, federated tables now can not be backed up
since "SHOW CREATE TABLE" does not include the CONNECTION

How to repeat:
try using the output of
SHOW CREATE TABLE federated_table_name \G
to create a new table.
Or try this:
$ mysqldump --no-data -h host1 database_name federated_table_name | \
     mysql -h host2 database_name

Suggested fix:
         No direct workaround.
         When creating a federated table, add a COMMENT with the connection string,
         and then you can do something like

         $ mysqldump --no-data -h host1 database_name federated_table_name | \
                 perl -pe 's/\bCOMMENT\s*=\b/CONNECTION=/' | \
                 mysql -h host2 database_name

Notice that, if you have enough privileges to access the .frm file, you can get the
connection string with a regexp:
$ perl -lne 'print $1 if m{(mysql://[^/]+\@[^/]+/[^/]+/\w+)}' /datadir/dbname/fed_table.frm
[3 Oct 2005 19:09] MySQL Verification Team
Thank you for the bug report.

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) default NULL
1 row in set (0.00 sec)

mysql> select version();
| version()       |
| 5.0.15-rc-debug |
1 row in set (0.00 sec)
[11 Oct 2005 0:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

[20 Oct 2005 8:01] Jon Stephens
Please don't place bugs in Documenting status until you've pushed the fix, and indicated the applicable 3-part version number(s) here. Thanks.
[21 Nov 2005 14:31] Eric Herman
fixed in 5.0.16
[24 Nov 2005 0:21] Paul DuBois
Noted in 5.0.16 changelog.