Mangled SQL on load into SQL editors

Mangled SQL on load into SQL editors

Postby capt_henry » Wed 18 Jan 2017 14:36


I have a problem with a dump from MySQL dump that gets garbled when loaded into SQLyog. It is the same with HeidiSQL and MySQL Workbench unless I set 'Unicode' or 'UTF-16 as the collation on load.

SQLyog autodetects the format and it fails unless I open the file in notepad (or any other text editor) and save it.

One or more tables has 'DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;' which causes the problem.

This is the response from SQLyog tech support :

"You are right that it garbles when opening the file in the SQLyog editor.

It seems he problem is that the file has no BOMs (Byte Order Marks) - refer and also attached image where I have opened the file in a HEX editor. BOMs are required for automatic detection of the encoding if there are no BOMs SQLyog will assume that the file is ANSI/UTF8. Because with a UTF16 file without BOMs the byteorder ("little endian" as on Windows - "big endian" as on various UNIXes (Mac, Solaris, BSDs etc.)) cannot be known from the file itself.

Doesn't this Dewart tool have an option either to use UTF8 or UTF16 with proper BOMs when generating the dump? It actually surprises me that there are no BOMs. If you try to save a file as (Windows) "Unicode" from Notepad for instance you will see that BOMs are generated and I dont think I have ever seen a Windows program before that does not. "

This is a snippet of the dump code:

Dim dbDump As MySqlDump = New MySqlDump() With {
.Connection = MySqlConnection,
.Database = DbName,
.GenerateHeader = True,
.IncludeDrop = True,
.IncludeUse = True,
.UseExtSyntax = False,
.ObjectTypes = MySqlDumpObjects.Procedures _
Or MySqlDumpObjects.Events _
Or MySqlDumpObjects.Views _
Or MySqlDumpObjects.Triggers _
Or MySqlDumpObjects.Tables _
Or MySqlDumpObjects.Functions,
.QuoteIdentifier = True,
.Mode = DumpMode.All}


Is there a property to set this?


Posts: 10
Joined: Sat 06 Nov 2004 09:33

Re: Mangled SQL on load into SQL editors

Postby capt_henry » Thu 19 Jan 2017 17:42

Sussed it.

The backup is to a memory stream as I use the memory stream to then zip the file up in one process.

The encoding for the stream was set to System.Text.UnicodeEncoding, when set to System.Text.UTF8Encoding it is fine!

Posts: 10
Joined: Sat 06 Nov 2004 09:33

Return to dotConnect for MySQL