1

In my java project, I keep getting [SqlExceptionHelper] Incorrect string value: '\xF0\x9F\x90\xBE V...', although MySQL Workbench show that the table is in UTF-8, and adding in the workbench the value "Brandstätter" works.

I changed in the workbench the collation from utf8 to utf8mb4, and that did not work. Also, in the java project, I have in my application.properties file: jdbc.url = jdbc:mysql://localhost:3306/recommendations?useUnicode=true&characterEncoding=utf8 (changing utf8 to utf8mb4 coused Unsupported character encoding 'utf8mb4').

Also, this returns utf8: SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "db-name";

What can I do?

Edit: The data being inserted: "Loy ߐ¾ Virtual Pet Game".

Edit2: Now that I look more closly, this application's name coases the problem. What should I do? Neglect the weird chars (with Character.isISOControl, or is there a collation even more powerful then UTF-8?

Ran
  • 657
  • 2
  • 13
  • 29
  • Can you add your java code that insert the data to your DB? – Vladimir Vagaytsev Jul 24 '16 at 09:00
  • monkeyStix - as i've said, I tried to state utf8mb4 but it didn't work. Did I do something wrong? – Ran Jul 24 '16 at 09:15
  • what version of mysql do you use? – monkeyStix Jul 24 '16 at 09:21
  • @Ran, do you use Spring JDBC? – Vladimir Vagaytsev Jul 24 '16 at 09:28
  • Yes, I use Spring. Also, MySQL said when I tried to export the data on it that it is in version 5.7.12. – Ran Jul 24 '16 at 09:38
  • @Ran If you use Spring, you have to build parameter map for your insert quury, so yo can either use `Map` or `SqlParameterSource` for it. Make sure you don't pass any custom objects to this map, it should contain DB compatible values, otherwise the custom object might be serialized to a binary data, and these data in turn would be written as a string to the DB. So binary data represented as a string may contain some illegal characters. – Vladimir Vagaytsev Jul 24 '16 at 10:08
  • My entities are simple POJO's made out of ints and Strings. No custom objects, just plain Strings. The data I put in them comes from Google Play's website, with URLConnection and InputStream classes. Can URLConnection or InputStream contain non-utf-8 data? How can I handle it? – Ran Jul 24 '16 at 10:15
  • `ä` works in both utf8 and utf8mb4. – Rick James Jul 24 '16 at 17:47

1 Answers1

1

You need utf8mb4 in your table and in your connection to the database.

Here's the clue:

\xF0\x9F\x90\xBE

Notice the first byte is hex F0. That is the UTF8 lead-in for a 4-byte 'character'. MySQL's utf8 does not handle longer than 3 bytes; utf8mb4 does.

Note: Outside MySQL, "UTF-8" means characters of any length. Inside MySQL there is the confusing distinction between "utf8" and "utf8mb4".

Rick James
  • 135,179
  • 13
  • 127
  • 222