Red Gate forums: MySQL Compare

RE: SSL Connections to MySQL Server

Michael Christofides — Tue, 11 Jun 2013 15:50:25 GMT

Thanks James, that's great to hear. Don't hesitate to get in touch if you have any other requests, issues or feedback.

Michael

RE: SSL Connections to MySQL Server

james.healy — Tue, 11 Jun 2013 15:48:19 GMT

Thanks Michael, sounds good.

Our company has three licences I believe, and we get great use out of the tool on a regular basis so would be interested in this feature.

James

RE: SSL Connections to MySQL Server

Michael Christofides — Tue, 11 Jun 2013 14:47:49 GMT

Hi James,

Unfortunately not. Thanks for the request though, it reminded me I've been wanting to set up a public feature request forum for our MySQL tools, which I've now done:
http://redgate.uservoice.com/forums/210413-mysql-tools

I've taken the liberty to add your request, no doubt others will add votes and comments for it over time. This will help us prioritise future work on the tools and find people to discuss solutions with.

We have no confirmed plans for additional features at this point, but I'll update the feature request and notify you if that changes.

Out of interest do you own the tool or are you evaluating options?

Michael

SSL Connections to MySQL Server

james.healy — Mon, 10 Jun 2013 13:39:22 GMT

I couldn't find this functionality after perusing the project setup options but maybe missed it: does RedGate MySQL Compare allow for SSL connections to the MySQL server somehow? (I do see the SSH options.) We've recently changed our MySQL accounts to all require SSL.

If not, any plans to add it in the future?

Thanks,

James

RE: Registrering fails for database with federation

Michael Christofides — Tue, 28 May 2013 09:58:06 GMT

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.

RE: Registrering fails for database with federation

evaluator — Mon, 27 May 2013 11:56:44 GMT

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.

RE: Registrering fails for database with federation

Michael Christofides — Fri, 24 May 2013 08:24:26 GMT

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

RE: Registrering fails for database with federation

evaluator — Thu, 23 May 2013 14:47:35 GMT

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

Code:

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?

RE: Registrering fails for database with federation

Michael Christofides — Thu, 23 May 2013 09:10:21 GMT

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

Registrering fails for database with federation

evaluator — Tue, 21 May 2013 12:48:50 GMT

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?

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

RE: Feature request: "Compare to folder"

MelissaOlson — Wed, 08 May 2013 06:26:00 GMT

I'd love to hear about any luck you had finding a tool as well. We are just beginning our MySQL implementation and will have a need to compare to SVN as well.

RE: Database registering slow

Michael Christofides — Tue, 30 Apr 2013 13:50:33 GMT

Fantastic, and thank you for taking the time to post the solution, I'm sure others will find this useful in future.

You may also wish to turn off logging now (if you haven't done so already) as this can also slow you down a little.

All the best,
Michael

RE: Database registering slow

bstewart — Tue, 30 Apr 2013 12:34:15 GMT

Fixed:

And another update. I had a look at the server and innodb_stats_on_metadata was set to 1. I set this to 0 with the following:

Code:

SET GLOBAL innodb_stats_on_metadata=0;

Or you could set it in your my.conf. Now when I run a comparison, it takes about 6 seconds to register a database on that server which is much faster than I was getting before. Credit to this post for details on the effect that setting has on information_schema query peformance:

http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

RE: Database registering slow

Michael Christofides — Tue, 30 Apr 2013 12:31:12 GMT

bstewart wrote:

Thank you, it looks like the issue is somewhere around populating index and FK constraints, is there anything obviously different about this server in terms of constarints?

In the meantime I'll ask the team if there's any obvious reason this might be the case.

RE: Database registering slow

bstewart — Tue, 30 Apr 2013 12:22:11 GMT

Another quick update here. I've noticed that queries involving the information_schema database on the server in question are very slow which would suggest the problem doesn't lie with MySQL compare.