Saving tweets in MySql throws "Incorrect string value: '\xF0\x9F\x92\xB2\xF0\x9F"

Question

I am trying to save tweets into MySql db, most of the time it works fine, but when tweet's like the ones given below come,

I get the following exception from MySql,

java.sql.BatchUpdateException: Incorrect string value: '\xF0\x9F\x92\xB2\xF0\x9F...' for column 'twtText' at row 1

How can we handle such texts.

Also getting this error in phpMyAdmin, Maria DB 10.1. Column and table are set to `utf8mb4_unicode_ci`, not sure what the issue is. — William Isted, Oct 18 '16 at 09:47

score 1 · Answer 1 · edited Mar 13 '16 at 18:07

1

This works for me. Changing the character set in MySql

ALTER TABLE TableName MODIFY COLUMN ColumnName varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

edited Mar 13 '16 at 18:07

hotzst

answered Mar 13 '16 at 17:43

Vang Lian

dont use 255 for uft8mb, it uses 4 byets per char, not 3. – Arnold Roa Apr 05 '18 at 00:26

score 0 · Answer 2 · answered Aug 19 '13 at 11:25

0

Try changing your column's charset to the value reflecting the charset of the strings you want to insert.

Example:

ALTER TABLE database.table MODIFY COLUMN col VARCHAR(255)  CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

answered Aug 19 '13 at 11:25

cassi.lup

score 0 · Answer 3 · edited May 23 '17 at 12:33

0

Besides setting the collation on the database table / column, we also had to set on the client, in the app, the collation with: SET NAMES 'utf8mb4';

edited May 23 '17 at 12:33

Community

answered Oct 31 '15 at 19:43

devplayer

score 0 · Answer 4 · answered Feb 10 '18 at 11:59

I got the same issure and solveld it. The cause of the error is that the string contains emoticons.

set your mysql column's charset to utf8mb4 and Collation to utf8mb4_general_ci
set your connection string of charset to utf8mb4 like charset=utf8mb4
ok, test it

4 Answers4