mysqllaravelutf8mb4character-set

MySQL: 1366 Incorrect string value: '\xE0' in utf8mb4 column


I'm getting errors when certain characters are being added to a table... even when the column is has utf8mb4 character set. For example:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE0' for column 'surname'

The data in question is: SEGAL‡ (note the double dagger)

Is this character beyond even 4 byte UTF8 or is the collation causing the issue? Or is it something else?

Screenshot showing character set and collation of the column:

screenshot showing utf8mb4 character set

It's a Laravel 8 app and the MySQL connection is configured to the following:

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

Looking at the CSV file in PHPStorm, non-ASCII characters are displayed as . I've tried explicitly setting the file encoding to UTF-8 in PHPStorm (with and without BOM).

If I open the CSV in Excel then the non-ASCII characters display correctly. Confused.

Update

Examining the CSV in a HEX editor shows that a character like ä is stored as a single byte (8A). When this CSV is opened in Excel it correctly shows ä, but in everything else it shows .

I don't know what character encoding Excel is using, as this character should be typically stored as E4 when using a single byte, or C3 A4 in UTF-8.


Solution

  • Double dagger is hex E2 80 A1 (only 3 bytes) when encoded in CHARACTER SET utf8 or utf8mb4. It is also available in latin1 as hex 87.

    C3A4 [ä]LATIN SMALL LETTER A WITH DIAERESIS (in utf8/utf8mb4) or E4 in latin1.

    Please provide SHOW VARIABLES LIKE 'char%';

    See "black diamond" in Trouble with UTF-8 characters; what I see is not what I stored for the likely cause.

    The CHARACTER SET is the "encoding" and is relevant; the COLLATION is how characters compare (eg case-folding) and is not relevant.

    Nothing in the Question hints of a BOM (http://mysql.rjweb.org/doc.php/charcoll#bom_byte_order_mark) EF BB BF at the beginning of some UTF-8-encoded files.

    Do not use ucs-2 (UTF-16) unless you happen to have a file encoded that way. Still, I would declare the tables as utf8, not ucs-2.

    More

    I plugged 8AE0E8 into a handy script (that does CONVERT(CONVERT(UNHEX('8ae0e8') USING %s) USING utf8mb4) and got:

    ascii, big5, binary, cp932, eucjpms, euckr,
        gb18030, gb2312, gbk, sjis, ujis,
        utf8mb3, utf8mb4   ---invalid in these charsets
                                         swe7   3  3 '???'
                                       cp1257   5  3 '?ąč'
                                       cp1256   5  3 '?àè'
                                      geostd8   5  3 '?ჭ?'
                                       latin7   6  3 'ąč'
                                 dec8, latin5   6  3 'àè'
                                          hp8   6  3 'ÁÒ'
                                        macce   6  3 'äŗŤ'
                                        cp850   6  3 'èÓÞ'
                                      keybcs2   6  3 'ĹαΦ'
                                        cp852   6  3 'ŐÓŔ'
                                       latin2   6  3 'ŕč'
                                       latin1   6  3 'Šàè'
                                       cp1250   6  3 'Šŕč'
                                        greek   6  3 'ΰθ'
                                        cp866   6  3 'Крш'
                                       cp1251   6  3 'Љаи'
                                     armscii8   6  3 'ՈՌ'
                                       hebrew   6  3 'אט'
                                 koi8r, koi8u   7  3 '┼ЮХ'
                                     macroman   7  3 'ä‡Ë'
                                       tis620   8  3 'เ่'
    

    It looks like macroman is the charset. I think that is specific to Apple.