Registrering fails for database with federation

evaluatorevaluator Posts: 6
edited May 28, 2013 5:58AM in MySQL Compare
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.

Comments

  • 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
  • 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?
  • 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
  • Problem is now solved. :D 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:
  • 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.
Sign In or Register to comment.