Account transfer or backup restore fail to create MySQL database users: 1410 (You are not allowed to create a user with GRANT)

Symptoms

• After an account transfer using WHM Transfer Tools, MySQL database users are missing on the destination server. The WHM transfer log contains:

  CPANEL_ERROR: Error from MySQL query :(XID xxxxxxxx) The system received an error from the “MySQL” database “mysql”: 1410 (You are not allowed to create a user with GRANT)

• After a JetBackup account restore, MySQL database users are not restored. The JetBackup restore log contains:

  CONFIG_TEXT: [ERROR] Failed making API Call to cPanel/WHM (uapi). Error: The request failed. (Error ID: xxxxxxxx) Ask your hosting provider to research this error in cPanel & WHM’s main error log.

  The cPanel main error log at /usr/local/cpanel/logs/error_log contains:

  CPANEL_ERROR: Cpanel::Exception::Database::Error/(XID xxxxxxxx) The system received an error from the “MySQL” database “mysql”: 1410 (You are not allowed to create a user with GRANT)

Cause

One or more MySQL database users on the source server are using the caching_sha2_password authentication plugin, which cPanel & WHM does not support. When the transfer or restore attempts to recreate these users, MySQL rejects the operation with error 1410. This is a known cPanel & WHM bug tracked as CPANEL-46219.

Resolution

1. Log into the server via SSH.

2. Check which MySQL users are using the unsupported caching_sha2_password plugin:

   # mysql -e "SELECT User, Host, plugin FROM mysql.user WHERE plugin = 'caching_sha2_password';"

3. Back up the MySQL configuration file:

   # cp -a /etc/my.cnf /etc/my.cnf.bak

4. Open /etc/my.cnf in a text editor and add the following line under the [mysqld] section:

   CONFIG_TEXT: default-authentication-plugin=mysql_native_password

   Note: On MySQL 8.4 and later, default-authentication-plugin is not supported. Use mysql_native_password=ON under [mysqld] instead. See MySQL log warning — ‘mysql_native_password’ is deprecated for details.

5. Restart MySQL to apply the change:

   # /scripts/restartsrv_mysql

6. For each user returned in step 2, reset the password to switch the authentication plugin to mysql_native_password:

   Note: Replace username, hostname, and newpassword with the actual values for each affected user.

   # mysql -e "ALTER USER 'username'@'hostname' IDENTIFIED WITH mysql_native_password BY 'newpassword'; FLUSH PRIVILEGES;"

7. Retry the account transfer or JetBackup restore.