Red Gate forums: MySQL Compare

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.

RE: Database registering slow

bstewart — Tue, 30 Apr 2013 12:10:59 GMT

I should also note that my network connection to the server is generally good. Pings are always < 1ms and I can transfer a 900MB ISO file in < 20 seconds.

RE: Database registering slow

bstewart — Tue, 30 Apr 2013 12:09:18 GMT

RE: Database registering slow

bstewart — Tue, 30 Apr 2013 12:01:16 GMT

Michael Christofides wrote:

Hi, thank you for the details.

Very strange indeed. In terms of logging, there are instructions described for an equivalent product here:
https://documentation.red-gate.com/display/SCO130/Logging+and+log+files

I assume you are using the same mode of transport (TCP/IP, Named Pie, or SSH) in each case, so that couldn't be the issue?

Best regards,
Michael

Hi Michael,

Yes, it's TCP/IP connections. I'll have a look at the logging link you sent.

RE: Database registering slow

Michael Christofides — Tue, 30 Apr 2013 10:38:32 GMT

RE: Database registering slow

bstewart — Tue, 30 Apr 2013 09:50:06 GMT

I'm seeing this same problem. We have an internal MySQL server we use for development. The details of this server are as follows:

OS - Windows Server 2008 R2 (64-bit)
MySQL version - 5.1.46-community

That sits on the same network as my development machine. It has 57 databases on it but there are only about 10 of them being used in active development and even at that, there is a very light load on the server. For arguments sake, we'll call this Server A. If I have to use MySQL compare to compare the schema of a database on Server A to any other database, you're talking at least 80 seconds for MySQL compare to register that database. If you're comparing two databases on Server A then that minimum time would double. The complexity of the schema appears to have little effect on the length of time taken to register the database. Even if I create a test database which contains just a single table with two INT columns, it still takes > 80 seconds to register the db.

The strange thing is that with any other remote servers I've tried, the registering of a database is usually complete within 5 seconds. Is there any logging built into MySQL compare that I can switch on to try and work out why this is taking so long?

RE: Comparison Failure, Index out of range

james.healy — Wed, 24 Apr 2013 18:39:17 GMT

Michael-

I wasn't able to get anywhere on this error - but we continued to work with the DB and some subsequent change to the schema allowed MySQL Compare to function correctly now! Not sure what it was unfortunately. If we encounter again I'll post more details.

Thanks,

James

RE: Comparison Failure, Index out of range

Michael Christofides — Mon, 15 Apr 2013 10:01:47 GMT

Any luck James?

RE: Comparison Failure, Index out of range

james.healy — Mon, 08 Apr 2013 20:07:37 GMT

Michael - sorry for my delayed response.

Unfortunately I can't send out the database schemas. These DB's should be quite similar - probably only on the order of a dozen differences between the two.

Am launching the comparison from scratch (a fresh New Project).

MySQL version is 5.5 (at Amazon RDS), and we've tried on both Windows7 and Windows8 machines.

I'm going to try to manually roll back some changes and see if I can isolate the problematic schema objects that are causing the crash. Will update if I can anything significant.

RE: Comparison Failure, Index out of range

Michael Christofides — Wed, 03 Apr 2013 16:49:57 GMT

Hello James,

Apologies for the delayed response. It seems to me you've hit a bug, thank you for the verbose logs.

If it would be possible to send us the schemas of the two databases in question, please do so to mysql@red-gate.com , this would hopefully help us recreate the issue.

Out of interest are you expecting these two database schemas to be identical, mostly the same, or to have lots (1000s) of differences?

Also, are you launching the comparison form a Project, or starting the comparison from scratch?

Any additional information like MySQL/Windows versions would be much appreciated.

Best regards,
Michael

Comparison Failure, Index out of range

james.healy — Fri, 29 Mar 2013 16:13:40 GMT