Engine=innodb Default Charset=latin1

[turigeza]

Hi,

When ever I try to export db as SQL dump and than import it (into a different MySQL version) OR copy a database to a different host

IF the target host is an older version MySQL server (in my case 3.23.58-16.RHEL3.1) than the source host (in my case 4.1) I get the following error.

Error Code : 1064

You have an error in your SQL syntax near 'ENGINE=InnoDB DEFAULT CHARSET=latin1' at line 89

Now if you remove 'ENGINE=InnoDB DEFAULT CHARSET=latin1' from the sql code it seems to solve the problem.

But I'm still left to copy by sql dump and restore and hand remove all these 'ENGINE=InnoDB DEFAULT CHARSET=latin1'

I'm using SQLyog 4.07. (the free edition though it used to cost … no!? I though I paid for it something like $15 ???) Anyway ….

Is there any way I can avoid having to do this!????

Thank you for helping :))

Geza Turi

[Ritesh]

Please upgrade to 5.0. This issue has already been fixed in 5.0.

[turigeza]

Excellent :))

Thanks!

Ritesh wrote on Nov 24 2005, 02:01 PM:

Please upgrade to 5.0. This issue has already been fixed in 5.0.

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

[turigeza]

Well I did but I'm afraid this did not fix anything 🙁

Still getting the same problem …

You meant sqlyog 5 not MySQL 5 !??

Any other way !!??

Thanks!

Geza

[peterlaursen]

@ritesh .. you should read the question before you answer! 😛

@turigeza .. it can't be otherwise A table definition in MySQL 4.1 and 5.0 has pr. definition charset and engine definition with it. Before it has not. When SQLyog queries the server for the table structure this information is returned.

Search 'ENGINE=InnoDB DEFAULT CHARSET=latin1' and replace with '' (empty string) takes less than 10 seconds in an editor. Besides you can use ODBC import instead if you don't want that.

SQLyog is a MySQL client. It should not 'overrule' the server. And can't and doesn't!

[turigeza]

I see it on a different way I'm sorry:(

I need to do this quite often and and it's pain:(

Sqlyog should be able to tell that if the mysql (it would take less time than for me) server version is less than 4.1 ( or what I don't know) creat valid sql statment for that server version.

But ok if you don't see it I find an other way around it …

Thank you for helping …

Geza

[peterlaursen]

@geza …

when the sql-file is created no one can tell where it will be used. And BTW the is no way to make the server not include this. It should then be 'stripped away' when imported to 3.x or 4.0. Well that is theoreticaly possible. But as of now the SQLyog code does not parse – or even read – the .sql at all. And I personally think that there are many more important things to do.

There are lots of editor programs available where you can define this 'search and replace' operation as a macro. I just created one with Textpad http://www.texpad.com in less than a minute. It is just recording it like a Word or Excel macro. A Textpad is a pretty simple program compared to others.

The basic thing to understand is the SQLyog – being a MySQL client – actually does very little by itself. It controls what the server should do! And thus only things that the server can do can be done with SQLyog.

But yes … this 'macro' capability could be implemented in the SQLyog editor as well as in any editor.

[turigeza]

peterlaursen wrote on Nov 24 2005, 04:15 PM:

@geza …

when the sql-file is created no one can tell where it will be used.  And BTW the is no way to make the server not include this.  It should then be 'stripped away' when imported to 3.x or 4.0.  Well that is theoreticaly possible.  But as of now the SQLyog code does not parse – or even read – the .sql at all.  And I personally think that there are many more important things to do.

There are lots of editor programs available where you can define this 'search and replace' operation as a macro.  I just created one with Textpad http://www.texpad.com in less than a minute.  It is just recording it like a Word or Excel macro.  A Textpad is a pretty simple program compared to others.

The basic thing to understand is the SQLyog – being a MySQL client – actually does very little by itself.  It controls what the server should do!  And thus only things that the server can do can be done with SQLyog.

But yes … this 'macro' capability could be implemented in the SQLyog editor as well as in any editor.

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

[turigeza]

I see! I'm sorry i'm not big on Mysql. So it's a mysql server what creates the mysql batch file not your application … 😀

I didn't know that ! 🙂 You have no control over it what is being writen in there. All you can do is take it out.

>> when the sql-file is created no one can tell where it will be used.

Unless it's an option user can choose and tell your application.

But I was specificaly talking about here the “Copy this database to a different host” functionality where the application can tell where it's copying from and where to.

This thing renders this function useless in my case:( But hey I understand now where you are coming from … 🙄

Thanks anyway …

Geza

[Ritesh]

Aaah…I thought you are using DB -> Copy DB to different Host/DB option. SQLyog supports MySQL versions using that option.

@Peterlaursen: Its SQLyog which creates the .sql file. Its just that SQLyog first queries MySQL to get the info and simply writes it down to the file on disk.

[Ritesh]

Quote:

But I was specificaly talking about here the “Copy this database to a different host” functionality where the application can tell where it's copying from and where to.

SQLyog checks for the target MySQL version and generates the CREATE TABLE queries accordingly. If you this option, then you should *not* have any problem.

[peterlaursen]

Quote:

Its SQLyog which creates the .sql file.

yesyes .. but what I meant was that the .sql file starts with the 'create statement for the table' as returned by MySQL. And it is that part of the .sql file that creates the problem with import.

[Author]

Posts