distal-attribute
distal-attribute
distal-attribute
distal-attribute

gibberish exporting from Latin to UTF-8 server

User, date Message
Written by Joselitux
2 years ago
Category: General
21 posts since Mon, 22 Oct 12
Hi
I've got a Mysql server with Latin Encoding and a Target server configured UTF-8
I did an export as SQL from Latin Server to UTF server and now all tables are full of gibberish and strange symbols.

After this, I've tried Database Syncronization but obtained an error:

/* [Unnamed] SQL Error (1548): Cannot load from mysql.proc. The table is probably corrupted */
/* [Unnamed] SQL Error (1577): Cannot proceed because system tables used by Event Scheduler were found damaged at server start */




how can i set HeidiSQL to work properly with UTF-8 encoding?


Thanks
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
Attached data sample after exporting:
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
Welche Nachspeise gef?¤llt Ihnen am besten?
Qu?¨ complement ?©s el teu preferit?
Qu?¨ postres t'agrada m?©s?
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
What did you select in the export dialog - was it a sql file, or a direct session to session export? However, should not make any difference. And exporting from latin1 tables to utf8 tables also should work perfect. HeidiSQL's communication on all servers is set to utf8, which is most important here. Anyway, if you have found broken content, then please describe exactly what you did:
- HeidiSQL revision?
- MySQL versions on both servers
- export to file, or ...
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
Session to session export (click on database, export as SQL, choose target server, drop database option selected, insert data activated)

- Heidi 7.0.0.4234
- Origin server: 5.5.8-enterprise-commercial-advanced-log
- Target server: 5.5.28-enterprise-commercial-advanced-log

I've noticed if I connected with my user granted ALL privileges except SUPER got many

/* SQL Error (1548): Cannot load from mysql.proc. The table is probably corrupted */



Accessed with root (ALL included SUPER) and the same error is shown but not so many times
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
The error looks like you did not properly upgrade your tables in the mysql database. "proc" has got a handful of new columns since 5.5 or earlier. See http://bugs.mysql.com/bug.php?id=62379 , you will find the advice to use the "mysql_upgrade" tool.

However, this has nothing to do with your broken characters.

You said you were exporting "from Latin Server to UTF server". What exactly does that mean - the tables on the source server have a latin1 charset? Or is it the servers own charset? You could post the CREATE table for one relevant table here, probably I can test that here. And, did you create the tables on the target server via HeidiSQL's export tool? Or were they created earlier, by some manual queries? In the latter case, how does the target table's CREATE code look like?
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
Exporting from Latin server to utf server means the origin server is installed and started with default Latin character set and collation:
[

mysql]
default-character-set=Latin1


The target server is installed and started with my.cnf options set to UTF

[mysql]
default-character-set=utf8mb64



All tables and databases are the same encoding and collation from their respective servers. Latin1 for "origin" server and utf8mb64 for the "target" server.

At the very first moment, only first server contains data. Create an empty database. Then select export first database as SQL creating tables and inserting data to target server.

don't know if CREATE table script would be enough to determine the cause of this issue, but anyway, here you are:


DROP TABLE IF EXISTS `OPTIONS`;
CREATE TABLE IF NOT EXISTS `OPTIONS` (
`id` bigint(19) unsigned NOT NULL auto_increment,
`text` varchar(15) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idoption_UNIQUE` (`id`)
ENGINE=InnoDB;



Noticed that I'm unable to use the newly added "sync Databases" menu option.
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
More detailed: when I said "create an empty database" I mean in the target server.
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
Looks ok so far, just I cannot see what collation and charset the table has. Can you please post what SHOW CREATE TABLE `OPTIONS` gives you?

Btw, utf8mb64 is NOT utf8. I never came across these mb64 versions, but they are somehow different to utf8 and may cause broken character problems here.
Written by jfalchMoney, Euro
2 years ago
381 posts since Sat, 17 Oct 09
What exactly is utf8mb64 ? I find exactly one search result with google, none in the mysql docs; could it be that this is not used very often ?
Written by TTSneko
2 years ago
41 posts since Thu, 19 Jul 12
As Anse noted UTF8mb64 is not really UTF8, it's a special UTF8-based Multi-Byte (mb) encased form ... THAT is the reason for the problems. Simply use plain UTF8, problem solved ;)
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
Cool
Written by Joselitux
2 years ago
21 posts since Mon, 22 Oct 12
You're completely wrong. Mysql's UTF8 is not really utf, that's because oracle designed the utf8mb4 to fullfill the need for a real UTF Charset.
Be very careful. I repeat again: MySQL’s utf8 charset only partially implements proper UTF-8 encoding.
http://mathiasbynens.be/notes/mysql-utf8mb4

Anyway, Turning to plain UTF8 did not solve the issue because everybody need REAL UTF encoding.
Written by ansgar
2 years ago
4940 posts since Fri, 07 Apr 06
Hm well, that's bad news. Zillions of users have switched to utf8, leaving a long history of ANSI/ASCII/LATIN encoding behind. Now the same migration way is required because MySQL had a bug in its utf8 interpretation?

I guess most users are ok with the current utf8 implementation, as the problems only come in when you store characters which are quite uncommon in any world language I guess. But good to know about that "bug", thanks for the hint.
Written by TTSneko
2 years ago
41 posts since Thu, 19 Jul 12
Joselitux: "... You're completely wrong..."

UTF8mb64 is per definition a multi-byte encased form. You were trying to shift plain "latin" -> "UTF8mb64" directly, result: nerf, as you saw.

Solution: transfer "latin" -> "UTF8", problem solved.

UTF8 data can without problems be transferred to UTF8mb64, as the latter is backwards compatible. And exactly that transfer was part of your initial problem, so what is wrong with that?

Again: convert your existing "latin" data to "UTF8" on the source server. Then export the "UTF8" data to the "UTF8mb64" target server.
 

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