Escaping Problem In "export Db As Sql Statements"

[vygi]

Hey here,

I've tried to export the whole database as SQL statements and all was OK but when I after them tried to import created file via “Import From SQL Statements” I've got several MySQL errors due missing ' escaping.

Most of the apostrophes were escaped but some weren't.

Here are two lines from the same SQL file created by SQLyog Ent. 9.01 beta.

First one is correct escaped (“Dylan's”):

(3244,NULL,'1984-02-29',NULL,1,99,'X','Malibu','CA','USA','The Plugz Rehearsal Dylan's Garage Studio',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL),

Second one, just 10 lines below, is not (“Dylan's”):

(3254,NULL,'1988-04-03',NULL,1,99,'X','Malibu','CA','USA','Bob Dylan's Garage Studio',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL),

There are about 3,300 rows in the table and 5 or 6 of them were not properlly escaped.

Exported SQL file (1.5M:cool: on request.

Vygi

[vygi]

Now I've found out that ’ (ASCII #146) is a problem and not an usual “vertical” apostrophe '.

The affected line in the SQL file looks like this:

(3222,NULL,'1961-04-30',NULL,1,99,'X','Minneapolis','MN','USA','Bonnie Beecher’s Apartment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL),

I was able to import this file into my local MySQL installation on Windows without any problems, but remote MySQL on Linux reports following error:

Error Code: 1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Apartment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL),

and ´ is replaced by ' in sqlyog.err:

(3222,NULL,'1961-04-30',NULL,1,99,'X','Minneapolis','MN','USA','Bonnie Beecher's Apartment',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,NULL),

Strange….

[vygi]

Sorry guys, I must investigate more before posting….

I have finally find out that Windows has replaced all ’ by ' after I've edited this file.

The bulk insert statement was too large and I split it into two parts.

And all ’ characters were automatically replaced by ' for some reason.

Sometimes I just hate windows….

Sorry for confusion once more!

[peterlaursen]

Quote:

Now I've found out that ’ (ASCII #146) is a problem and not an usual “vertical” apostrophe

I would like to go a little deeper into this. Did you just find out for yourself or do you have some reference?

What is version and the localization of your Windows, the MySQL versions and the charset/collation used with the MySQL servers?

And what editor did you use?

[peterlaursen]

Quote:

…. SQL file created by SQLyog Ent. 9.01 beta.

SQLyog Ent. 9.01 beta ….

Where did you get if from 😛 🙂 😮 😆 😮 😕 😉 😎

[Ritesh]

Even I would like to get to the bottom of this problem. Whatever the character might be, it should be correctly escaped!

[peterlaursen]

As I see it it IS correctly escaped! this ' (apostrophe) must be escaped – this ´ (accent) must not. SQLyog and MySQL on Windows sees that it is a ´ and does not escape. Rightly so!

However when the SQL is sent to MySQL on Linus, MySQL sees it as a ' – and thus an escaping error.

The crazy thing is that putting the file into a text editor transforms the ´ to a ' . After that it is escaped (as it should be) and works on both platforms.

Where does Vygi live? Which Windows does he use (version and localisation) ?

And does the editor support the same language? And it is not an 'old model' that works with some extended ASCII only? (I can tell that in Denmark we have tons of problems with the pipe | character, because in 'extended ASCII' it was mapped to Danish letter 'Ø'.

If Vygi's language uses ´ for accents for instance ….

Vygi .. help us … or move somewhere else! 😀

[vygi]

peterlaursen wrote on Nov 20 2005, 05:57 AM:

As I see it it IS correctly escaped!  this ' (apostrophe) must be escaped – this  ´ (accent) must not.  SQLyog and MySQL on Windows sees that it is a ´ and does not escape.  Rightly so!

[…]

Vygi .. help us … or move somewhere else!  😀

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

No panic! Please read my 3rd posting: SQLyog works correct!

*MS WINDOWS* has replaced alls “standalone” accents ´ by apostrophe ' when I have edited SQL file, that was the only reason! I've realized it just after my first posting here, sorry guys…

[Ritesh]

Great 😀

[Author]

Posts