Incorrect string value xF0 x9F x8E xB6 xF0 x9F MySQL

Question

I am trying to store a tweet in my MYSQL table   The tweet is      quiero que me escuches  no te burles no te rias  anoche tuve un sue  o que te fuiste de mi vida   x1f3b6   x1f3b6    The final two characters are both  MULTIPLE MUSICAL NOTES   U 1F3B6   for which the UTF-8 encoding is 0xf09f8eb6   The tweet text field in my table is encoded in utf8mb4   But when I try to store the tweet in that column I get the following error message      Incorrect string value    xF0 x9F x8E xB6 xF0 x9F      for column  tweet text  at row 1    What is going wrong   How can I fix this   I need to store multiple languages as well and this character set works for all languages but not for the special characters like emoticons and emojis   This is my create table statement   CREATE TABLE  twitter status data       unique status id  bigint 20  NOT NULL AUTO INCREMENT     metadata result type  text CHARACTER SET utf8     created at  text CHARACTER SET utf8 NOT NULL COMMENT  UTC time when this Tweet was    created       id  bigint 20  unsigned NOT NULL COMMENT  Unique tweet identifier      id str  text CHARACTER SET utf8 NOT NULL     tweet text  text COMMENT  Actual UTF-8 text      user id str  text CHARACTER SET utf8     user name  text COMMENT  User  s name      user screen name  text COMMENT  Twitter handle      coordinates  text CHARACTER SET utf8    PRIMARY KEY   unique status id      KEY  user id index    user id      FULLTEXT KEY  tweet text index    tweet text     ENGINE InnoDB AUTO INCREMENT 82451 DEFAULT CHARSET utf8mb4

User · Answer

I had hit the same problem and learnt the following-

Even though database has a default character set of utf-8, it's possible for database columns to have a different character set in MySQL. Modified dB and the problematic column to UTF-8:

mysql> ALTER DATABASE MyDB CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'

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

Now creating new tables with:

> CREATE TABLE My_Table_Name (
    twitter_id_str VARCHAR(255) NOT NULL UNIQUE,
    twitter_screen_name VARCHAR(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci,
    .....
  ) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

User · Answer

It may be obvious  but it still was surprising to me  that SET NAMES utf8 is not compatible with utf8mb4 encoding  So for some apps changing table column encoding was not enough  I had to change encoding in app configuration    Redmine  ruby  ROR   In config database yml   production    adapter  mysql2   database  redmine   host  localhost   username  redmine   password  passowrd   encoding  utf8mb4   Custom Yii application  PHP   In config db php   return        class    gt  yii db Connection  class       dsn    gt   mysql host localhost dbname yii        username    gt   yii        password    gt   password        charset    gt   utf8mb4        If you have utf8mb4 as a column table encoding and still getting errors like this  make sure that you have configured correct charset for DB connection in your application

User · Answer

According to the create table statement  the default charset of the table is already utf8mb4  It seems that you have a wrong connection charset    In Java  set the datasource url like this  jdbc mysql   127 0 0 1 3306 testdb useUnicode true amp characterEncoding utf-8      useUnicode true amp characterEncoding utf-8  is necessary for using utf8mb4   It works for my application

User · Answer

Change database charset and collation ALTER DATABASE     database name     CHARACTER SET   utf8mb4     COLLATE   utf8mb4 unicode ci   change specific table s charset and collation ALTER TABLE     table name     CONVERT TO CHARACTER SET utf8mb4     COLLATE utf8mb4 unicode ci   change connection charset in mysql driver before charset utf8 amp parseTime True amp loc Local  after charset utf8mb4 amp collation utf8mb4 unicode ci amp parseTime True amp loc Local  From this article https   hackernoon com today-i-learned-storing-emoji-to-mysql-with-golang-204a093454b7

User · Answer

FOR SQLALCHEMY AND PYTHON  The encoding used for Unicode has traditionally been  utf8   However  for MySQL versions 5 5 3 on forward  a new MySQL-specific encoding  utf8mb4  has been introduced  and as of MySQL 8 0 a warning is emitted by the server if plain utf8 is specified within any server-side directives  replaced with utf8mb3  The rationale for this new encoding is due to the fact that MySQL   s legacy utf-8 encoding only supports codepoints up to three bytes instead of four  Therefore  when communicating with a MySQL database that includes codepoints more than three bytes in size  this new charset is preferred  if supported by both the database as well as the client DBAPI  as in   e   create engine       mysql pymysql   scott tiger localhost test charset utf8mb4   All modern DBAPIs should support the utf8mb4 charset    enter link description here

User · Answer

I was finally able to figure out the issue  I had to change some settings in mysql configuration my ini  This article helped a lot http   mathiasbynens be notes mysql-utf8mb4 character-sets  First i changed the character set in my ini to utf8mb4 Next i ran the following commands in mysql client  SET NAMES utf8mb4   ALTER DATABASE dreams twitter CHARACTER SET   utf8mb4 COLLATE   utf8mb4 general ci    Use the following command to check that the changes are made  SHOW VARIABLES WHERE Variable name LIKE  character  set     OR Variable name LIKE  collation

[mysql] Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' MySQL

Examples related to mysql

Examples related to twitter

Examples related to utf-8

Examples related to emoticons