Mysql – DatabaseError: 1366 (HY000): Incorrect string value: ‘\xF0\x9F\x92\x96’

MySQL

I'm using MySQL 5.7.20. My schema, tables and columns are all supporting utf8mb4. I used the following:

ALTER SCHEMA `MyDB`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_general_ci ;
ALTER TABLE MyDB.Videos CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

I don't understand why I'm still getting the following error:

DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x92\x96' for column 'Content' at row 1

I can't figure out the reason, please help.

Best Answer

When I specified CHARSET='UTF8MB4'in my connection parameters, it worked.

Related Solutions

MySQL: Converting Character set fails

The ALTER TABLE ... DEFAULT CHARACTER SET ... command sets the default character set and collation for the table. It does not affect the existing VARCHAR/TEXT columns and their data. Only newly created fields will use the specified charset and collation.

The ALTER TABLE ... CONVERT TO CHARACTER SET ... command changes the character sets and collations of all the existing VARCHAR/TEXT fields (and converts the data). It actually should change the default charset for the table as well. It is not specified obvious enough here: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html but it works in that way. On my MySQL 5.1.45 the default charset is changed by the CONVERT operation even it does not include VARCHAR-fields. Also the CONVERT operation may change the datatype if it cannot store the new length of data.

Mysql – How to determine column character set used on creation

This shows the collation and character set for one table:

SELECT column_name, 
       character_set_name, 
       collation_name 
FROM information_schema.columns 
WHERE table_name = 'your_table_name'

Best Answer

Related Solutions

MySQL: Converting Character set fails

Mysql – How to determine column character set used on creation

Related Question