Database import utf8 encoded data (from .sql)

[George]

Hello, I have been using DBeaver for a month and I find it very good and comprehensive and thank you for that !

I am having troubles with utf8 encoding, which I noticed while taking backups of my database. I have a database call it DB1 with the following settings:
Default charset: utf8
Default collation: utf8_general_ci

I have a mysql connection to this database with DBeaver. My DBeaver version is 3.5.9 (current latest). My project environment is using Laravel/Homestead/vagrant/virtualbox.
I am storing data in this database normally, no problems at all and I am able to export these data as test.sql in utf8 (no BOM) successfully, the characters are appearing normally and the file is encoded as UTF8. Now comes the tricky part, when I import this .sql file into a fresh new database (call this DB2), the utf8 characters are not imported correctly. I am using tools->export and tools-> import for this procedure. I have found an alternative solution, which is to import the table with the utf8 characters from the database directly (right click on the table and import from database DB1), but this is slow and inconvenient.

My exported .sql file looks like this:

DROP TABLE IF EXISTS `mytable`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytable` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

LOCK TABLES `mytable` WRITE;
/*!40000 ALTER TABLE `mytable` DISABLE KEYS */;
INSERT INTO `mytable` VALUES (1,'Water Água');
/*!40000 ALTER TABLE `mytable` ENABLE KEYS */;
UNLOCK TABLES;

In this example, if I try to import this .sql file into DB2 I should have got single row in table mytable with id=1 and name ="Water Água". But the name I am getting instead is "Water ". When I create the database DB2 I set the same options as DB1 (Default charset: utf8 and Default collation: utf8_general_ci). If I go right click on the connection->edit connection->Driver properties I can see that "characterEncoding" is set to utf-8. If I go right click my mysql connection->properties->Result Sets->Binaries I can see that the Binary data's String encoding is set to UTF-8. I've been digging into DBeaver forum/google/DBeaver options and I can't find anything for this supposedly simple problem. I am using XAMPP and I have linked xampp/mysql/bin as my local client for DBeaver. I am using windows and my current alternative to import my backup into my database is calling mysql.exe through command prompt and without needing to set any encoding options it works so smooth that I want to throw my laptop on the floor and start dancing on it ! The command I'm running is this by the way: C:/xampp/mysql/bin/mysql -u MY_USERNAME -h MY_HOST -p DB2 < C:/test.sql

I tried to cover the details here and I must be missing something really simple in DBeaver. I would honestly appreciate your support, thanks in advance !

Best Regards,
George

[George]

What the hell, I just updated to new version 3.6.0 and the importing works like a charm. No idea if it was coincidence to be fixed so fast after my post, but thanks anyway

Problem solved !

[Serge]

I think it was fixed here: https://github.com/serge-rider/dbeaver/issues/162

[George]

What the hell !! The encoding is messed up again in new version. Now instead of empty string on UTF-8 characters I am getting invalid characters like ├£ for character Ü. Any help is appreciated ! I hope a stable solution will be released. Time to go back to command prompt I guess.

Best Regards.
George

[Serge]

Are you sure your dump file is in UTF-8?
How did you create that dump - by using pure cli or by using DBeaver database export?

[George]

Yeap, the .sql dump file is UTF-8 without BOM and the database settings are the ones mentioned in my first post.
It's weird, because this was fixed in the version that came after the one mentioned in first post, but now in newest version (3.6.2) the problem reappears in different form.

I used tools->export when taking backup from DBeaver and then tools->import.

Are you able to reproduce this problem ?

EDIT:

Oh man, I'm really sorry probably it's my fault, I have deleted these flags from the dump file, probably this the reason of the problem, I'll test again with the flags included and let you know !

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

[Serge]

Ok.
I can't reproduce that. If you do then please provide more details.
Attach problem dump file if it is possible (no need to post production db, just a dump with a few problem rows).

[George]

Hello Serge,

I'm really sorry, it was as I suspected, I shouldn't have removed the flags from the dump file, the export/import is working nicely, the problem was my manual edit on the dump file's flags.

Thank you for your concern, I believe this issue won't happen again