Bug #83657 MySqlSimpleRoleProvider.AddUsersToRoles violates FK constraint and fails
Submitted: 2 Nov 2016 19:28 Modified: 16 Apr 2020 21:32
Reporter: Stein Setvik (OCA) Email Updates:
Status: Closed Impact on me:
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.9 OS:Windows
Assigned to: CPU Architecture:Any

[2 Nov 2016 19:28] Stein Setvik
MySqlSimpleRoleProvider.AddUsersToRoles results in a FK constraint violation and fails to execute when called due to a bug (typo) in MySqlSimpleRoleProvider.GetRoleId.

How to repeat:
1. Call MySqlSimpleRoleProvider.AddUsersToRoles with valid string[] usernames and string[] roleNames arguments.

2. BUG: Cannot add or update a child row: a foreign key constraint fails (`[db name]`.`webpages_usersinroles`, CONSTRAINT `fk_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `webpages_roles` (`RoleId`) ON DELETE NO ACTION ON UPDATE NO ACTION)

Suggested fix:
On line 495 in SimpleRoleProvider.cs (GetRoleId function), change:
    return (int)role[0];
    return (int)roleid[0];

Above fix included in attached patch.
[2 Nov 2016 19:29] Stein Setvik
Patch for MySqlSimpleRoleProvider.GetRoleId bug

Attachment: mysql-connector-net-simpleroleprovider-getroleid.patch (application/octet-stream, text), 537 bytes.

[2 Nov 2016 19:37] Stein Setvik
Bug also affects 7.x. Fix is the same
[4 Nov 2016 6:10] Chiranjeevi Battula
Hello Stein Setvik,

Thank you for the report and contribution.
In order to submit contributions you must first sign the Oracle Contribution Agreement (OCA). For additional information please check http://www.oracle.com/technetwork/community/oca-486395.html.
If you have any questions, please contact the MySQL community team.

[4 Nov 2016 23:09] Stein Setvik
I completed the application and e-mailed it in as instructed about 30 minutes before submitting the bug report. Do you know how long application review/acceptance takes?
[16 Apr 2020 21:32] Christine Cole
Fixed as of the upcoming MySQL Connector/NET 8.0.21 release, and here's the proposed changelog entry from the documentation team:

A valid call made to the MySqlSimpleRoleProvider.AddUsersToRoles method
failed to execute because it violated the foreign key constraint. This fix
removes an error from the code that gets the role ID. Thanks to Stein
Setvik for the patch.

Thank you for the bug report.