Geek Answers Handbook

Unable to save unicode emoji "Pile of Poo" using Spring Boot Hibernate and MySQL

When running the Spring Boot JPA example: https://spring.io/guides/gs/accessing-data-jpa/ in the MySQL database and trying to save the "heap poo" emoji 💩 I get java.sql.SQLException exception java.sql.SQLException: Incorrect string value: '\xF0\x9F\x92\xA9\xF0\x9F...'

I configured my database to use utf8mb4 encoding.

I know that this is NOT a problem with MySQL, since I can create a Client and save it in the database using the MySQL client. I can even run a sample application and find it using the heap poo emoji.

 2015-06-05 18:10:12.382 INFO 5119 --- [ main] scaAnnotationConfigApplicationContext : Refreshing org.spring framework.context.annotation.AnnotationConfigApplicationContext@ 446b0224: startup date [Fri Jun 05 18:10:12 PDT 2015]; root of context hierarchy 2015-06-05 18:10:13.567 INFO 5119 --- [ main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default' 2015-06-05 18:10:13.588 INFO 5119 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [ name: default ...] 2015-06-05 18:10:13.648 INFO 5119 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {4.3.8.Final} 2015-06-05 18:10:13.649 INFO 5119 --- [ main] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found 2015-06-05 18:10:13.651 INFO 5119 --- [ main] org.hibernate.cfg.Environment : HHH000021: Bytecode provider name : javassist 2015-06-05 18:10:13.804 INFO 5119 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {4.0.5.Final} 2015-06-05 18:10:14.086 INFO 5119 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect 2015-06-05 18:10:14.171 INFO 5119 --- [ main] ohhiast.ASTQueryTranslatorFactory : HHH000397: Using ASTQueryTranslatorFactory 2015-06-05 18:10:14.365 INFO 5119 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000228: Running hbm2ddl schema update 2015-06-05 18:10:14.365 INFO 5119 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000102: Fetching database metadata 2015-06-05 18:10:14.366 INFO 5119 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000396: Updating schema 2015-06-05 18:10:14.390 INFO 5119 --- [ main] o.hibernate.tool.hbm2ddl.TableMetadata : HHH000261: Table found: poo_test.Customer 2015-06-05 18:10:14.390 INFO 5119 --- [ main] o.hibernate.tool.hbm2ddl.TableMetadata : HHH000037: Columns: [id, lastname, firstname] 2015-06-05 18:10:14.390 INFO 5119 --- [ main] o.hibernate.tool.hbm2ddl.TableMetadata : HHH000108: Foreign keys: [] 2015-06-05 18:10:14.391 INFO 5119 --- [ main] o.hibernate.tool.hbm2ddl.TableMetadata : HHH000126: Indexes: [primary] 2015-06-05 18:10:14.391 INFO 5119 --- [ main] org.hibernate.tool.hbm2ddl.SchemaUpdate : HHH000232: Schema update complete 2015-06-05 18:10:14.777 INFO 5119 --- [ main] osjeaAnnotationMBeanExporter : Registering beans for JMX exposure on startup Customer found with findOne(1L): -------------------------------- Customer[id=1, firstName='💩💩💩💩', lastName='💩💩💩💩'] 2015-06-05 18:10:14.836 INFO 5119 --- [ main] hello.Application : Started Application in 2.819 seconds (JVM running for 3.077) 2015-06-05 18:10:14.837 INFO 5119 --- [ Thread-1] scaAnnotationConfigApplicationContext : Closing org.spring framework.context.annotation.AnnotationConfigApplicationContext@ 446b0224: startup date [Fri Jun 05 18:10:12 PDT 2015]; root of context hierarchy 2015-06-05 18:10:14.838 INFO 5119 --- [ Thread-1] osjeaAnnotationMBeanExporter : Unregistering JMX-exposed beans on shutdown 2015-06-05 18:10:14.839 INFO 5119 --- [ Thread-1] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

This is a modified Application.java application from the example:

 @SpringBootApplication public class Application implements CommandLineRunner { @Autowired CustomerRepository repository; public static void main(String[] args) { SpringApplication.run(Application.class); } @Override public void run(String... strings) throws Exception { // save a couple of customers repository.save(new Customer("💩💩💩💩", "💩💩💩💩")); // fetch all customers System.out.println("Customers found with findAll():"); System.out.println("-------------------------------"); for (Customer customer : repository.findAll()) { System.out.println(customer); } System.out.println(); // fetch an individual customer by ID Customer customer = repository.findOne(1L); System.out.println("Customer found with findOne(1L):"); System.out.println("--------------------------------"); System.out.println(customer); System.out.println(); } }

This is the application.yml file that I use to configure my JDBC connection. I am using a number of methods that I have found while investigating this problem, but nothing works.

 spring: datasource: url: "jdbc:mysql://localhost:3306/poo_test?useUnicode=yes&characterEncoding=utf8&characterResultSets=utf8" username: root password: connectionInitSqls: "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;" driverClassName: com.mysql.jdbc.Driver testOnBorrow: true validationQuery: SELECT 1 jpa: show-sql: false database-platform: org.hibernate.dialect.MySQL5Dialect hibernate: ddl-auto: update naming_strategy: org.hibernate.cfg.EJB3NamingStrategy connection: CharSet: utf8mb4 characterEncoding: utf8 useUnicode: true

This is apparently a common problem, but I still have to find a solution. Any help would be greatly appreciated.

+5
source share
3 answers

After trying various approaches, I managed to get the job using the following application.yml

 spring: datasource: url: "jdbc:mysql://localhost:3306/poo_test?useUnicode=yes&characterEncoding=utf8" username: root password: initSQL: "SET NAMES 'utf8mb4'" jpa: hibernate: ddl-auto: update

The trick seems to be a property of initSQL

+6
source

I did the same, but it did not work for me, after searching, I found that I needed to edit my.cnf mysql file

 sudo nano /etc/mysql/my.cnf

Then this line is added in mysqld

 character_set_server=utf8mb4

Then restart mysql

 sudo /etc/init.d/mysqld restart

Note. If you are using a Google cloud, you must add a flag and restart mysql from the console, because mysql is not on the same server as your code.

0
source

In my case, the following link solves my Mathias mysql problem

special

 [client] default-character-set = utf8mb4 [mysql] default-character-set = utf8mb4 [mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
0
source

More articles:


All Articles