Registrering fails for database with federation
![evaluator](https://us.v-cdn.net/6029854/uploads/defaultavatar/nZUSABQN8JEE0.jpg)
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:
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.
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
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
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?
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
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:
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.