Bug #32038 mysqldump and read-only federated tables
Submitted: 2 Nov 2007 0:23 Modified: 18 Nov 2009 12:17
Reporter: Nicklas Westerlund (OCA) Email Updates:
Status: Won't fix Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:>=5 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[2 Nov 2007 0:23] Nicklas Westerlund
When having a federated table towards a read only source, and then taking a dump of it will include all data, then when inserting it for whatever reason, you will run into the "storage engine for 'table2' doesn't have this option".  It would be nice to have an option to include/exclude the data in a federated table. (as that should be handled somewhere else, although we still want the DDL)

How to repeat:
dump a federated table where the source is read-only, then try to restore from that logical dump. 

Suggested fix:
See attached patch.
[2 Nov 2007 0:23] Nicklas Westerlund
patch to handle --federated-data

Attachment: mysqldump-federated.diff (application/octet-stream, text), 2.00 KiB.

[7 Nov 2007 5:31] Valeriy Kravchuk
Thank you for a reasonable feature request and patch contributed.
[3 Feb 2009 18:16] Sergei Golubchik
Wouldn't it be easier to ignore federated always ? why one may need to *not* ignore it ? MERGE tables are, for example, always ignored, I think FEDERATED feels into the same category.

Anyway, you change the code in the wrong place. See where result= IGNORE_DATA set for MERGE tables.
[4 Feb 2009 19:06] Nicklas Westerlund
Sergei, if we are talking about data -- sure, that could, in theory, always be ignored (although there may be cases where someone wants the data, then change the table to another engine in the file and reload on another server), but if we're talking about table definitions, no -- because there is plenty of reasons of wanting the definitions included in a dump, but no data, don't you agree?
[4 Feb 2009 19:36] Sergei Golubchik
I'm talking about data.
See that place in the code that I mentioned in the previous comment.
[4 Feb 2009 20:06] Nicklas Westerlund

Ok. I'll take a look at the code over the weekend to implement the change there, but as we're talking about data, I agree, data could potentially always be ignored. I'm just not sure if there should be an option to include it but change the table to a local one in the process.
[6 May 2009 15:29] Jim Winstead
Dumping of data for FEDERATED tables was disabled by the fix for Bug #21360 in 5.0.80, 5.1.34, and 6.0.11.

Nicklas, do you still want to pursue adding an option to dump the federated data? My inclination is that if someone wants that data, they're probably better off dumping it from the place it is being federated from, but I would take a look at a patch.
[6 May 2009 15:49] Nicklas Westerlund

No, the bugfix you referenced to accomplish pretty much the same as this request and patch, with the difference that this gives you an option to include data if you need it for some reason, but the main cause was to avoid getting data out. 

I could probably still change the patch to match what has been committed and make sure it works, but it isn't needed as much now (well, as of 5.0.80) as before.  Thanks for the update!
[18 Nov 2009 12:17] Valeriy Kravchuk
So, looks like the fix is not needed any more.
[30 Jun 2013 7:13] ahmed mahmood
I have the same problem but on a windows machine how can I backup the database without the federated data then restore it? please assist me
[5 Jan 2017 10:35] Nick Weavers
Hi, my use case is that I have a service provided by a vendor that includes update access via the federated engine to a table in a database on their server (ie remote to me). When developing, I need to have a local copy (regular INNODB table) but since updating MySQL I find I can't now get a dump of their table to populate my table, even though I'm permitted to read, update or insert into it via the federated engine. I don't have access to their server via SSH so can't use mysqldump there and SFTP it back to my server, so I could really use the patch to handle the --federated-data flag.

It would also be useful to have support for restoring data from a dump from federated table via the same flag so If I do screw up on the remote live db, I can restore from data on my server. 

I hope you'll consider it.

Many thanks,
Nick Weavers