Spring-boot JPA MySQL emoji utfmb4 exception handling

Known as one of the most popular open source database, MySQL is widely used in various scenarios, ApsaraDB RDS for MySQL of Alibaba Cloud provided critical relief for companies, especially small and medium-sized enterprises affected by novel coronavirus (COVID-19).

Spring-boot JPA MySQL UTF8MB4

Emoji Write failed MySQL database,table,column default to Utf8mb4

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)
... More

Early and Spring-boot 1.4 wouldn't have this problem

The new service application spring cloud with spring boot makes mistakes. All kinds of schemes on the Internet have been tried, but they do not work.

The basic idea is to set the Connectioninitsql in the configuration file

spring-boot2.0.0.rc2

mysql5.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>

Code Environment
Spring-boot 2.0.0.RC2

After several unsuccessful attempts, there was a vague idea

Some of these approaches are different from spring-boot JPA, although they are based on JDBC Hibernate, but spring-boot JPA may be different.

In the context of the original text these configurations may be in effect, but not on my spring-boot JPA

On-line, all are valid for hibernate (assuming valid), but not applicable to Spring-boot JPA

Spring-boot environment is more complex, which step out of the problem

I got the source, I'm joking, it's a lot of work.

This kind of problem has two kinds of thinking, see source code is forward thinking

The most practical way is to look at the reverse

There's hikari.pool in the exception, this is a SQL connection pool that I haven't seen before.

Reference Https://github.com/brettwooldridge/HikariCP#configuration-knobs-baby Official configuration

Https://stackoverflow.com/questions/23172643/how-to-set-up-datasource-with-spring-for-hikaricp

Http://www.cnblogs.com/yjmyzz/p/mysql-utf8mb4-jdbc.html

Tried it.

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

Problem solving

Should be this connection pool which link problem, should have loaded spring-boot configuration parameters datasource actually started, but here is not effective.

Specify the datasource of the individual to resolve the problem

Maybe a different connection pool will solve this problem.

Spring-boot JPA MySQL emoji utfmb4 exception handling