encoding of 'export database as sql'

[expired user #6736]'s profile image [expired user #6736] posted 11 years ago in General Permalink
Hi there,
When "exporting database as sql", it does not seem to preserve the original encoding (in all tables I use utf8_unicode_ci). In my case I have data entries in Hebrew. They read fine in the data view, but the exported file uses some encoding that causes it to become gbrish. By the way if I do 'export grid rows' there is no such problem.

Any idea ?
Thanks,
Ofer
jfalch's profile image jfalch posted 11 years ago Permalink
a) utf8_unicode_ci is not an encoding, but a collation, i.e. a sort order within an encoding. If you want to see a table´s encoding, and heidisql shows no CHARACTER SET clause within the "Create Code", view its database´s definition (right click + "edit" on db in tree on the left of heidisql).

b) AFAIK the output of menu "tools" / "export database as sql" is always encoded as utf8; it would be helpful if you could post a few initial lines of this output.
[expired user #6736]'s profile image [expired user #6736] posted 11 years ago Permalink

Thank you. edit on db says utf8.

It is the strangest thing: if I export a single table - it looks fine. When I exported the entire db as one file, the encoding was altered. I checked the text before the table: it is identical in both. Here is the beginning of the table (depending on the encoding in your browser, you will see some gbrish in there)

Ofer




-- Dumping structure for table css.course
CREATE TABLE IF NOT EXISTS `course` (
`CourseID` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`CourseName` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`LectureLen` tinyint(1) unsigned NOT NULL DEFAULT '0',
`TutorialLen` tinyint(1) unsigned NOT NULL DEFAULT '0',
`LabLen` tinyint(1) unsigned NOT NULL DEFAULT '0',
`IsBasic` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
`IsElective` int(1) NOT NULL DEFAULT '0',
`IsScience` tinyint(1) NOT NULL DEFAULT '0',
`IsME` tinyint(1) NOT NULL DEFAULT '0',
`IsDedicated` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'external courses like hedva that are exlusively for our faculty',
`IsAdjacent` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'recitation adjacent to lecture (preferable only for electives)',
`IsFake` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'not a real course',
PRIMARY KEY (`CourseID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table css.course: 294 rows
DELETE FROM `course`;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` (`CourseID`, `CourseName`, `LectureLen`, `TutorialLen`, `LabLen`, `IsBasic`, `IsElective`, `IsScience`, `IsME`, `IsDedicated`, `IsAdjacent`, `IsFake`) VALUES
('094188', '׳ž׳‘׳•׳ ׳œ׳”׳ ׳“׳¡׳× ׳×׳¢׳©׳™׳”', 2, 0, 0, 'N', 2, 0, 0, 0, 0, 0),
('234111', '׳ž׳‘׳•׳ ׳œ׳ž׳“׳¢׳™ ׳”׳ž׳—׳©׳‘', 2, 2, 2, 'Y', 0, 0, 0, 0, 0, 0),
('104017', '׳—׳“׳•׳ 1 ׳ž 1', 4, 2, 0, 'Y', 0, 0, 0, 1, 0, 0),
jfalch's profile image jfalch posted 11 years ago Permalink
hm. formally looks Ok to me; what does SHOW VARIABLES LIKE "character_set_%" show on your system (in case MySQL´s automatic enconding translation system should be the cause) ?
Also, could you please post the name of course 094188 as it is supposed to look ?
[expired user #6736]'s profile image [expired user #6736] posted 11 years ago Permalink
1. where should I look for 'show variables like ? '
2. Here is the same table, when I exported it by itself (I am not sure that exporting it by itself or as a big file is what makes the difference. I think exporting used to work for me in the past):

-- Dumping structure for table css.course
CREATE TABLE IF NOT EXISTS `course` (
`CourseID` varchar(6) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`CourseName` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`LectureLen` tinyint(1) unsigned NOT NULL DEFAULT '0',
`TutorialLen` tinyint(1) unsigned NOT NULL DEFAULT '0',
`LabLen` tinyint(1) unsigned NOT NULL DEFAULT '0',
`IsBasic` enum('Y','N') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Y',
`IsElective` int(1) NOT NULL DEFAULT '0',
`IsScience` tinyint(1) NOT NULL DEFAULT '0',
`IsME` tinyint(1) NOT NULL DEFAULT '0',
`IsDedicated` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'external courses like hedva that are exlusively for our faculty',
`IsAdjacent` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'recitation adjacent to lecture (preferable only for electives)',
`IsFake` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'not a real course',
PRIMARY KEY (`CourseID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Dumping data for table css.course: 294 rows
DELETE FROM `course`;
/*!40000 ALTER TABLE `course` DISABLE KEYS */;
INSERT INTO `course` (`CourseID`, `CourseName`, `LectureLen`, `TutorialLen`, `LabLen`, `IsBasic`, `IsElective`, `IsScience`, `IsME`, `IsDedicated`, `IsAdjacent`, `IsFake`) VALUES
('094188', 'מבוא להנדסת תעשיה', 2, 0, 0, 'N', 2, 0, 0, 0, 0, 0),
('234111', 'מבוא למדעי המחשב', 2, 2, 2, 'Y', 0, 0, 0, 0, 0, 0),
('104017', 'חדוא 1 מ 1', 4, 2, 0, 'Y', 0, 0, 0, 1, 0, 0),
jfalch's profile image jfalch posted 11 years ago Permalink
1. this is an sql query, to find out some server status variables; put it into a heidisql query pane, press F9, and then right click on results pane below to export results to clipboard.

2. ah, now at least hebrew-looking characters are shown in my browser (evan if i cannot read them, not knowing hebrew).
will try to compare their values to your previous post ...
[expired user #6736]'s profile image [expired user #6736] posted 11 years ago Permalink
I exported as delimited text:


Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem utf8
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /local/lampstack/mysql/share/charsets/
jfalch's profile image jfalch posted 11 years ago Permalink
that looks Ok too...
What version of heidisql are you using ?
what server version (returned by SELECT VERSION()) ?
[expired user #6736]'s profile image [expired user #6736] posted 11 years ago Permalink
5.5.24-log
heidisql 7.0.0.4051 32 bit
version 7.0.0.4053
compiled on 2012-02-12 12:45:08

I assume that the fact that my os is win7-64 bit has nothing to do with it (?)
jfalch's profile image jfalch posted 11 years ago Permalink
dont know about -64 but do not think so.

otherwise, i have experimented a bit with the data you posted above; when I copy your second CREATE... post into my heidisql, I get a table `course` that displays the correct hebrew text in the data grid, and I am also able to export it again correctly using tools/export as sql.

I have not been able to correlate the `CourseName` data in the first post somehow to the (utf8) data in the second post; they appear to be completetly different, I am not able to identify how the first data could have erroneously been derived from the second.

One thing: the version you are using is about 10 months old, that is really ages in program development; lots of bugs have been fixed in the meantime. I would suggest to
a) save your current heidisql direcory (just in case), and uninstall it; then
b) download the current version (.4287 !) from the web site (download "installer"), and install it, and try again.

if the error should still occurs, you should file a bug report here. Sign in (register first if necessary), then press "new issue" in the upper left, and describe the problem; these issues are regularly viewed by the developer (anse).

Good Luck.
jfalch's profile image jfalch posted 11 years ago Permalink
PS. before you uninstall, I suggest you use File / Export Settings to save your program settings and session definitions, in case uninstall would remove these.
ansgar's profile image ansgar posted 11 years ago Permalink
By the way, there were some users in the past - and this is what I could guess is also your problem - which just misdetected the utf8 file encoding of the SQL file produced by HeidiSQL. How did you open that file and saw the broken chars, was it some editor? What encoding did it detect? If not UTF8, then most text editors have some manual option to specify the encoding of an opened file.
[expired user #6736]'s profile image [expired user #6736] posted 11 years ago Permalink
I see the gbrish in heidysql'w own editor (when I load the sql file).
I repeated the experiment now after installing the new version (7.0.0.4053).
Exporting a single table - works fine.
Exporting more than one (or all) - produces gbrish.
ansgar's profile image ansgar posted 11 years ago Permalink
Even HeidiSQL itself sometimes misdetects the encoding of a loaded file. That's because the "file open" dialog says "Auto detect (may fail!)". You can, however, try to open the file with manually selecting "UTF8" encoding in the dialog. Please try and report back if that helps.
jfalch's profile image jfalch posted 11 years ago Permalink
"I repeated the experiment now after installing the new version (7.0.0.4053)" - areyou running the most recent version 7.0.0.4287 or 7.0.0.4288 ? if not, why not ?
[expired user #6736]'s profile image [expired user #6736] posted 11 years ago Permalink
indeed changing the encoding manually (instead of 'auto-detect') solved the problem. It is strange, however, that auto-detect succeeds if it is an export of a single table, but fails when it is an export of all or few (>1) tables.
ansgar's profile image ansgar posted 11 years ago Permalink
Oh, that fits very well to my experiences. Welcome in the world of encodingssmileAuto detection examines the first bunch of text in the file, not the whole file. And if there are no specific multibyte characters in that first part, it detects ANSI. That's because I appended this "may fail" to the "Auto detect" item.

Please login to leave a reply, or register at first.