Incorrect string value: '\\xA0Consu...' for column even though column has utf8mb4 encoding

Question

I am running python script to insert record in table. I am reading data from spreadsheet that contain cell with special characters.

In my script I am running following commands before executing below commands:

SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET character_set_connection=utf8mb4;
SET collation_connection = utf8mb4_unicode_ci;

Here is my table structure:

CREATE TABLE `staging` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`d_date` date NOT NULL,
`m_date` date NOT NULL,
`market` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'US',
`cola` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`colb` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Table Encoding: UTF8MB4 Table Collation: UTF8MB4_UNICODE_CI

Environment Variables:

SHOW VARIABLES LIKE '%collation%';

collation_connection    latin1_swedish_ci
collation_database  utf8mb4_unicode_ci
collation_server    utf8mb4_unicode_ci

SHOW VARIABLES LIKE '%char%';

character_set_client    latin1
character_set_connection    latin1
character_set_database  utf8mb4
character_set_filesystem    binary
character_set_results   latin1
character_set_server    utf8mb4
character_set_system    utf8
character_sets_dir  /usr/local/mysql-5.7.20-macos10.12-x86_64/share/charsets/

Query:

insert into staging (d_date, m_date, market, cola, colb ) 
values ('2019-07-18', '2019-07-01', 'US', 'ARCA', 'Sodius\xa0Consumer Auto');

Error:

_mysql_exceptions.OperationalError: (1366, "Incorrect string value: '\\xA0Consu...' for column 'colb' at row 1")

Expected Result is row should be inserted into the database.

That string literal shown in the INSERT statement should evaluate without error. The backslash character will get dropped because the character following it doesn't form a valid MySQL escape sequence. `Sodiusxa0Con...` (If sql_mode includes NO_BACKSLASH_ESCAPES then the backslash character would be treated as a regular character.) I'm suspicious that the SQL statement shown in the question, with the string literal, is *not* the actual statement being executed. — spencer7593, Jul 19 '19 at 21:28
@spencer7593 yes, the query is not an exact query. I have copied that from the command output. — Amit S, Jul 19 '19 at 21:41

score 1 · Accepted Answer · answered Jul 19 '19 at 21:48

This says that the encoding in the client is UTF-8: SET NAMES utf8mb4;

This says that the encoding in the client is latin1: \xa0.

This says that cola will be encoded UTF-8, regardless of what the client has: cola varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL.

These say that you did SET NAMES latin1:

character_set_client    latin1
character_set_connection    latin1
character_set_results   latin1

If you really want the \xA0 (hard space), then the entire text in the client must also be latin1, and you must say SET NAMES latin1 (or ... -- there are several ways to do this).

If you don't care what type of space you use, then use a simple space.

If the rest of the client is talking UTF-8, and you want a "non-breaking space", then USE \xc2a0 (the UTF-8 encoding).

Hi Rick, Thanks a lot for your response. Got it. It's working now. — Amit S, Jul 19 '19 at 22:24

Incorrect string value: '\\xA0Consu...' for column even though column has utf8mb4 encoding

1 Answers1