Is this a MySQL Query bug?

[Carl Anatorian]

I wrote a SQL script in MySQL Query, and saved it as script.sql, but
when I executed it in windows command line "mysql -h localhost -u root
-ppassword -e "source script,sql" ", an error was shown ,which say I had
a syntax error at the first line.I checked the script again and again,
but no error was found ? What's wrong?At last ,I opened this script file
in hex mode, and found two strange bytes at the beginning ---"0xFFFE". I
removed it, and executed the script again. No Error, everything 's OK!
But who added these two bytes? I opened this file using MySQL Query
again , and saved it ,then opened it using UltraEdit in hex mode , and
I found the two bytes again. Who knows why MySQL Query adds "0xFFFE" at
the script file's beginning ? Is this a bug?

[Axel Schwenke]

Carl Anatorian <anatoranato@gmail.com> wrote:

> I wrote a SQL script in MySQL Query,

Do you mean the "MySQL Query Browser"?

> and saved it as script.sql, but
> when I executed it in windows command line "mysql -h localhost -u root
> -ppassword -e "source script,sql" ", an error was shown ,which say I had
> a syntax error at the first line.I checked the script again and again,
> but no error was found ? What's wrong?At last ,I opened this script file
> in hex mode, and found two strange bytes at the beginning ---"0xFFFE". I
> removed it, and executed the script again. No Error, everything 's OK!

Those two bytes are called BOM - byte order mark. This BOM is used to
flag unicode files and to give a hint on the byte order in this file.

> But who added these two bytes? I opened this file using MySQL Query
> again , and saved it ,then opened it using UltraEdit in hex mode , and
> I found the two bytes again.

Seems pretty clear, that your ominous "MySQL Query" programm saves the
query in unicode format. But then the MySQL monitor is not able to read
this unicode stuff.

> Who knows why MySQL Query adds "0xFFFE" at
> the script file's beginning ? Is this a bug?

I guess it's intended behaviour. You could try one of the following:

1. look in the options of "MySQL Query" if you can change the encoding
for saved files from unicode to 8-bit (Windows-1250, latin-1 etc.)

2. add "--default-charset=utf8" to the mysql.exe command line, now
mysql should be able to read unicode

3. convert the saved files from unicode to latin-1 using an external
tool. Probably UltraEdit can do this.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]

[Carl Anatorian]

Axel Schwenke wrote:

> Carl Anatorian <anatoranato@gmail.com> wrote:
>
>

>> I wrote a SQL script in MySQL Query,
>>

>
> Do you mean the "MySQL Query Browser"?
>
>

>> and saved it as script.sql, but
>> when I executed it in windows command line "mysql -h localhost -u root
>> -ppassword -e "source script,sql" ", an error was shown ,which say I had
>> a syntax error at the first line.I checked the script again and again,
>> but no error was found ? What's wrong?At last ,I opened this script file
>> in hex mode, and found two strange bytes at the beginning ---"0xFFFE". I
>> removed it, and executed the script again. No Error, everything 's OK!
>>

>
> Those two bytes are called BOM - byte order mark. This BOM is used to
> flag unicode files and to give a hint on the byte order in this file.
>
>

>> But who added these two bytes? I opened this file using MySQL Query
>> again , and saved it ,then opened it using UltraEdit in hex mode , and
>> I found the two bytes again.
>>

>
> Seems pretty clear, that your ominous "MySQL Query" programm saves the
> query in unicode format. But then the MySQL monitor is not able to read
> this unicode stuff.
>
>

>> Who knows why MySQL Query adds "0xFFFE" at
>> the script file's beginning ? Is this a bug?
>>

>
> I guess it's intended behaviour. You could try one of the following:
>
> 1. look in the options of "MySQL Query" if you can change the encoding
> for saved files from unicode to 8-bit (Windows-1250, latin-1 etc.)
>
> 2. add "--default-charset=utf8" to the mysql.exe command line, now
> mysql should be able to read unicode
>
> 3. convert the saved files from unicode to latin-1 using an external
> tool. Probably UltraEdit can do this.
>
>
> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
> MySQL User Forums: [url]http://forums.mysql.com/[/url]
>

Thanks for you answer . Yes I mean the "MySQL Query Browser". It's the
first time that I hear about BOM. I have tried to add option
"--default-charset=utf8", but it is still error. I think the simplest
way is just to removing the two bytes. I haven't tried it in linux, may
be it will work well.

[Axel Schwenke]

Carl Anatorian <anatoranato@gmail.com> wrote:

[full quote including signature]

Carl,

please follow the usenet standards for quoting, as described i.e. here:
[url]http://www.xs4all.nl/~hanb/documents/quotingguide.html[/url]

Thanks!

> Thanks for you answer . Yes I mean the "MySQL Query Browser". It's the
> first time that I hear about BOM.

There is a good explanation in Wikipedia:
[url]http://en.wikipedia.org/wiki/Byte_Order_Mark[/url]

> I have tried to add option
> "--default-charset=utf8", but it is still error.

Yes. I just remember (from my very scarce Windoze experiences) that the
default unicode encoding in Windows is UFT16. This matches your BOM of
0xFF, 0xFE. However mysql.exe implements UTF8 only. So no chance to
feed the Query Browser output directly into mysql.exe.

> I think the simplest
> way is just to removing the two bytes.

No!

As Peter points out, this will ruin all string literals in your queries
that contain characters outside the ASCII range. You should recode the
file to a suitable encoding for mysql.exe. If you don't use special
characters, latin1 (or the Windows variant Windows1250) will do fine.
Otherwise UTF8 would be a good choice.

In Linux you can use the 'recode' tool for that. Certainly there will
be a tool for Windows as well. Also most text editors can save text
files in different encodings.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
MySQL User Forums: [url]http://forums.mysql.com/[/url]

[Mike Lischke]

Carl Anatorian wrote

>Thanks for you answer . Yes I mean the "MySQL Query Browser". It's the
>first time that I hear about BOM. I have tried to add option
>"--default-charset=utf8", but it is still error. I think the simplest way
>is just to removing the two bytes. I haven't tried it in linux, may be it
>will work well.

I don't recommend making manual changes in the file, unless you know what
you are doing. Better find out what gives you the trouble. In this case it
is that you feed the CLI a file with an encoding it does not understand.
Hence it would be better to use another one. QB can store files in either
ANSI (using the current user locale), UTF-8 and UTF-16. The CLI understands
the first two. I'd recommend to stay with Unicode whereever possible, so
UTF-8 would be a natural choice for you. If that, for whatever reason, does
still not work out for you then try the ANSI variant.

You can pick the encoding in the save dialog (just in case you did not
realize it).

Mike
--
Mike Lischke, Software Engineer GUI
MySQL AB, [url]www.mysql.com[/url]