Registrering fails for database with federation

evaluator

I am evaluating MySQL Compare.

When trying to compare two databases where one contains a federated table (using the FEDERATED database engine), registering fails for the database containing the federated data (not for the other), aborting the comparison operation.

Only a portion of the MySQL Compare error message fits the screen:

The foreign data source you're trying to reference does not exist. Data source error: error: 1142 'SELECT command denied to user 'fedUser'@...

Accessing the sole federated table in MySQL Workbench 5.2.47 which uses "fedUser" as user in its connection works fine. It clearly exists and data is retrieved. So why is MySQL Compare complaining about this? :?:

The connection method is TCP/IP for the problematic database containing the federated table; SSH for the other database.

Michael Christofides

Hello,

Thanks for your message. I've not seen that before, but it seems from an initial search that the rest of that error message could be useful (e.g. http://pento.net/2009/05/05/dont-forget ... ed-tables/)

To get it would you mind turning on verbose logging, recreating and sending the files in to mysql@red-gate.com?

Details on how to do so here (it's the same for lots of Red Gate tools, the images shown are for our Oracle tool):
http://documentation.red-gate.com/displ ... +log+files

Thanks again,
Michael

evaluator

Thanks for your response. I enabled logging and recreated the error:

16:30:46.801|Info   |Logging             |1  |Current Logging levels enabled: Verbose,Warning,Fatal,Debug,Information,Trace,Error
16:31:01.812|Info   |Serializer          |1  |:Deserializing object from stream
16:31:01.817|Debug  |Serializer          |1  |:Reading serialization info for type RedGate.Compare.EngineController.Schema.MySQL.MySQLSchemaProject
16:31:01.817|Debug  |Serializer          |1  |:Reading serialization info for type RedGate.Compare.EngineController.MySQL.DataSources.LiveMySQLSshDataSource
16:31:01.818|Debug  |Serializer          |1  |:Reading serialization info for type RedGate.Compare.EngineController.MySQL.DataSources.LiveMySQLTcpIpDataSource
16:31:01.818|Debug  |Serializer          |1  |:Reading serialization info for type RedGate.Compare.EngineController.Schema.MySQL.Options
16:31:01.819|Debug  |Serializer          |1  |:Reading serialization info for type RedGate.Compare.EngineController.Schema.MySQL.DifferenceFilter
16:31:01.819|Debug  |Serializer          |1  |:Reading serialization info for type RedGate.Compare.EngineController.Schema.MySQL.SelectedRows
16:31:13.681|Debug  |PopulationLogger    |12 |ProgressTask:Populating DB FirstDB
16:31:13.681|Debug  |PopulationLogger    |12 |ProgressTask:Populating Dependencies
16:31:13.757|Debug  |PopulationLogger    |12 |ProgressTask:Populating Tables
16:31:13.922|Debug  |PopulationLogger    |12 |ProgressTask:Populating Columns
16:31:13.941|Debug  |PopulationLogger    |12 |ProgressTask:Populating Indexes
16:31:13.996|Debug  |PopulationLogger    |12 |ProgressTask:Populating Constraints
16:31:13.997|Debug  |PopulationLogger    |12 |ProgressTask:Populating Index Constraints
16:31:14.163|Debug  |PopulationLogger    |12 |ProgressTask:Populating FK Constraints
16:31:14.495|Debug  |PopulationLogger    |12 |ProgressTask:Populating Views
16:31:14.499|Debug  |PopulationLogger    |12 |ProgressTask:Populating Routines
16:31:14.579|Debug  |PopulationLogger    |12 |ProgressTask:Populating Triggers
16:31:14.601|Debug  |PopulationLogger    |12 |ProgressTask:Populating Events
16:31:14.637|Debug  |PopulationLogger    |12 |ProgressTask:Populating DB SecondDB
16:31:14.637|Debug  |PopulationLogger    |12 |ProgressTask:Populating Dependencies
16:31:15.065|Error  |Engine Service      |1  |:Exception in progress dialog
The foreign data source you are trying to reference does not exist. Data source error:  error: 1142  'SELECT command denied to user 'fedUser'@'

The error message seems to be truncated in the log file as well.

I have discovered that both databases actually contain FEDERATED tables, and one of those registers fine. So I suspect that MySQL Compare requires more/different rights than MySQL Workbench and other applications of MySQL. Could that be true?

Michael Christofides

Thank you for doing so, my apologies, it seems that was the entire error message after all.

I think you've worked it out, MySQL Compare requires minimum rights of 'select' for Tables and Views, and 'execute' for other objects like functions and procedures. (Naturally more access is required if you wish to then run the deployment script.)

It doesn't seem clear to me on the MySQL Workbench site whether they require lower permission levels than that.

Is granting feduser select permission on that table an option for you?

Best regards,
Michael

evaluator

Problem is now solved. Thanks for all help.

Turns out the problematic database had an unknown and invalid federated table which indeed was impossible to access. However, that table's existence was not known to me. And since the MySQL error message was truncated (by MySQL it turns out, not Redgate, omitting the name of the table), it was only natural to misunderstand it.

Due to the perceived poor error reporting capabilities of MySQL, I think it would be very helpful if the Redgate product could echo extra debugging data (such as the name of a problematic item when iterating through the tables, views etc.), instead of simply forwarding the inadequate error output of MySQL. That would make it easier to understand and solve problems of this kind. :idea:

Michael Christofides

Fantastic, thank you for letting us know.

That's a really nice idea, I'll add it to our ideas for the next version. We don't have confirmed plans as yet though.