CREATE ROLE fails with duplicate-key error but leaves the role without a roles_mapping row

When mysql.roles_mapping has a three-column unique key on (Host, User, Admin_option) instead of (Host, User, Role), creating a second distinct role fails with a duplicate-key error. Despite reporting failure, MariaDB persists the second role in mysql.global_priv without its automatic mapping.

This occurs when an administrator reloads grant tables after replacing mysql.roles_mapping with a schema whose three-column unique key uses Admin_option instead of Role.

The following scripts require an account permitted to modify the mysql system tables and create roles. Run them in order as separate client invocations.

Save this as setup.sql:

DROP ROLE IF EXISTS role_a;

DROP ROLE IF EXISTS role_b;

RENAME TABLE mysql.roles_mapping

  TO mysql.roles_mapping_saved;

CREATE TABLE mysql.roles_mapping

  LIKE mysql.roles_mapping_saved;

ALTER TABLE mysql.roles_mapping

  DROP KEY Host,

  ADD UNIQUE KEY Host (Host, User, Admin_option);

INSERT INTO mysql.roles_mapping

  SELECT * FROM mysql.roles_mapping_saved;

FLUSH PRIVILEGES;

CREATE ROLE role_a;

SELECT Host, User, Role, Admin_option

FROM mysql.roles_mapping

WHERE Role IN ('role_a', 'role_b');

Save this as trigger.sql:

CREATE ROLE role_b;

Save this as verify.sql:

SELECT Host, User

FROM mysql.global_priv

WHERE User IN ('role_a', 'role_b')

ORDER BY User;

SELECT Host, User, Role, Admin_option

FROM mysql.roles_mapping

WHERE Role IN ('role_a', 'role_b')

ORDER BY Role;

Execute the files with the MariaDB client:

mariadb -uroot < setup.sql

mariadb -uroot < trigger.sql

mariadb -uroot < verify.sql

In the recorded MariaDB 12.3.3 execution, the second command returned:

ERROR 1062 (23000): Duplicate entry '%-root-Y' for key 'Host'

The subsequent queries showed both roles in mysql.global_priv, but only role_a in mysql.roles_mapping:

Host  User

      role_a

      role_b

Host  User  Role    Admin_option

%     root  role_a  Y

The two role names are distinct, so their creation must not collide merely because both mappings have the same host, definer and administration flag. If the grant-table layout is invalid for role identity, it should be rejected before mutation. A failed CREATE ROLE must not leave the role persisted without its corresponding roles_mapping row.

After collecting the result, restore the system table with:

DROP TABLE IF EXISTS mysql.roles_mapping;

RENAME TABLE mysql.roles_mapping_saved TO mysql.roles_mapping;

FLUSH PRIVILEGES;

DROP ROLE IF EXISTS role_a;

DROP ROLE IF EXISTS role_b;