CODEWITHSUNDEEP
javaencodingutf-8spring-bootflyway
shinchillahh
shinchillahh

Reputation: 515

Flyway Migration: Data Import With German Umlauts

I'm using Flyway 4.0 with SpringBoot 1.3.0.RELEASE and a MySQL 5.6.27 Community Server.

When I inspect my schema with MySQL Workbench it has the "utf8_general_ci" as default collation and "utf8" as default characterset (the schema was created with "utf8 - default collation"). Inside there is a table "Colors" and its characterset is also utf8 like a few varchar columns.

Now I have an initial Flyway migration script which creates the database with its complete structure. Additionally it fills the color table with some items.

...

DROP TABLE IF EXISTS `static_color`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `static_color` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
  `color_group_name` varchar(15) DEFAULT NULL,
  ...
  ...
) ENGINE=InnoDB AUTO_INCREMENT=845 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

...

LOCK TABLES `static_color` WRITE;
/*!40000 ALTER TABLE `static_color` DISABLE KEYS */;
INSERT INTO `static_color` VALUES (7,'2C','Light Yellow Green','45-3',240,233,145,11,'Grün',119,'Light Yellowish Green',237,234,115,'','edea73');
/*!40000 ALTER TABLE `static_color` ENABLE KEYS */;
UNLOCK TABLES;

...

In one column I want to hold a color group name (e.g. Grün). Flyway tries to import the data but I get a warning like

[localhost-startStop-1] DEBUG o.f.c.internal.dbsupport.SqlScript - Executing SQL: INSERT INTO `static_color` VALUES (7,'2C','Light Yellow Green','45-3',240,233,145,11,'Grün',119,'Light Yellowish Green',237,234,115,'','edea73')
[localhost-startStop-1] WARN  o.f.c.i.dbsupport.JdbcTemplate - DB: Invalid utf8 character string: 'FC6E' (SQL State: HY000 - Error Code: 1300)
[localhost-startStop-1] WARN  o.f.c.i.dbsupport.JdbcTemplate - DB: Incorrect string value: '\xFCn' for column 'color_group_name' at row 1 (SQL State: HY000 - Error Code: 1366)

The result is that the entry in the database only holds "Gr" instead of "Grün" in column "color_group_name". It cuts off every entry with "Ä,ä,Ü,ü,ß...".

I don't know why this occurs. When I look at the properties of my migration file in eclipse it says that it has also the UTF-8 text file encoding. Even the Flyway debug output has the right encoding... Does anyone else have this problem?

Upvotes: 2

Views: 1271

Answers (2)

shinchillahh
shinchillahh

Reputation: 515

I don't know why but it works after removing the following lines:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
...
/*!40101 SET character_set_client = @saved_cs_client */;

I got these lines from MySQL Workbench export so I just used them...

Upvotes: 4

jvaclavovic
jvaclavovic

Reputation: 1

Try to put utf-8 encoding into connection string, like this:

jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8

Upvotes: 0

Related Questions