MySQL - The password hash doesn't have the expected format

Question

I am using percona tollkit pt-show-grants command to replicate a user and privileges in another environment, the command return something like it

[root@mysqlen1 ~]# pt-show-grants --only my_user
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 8.0.33-commercial at 2023-07-06 13:32:06
-- Grants for 'my_user'@'%'
CREATE USER IF NOT EXISTS `my_user`@`%`;
ALTER USER `my_user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$E)\,\ZrJ%8K[y!MrHfAAAN44c/fazzdk49SINtIQYq6zPo67VJ0lBbLX2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROLE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, DROP ROLE, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO `my_user`@`%` WITH GRANT OPTION;
GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `my_user`@`%` WITH GRANT OPTION;

But executing the alter user return the following error:

mysql> ALTER USER `my_user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$E)\,\ZrJ%8K[y!MrHfAAAN44c/fazzdk49SINtIQYq6zPo67VJ0lBbLX2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
ERROR 1827 (HY000): The password hash doesn't have the expected format.

Why? I belive there is nothing wrong with percona tollkit pt-show-grants command, because it basically execute the SHOW CREATE USER command, and it's the same result:

mysql> SHOW CREATE USER 'my_user'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for my_user@%                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `my_user`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$E)\,\ZrJ%8K[y!MrHfAAAN44c/fazzdk49SINtIQYq6zPo67VJ0lBbLX2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 1

This may be due to binary symbols in the hash, which may not be displayed/handled correctly in your terminal application. This can also be affected by the character set of your current connection.

Have you tried:

SET @@SESSION.print_identified_with_as_hex = 1;

From the MySQL docs:

Password hash values displayed in the IDENTIFIED WITH clause of output from SHOW CREATE USER may contain unprintable characters that have adverse effects on terminal displays and in other environments. Enabling print_identified_with_as_hex causes SHOW CREATE USER to display such hash values as hexadecimal strings rather than as regular string literals. Hash values that do not contain unprintable characters still display as regular string literals, even with this variable enabled.

For example (using Windows Command Prompt with my default character_set_client cp850), if we create a user:

mysql> CREATE USER `my_user`@`localhost` IDENTIFIED WITH 'caching_sha2_password' BY 'my strong password';
Query OK, 0 rows affected (0.01 sec)

And then (cropped output as we are only interested in the password hash):

mysql> SHOW CREATE USER 'my_user'@'localhost';
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for my_user@localhost                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `my_user`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$lL`\'?5xT.%↓cJ.¶.PQbn/IHRX12PeN9qrO23RAla71qmV28pEeeztcgJ.Cvgul.' |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now, try to create another user (my_user2) from the above output:

mysql> CREATE USER `my_user2`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$lL`\'?5xT.%↓cJ.¶.PQbn/IHRX12PeN9qrO23RAla71qmV28pEeeztcgJ.Cvgul.';
ERROR 1396 (HY000): Operation CREATE USER failed for 'my_user2'@'localhost'

Set print_identified_with_as_hex = 1:

mysql> SET @@SESSION.print_identified_with_as_hex = 1;
Query OK, 0 rows affected (0.00 sec)

Now try again to create a new user with output from SHOW CREATE USER ...:

mysql> SHOW CREATE USER 'my_user'@'localhost';
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for my_user@localhost                                                                                                                                                                                           |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `my_user`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035246C4C60273F3578542E2519634A2E142E5051626E2F49485258313250654E3971724F323352416C613731716D563238704565657A7463674A2E437667756C2E |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> CREATE USER `my_user2`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035246C4C60273F3578542E2519634A2E142E5051626E2F49485258313250654E3971724F323352416C613731716D563238704565657A7463674A2E437667756C2E;
Query OK, 0 rows affected (0.00 sec)

Note: Changing the character set for my connection also worked, without needing to change print_identified_with_as_hex.

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)