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