Incorrect string value: '\xF0\x9F\x8E\xB6\xF0\x9F...' MySQL
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&parseTime=True&loc=Local
after
charset=utf8mb4&collation=utf8mb4_unicode_ci&parseTime=True&loc=Local
From this article https://hackernoon.com/today-i-learned-storing-emoji-to-mysql-with-golang-204a093454b7
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;
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' => yii\db\Connection::class,
'dsn' => 'mysql:host=localhost;dbname=yii',
'username' => 'yii',
'password' => 'password',
'charset' => '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.
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%';