MySQL throws Incorrect string value error

Question

I'm trying to store the following tweet into a longtext column / utf8 charset / MySQL 5.5. database with MyISAM storage on.

We also tried utf8mb4, utf16, utf32 charsets but are unable to get past this issue.

tweet="@Dorable_Dimples: Okay enough of those #IfYouWereMines I'm getting dep
ressed. #foreveralone ?" lol yes

mysql> ALTER DATABASE foo CHARACTER SET utf8 COLLATE utf8_bin;

mysql> show variables like 'char%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /rdsdbbin/mysql-5.5.12.R1/share/charsets/ |

Incorrect string value: '\xF0\x9F\x98\x94\xE2\x80...' for column 'tweet' at row 1

Unable to store tweet "@Dorable_Dimples: Okay enough of those #IfYouWereM
ines I'm getting depressed. #foreveralone ?" lol yes
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCExcept
ion: could not insert
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMana
gerImpl.java:1387)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMana
gerImpl.java:1315)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMana
gerImpl.java:1321)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityMana
gerImpl.java:843)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(
SQLStateConverter.java:140)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.ja
va:128)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelpe
r.java:66)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(Abstra
ctReturningDelegate.java:64)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstract
EntityPersister.java:2345)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstract
EntityPersister.java:2852)
at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentity
InsertAction.java:71)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplica
te(AbstractSaveEventListener.java:320)
at org.hibernate.event.def.AbstractSaveEventListener.performSave(Abstract
SaveEventListener.java:203)
at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(
AbstractSaveEventListener.java:129)
at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(E
JB3PersistEventListener.java:69)
at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(
DefaultPersistEventListener.java:179)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultP
ersistEventListener.java:135)
at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultP
ersistEventListener.java:61)
at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:808)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:782)
at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:786)
at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityMana
gerImpl.java:837)
... 5 more
Caused by: java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x94\xE2\x
80...' for column 'tweet' at row 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.jav
a:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:
2330)
at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAnd
Extract(IdentityGenerator.java:94)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(Abstra
ctReturningDelegate.java:57)

Solution 1

It's the character at the end of the tweet that's causing the problem.

It looks like an 'emoji' character aka japanese smiley face but it's not displaying for me in either Chrome or Safari.

There are known issues storing 4byte utf characters in some versions of MySQL. Apparently you must use utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length and so can't store character which are outside of the Basic Multilingual Plane

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

Which is news to me as it basically means that the utf8 datatype in MySQL isn't really proper utf8.

There are suggestions of how to handle this here How to insert utf-8 mb4 character(emoji in ios5) in mysql? including:

"Also make sure your app layer sets its database connections' character set to utf8mb4. Double-check this is actually happening – if you're running an older version of your chosen framework's mysql client library, it may not have been compiled with utf8mb4 support and it won't set the charset properly. If not, you may have to update it or compile it yourself"

If you're using Connector/J you need to set character_set_server=utf8mb4 in the connection config.

All your character sets should be utf8mb4, which you may have tried but aren't currently set.

Solution 2

I like Danask57's answer - it's correct and the 'right' way to do it. (I up voted it myself)

However, another quick-and-dirty solution is to change the schema. use a varbinary or binary to store the tweet string:

http://dev.mysql.com/doc/refman/5.0/en/binary-varbinary.html

The upside is that you won't get any character set problems.

The downside is that your string comparison and sorting will be lost, and you won't be able to full text index the column.

Just a suggestion, but this is not the 'right' answer, just a quick and dirty solution that gets things working.

Solution 3

I had this exact issue. To solve, change the default encoding on the mysql server side to utf8mb4 following this excellent guide: http://mathiasbynens.be/notes/mysql-utf8mb4 .

Remember to restart your mysqld service after making changes to the configuration file.

For me, I also needed to update the mysql jdbc driver to version 5.1.18 (from version 5.1.6). I have read somewhere that you must use at least version 5.1.14 for the mysql jdbc driver to play nicely with utf8mb4 character encoding. Hope this helps!

Solution 4

Why do you have text outside of the quotes in your example - ie 'lol yes'

tweet="@Dorable_Dimples: Okay enough of those #IfYouWereMines I'm getting depressed. #foreveralone ?" lol yes

View more solutions

Comments

[priya]

The above message corresponds to this tweet - twitter.com/#!/Dorable_Dimples/status/154099896998309888

[priya]

The weird part is we see a square box at the end of the message in our browser and we fetch these values using the twitter4j API