Percona XtraDb cluster AES_Encrypt when Decrypt did not return the character string ?

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

create database encrypted;use encrypted;create table t(c1 int, c2 varbinary(255), rnd_pad varbinary(16), primary key(c1));SET block_encryption_mode = ‘aes-256-cbc’;SET @key_str = SHA2(‘My secret passphrase’,512);SET @init_vector = RANDOM_BYTES(16);insert into t (c1,c2, rnd_pad) values (1, AES_ENCRYPT(‘Secret’, @key_str, @init_vector), @init_vector);-- decrypt dataselect c1, AES_DECRYPT(c2,@key_str, rnd_pad) from t;
±—±---------------------------------------------------------------------+| c1 | AES_DECRYPT(c2,@key_str, rnd_pad)                                    |±—±---------------------------------------------------------------------+|  1 | 0x536563726574                                                       |±—±---------------------------------------------------------------------+

2

This appears to be an issue with upstream MySQL.
I tested the commands you provided on 8.0.16 and they worked correctly. But when I upgraded to 8.0.21 (the latest community version), I get back the same result as you.
root@d7b85abf00db:/# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 8.0.21 MySQL Community Server - GPL
…mysql> create database foo;Query OK, 1 row affected (0.28 sec)mysql> use foo;Database changedmysql> SET block_encryption_mode = ‘aes-256-cbc’;Query OK, 0 rows affected (0.00 sec)mysql> SET @key_str = SHA2(‘My secret passphrase’,512);Query OK, 0 rows affected (0.00 sec)mysql> SET @init_vector = RANDOM_BYTES(16);Query OK, 0 rows affected (0.00 sec)mysql> SET @crypt_str = AES_ENCRYPT(‘text’,@key_str,@init_vector);Query OK, 0 rows affected (0.00 sec)mysql> SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);±---------------------------------------------------------------------------------------------+| AES_DECRYPT(@crypt_str,@key_str,@init_vector)                                                |±---------------------------------------------------------------------------------------------+| 0x74657874                                                                                   |±---------------------------------------------------------------------------------------------+1 row in set (0.00 sec)So, this is not an issue specific to PXC, but an issue with Community MySQL. I do not see any bugs on this. I suggest you open a bug report on https://bugs.mysql.com/

3

I got result when I convert the result in binary to  char.

4

Good that you got a result, but bad that you had to do that extra step which is not documented in the MySQL manual. You could still open a documentation bug on mysql’s website for Documentation category.

5

But I am running it on percona Xtradb cluster which handle by percona only?

6

As I said above, this issue is NOT related to PXC. The issue comes from upstream MySQL, produced by Oracle. I am getting the same inconsistent result on MySQL Community 8.0.21.
Percona is a rebase of Community. This means that we download the Community source code as a “starting point”, then we add on to that extra features and enhancements. Thus, if there is a bug within the “starting point”, it’s a bug in the core Community code, which is included in Percona’s code. When MySQL/Oracle Community gets fixed, that fix becomes the new “starting point” for the next version of PXC.