Bug #114628 Cannot convert string '\x80' from binary to utf8mb4

• Submitted: 12 Apr 2:58
• Modified: 12 Apr 7:02
• Reporter: peng a
• Status: Can't repeat
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: mysql 8.0.28
• OS: Any
• CPU Architecture: Any

[12 Apr 2:58] peng a

Description:
When I execute an SQL statement, an error message is generated on version 8.0.28, but it can be executed on version 8.0.35. Excuse me, is there a link to fix this issue?

How to repeat:
mysql> create table tbl(`decimal_col` decimal(25,5) DEFAULT NULL, bit_col bit(8) default 0);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tbl values(-99999999999999999999.99999,b'10000000');
Query OK, 1 row affected (0.01 sec)

----------------------------------8.0.28---------------------------------------
mysql> select decimal_col,bit_col from tbl where bit_col NOT LIKE '0xAA%';
ERROR 3854 (HY000): Cannot convert string '\x80' from binary to utf8mb4

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.28    |
+-----------+
1 row in set (0.00 sec)

----------------------------------8.0.35---------------------------------------
mysql> select decimal_col,bit_col from tbl where bit_col NOT LIKE '0xAA%';
+-----------------------------+------------------+
| decimal_col                 | bit_col          |
+-----------------------------+------------------+
| -99999999999999999999.99999 | 0x80             |
+-----------------------------+------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+--------------+
| @@version    |
+--------------+
| 8.0.35|
+--------------+
1 row in set (0.01 sec)

--------------------------------------------------------------------------
//The character parameters in 8.0.28 and 8.0.35 are the same as follows.
mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------+
| 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       | xxx |
+--------------------------+---------------------------------------------------+
8 rows in set (0.01 sec)

[12 Apr 7:02] MySQL Verification Team

Hello peng a,

Thank you for the report.
IMHO this issue seems to be fixed in 8.0.32:

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table tbl(`decimal_col` decimal(25,5) DEFAULT NULL, bit_col bit(8) default 0);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tbl values(-99999999999999999999.99999,b'10000000');
Query OK, 1 row affected (0.01 sec)

mysql> select decimal_col,bit_col from tbl where bit_col NOT LIKE '0xAA%';
+-----------------------------+------------------+
| decimal_col                 | bit_col          |
+-----------------------------+------------------+
| -99999999999999999999.99999 | 0x80             |
+-----------------------------+------------------+
1 row in set (0.00 sec)

I couldn't confirm which exact or related bug fixed this but will check and update here. https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-32.html

regards,
Umesh