Copy table to different host – syntax error

[seventree]

I am trying to copy tables between my localhost DB and the one at my web host. It gets a syntax error on the generated CREATE TABLE statement, apparently caused by the “DEFAULT CHARSET=latin1” clause. If I remove that part and execute the statement manually it works, but I don't know how to get the Copy Table function to not use it.

The local MySQL version is 4.1.12-nt and the remote is 4.0.23-nt.

I have database experience but am new to MySQL. Am I doing something wrong, or is it a problem with the web host server?

thanks

Rick

[peterlaursen]

Quote:

The local MySQL version is 4.1.12-nt and the remote is 4.0.23-nt.

That''s the crux of the problem. With MySQL 4.1 a much more sophisticated handling of localisation issues (among them new charset's and collations) was introduced. You can study the 4.1.x changelogs here http://dev.mysql.com/doc/mysql/en/news-4-1-x.html

Quote:

“DEFAULT CHARSET=latin1” clause. If I remove that part and execute the statement manually it works

There is no other way I believe … I use both 4.0.24 and and 5.0.7 and edit my SQL the same way when importing a DUMP from 5.x to 4.x

[peterlaursen]

this link probably is more relevant:

http://dev.mysql.com/doc/mysql/en/charset-general.html

[seventree]

Ok, thanks. When you say there is no other way, do you mean I have to manually execute the steps that the Copy Tables function generates automatically?

My problem is I don't know what these are. I can modify and execute the CREATE TABLE part but don't know what comes next.

Probably I should also be bugging my web host provider to upgrade to 4.1.

Rick

[peterlaursen]

Instead of “copy table to another host” I export table to a text-file, edit the text-file and and import it using SQLyog export/import-function.

Thsi incompability between MySQL-version affect several functions in SQLyog. Also the DB-syncronization feature. And here there is is no easy workaround! Actually I have 3 MySQL versions installed on my local machine (4.0.24, 4.1.12 and 5.0.7), and switch between them as needed (that's quite easy using “MySQL Administrator” “Service Control” and “Startup Variables” options – you only need to change 4 strings values, stop and restart the server. It can be done in less than 15 seconds. And exporting/importing even big files on localhost is very fast too). That's my workaround.

I know that the Webyog people would like to code some workaround into the program. But I don't know if they found a safe way to do it yet!

Quote:

Probably I should also be bugging my web host provider to upgrade to 4.1.

I would like mine to have 5.0.x since I make extensive use of VIEWs. But ISP are very conservative with that. And they have good reasons since that would involve also PHP and ASP configuration. Some users' .php and .asp code might need to be changed for it to work with new versions …

So most ISP's are still running Apache 1.3, php4 and MySQL 4.0, although newer version af all of them are available.

But I do believe that very soon we will se a change with the best (and most expensive!) ISPs and hosting providers.

[seventree]

Thanks once again for the information. Since my database needs are simple and I am just getting started, probably my best option is to uninstall 4.1 locally and replace it with 4.0. That way I can stay with the same ISP and still use all the features of Webyog which looks to be a terrific time saver.

[peterlaursen]

You won't have to uninstall 4.1 to install 4.0.

But there is more “fun stuff” in 5.0 than in 4.1 …

[Author]

Posts