3

We try to store and read emoji in our MySQL 5.6 database with JOOQ. The database, table and column are using character set utf8mb4 and collation utf8mb4_unicode_ci. With MySQL Workbench I can create and select emojis. So the database should be ready.

But when I store an emoji with JOOQ I get: Incorrect string value: '\xF0\x9F\x98\x80' for column 'test' at row 1SQL

 DSLContext dslContext = DSL.using(dataSource, SQLDialect.MYSQL);

 dslContext.insertInto(table)
                .set(testRecord)
                .returning()
                .fetchOne();

Retrieving en emoji I stored with MySQL Workbench works fine.

user1482309
  • 289
  • 3
  • 16

1 Answers1

1

To use utf8mb4 in the application make sure you set it on the server level or before performing the query.

There are 2 ways of doing it:

  1. Server level: add character_set_server=utf8mb4 to my.cnf or "set global character_set_server=utf8mb4"

  2. Before running query: "set names utf8mb4"

Alexander Rubin
  • 421
  • 3
  • 9
  • 1
    Do you know how to do this with JOOQ? The problem is I running the service on an own writing server. – user1482309 Jul 01 '16 at 19:28
  • 1
    Not sure how to do it with JOOQ specifically. But if you have a mysql root password, you can globally set the character set for the server: "set global character_set_server=utf8mb4" – Alexander Rubin Jul 01 '16 at 19:58