MySQL Bugs: #32038: mysqldump and read-only federated tables

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:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:	>=5	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
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.

patch to handle --federated-data

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

Thank you for a reasonable feature request and patch contributed.

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.

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?

I'm talking about data.
See that place in the code that I mentioned in the previous comment.

Sergei, 

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.

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.

Jim,

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!

So, looks like the fix is not needed any more.

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

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