We fixed the issue using different call. Instead of cast we are using convert now with explicit encoding. Hopefully that call is more stable. The results are what we used to get from the cast call.

convert(unhex(...) using latin1),

Hello there,

Thanks for reaching out, Below is my understanding of your issue :

After the engine version upgrade, you are getting different result for below query when the same query gives correct value in mysql version 5.7.

> mysql> select cast(unhex('DE') as char(255)) = ''; -----> In Aurora MySQL 3.05.2
+-------------------------------------+
| cast(unhex('DE') as char(255)) = '' |
+-------------------------------------+
|                                   1 |
+-------------------------------------+

In MySQL:

> mysql> select cast(unhex('DE') as char(255)) = ''; -------> In MySQL 5.7
+-------------------------------------+
| cast(unhex('DE') as char(255)) = '' |
+-------------------------------------+
|                                   0 |
+-------------------------------------+

Analysis:

The behavior you are seeing is indeed strange , this issue could very well due to the difference in the default chracter_set used by both versions.

In MySQL 8.0.32 , default character_set : utf8mb4 character set and the utf8mb4_0900_ai_ci collation. In MySQL 5.7.44 or below , default character_set : latin1

To test this , I setup 2 different instances in my lab,

1. Aurora MySQL - 8.0.mysql_aurora.3.05.2
2. MySQL 5.7 - 5.7.44-rds.20240529

in Aurora MySQL 8.0.mysql_aurora.3.05.2 , below queries were run :

mysql>  select cast(unhex('DE') as char(255)) = '';
+-------------------------------------+
| cast(unhex('DE') as char(255)) = '' |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set, 1 warning (0.01 sec)

MySQL : 8.0.32

mysql> select cast(unhex('DE') as char(255)) = '';
+-------------------------------------+
| cast(unhex('DE') as char(255)) = '' |
+-------------------------------------+
|                                   1 |
+-------------------------------------+
1 row in set, 1 warning (0.00 sec)

In MySQL 5.7 :

mysql> select cast(unhex('DE') as char(255)) = '';
+-------------------------------------+
| cast(unhex('DE') as char(255)) = '' |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (0.00 sec)

Now to confirm this , I ran below command in Aurora MySQL 8 by explicitly specifying the character_set to use :

mysql> SELECT CAST(UNHEX('DE') AS CHAR(255) CHARACTER SET latin1) = '';
+----------------------------------------------------------+
| CAST(UNHEX('DE') AS CHAR(255) CHARACTER SET latin1) = '' |
+----------------------------------------------------------+
|                                                        0 |
+----------------------------------------------------------+
1 row in set (0.01 sec)

Here you can see, the output is 0 when latin1 is used in teh query for Aurora instead of 1. This is an expected behavior of the character_set for two different engine versions.

Summary:

The issue seems to be related to the character set and collation used by the CHAR data type in MySQL. By default, MySQL uses the utf8mb4 character set and the utf8mb4_0900_ai_ci collation.

When you use CAST to convert a binary string (e.g., the result of UNHEX) to a CHAR data type, MySQL will attempt to interpret the binary data as a string using the default character set and collation. If the binary data contains unprintable characters, MySQL may not be able to represent them correctly, resulting in an empty string.

To resolve this issue, you can try the following:

1. Specify the character set: Use the CHARACTER SET clause to specify the character set that should be used for the CHAR data type. For example:

mysql> SELECT CAST(UNHEX('DE') AS CHAR(255) CHARACTER SET latin1) = '';

2. Use the BINARY data type: Instead of casting to CHAR, try casting to BINARY. This will preserve the original binary data without attempting to interpret it as a string:

mysql> select cast(unhex('DE') as binary(255)) = '';

3. Verify the data: Make sure that the data in the column is correct and not corrupted. You can try using HEX to verify the contents of the column:

SELECT HEX(column) FROM table;

This will show you the hexadecimal representation of the data, which can help you identify any issues.

In addition, If this is not much help, I would suggest you reach out to AWS support via a case from your end instead of RE:Post.

I hope the information helps !

References:

[+] https://dev.mysql.com/doc/refman/8.4/en/charset.html