Red Gate forums :: View topic - Registrering fails for database with federation
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
MySQL Compare
MySQL Compare forum

Registrering fails for database with federation

Search in MySQL Compare forum
Post new topic   Reply to topic
Jump to:  
Author Message
evaluator



Joined: 21 May 2013
Posts: 3

PostPosted: Tue May 21, 2013 1:48 pm    Post subject: Registrering fails for database with federation Reply with quote

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:

Code:
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? Question

The connection method is TCP/IP for the problematic database containing the federated table; SSH for the other database.
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 88
Location: Red Gate Software

PostPosted: Thu May 23, 2013 10:10 am    Post subject: Reply with quote

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-to-alter-your-federated-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/display/SCO130/Logging+and+log+files

Thanks again,
Michael
Back to top
View user's profile Send private message
evaluator



Joined: 21 May 2013
Posts: 3

PostPosted: Thu May 23, 2013 3:47 pm    Post subject: Reply with quote

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

Code:
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?
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 88
Location: Red Gate Software

PostPosted: Fri May 24, 2013 9:24 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
evaluator



Joined: 21 May 2013
Posts: 3

PostPosted: Mon May 27, 2013 12:56 pm    Post subject: Reply with quote

Problem is now solved. Very Happy 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
Back to top
View user's profile Send private message
Michael Christofides



Joined: 20 Apr 2011
Posts: 88
Location: Red Gate Software

PostPosted: Tue May 28, 2013 10:58 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group