MySQL throws Incorrect string value error

by

It’s the character at the end of the tweet that’s causing the problem.

It looks like an ’emoji’ character aka japanese smiley face but it’s not displaying for me in either Chrome or Safari.

There are known issues storing 4byte utf characters in some versions of MySQL. Apparently you must use utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length and so can’t store character which are outside of the Basic Multilingual Plane

http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

Which is news to me as it basically means that the utf8 datatype in MySQL isn’t really proper utf8.

There are suggestions of how to handle this here
How to insert utf-8 mb4 character(emoji in ios5) in mysql? including:

“Also make sure your app layer sets its database connections’ character set to utf8mb4. Double-check this is actually happening – if you’re running an older version of your chosen framework’s mysql client library, it may not have been compiled with utf8mb4 support and it won’t set the charset properly. If not, you may have to update it or compile it yourself”

If you’re using Connector/J you need to set character_set_server=utf8mb4 in the connection config.

All your character sets should be utf8mb4, which you may have tried but aren’t currently set.

More Related Contents:

  1. “Incorrect string value” when trying to insert UTF-8 into MySQL via JDBC?
  2. What is the difference between utf8mb4 and utf8 charsets in MySQL?
  3. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci
  4. Mysql server does not support 4-byte encoded utf8 characters
  5. Trouble with UTF-8 characters; what I see is not what I stored
  6. What’s the difference between utf8_general_ci and utf8_unicode_ci?
  7. How to make MySQL handle UTF-8 properly
  8. How to conduct an Accent Sensitive search in MySql
  9. How to display UTF-8 characters in phpMyAdmin?
  10. UTF-8: General? Bin? Unicode?
  11. How to remove accents in MySQL?
  12. MySQL utf8mb4, Errors when saving Emojis
  13. Differences between utf8 and latin1
  14. Incorrect string value: ‘\xF0\x9F\x8E\xB6\xF0\x9F…’ MySQL
  15. MySQL VARCHAR Lengths and UTF-8
  16. How to detect UTF-8 characters in a Latin1 encoded column – MySQL
  17. Fetching UTF-8 text from MySQL in R returns “????”
  18. How to fix double-encoded UTF8 characters (in an utf-8 table)
  19. Loading utf-8 encoded text into MySQL table
  20. MySQL command line formatting with UTF8
  21. Detecting utf8 broken characters in MySQL
  22. The ultimate emoji encoding scheme
  23. UTF8 problem with MySQL 5
  24. ERROR 1115 (42000): Unknown character set: ‘utf8mb4’
  25. MySql: MyISAM vs. Inno DB! [closed]
  26. MySQL 8.0.11 error connect to caching_sha2_password the specified module could not be found
  27. Using group by on two fields and count in SQL
  28. MySQL CONCAT returns NULL if any field contain NULL
  29. Node.js connect to MySQL Docker container ECONNREFUSED
  30. MySQL optimizing INSERT speed being slowed down because of indices

Leave a Comment