Page 1 of 1

Database import utf8 encoded data (from .sql)

PostPosted: Fri Feb 19, 2016 4:43 pm
by 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:

/*!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,
/*!40101 SET character_set_client = @saved_cs_client */;

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

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,

Re: Database import (from .sql)

PostPosted: Tue Feb 23, 2016 8:22 am
by 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 !

Re: Database import utf8 encoded data (from .sql)

PostPosted: Tue Feb 23, 2016 5:53 pm
by Serge

Re: Database import utf8 encoded data (from .sql)

PostPosted: Tue Mar 22, 2016 2:28 pm
by 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.

Re: Database import utf8 encoded data (from .sql)

PostPosted: Tue Mar 22, 2016 5:43 pm
by 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?

Re: Database import utf8 encoded data (from .sql)

PostPosted: Wed Mar 23, 2016 1:06 pm
by 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 ?


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 NAMES utf8 */;
/*!40103 SET TIME_ZONE='+00:00' */;

Re: Database import utf8 encoded data (from .sql)

PostPosted: Thu Mar 24, 2016 10:42 am
by Serge
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).

Re: Database import utf8 encoded data (from .sql)

PostPosted: Thu Mar 24, 2016 3:13 pm
by 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 :)