Incorrect string value: \'\\xC3\' for column \'description\' at row 1 in mySql

Question

Here, In my table, I've one column name as description.

As per my error, I've tried many solutions from SO to change the collation type.

I've tried below collection

1) utf8mb4_unicode_ci
2) utf8_general_ci

Here, SHOW FULL COLUMNS FROM your_table;

Can anyone know what is the right collation for \'\\xC3\' this type of string?

Smek · Accepted Answer · 2020-04-16T08:02:21.713

4

To support full UTF-8 Unicode like for example emojis in your case it is the character À you should use utf8mb4 and utf8mb4_unicode_ci utf8 is outdated.

You can find a full explanation at https://mathiasbynens.be/notes/mysql-utf8mb4.

You can check the current collations of your table like this:

SHOW FULL COLUMNS FROM your_table;

I assume your description column has type TEXT otherwise you might need to change the type.

To alter the table default character set you can use:

ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4;

But this does not change the collation of your column. To change the collation of your column you should use:

ALTER TABLE your_table MODIFY description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

edited Apr 16 '20 at 08:02

answered Apr 16 '20 at 07:53

Smek

1,158
11
33

Well explained :) I followed your inscription but still I'm getting same error. see my edit answer I've uploaded `SHOW FULL COLUMNS FROM your_table;` – Dilip Hirapara Apr 16 '20 at 08:40
I do not have any experience with Laravel but maybe you need to change the collation and charset in your database configuration so that you connect with charset utf8mb4 and collation utf8mb4_unicode_ci. See https://laravel.com/docs/7.x/database#read-and-write-connections. – Smek Apr 16 '20 at 09:33
I followed another way as whatever string I get I convert that string base64_encode and store it as a string. while fetch to show I convert base64_decode. Thank you for you kind help :) – Dilip Hirapara Apr 16 '20 at 10:06
No problem happy to help! – Smek Apr 16 '20 at 10:44

score 1 · Answer 2 · answered Apr 16 '20 at 07:41

1

Try this first

ALTER TABLE your_database_name.your_table CONVERT TO CHARACTER SET utf8

OR If above solution won't work then do the following after connecting to your database

SET NAMES 'utf8';
SET CHARACTER SET utf8;

answered Apr 16 '20 at 07:41

zealous

7,336
4
16
36

Thank you for your answer, It gives me the same error. – Dilip Hirapara Apr 16 '20 at 08:43

score 0 · Answer 3 · answered May 02 '23 at 11:17

I was stuck in the same problem for so long. Then this function helped me

function replacebadchar($str) {
    global $conn;
    $str = UConverter::transcode($str, 'ISO-8859-1', 'UTF-8');
    $str = UConverter::transcode($str, 'ISO-8859-1', 'UTF-8', ['to_subst' => '?']);
    $str = UConverter::transcode($str, 'ISO-8859-1', 'UTF-8', ['to_subst' => '?']);
    return mysqli_real_escape_string($conn, $str);
}

Incorrect string value: \'\\xC3\' for column \'description\' at row 1 in mySql

3 Answers3