Description:
When we modify the role_edges or default_roles tables manually with any incorrect entries and execute flush operation, then we will come to know about the wrong authentication ID as we get error "ERROR 1105 - (HY000): Unknown authorization identifier `user`@`host`".
>> In case if we modify the role_edges or default_roles tables manually with any incorrect entries and restart the mysqld instance then it will fail to start with error MY-010952.
In here the only way to resolve the issue is - start mysqld with --skip-grant-tables and delete/update incorrect records in role_edges/default_roles table and restart without skipping grants.
How to repeat:
Below are the steps to repo this issue:
>> Connect to RDS MySQL using root user and execute below commands. The "flush" command fails with error
create user vidya@'44.230.189.130' identified by 'vidya123';
create user vidyaselectall@'44.230.189.130' identified by 'vidyaselectall123';
grant select on *.* to vidyaselectall@'44.230.189.130';
grant select, insert, create, drop on *.* to vidya@'44.230.189.130';
insert into mysql.default_roles values('%','vidya','44.230.189.130','vidyaselectall');
insert into mysql.role_edges values('44.230.189.130','vidyaselectall','%','vidya','N');
select * from mysql.role_edges;
select * from mysql.default_roles;
flush privileges;
>> When we try to restart mysqld, it fails with below error.
[ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual.
Suggested fix:
I completely understand that this is not intended way to modify the system tables directly and we should use account management statements like SET DEFAULT ROLE, ALTER USER, etc. However, in cases like these rather than blocking the mysqld from starting, it would be nice - if we can ignore incorrect records and write warning to mysql error log.
Description: When we modify the role_edges or default_roles tables manually with any incorrect entries and execute flush operation, then we will come to know about the wrong authentication ID as we get error "ERROR 1105 - (HY000): Unknown authorization identifier `user`@`host`". >> In case if we modify the role_edges or default_roles tables manually with any incorrect entries and restart the mysqld instance then it will fail to start with error MY-010952. In here the only way to resolve the issue is - start mysqld with --skip-grant-tables and delete/update incorrect records in role_edges/default_roles table and restart without skipping grants. How to repeat: Below are the steps to repo this issue: >> Connect to RDS MySQL using root user and execute below commands. The "flush" command fails with error create user vidya@'44.230.189.130' identified by 'vidya123'; create user vidyaselectall@'44.230.189.130' identified by 'vidyaselectall123'; grant select on *.* to vidyaselectall@'44.230.189.130'; grant select, insert, create, drop on *.* to vidya@'44.230.189.130'; insert into mysql.default_roles values('%','vidya','44.230.189.130','vidyaselectall'); insert into mysql.role_edges values('44.230.189.130','vidyaselectall','%','vidya','N'); select * from mysql.role_edges; select * from mysql.default_roles; flush privileges; >> When we try to restart mysqld, it fails with below error. [ERROR] [MY-010952] [Server] The privilege system failed to initialize correctly. For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual. Suggested fix: I completely understand that this is not intended way to modify the system tables directly and we should use account management statements like SET DEFAULT ROLE, ALTER USER, etc. However, in cases like these rather than blocking the mysqld from starting, it would be nice - if we can ignore incorrect records and write warning to mysql error log.