Incorrect string value when trying to insert UTF-8 into MySQL via JDBC

Question

This is how my connection is set  Connection conn   DriverManager getConnection url   dbName     useUnicode true amp characterEncoding utf-8   userName  password    And I m getting the following error when tyring to add a row to a table  Incorrect string value    xF0 x90 x8D x83 xF0 x90     for column  content  at row 1  I m inserting thousands of records  and I always get this error when the text contains  xF0  i e  the the incorrect string value always starts with  xF0    The column s collation is utf8 general ci   What could be the problem

User · Answer

I had this problem with my PLAY Java application. This is my stack trace for that exception:

javax.persistence.PersistenceException: Error[Incorrect string value: '\xE0\xA6\xAC\xE0\xA6\xBE...' for column 'product_name' at row 1]
  at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:52)
  at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:192)
  at io.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:83)
  at io.ebeaninternal.server.persist.dml.DmlBeanPersister.insert(DmlBeanPersister.java:49)
  at io.ebeaninternal.server.core.PersistRequestBean.executeInsert(PersistRequestBean.java:1136)
  at io.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:723)
  at io.ebeaninternal.server.core.PersistRequestBean.executeNoBatch(PersistRequestBean.java:778)
  at io.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:769)
  at io.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:456)
  at io.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:406)
  at io.ebeaninternal.server.persist.DefaultPersister.save(DefaultPersister.java:393)
  at io.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1602)
  at io.ebeaninternal.server.core.DefaultServer.save(DefaultServer.java:1594)
  at io.ebean.Model.save(Model.java:190)
  at models.Product.create(Product.java:147)
  at controllers.PushData.xlsupload(PushData.java:67)
  at router.Routes$$anonfun$routes$1.$anonfun$applyOrElse$40(Routes.scala:690)
  at play.core.routing.HandlerInvokerFactory$$anon$3.resultCall(HandlerInvoker.scala:134)
  at play.core.routing.HandlerInvokerFactory$$anon$3.resultCall(HandlerInvoker.scala:133)
  at play.core.routing.HandlerInvokerFactory$JavaActionInvokerFactory$$anon$8$$anon$2$$anon$1.invocation(HandlerInvoker.scala:108)
  at play.core.j.JavaAction$$anon$1.call(JavaAction.scala:88)
  at play.http.DefaultActionCreator$1.call(DefaultActionCreator.java:31)
  at play.core.j.JavaAction.$anonfun$apply$8(JavaAction.scala:138)
  at scala.concurrent.Future$.$anonfun$apply$1(Future.scala:655)
  at scala.util.Success.$anonfun$map$1(Try.scala:251)
  at scala.util.Success.map(Try.scala:209)
  at scala.concurrent.Future.$anonfun$map$1(Future.scala:289)
  at scala.concurrent.impl.Promise.liftedTree1$1(Promise.scala:29)
  at scala.concurrent.impl.Promise.$anonfun$transform$1(Promise.scala:29)
  at scala.concurrent.impl.CallbackRunnable.run$$$capture(Promise.scala:60)
  at scala.concurrent.impl.CallbackRunnable.run(Promise.scala)
  at play.core.j.HttpExecutionContext$$anon$2.run(HttpExecutionContext.scala:56)
  at play.api.libs.streams.Execution$trampoline$.execute(Execution.scala:70)
  at play.core.j.HttpExecutionContext.execute(HttpExecutionContext.scala:48)
  at scala.concurrent.impl.CallbackRunnable.executeWithValue(Promise.scala:68)
  at scala.concurrent.impl.Promise$KeptPromise$Kept.onComplete(Promise.scala:368)
  at scala.concurrent.impl.Promise$KeptPromise$Kept.onComplete$(Promise.scala:367)
  at scala.concurrent.impl.Promise$KeptPromise$Successful.onComplete(Promise.scala:375)
  at scala.concurrent.impl.Promise.transform(Promise.scala:29)
  at scala.concurrent.impl.Promise.transform$(Promise.scala:27)
  at scala.concurrent.impl.Promise$KeptPromise$Successful.transform(Promise.scala:375)
  at scala.concurrent.Future.map(Future.scala:289)
  at scala.concurrent.Future.map$(Future.scala:289)
  at scala.concurrent.impl.Promise$KeptPromise$Successful.map(Promise.scala:375)
  at scala.concurrent.Future$.apply(Future.scala:655)
  at play.core.j.JavaAction.apply(JavaAction.scala:138)
  at play.api.mvc.Action.$anonfun$apply$2(Action.scala:96)
  at scala.concurrent.Future.$anonfun$flatMap$1(Future.scala:304)
  at scala.concurrent.impl.Promise.$anonfun$transformWith$1(Promise.scala:37)
  at scala.concurrent.impl.CallbackRunnable.run$$$capture(Promise.scala:60)
  at scala.concurrent.impl.CallbackRunnable.run(Promise.scala)
  at akka.dispatch.BatchingExecutor$AbstractBatch.processBatch(BatchingExecutor.scala:55)
  at akka.dispatch.BatchingExecutor$BlockableBatch.$anonfun$run$1(BatchingExecutor.scala:91)
  at scala.runtime.java8.JFunction0$mcV$sp.apply(JFunction0$mcV$sp.java:12)
  at scala.concurrent.BlockContext$.withBlockContext(BlockContext.scala:81)
  at akka.dispatch.BatchingExecutor$BlockableBatch.run(BatchingExecutor.scala:91)
  at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:40)
  at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(ForkJoinExecutorConfigurator.scala:43)
  at akka.dispatch.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
  at akka.dispatch.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
  at akka.dispatch.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
  at akka.dispatch.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)
Caused by: java.sql.SQLException: Incorrect string value: '\xE0\xA6\xAC\xE0\xA6\xBE...' for column 'product_name' at row 1
  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2734)
  at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2458)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2375)
  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2359)
  at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
  at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
  at io.ebeaninternal.server.type.DataBind.executeUpdate(DataBind.java:82)
  at io.ebeaninternal.server.persist.dml.InsertHandler.execute(InsertHandler.java:122)
  at io.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:73)
  ... 59 more

I was trying to save a record using io.Ebean. I fixed it by re creating my database with utf8mb4 collation, and applied play evolution to re create all tables so that all tables should be recreated with utf-8 collation.

CREATE DATABASE inventory CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

User · Answer

I had the same problem in my rails project   Incorrect string value    xF0 xA9 xB8 xBDs      for column  subject  at row1   Solution 1   before saving to db convert string to base64 by    Base64 encode64 subject  and after fetching from db use Base64 decode64 subject   Solution 2   Step 1   Change the character set  and collation  for subject column by  ALTER TABLE t1 MODIFY subject VARCHAR 255    CHARACTER SET utf8mb4   COLLATE utf8mb4 unicode ci    Step 2  In database yml use   encoding  utf8mb4

User · Answer

I you only want to apply the change only for one field  you could try serializing the field  class MyModel  lt  ActiveRecord  Base   serialize  content    attr accessible  content   title end

User · Answer

Got the same problem  to save the data with utf8mb4 needs to make sure    character set client  character set connection  character set results are utf8mb4  character set client and character set connection indicate the character set in which statements are sent by the client  character set results indicates the character set in which the server returns query results to the client  See charset-connection  the table and column encoding is utf8mb4   For JDBC  there are two solutions   Solution 1  need to restart MySQL     modify my cnf like the following and restart MySQL    mysql  default-character-set utf8mb4   mysqld  character-set-server utf8mb4 collation-server utf8mb4 unicode ci    this can make sure the database and character set client  character set connection  character set results are utf8mb4 by default    restart MySQL change the table and column encoding to utf8mb4 STOP specifying characterEncoding UTF-8 and characterSetResults UTF-8 in the jdbc connector cause this will override character set client  character set connection  character set results to utf8   Solution two  don t need to restart MySQL     change the table and column encoding to utf8mb4 specifying characterEncoding UTF-8 in the jdbc connector cause the jdbc connector doesn t suport utf8mb4  write your sql statment like this  need to add allowMultiQueries true to jdbc connector     SET NAMES utf8mb4 INSERT INTO Mytable          this will make sure  each connection to the server  character set client character set connection character set results are utf8mb4  Also see charset-connection

User · Answer

Assuming you are using phpmyadmin to solve this error  follow these steps     phpMyAdmin your table   Structure tab  change the Collation of your field from latin1 swedish ci  or whatever it is  to utf8 general ci

User · Answer

You need to set utf8mb4 in meta html and also in your server alter tabel and set collation to utf8mb4

User · Answer

Hint  On AWS RDS you need a new Parameter Group for your MySQL DB with the params  instead of editing a my cnf    collation connection  utf8mb4 unicode ci collation database  utf8mb4 unicode ci collation server  utf8mb4 unicode ci character set client  utf8mb4 character set connection  utf8mb4 character set database  utf8mb4 character set results  utf8mb4 character set server  utf8mb4   Note  character set system stays  utf8   These SQL commands do NOT WORK PERMANENTLY - only in a session   set character set server   utf8mb4  set collation server   utf8mb4 unicode ci

User · Answer

this is not the recommendation solution   But worth to share  Since my project are upgrade the DBMS from old Mysql to newest  8   But I cant change the table structure  only the DBMS config  mysql   The solution for mysql server   test on Windows mysql 8 0 15  on mysql config search for     sql-mode           uncomment it  Or in my case just type add     sql-mode  NO ENGINE SUBSTITUTION    why not recommended solution  because if you use latin1  my case    the data insert successly but not the content  mysql not respond with error      for example you type info like this     bla  x12    it save     bla     box    okay   for my problem   I can change the field to UTF8   But there is small problem   see above answer about other solution is failed because the word is not inserted because contain more than 2 bytes  cmiiw    this solution make your insert data become box  The reasonable is to use blob   and you can skip my answer   Another testing related to this were   using utf8 encode on your code before save  I use on latin1 and it was success  I m not using sql-mode   same as above answer using base64 encode     My suggestion to analys your table requirement and tried to change from other format to UTF8

User · Answer

In my case  I tried everything above  nothing worked  I am pretty sure  my database looks like below   mysql  Ver 14 14 Distrib 5 7 17  for Linux  x86 64  using  EditLine wrapper  Connection id       12 Current database    xxx Current user        yo localhost SSL             Not in use Current pager       stdout Using outfile          Using delimiter       Server version      5 7 17-0ubuntu0 16 04 1  Ubuntu  Protocol version    10 Connection      Localhost via UNIX socket Server characterset     utf8 Db     characterset     utf8 Client characterset     utf8 Conn   characterset     utf8 UNIX socket          var run mysqld mysqld sock Uptime          42 min 49 sec  Threads  1  Questions  372  Slow queries  0  Opens  166  Flush tables  1  Open tables  30  Queries per second avg  0 144   so  I look up the column charset in every table  show create table company    It turns out the column charset is latin  That s why  I can not insert Chinese into database    ALTER TABLE company CONVERT TO CHARACTER SET utf8    That might help you

User · Answer

Droppping schema and recreating it with utf8mb4 character set solved my issue

User · Answer

I also had to drop and re-create all the database   s stored procedures  and functions too  in order that they execute within the new character set of utf8mb4   Run   SHOW PROCEDURE STATUS       to see which procedures have not been updated to the server   s new character set client  collation connection and Database Collation values

User · Answer

my solution is change the column type from varchar 255  to blob

User · Answer

Its mostly caused due to some unicode characters   In my case it was the Rupee currency symbol    To quickly fix this  I had to spot the character causing this error   I copy pasted the entire text in a text editor like vi and replaced the troubling character with a text one

User · Answer

just do   ALTER TABLE  some table   CHARACTER SET   utf8   COLLATE   utf8 general ci    ALTER TABLE  some table   CHANGE COLUMN  description with latin or something   description  TEXT CHARACTER SET  utf8  NOT NULL

User · Answer

If you are creating a new MySQL table  you can specify the charset of all columns upon creation  and that fixed the issue for me   CREATE TABLE tablename    lt list-of-columns gt    CHARSET SET utf8mb4 COLLATE utf8mb4 unicode ci    You can read more details  https   dev mysql com doc refman 8 0 en charset-column html

User · Answer

The strings that contain  xF0 are simply characters encoded as multiple bytes using UTF-8  Although your collation is set to utf8 general ci  I suspect that the character encoding of the database  table or even column may be different   They are independent settings   Try  ALTER TABLE database table MODIFY COLUMN col VARCHAR 255        CHARACTER SET utf8 COLLATE utf8 general ci NOT NULL   Substitute whatever your actual data type is for VARCHAR 255

User · Answer

MySQL s utf8 permits only the Unicode characters that can be represented with 3 bytes in UTF-8  Here you have a character that needs 4 bytes   xF0 x90 x8D x83  U 10343 GOTHIC LETTER SAUIL    If you have MySQL 5 5 or later you can change the column encoding from utf8 to utf8mb4  This encoding allows storage of characters that occupy 4 bytes in UTF-8   You may also have to set the server property character set server to utf8mb4 in the MySQL configuration file  It seems that Connector J defaults to 3-byte Unicode otherwise      For example  to use 4-byte UTF-8 character sets with Connector J  configure the MySQL server with character set server utf8mb4  and leave characterEncoding out of the Connector J connection string  Connector J will then autodetect the UTF-8 setting

User · Answer

However  it is important to note that the mysql connector driver version must be older than 5 1 47 and later

User · Answer

I wanted to combine a couple of posts to make a full answer of this since it does appear to be a few steps    Above advice by  madtracey    etc mysql my cnf or  etc mysql mysql conf d mysqld cnf   mysql  default-character-set utf8mb4   mysqld safe  socket             var run mysqld mysqld sock nice              0   mysqld     character-set-server utf8mb4 collation-server utf8mb4 unicode ci init connect  SET NAMES utf8mb4  sql mode STRICT TRANS TABLES NO ZERO IN DATE NO ZERO DATE ERROR FOR DIVISION BY ZERO NO AUTO CREATE USER NO ENGINE SUBSTITUTION   Again from advice above all jdbc connections had characterEncoding UTF-8and characterSetResults UTF-8 removed from them  With this set -Dfile encoding UTF-8 appeared to make no difference    I could still not write international text into db getting same failure as above  Now using this how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8   Update all your db to use utf8mb4  ALTER DATABASE YOURDB CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    Run this query that gives you what needs to be rung  SELECT CONCAT   ALTER TABLE     table name    CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci        ALTER TABLE     table name    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci      FROM information schema TABLES AS T  information schema  COLLATION CHARACTER SET APPLICABILITY  AS C WHERE C collation name   T table collation AND T table schema    YOURDB  AND  C CHARACTER SET NAME     utf8mb4      OR  C COLLATION NAME not like  utf8mb4      Copy paste output in editor replace all   with nothing post back into mysql when connected to correct db   That is all that had to be done and all seems to work for me  Not the -Dfile encoding UTF-8 is not enabled and it appears to work as expected  E2A Still having an issue   I certainly am in production so it turns out you do need to check over what has been done by above  since it sometimes does not work  here is reason and fix in this scenario   show create table user     password  varchar 255  CHARACTER SET latin1 NOT NULL     username  varchar 255  CHARACTER SET latin1 NOT NULL    You can see some are still latin  attempting to manually update the record   ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4  ERROR 1071  42000   Specified key was too long  max key length is 767 bytes   So let s narrow it down   mysql gt  ALTER TABLE user change username username varchar 255  CHARACTER SET utf8mb4 not NULL  ERROR 1071  42000   Specified key was too long  max key length is 767 bytes mysql gt  ALTER TABLE user change username username varchar 100  CHARACTER SET utf8mb4 not NULL  Query OK  5 rows affected  0 01 sec    In short I had to reduce the size of that field in order to get the update to work   Now when I run   mysql gt  ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4  Query OK  5 rows affected  0 01 sec  Records  5  Duplicates  0  Warnings  0   It all works

[mysql] "Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC?

Examples related to mysql

Examples related to jdbc

Examples related to utf-8

Examples related to utf8mb4