“Incorrect string value” when trying to insert UTF

问题:

This is how my connection is set:
Connection conn = DriverManager.getConnection(url + dbName + \"?useUnicode=true&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?

回答1:

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.

回答2:

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)

回答3:

Got the same problem, to save the data with utf8mb4 needs to make sure:

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

2. the table and column encoding is utf8mb4

For JDBC, there are two solutions:

Solution 1 (need to restart MySQL):

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

1. restart MySQL

2. change the table and column encoding to utf8mb4

3. 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):

1. change the table and column encoding to utf8mb4

2. specifying characterEncoding=UTF-8 in the jdbc connector,cause the jdbc connector doesn\'t suport utf8mb4.

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

回答4:

I wanted to combine a couple of posts to make a full answer of this since it does appear to be a few steps.

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

回答5:

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

回答6:

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 ;

回答7:

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

回答8:

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;

回答9:

Assuming you are using phpmyadmin to solve this error, follow these steps:

1. phpMyAdmin
2. your_table
3. \"Structure tab\"
4. change the Collation of your field from latin1_swedish_ci (or whatever it is) to utf8_general_ci

回答10:

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.

回答11:

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

回答12:

I you only want to apply the change only for one field, you could try serializing the field

class MyModel < ActiveRecord::Base
  serialize :content

  attr_accessible :content, :title
end

回答13:

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