Bug #73242 Error Code: 1366. Incorrect string value: '\xF0\x9F\x94\x8D'

• Submitted: 9 Jul 2014 10:57
• Modified: 13 Jul 2014 9:19
• Reporter: Erwin Derksen
• Status: Closed
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 5.6.19
• OS: Any
• CPU Architecture: Any

[9 Jul 2014 10:57] Erwin Derksen

Description:
Trying to insert the Unicode character U+1F50D (LEFT-POINTING MAGNIFYING GLASS) I get the following error:

Error Code: 1366. Incorrect string value: '\xF0\x9F\x94\x8D' for column 'test' at row 1

How to repeat:
CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `test` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into test values (1, '

Suggested fix:
Allow all valid UTF-8 characters.

[9 Jul 2014 10:58] Erwin Derksen

reproduce

Attachment: test.sql (application/octet-stream, text), 156 bytes.

[9 Jul 2014 11:00] Erwin Derksen

The bug tracker seems to suffer from a similar error as well. Therefore I added the sql to reproduce the error as a file.

[9 Jul 2014 12:10] Umesh Shastry

Thank you for the report.
Imho utf8 charset only partially implements proper UTF-8 encoding, and it can only store UTF-8-encoded symbols that consist of one to three bytes; encoded symbols that take up four bytes aren’t supported. Please use utf8mb4..Ref http://dev.mysql.com/doc/refman/5.6/en/charset-unicode-utf8mb4.html

Try this:

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS test;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `test` (
    ->   `id` int(11) NOT NULL,
    ->   `test` varchar(10) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.15 sec)

mysql> insert into test values (1, '\U+1F50D');
Query OK, 1 row affected (0.02 sec)

mysql> select * from test;
+----+------+
| id | test |
+----+------+
|  1 |

[13 Jul 2014 9:19] Erwin Derksen

Quite ridiculous to call a partial utf-8 implementation utf8, and to call the proper utf-8 implementation utf8mb4, even more while it seems it is forwards compatible, so you could just have changed the utf8 character set (only not supporting downgrading)

But anyway, your answer clarified the problem, so closing it.