gibberish exporting from Latin to UTF-8 server

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

Attached data sample after exporting:

Welche Nachspeise gef?¤llt Ihnen am besten?
Qu?¨ complement ?©s el teu preferit?
Qu?¨ postres t'agrada m?©s?

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 ...

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

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?

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.

More detailed: when I said "create an empty database" I mean in the target server.

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.

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 ?

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 ;)

Cool

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.

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.

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.