Problems With Data Import

[ruirib]

I am using SQLYog Ultimate 8.3. While trying to transfer a database to a remote server, I have been experiencing some difficulties.

First, a direct import of the SQL dump is unfeasable, do to its slowness (in spite of my reasonably high upload speed, usually well above 4 Mbit/s). I don't ever remember seeing SQLYog being so slow and I have done quite a few migrations.

Secondly, I have tried to overcome this slowness by using the Copy Database to Different Host / database. I found using Bulk Insert to be totally unreliable. Bigger tables (10k + records) all failed to be uploaded, but what is worse is that SQLYog didn't complain at all. I found out about the failure after seeing that the tables had very few records compared to what they used to have).

I really don't undersstand this. How its it possible that a database transfer fails so clearly and SQLYog fails to notice it?!! What is going on? This is not the SQLYog I have been reliably using for quite a few years now!

The only way that seems to work is not use bulk insert, but then all gets painfully slow !

[Rohit]

This is very strange. We don't have a similar report elsewhere

Can you try the same import with an old copy of SQLyog? From you description it looks like the problem has cropped up recently.

Anything else you can do to help us reproduce the problem? Can you attach some sample cases by creating a private ticket at http://www.webyog.com/support

In the meantime, we will try to reproduce your problem in a similar environment.

Can you tell me the following info?

1) Size of the SQL dump

2) Structure of the target tables

3) Target MySQL version

4) Innodb or MyISAM tables (Insert on Innodb is sometimes slower than MyISAM)

If we can reproduce the problem, we guarantee a fix within 72 hours.

[ruirib]

'Rohit' wrote on '02:

This is very strange. We don't have a similar report elsewhere

Can you try the same import with an old copy of SQLyog? From you description it looks like the problem has cropped up recently.

Anything else you can do to help us reproduce the problem? Can you attach some sample cases by creating a private ticket at http://www.webyog.com/support

In the meantime, we will try to reproduce your problem in a similar environment.

Can you tell me the following info?

1) Size of the SQL dump

2) Structure of the target tables

3) Target MySQL version

4) Innodb or MyISAM tables (Insert on Innodb is sometimes slower than MyISAM)

If we can reproduce the problem, we guarantee a fix within 72 hours.

Hi,

Thank you for your reply.

I was very surprised with this, as I have transferred a few databases and some with sizes of up to 1 GB and never felt this was too slow. This was with a dump file with just 40 MB and it took a lot of time. Also, these were MyISAM tables.

Anyway, I found a solution – I started using the Compressed Protocol. Surprisingly, something that was taking hours, took less than 3 minutes! Have tried a few times afterwards and it's always been that fast. Also, I have tried one of the biggest tables in the problematic database and was able to transfer it to another server very fast, without using the compressed protocol.

So, whatever it was, was related to the server I was uploading to and the compressed protocol resulted in amazingly fast transfers. I don't know why the help about compressed protocol advises against its use (well, almost). I used it as a last resort!!!! Seems I will be using it a lot more.

Anyway, problem solved.

Thanks for a great product .

Regards

[Author]

Posts