Copy (table)/(db) To Different Host/db Won't Work!

[alan4573]

Hi – Can somebody please please please help.

I cannot get the copy table/db to different host/db function to work when copying from local to remote. The function works perfectly when copying from one local DB to another, but when trying to copy to remote get 1064 error – error in your syntax… blah blah blah …at line 1.

I read a while ago that this bug was fixed in 4.2 – I downloded it no joy, bug still there. I read yesterday that bug was fixed in 5. Downloaded it yesterday – still no joy, the bug is still there. Only way I can get around this in any fashion is to export as SQL then strip out the default char set bits as this also causes syntax errors then build and populate all DB's one table at a time manually. when doing this for a site with 70+ tables, you can understand why I dont like the workaround.

I am using SQLYog v5 on a local machine running MySQL 4.1. Remote version of MySQL is 4.01. I was under the impression that SQLYog is supposed to check the target version and then build the create statements accordingly, so different versions shouldn't be an issue???? Should they???

Apart from this, I love this product and would love to upgrade, but until I see this function working correctly, there's no way I am willing to. Maybe I need to look at some other MySQL admin GUI's…. 🙁

Thanks in advance of any help Alan

[peterlaursen]

Yes it should be fixed and it works here! See attached.

This is a “Copy Table” from local MySQL 5.0.16 to webhost 4.0.26 (and using HTTP-tunnel actually).

On the source (ver 5.0.16) the create statement goes

Code:

CREATE TABLE `account` (                          
          `AcCode` varchar(3) NOT NULL default '',        
          `AccountName` varchar(40) NOT NULL default '',  
          PRIMARY KEY  (`AcCode`)                        
        ) ENGINE=MyISAM DEFAULT CHARSET=latin1

On the Target (4.0.26) it is after copy

Code:

CREATE TABLE `account` (                          
          `AcCode` char(3) NOT NULL default '',          
          `AccountName` varchar(40) NOT NULL default '',  
          PRIMARY KEY  (`AcCode`)                        
        ) TYPE=MyISAM  

Two questions:

1) I believe that it is this ENGINE=MyISAM DEFAULT CHARSET=latin1 that you must 'strip out' to make it work ? Could you show us EXACTLY the create statement that works and one that does not?

2) You seriously mean that target mySQL version is 4.0.1? This is a very early 4.0 alpha that is NOT recommended for production use!

I'll check with 4.1 too as the source. But I doubt that it matters!

[peterlaursen]

You are right!

When copying from local 4.1.15 to remote 4.0.26 I get the error message too.

All relevant info should be in the screenshot!

[alan4573]

Thanks for the prompt reply:

The bit I strip out is the DEFAULT CHARSET=Latin1 (The ENGINE=MyISAM bit works fine

The target version is 4.0.25-standard (apologies for the mistake – it was late and I was very tired).

Anyhows, I notice you have created the error – can it be fixed?? I really hope so, it would make things heaps easier. 😀

Alan

[peterlaursen]

Quote:

the error – can it be fixed??

I don't have access to the program code (and probably I would not be able to understand it anyway), so wait for Ritesh to show up and give his comment.

But to me it looks like the program tests for version() >= 5.0 (where it should be >= 4.1) . There has been a lot of focus on the new features of MySQL 5.0 – so maybe it simple was forgot that some of the new stuff was also introduced with 4.1.

That is my guess. And if that is it, I believe it should be quite simple to fix.

[alan4573]

Ritesh – Pleeeease hurry up and fix this – I desperately could do with it working as I need to copy rather a lot of data in the next couple of days

Peter – thanks a lot for your help. Much appreciated

Alan

[peterlaursen]

Just tested copy from 4.1 to 5.0 and it works fine.

but the 'default charset …' in the create statement also is identical across these versions.

[alan4573]

peterlaursen wrote on Dec 1 2005, 01:23 PM:

Just tested copy from 4.1 to 5.0 and it works fine.

but the 'default charset …' in the create statement also is identical across these versions.

[post=”8051″]<{POST_SNAPBACK}>[/post]

When you say just tested copy from 4.1 to 5.0, what products are you referring to SQLYog or MySQL???? Not clear.

My problem is that using SQLYog v5.0 (or even 4.2 and earlier versions) I cannot copy from a local MySQL server v4.1 to a remote MySQL server v4.025. Are you saying that I should be able to copy from MySQL v4.1 to MySQL v5?? If so, that doesn't help me as my host will not upgrade MySQL to a later version (I've asked)

[peterlaursen]

Quote:

what products are you referring to SQLYog or MySQL???? Not clear.

Sorry! I was copying tables successfully from MySQL 4.1.15 to MySQL 5.0.16 using SQLyog 5.0.

I beleive this is a problems that occurs only when copying from mySQL 4.1 to MySQL 4.0 or earlier. And I believe it is an easily-fixable bug with SQLyog (when it can work with MySQL 5 it should so with 4.1 too)

I did not suggest that your hosting provider should upgrade MySQL. That last post of mine was just some additional info for Ritesh & Co.

However YOU can upgrade from MySQL 4.1 to 5.0 on your local if you can't wait for the fix.

[alan4573]

Are you saying that if I upgrade my local Version of MySQL to v5.0 then I will be able to copy from MySQL v5.0 to MySQL v4.025 using SQLYOG v5.0…..

[peterlaursen]

Quote:

Are you saying that if I upgrade my local Version of MySQL to v5.0 then I will be able to copy from MySQL v5.0 to MySQL v4.025 using SQLYOG v5.0…..

Exactly! I can copy tables from 5.0.16 to 4.0.26

Actually you won't have to uninstall MySQL 4.1. They can both be installed. (actully I have 3.23, 4.0, 4.1 and 5.0 installed at the same time). To change between versions you can edit the startup parameters (there are 4 strings to be edited) with “MySQL Administrator” from http://dev.mysql.com and stop and restart the servers.

But 4.1 and 5.0 won't use the same /datadir. So ether you must dump your 4.1-databases and import to 5.0 after the installation. Or if you make a copy of the old /datadir you must run the mysql_fix_privilege_tables.sql script from command-line client before using it with mySQL version 5.

I can guide you if you are totally helpless with this! It is a 2 minute show if you know what you are doing! But anyway backup anything – data and the my.ini file before playing around.

BTW: MySQL 5.0.16 is available from FTP-mirrors such as ftp://sunsite.dk/mirrors/mysql/Downloads/MySQL-5.0/ . http://www.mysql.com still only links to 5.0.15 (probably because all binaries are not finished yet – but the Windows binaries are!)

[peterlaursen]

Well .. situation changed a little ..

I wrote

Quote:

(actully I have 3.23, 4.0, 4.1 and 5.0 installed at the same time).

It is now

Quote:

(actully I have 3.23, 4.0, 4.1, 5.0 and 5.1 installed at the same time).

😀

Actually I can copy tables from 5.1.3 to 4.0.26 as well. It confirms what I wrote at the beginning

Quote:

But to me it looks like the program tests for version() >= 5.0 (where it should be >= 4.1) .

@ Ritesh ???

[alan4573]

Have justdownloaded MySQL v5.016 and am now about to try what you suggested – will let you know how I get on….

[alan4573]

alan4573 wrote on Dec 1 2005, 08:21 PM:

Have justdownloaded MySQL v5.016 and am now about to try what you suggested – will let you know how I get on….

[post=”8063″]<{POST_SNAPBACK}>[/post]

YAHOOO – You Beauty….It works. I am soooo happy 😀 😀 😀 😀

Peter – many many thanks for all your help. You have been outstanding. I rate your support as unbelievable…..Superb…

[Ritesh]

Great to know that it works for you but I think its a bug in SQLyog 😮

If you can copy from 5.x to 4.0.25, you should be able to copy from 4.1.x to 4.0.25 too. This is definitely a bug in SQLyog. I will notify my developers right now and this issue should be solved in 5.01.

Thanks for the bug report.

[Ritesh]

This issue has been fixed in v5.01 BETA.

[alan4573]

Ritesh wrote on Dec 6 2005, 05:52 AM:

This issue has been fixed in v5.01 BETA.

[post=”8122″]<{POST_SNAPBACK}>[/post]

Thanks Ritesh – excellent work as usual 😀

[Author]

Posts