CODEWITHSUNDEEP
spring-bootutf-8mariadbutf8mb4
psv
psv

Reputation: 3287

Insert emoji does not work with spring-boot and MariaDB

I would like to insert emoji like 😃 in mariaDB database but I always get a sql error.

Here is the stacktrace:

12-01-2018 16:01:44.466 [Executor - Migration - 1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions:129 - SQL Error: 1366, SQLState: 22007
12-01-2018 16:01:44.466 [Executor - Migration - 1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper.logExceptions:131 - (conn:498) Incorrect string value: '\xF0\x9F\x92\xB3\xF0\x9F...' for column 'notes' at row 1
Query is: insert into customer (backend_archiving_date, backend_creation_date, backend_update_date, genius_client_id, address, birthday, city, company_name, country, email, fidelity_account_id, first_name, last_name, notes, phone, siret, zip_code, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters [<null>,'2018-01-12 15:01:44.454','2018-01-12 15:01:44.454',2159,'20 rue raspail
',<null>,'LEVALLOIS PERRET','','FRANCE','','00c55854-99df-4db1-88b9-34f7e5608477','claude','amsellem','💳💳💳💳','','','92300','2152ed7d-80a1-4305-9fcb-4e21f5947e32']

I my Maria db, the table has

ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

and the field "notes" has also "utf8mb4 - Unicode" charset.

When I insert via a sql query

update customer set notes='💳💳💳💳' where id = 'f5920301-5ee0-4d58-a786-d4701d9e9d73';

it works, but when I want to insert emoji with my program (spring-boot, spring-data-jpa, hibernate) I always get the error (see stacktrace).

EDIT

FYI Here is the creation script

CREATE TABLE `customer` (
`id` char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`first_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`email` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`birthday` date DEFAULT NULL,
`notes` varchar(2048) DEFAULT NULL,
`address` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`city` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`country` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`zip_code` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`company_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`siret` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`fidelity_account_id` char(36) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`backend_creation_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`backend_update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`backend_archiving_date` timestamp NULL DEFAULT NULL,
`genius_client_id` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `fk_customer_fidelity_account` (`fidelity_account_id`),
CONSTRAINT `fk_customer_fidelity_account` FOREIGN KEY (`fidelity_account_id`) REFERENCES `fidelity_account` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EDIT 2

Here is the connection params (spring-boot application.yml)

# Datasource configuration
spring:
  datasource:
    type: "com.zaxxer.hikari.HikariDataSource"
    url: "jdbc:mariadb://localhost:3306/genius_back useUnicode=yes&characterEncoding=UTF8"
    hikari:
      driver-class-name: "org.mariadb.jdbc.Driver"
      username: "mariadb"
      password: "mariadb"
      minimum-idle: 5
      maximum-pool-size: 20
      validation-query: "SET NAMES utf8mb4"
  jackson:
     serialization:
      write_dates_as_timestamps: false
  http:
     client-user-agent: "Genius"
     multipart:
      max-file-size: 100Mb
      max-request-size: 150Mb

Upvotes: 5

Views: 8293

Answers (4)

psv
psv

Reputation: 3287

Okay, I found the problem.

The solution was to add

spring:
  datasource:
     connectionInitSql: "SET NAMES 'utf8mb4'"

in the application.yml.

connectionInitSql is used by HikariCP when it opens the connection.

Upvotes: 10

cclient
cclient

Reputation: 845

spring-boot 2.0.0.RC2

mysql 5.7.14

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>


Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\xAD' for column 'title' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
... 68 more

spring.datasource.connectionInitSql is not work for me

i see hikari.pool

then try

spring.datasource.hikari.connectionInitSql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

it works so do 

spring.datasource.hikari.connection-init-sql=SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

ref https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby

Upvotes: 7

MariuszS
MariuszS

Reputation: 31585

For Spring boot with tomcat embedded use:

spring.datasource.tomcat.initSQL = SET NAMES 'utf8mb4'

Upvotes: 4

Rick James
Rick James

Reputation: 142433

When you are connecting, set the charset to utf8mb4. Please provide the connection details.

Use utf8mb4 on the column(s) in the table. Please provide SHOW CREATE TABLE so we can verify that the column, not just the table default is utf8mb4.

Upvotes: 1

Related Questions