Getting rid of binary code from string before inserting row in MySQL database

Question

I am fetching tweets from Twitter and storing them in a database for future use. I am using UTF-8 encoding in my driver, utf8_mb4_bin in my VARCHAR fields and utf8mb4_general_ciserver collation. The problem with that is that when inserting a value in a VARCHAR field, if the text has any binary code then it will throw an exception since VARCHAR utf8 does not accept binary.

Here is an example, I am fetching the text from here and try inserting it in my database and I get the error:

Incorrect string value: '\xF0\x9F\x98\xB1\xF0\x9F...' for column 'fullTweet' at row 1

My guess is that the two emoticons are causing this. How do I get rid of them before inserting the tweet text in my database?

Update:

Looks like I can manually enter the emoticons. I run this query:

INSERT INTO `tweets`(`id`, `createdAt`, `screenName`, `fullTweet`, `editedTweet`) VALUES (450,"1994-12-19","john",_utf8mb4 x'F09F98B1',_utf8mb4 x'F09F98B1')

and this is what the row in the table looks like:

Are you sure that everything is configured correctly for utf8mb4 support? character-set-server=utf8mb4 in server settings, characterEncoding=UTF-8 in connection URL and correct collation for the field? — axtavt
– axtavt, Commented Apr 19, 2014 at 16:22
in the connection url at the end I add ?useUnicode=true&characterEncoding=UTF-8. The "Server Connection Collation" is utf8mb4_general_ci and the field collation is utf8mb4_bin (I double checked them a million times). — Aki K
– Aki K, Commented Apr 19, 2014 at 16:27
Server charset: UTF-8 Unicode (utf8) (I guess this is the one you are talking about). — Aki K
– Aki K, Commented Apr 19, 2014 at 16:32
What does show variables like 'character_set_server' show? — axtavt
– axtavt, Commented Apr 19, 2014 at 16:34

Mudassir Hasan · Accepted Answer · 2014-04-19 16:25:51Z

1

You can remove non ascii characters from tweet string before inserting.

tweetStr = tweetStr.replaceAll("[^\\p{ASCII}]", "");

answered Apr 19, 2014 at 16:25

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

axtavt · Accepted Answer · 2014-04-19 16:39:14Z

1

It looks like utf8mb4 support is still not configured correctly.

In order to use utf8mb4 in your fields you need to do the following:

Set character-set-server=utf8mb4 in your my.ini or my.cnf. Only character-set-server really matters here, other settings don't.

Add characterEncoding=UTF-8 to connection URL:

jdbc:mysql://localhost:3306/db?characterEncoding=UTF-8

Configure collation of the field

answered Apr 19, 2014 at 16:39

axtavt

243k42 gold badges518 silver badges486 bronze badges

4 Comments

Aki K Over a year ago

I still have the same problem. I literally set everything there was to utf8mb4. I went to my.ini and uncommented the line character_set_server=utf8mb4, I run the query you sent me before and it returns: character_set_server=utf8mb4. I altered the database collation to utf8mb4_general_ci. I altered the table's and each row's collation to utf8mb4_general_ci. The url is exactly how you wrote it. I really can't understand what is going on here.

axtavt Over a year ago

What if you try to insert literal value _utf8mb4 x'F09F98B1' into that column manually?

Aki K Over a year ago

Looks like that works (if I did it right). I will update my post with the query I run and how it is represented in the table.

Aki K Over a year ago

I found what's wrong. Looks like I don't need to change the connection URL as well since I'm settting the character-set-server. Now it sorta works, it saves the text but with question marks instead of emoticons. That is good enough for me now.

Collectives™ on Stack Overflow

Getting rid of binary code from string before inserting row in MySQL database

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related