Bug #117187 Cannot convert string '\xA0' from binary to utf8mb4
Submitted: 13 Jan 7:33 Modified: 13 Jan 10:15
Reporter: sravan gogana Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:8.0.32, 8.0.39, 8.0.40, 8.4.3 OS:Any
Assigned to: CPU Architecture:Any

[13 Jan 7:33] sravan gogana 
Description:
getting error "Cannot convert string '\xA0' from binary to utf8mb4"

How to repeat:
mysql> show global variables like '%char%';
+--------------------------------------+--------------------------------+
| Variable_name                        | Value                          |
+--------------------------------------+--------------------------------+
| character_set_client                 | utf8mb4                        |
| character_set_connection             | utf8mb4                        |
| character_set_database               | utf8mb4                        |
| character_set_filesystem             | binary                         |
| character_set_results                | utf8mb4                        |
| character_set_server                 | utf8mb4                        |
| character_set_system                 | utf8mb3                        |
| character_sets_dir                   | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1                              |
+--------------------------------------+--------------------------------+

mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4                  | utf8mb4_0900_ai_ci   |
+--------------------------+----------------------+

mysql> CREATE TABLE TABLE2 (
    -> TABLE_NAME varchar(100) COLLATE utf8mb4_bin NOT NULL,
    -> TALEND_CDC_TYPE varchar(5) COLLATE utf8mb4_bin DEFAULT NULL,
    -> TALEND_CDC_CREATION_DATE varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CCNTRY varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CPCCD varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CRCTNM varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUADD1 varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUADD2 varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUADD3 varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUCNY varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUCYST varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUNM varchar(28) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUNM2 varchar(28) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUNO varchar(7) COLLATE utf8mb4_bin NOT NULL,
    -> CUSRK varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUSTE varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUSTST varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CUTYP varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
    -> DIVI varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
    -> DTSYS8 varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
    -> FACSNO varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
    -> FLGDLI varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
    -> HDCGC1 varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> HDCGC2 varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> IVTYPI varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
    -> LBCC varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
    -> LOC varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> LSMN8 varchar(40) COLLATE utf8mb4_bin DEFAULT NULL,
    -> MCCC varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
    -> OECTNM varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
    -> PACC varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
    -> PHNO varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
    -> PRCUNO varchar(7) COLLATE utf8mb4_bin DEFAULT NULL,
    -> SVCTNM varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
    -> TERMCD varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
    -> TPDLCD varchar(4) COLLATE utf8mb4_bin DEFAULT NULL,
    -> ZIPCD9 varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
    -> STN1 varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
    -> MLSLI0 varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CINSND varchar(1) COLLATE utf8mb4_bin DEFAULT NULL,
    -> CRCLCD varchar(3) COLLATE utf8mb4_bin DEFAULT NULL,
    -> TXCD1 varchar(2) COLLATE utf8mb4_bin DEFAULT NULL,
    -> PRIMARY KEY (TABLE_NAME,CUNO),
    -> KEY CILNAME3 (CUNO)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

mysql> INSERT INTO TABLE2 (
    ->     TABLE_NAME, TALEND_CDC_TYPE, TALEND_CDC_CREATION_DATE, CCNTRY, CPCCD, CRCTNM, CUADD1, CUADD2, CUADD3,
    ->     CUCNY, CUCYST, CUNM, CUNM2, CUNO, CUSRK, CUSTE, CUSTST, CUTYP, DIVI, DTSYS8, FACSNO, FLGDLI,
    ->     HDCGC1, HDCGC2, IVTYPI, LBCC, LOC, LSMN8, MCCC, OECTNM, PACC, PHNO, PRCUNO, SVCTNM, TERMCD,
    ->     TPDLCD, ZIPCD9, STN1, MLSLI0, CINSND, CRCLCD, TXCD1
    -> ) VALUES
    -> ('CUSTOMER', 'I', '2024-11-19T09:13:34', 'USA', 'USD', 'John Smith', '123 Main St', 'Apt 4B', NULL, 'USD', 'NY', 'John Smith', NULL, '0000001', 'CUST001', 'NY', '01', 'RE', 'A', '2024-11-19T09:13:34', '555-1234', 'Y', '001', '1A', 'R', 'NY', 'NYC', '2024-11-19', '01', 'Sales Dept', '01', '555-123-4567', NULL, 'Support Team A', '1', 'STD', '10001', 'ST001', 'Y', 'N', 'ABC', '01'),
    -> ('CUSTOMER', 'U', '2024-11-19T10:15:00', 'CAN', 'CAD', 'Jane Doe', '456 Elm St', NULL, 'Suite 300', 'CAD', 'ON', 'Jane Doe', 'JD Inc', '0000002', 'CUST002', 'ON', '02', 'CO', 'B', '2024-11-19T10:15:00', '555-5678', 'N', '002', '1B', 'W', 'ON', 'TOR', '2024-11-20', '02', 'Marketing', '02', '555-987-6543', '0000001', 'Support Team B', '2', 'EXP', 'M5V 2T6', 'ST002', 'N', 'Y', 'DEF', '02'),
    -> ('CUSTOMER', 'I', '2024-11-20T08:30:15', 'GBR', 'GBP', 'Robert Brown', '789 Oak Rd', 'Floor 2', NULL, 'GBP', 'LDN', 'Robert Brown', NULL, '0000003', 'CUST003', 'LN', '03', 'IN', 'C', '2024-11-20T08:30:15', '555-9012', 'Y', '003', '1C', 'M', 'LN', 'LON', '2024-11-21', '03', 'Finance', '03', '555-345-6789', NULL, 'Support Team C', '1', 'STD', 'SW1A 1AA', 'ST003', 'Y', 'N', 'GHI', '03'),
    -> ('CUSTOMER', 'D', '2024-11-21T14:45:30', 'JPN', 'JPY', 'Yuki Tanaka', '1-1-1 Sakura', 'Chiyoda-ku', 'Tokyo', 'JPY', 'TK', 'Yuki Tanaka', NULL, '0000004', 'CUST004', 'TK', '04', 'WH', 'D', '2024-11-21T14:45:30', '555-3456', 'N', '004', '1D', 'S', 'TK', 'TKY', '2024-11-22', '04', 'Logistics', '04', '555-901-2345', '0000002', 'Support Team D', '2', 'EXP', '100-0001', 'ST004', 'N', 'Y', 'JKL', '04'),
    -> ('CUSTOMER', 'I', '2024-11-22T11:20:45', 'DEU', 'EUR', 'Hans Mueller', 'Hauptstr. 1', NULL, NULL, 'EUR', 'BE', 'Hans Mueller', 'HM GmbH', '0000005', 'CUST005', 'BE', '05', 'DI', 'E', '2024-11-22T11:20:45', '555-7890', 'Y', '005', '1E', 'T', 'BE', 'BER', '2024-11-23', '05', 'HR Dept', '05', '555-678-9012', NULL, 'Support Team E', '1', 'STD', '10115', 'ST005', 'Y', 'N', 'MNO', '05'),
    -> ('CUSTOMER', 'U', '2024-11-23T16:55:00', 'FRA', 'EUR', 'Marie Dubois', '10 Rue de la Paix', '3me tage', NULL, 'EUR', 'PA', 'Marie Dubois', NULL, '0000006', 'CUST006', 'PR', '06', 'RE', 'F', '2024-11-23T16:55:00', '555-2345', 'N', '006', '1F', 'U', 'PR', 'PAR', '2024-11-24', '06', 'Legal Dept', '06', '555-234-5678', '0000003', 'Support Team F', '2', 'EXP', '75001', 'ST006', 'N', 'Y', 'PQR', '06'),
    -> ('CUSTOMER', 'I', '2024-11-24T09:10:20', 'AUS', 'AUD', 'Sarah Johnson', '42 Wallaby Way', 'Sydney', NULL, 'AUD', 'NS', 'Sarah Johnson', 'SJ Pty Ltd', '0000007', 'CUST007', 'NS', '07', 'WH', 'G', '2024-11-24T09:10:20', '555-6789', 'Y', '007', '1G', 'V', 'NS', 'SYD', '2024-11-25', '07', 'R&D Dept', '07', '555-789-0123', NULL, 'Support Team G', '1', 'STD', '2000', 'ST007', 'Y', 'N', 'STU', '07'),
    -> ('CUSTOMER', 'D', '2024-11-25T13:40:35', 'BRA', 'BRL', 'Carlos Silva', 'Av. Paulista 1000', 'Sala 501', 'So Paulo', 'BRL', 'SP', 'Carlos Silva', 'CS Ltda', '0000008', 'CUST008', 'SP', '08', 'MA', 'H', '2024-11-25T13:40:35', '555-0123', 'N', '008', '1H', 'X', 'SP', 'SAO', '2024-11-26', '08', 'IT Dept', '08', '555-012-3456', '0000004', 'Support Team H', '2', 'EXP', '01310-100', 'ST008', 'N', 'Y', 'VWX', '08'),
    -> ('CUSTOMER', 'I', '2024-11-26T10:25:50', 'IND', 'INR', 'Priya Patel', '777 MG Road', 'Bangalore', NULL, 'INR', 'KA', 'Priya Patel', NULL, '0000009', 'CUST009', 'KA', '09', 'SE', 'I', '2024-11-26T10:25:50', '555-4567', 'Y', '009', '1I', 'Y', 'KA', 'BLR', '2024-11-27', '09', 'Customer Svc', '09', '555-456-7890', NULL, 'Support Team I', '1', 'STD', '560001', 'ST009', 'Y', 'N', 'YZA', '09'),
    -> ('CUSTOMER', 'U', '2024-11-27T15:05:10', 'SGP', 'SGD', 'Lee Kuan Yew', '1 Marina Bay', '#10-01', NULL, 'SGD', 'SG', 'Lee Kuan Yew', 'LKY Pte Ltd', '0000010', 'CUST010', 'SG', '10', 'DI', 'J', '2024-11-27T15:05:10', '555-8901', 'N', '010', '1J', 'Z', 'SG', 'SIN', '2024-11-28', '10', 'Operations', '10', '555-890-1234', '0000005', 'Support Team J', '2', 'EXP', '018989', 'ST010', 'N', 'Y', 'BCD', '10');

mysql> SELECT
    ->     CASE
    ->         WHEN TABLE2.CUNM2 IS NOT NULL AND TRIM(TABLE2.CUNM2) <> '' THEN
    ->             LTRIM(
    ->                 RTRIM(
    ->                     CONCAT(
    ->                         LTRIM(
    ->                             RTRIM(
    ->                                 REPLACE(
    ->                                     REPLACE(
    ->                                         REPLACE(
    ->                                             REPLACE(TABLE2.CUNM, CHAR(10), CHAR(32)),
    ->                                             CHAR(13), CHAR(32)
    ->                                         ),
    ->                                         CHAR(160), CHAR(32)
    ->                                     ),
    ->                                     CHAR(9), CHAR(32)
    ->                                 )
    ->                             )
    ->                         ),
    ->                         ' ',
    ->                         LTRIM(
    ->                             RTRIM(
    ->                                 REPLACE(
    ->                                     REPLACE(
    ->                                         REPLACE(
    ->                                             REPLACE(TABLE2.CUNM2, CHAR(10), CHAR(32)),
    ->                                             CHAR(13), CHAR(32)
    ->                                         ),
    ->                                         CHAR(160), CHAR(32)
    ->                                     ),
    ->                                     CHAR(9), CHAR(32)
    ->                                 )
    ->                             )
    ->                         )
    ->                     )
    ->                 )
    ->             )
    ->         ELSE
    ->             LTRIM(
    ->                 RTRIM(
    ->                     REPLACE(
    ->                         REPLACE(
    ->                             REPLACE(
    ->                                 REPLACE(TABLE2.CUNM, CHAR(10), CHAR(32)),
    ->                                 CHAR(13), CHAR(32)
    ->                             ),
    ->                             CHAR(160), CHAR(32)
    ->                         ),
    ->                         CHAR(9), CHAR(32)
    ->                     )
    ->                 )
    ->             )
    ->     END AS field_name
    -> FROM TABLE2;
ERROR 3854 (HY000): Cannot convert string '\xA0' from binary to utf8mb4
[13 Jan 7:44] sravan gogana 
issue reproducible on 8.4.3 as well.
[13 Jan 7:49] MySQL Verification Team 
Hello Sravan gogana,

Thank you for the report and test case.
Verified as described.

regards,
Umesh
[13 Jan 8:53] Roy Lyseng 
Posted by developer:
 
Not a bug.
By default, the CHAR function returns a value which is a BINARY string.
This is not always possible to convert to a UTF8 string, so the error message
looks correct.
[13 Jan 10:15] sravan gogana 
Any workaround ?
[13 Jan 21:52] Roy Lyseng 
The CHAR function can take a USING option that returns results in any
character set. You may experiment with that to find a solution that
satisfies your requirements.
[29 Jan 7:40] Vikas Singh 
I have tried with different clients on the original and the modified query :

Original query :

SELECT
			CASE 
               WHEN TEST2.CUNM2 IS NOT NULL AND TRIM(TEST2.CUNM2)<> '' THEN LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),char(160), CHAR(32)),CHAR(9),CHAR(32)))),' ',LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM2, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),char(160), CHAR(32)),CHAR(9),CHAR(32))))))) 
               ELSE LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),char(160), CHAR(32)),CHAR(9),CHAR(32))))
			END AS field_name
			FROM TEST2;

Modified Query :

SELECT
			CASE 
               WHEN TEST2.CUNM2 IS NOT NULL AND TRIM(TEST2.CUNM2)<> '' THEN LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),convert(char(160) using utf8mb4), CHAR(32)),CHAR(9),CHAR(32)))),' ',LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM2, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),convert(char(160) using utf8mb4), CHAR(32)),CHAR(9),CHAR(32))))))) 
               ELSE LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM, CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),convert(char(160) using utf8mb4), CHAR(32)),CHAR(9),CHAR(32))))
			END AS field_name
			FROM TEST2;

Observations are as below :

1. Error does not come in MySQL Workbench with the modified query.
2. Error does not come with Platform Independent (Architecture Independent) J connector
3. Error comes with J connector for Redhad Linux 8.1 Driver version 8.0.18 with the modified query as well

Invalid utf8mb4 character string: 'A0'
[13 Feb 19:37] Donald Quindardo 
MySQL 8.0's switch to utf8mb4 as the default character set may affect how converting non-breaking spaces (\xA0) are handled. To maintain consistent behavior when working with non-breaking spaces, you can replace "CHAR(160)" with "CONVERT(CHAR(160) USING latin1)" in your query statements.

This solution is provided as an example to address the issue reported with the provided test data and queries. It is strongly recommended to thoroughly test and validate this solution within your specific application context and database configuration before implementing in production.

=== UPDATED QUERY ===

SELECT CASE
           WHEN TEST2.CUNM2 IS NOT NULL
                AND TRIM(TEST2.CUNM2)<> '' THEN LTRIM(RTRIM(CONCAT(LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM, CHAR(10), CHAR(32)), CHAR(13), CHAR(32)), CONVERT(CHAR(160) USING latin1), CHAR(32)), CHAR(9), CHAR(32)))), ' ', LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM2, CHAR(10), CHAR(32)), CHAR(13), CHAR(32)), CONVERT(CHAR(160) USING latin1), CHAR(32)), CHAR(9), CHAR(32)))))))
           ELSE LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(TEST2.CUNM, CHAR(10), CHAR(32)), CHAR(13), CHAR(32)), CONVERT(CHAR(160) USING latin1), CHAR(32)), CHAR(9), CHAR(32))))
       END AS field_name
FROM TEST2;

=====================