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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[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