ChatGPT解决这个技术问题 Extra ChatGPT

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

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?

That would be LATIN SMALL LETTER N WITH TILDE (ñ).
For others encounter this issue, you could try: On the database: ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - It will solve the” from now on “ created tables. NOT for EXIST tables. For them you need to do : ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Source - digitalocean.com/community/questions/…
Tried the approach by @lingar Just to make the reader aware, this screws up performance of the table very very very badly. So badly that I had to revert the table back to utf8. Looking for another approach.
@Priyshrm, good to know, thanks

E
Ezekiel Victor

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.


What an odd choice to have utf8 really mean "the subset of UTF8 that can be represented in 3 bytes".
character_encoding_server is nota valid MySQL config variable name. I have tried to set character_set_server to utf8mb4 instead, in addition to individual columns, but it didn't change anything.
# For each database: ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; # For each table: ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; # For each column: ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Bizarre that UTF-8 isn't UTF-8 until it's updated to be UTF-8
So you are suggesting that UTF-8 with 3 (three) bytes is unable to store LATIN SMALL LETTER N WITH TILDE (ñ), and we need 4 (four) bytes to spell "España" correctly? Really? Could it be more inneficient than this? What can we store besides A-Z and 0-9 with 3 bytes then..
E
Eric J.

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 utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

Substitute whatever your actual data type is for VARCHAR(255)


Actually tried that, didn't work. The column's data type is LONGTEXT btw, if this matters.
Your app is in Java I take it? Try invoking Java with the file-encoding parameter specifying UTF-8, e.g. java -Dfile.encoding=UTF-8 or add an appropriate switch in your Tomcat (etc.) config file.
I suggest you put an emphasis on "character encoding of the database, table or even column may be different". That is the most important thing.
You will have to alter the table as well with CHARACTER SET utf8 COLLATE utf8_general_ci then after change the column CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
It worked! I wanted to store korean translations in the column. Thank you so much!
d
danronmoon

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


Point 3 was the clincher for me in conjunction with changing the db, table & field encodings: 'SET NAMES utf8mb4;INSERT INTO Mytable ...';
Point 3 did the trick for me too, my table encoding already set to utf8mb4.
The table encoding is just a default. It is sufficient to change the column encoding to utf8mb4.
The second approach should be used selectively, i. e. never be applied to SELECT queries, as set names utf8mb4; select ... from ... will never produce a ResultSet and instead result in a ResultSet is from UPDATE. No Data. error.
solution 2, just par. 1 helped me when I was trying to insert Cyrillic text through my contact form.
C
Community

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


Question: the last ALTER TABLE command will convert the contents of all VARCHAR fields into valid, genuine UTF8 encoded string? I ask because I'm having problems converting my LATIN1 fields to UTF8, specifically when the ñ character is found, conversion directly fails due to an incorrect string value (error 1366).
if you mean ALTER TABLE user CONVERT TO CHARACTER SET utf8mb4; strangely enough when I ran this the final time all the fields no longer had a character set defined. so password from above became password varchar(255) NOT NULL, (nothing about encoding). This means the last command simply must have made mysql lookup what the actual table definition was and because now the table was by default this the fields no longer need it - I presume they remained with the character set simply because during the dump of entire table updates it couldn't update it and hence it was left in that state
c
crazy_phage

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


B
BSMP

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

s
shareef

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 ;

what if I have a bunch of tables I want to change in database though? and what if all have different storage engine (innodb, etc)?
T
Teo Mihaila

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


Not valid, you are supposing he uses phpMyAdmin.
doesnt work...... and the collation is changed in 'operation' and not in structure
@OlorunfemiAjibulu yes, you can change it in "structure" as well. For some people here, it worked
@TeoMihaila Perhaps, it's versioning.
B
BTR Naidu

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.


The OP mentioned that there are a thousand records being inserted....
P
Paul Marclay

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

M
Md Ashfak Chowdhury

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;

e
electrobabe

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;

z
zhuochen shen

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


a
amucunguzi

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 (
<list-of-columns>
)
CHARSET SET utf8mb4 COLLATE utf8mb4_unicode_ci;

You can read more details: https://dev.mysql.com/doc/refman/8.0/en/charset-column.html


u
user2905554

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


In my settings.py (Django Project), I changed to sql-mode="NO_ENGINE_SUBSTITUTION". It's working.
S
Sona Israyelyan

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


V
Volodymyr Taras

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


E
Ethan Allen

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.


l
lyrl

However, it is important to note that the mysql connector driver version must be older than 5.1.47 and later.


K
KR93

After trying many queries finally, this works

ALTER TABLE
table_name
CHANGE column_name column_name 
varchar(256)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;