Cannot enable PI on MySQL Aurora v3 (4378740)

When running MySQL on an AWS RDS Aurora Engine at 3.0 or higher release, when the user tries to enable PI an error to the following is encountered in the FMS log file.

com.quest.qsi.fason.mysql.pi.plugins.installer.callback.MySqlValidateConnection - SQLException - Access denied for user 'foglight'@'%' to database 'mysql'
java.sql.SQLSyntaxErrorException: Access denied for user 'foglight'@'%' to database 'mysql'

A "Failed to validate connection to MySQL database" popup also appears with a message like "Access denied for user 'USERNAME'@'%' to database 'mysql'"

Aurora MySQL Version Mapping

In Aurora version 3 and higher the mysql database is read-only. The "create table" used by the prerequisites script cannot be done even by a superuser because creating temporary tables is not allowed in mysql database. This causes the prerequisite script to fail when the database agent connects to the mysql database and it is unrelated to the permissions which the user actually has. 

Knowledgebase article 4309267 provides details on the prerequisites script which is run by the MySQL PI installer on each monitored host during the PI enabling process. 

WORKAROUND

Edit the properties for the MySQL agent and change the database name from MySQL to a different database name.

1. Navigate to Administration | Agents | Agent Status
2. Select the checkbox for the MySQL agent
3. Click the Edit button to edit the agent properties
4. Click the link to Modify the private properties for the MySQL agent
5. Change the database name to something other than mysql. This could be an existing user database or a new user database that has only added for PI. Since this user database would be empty (without tables and data) it would be used by the agent as a means to add the PI functionality, 
6. Save the changes
7. Then return to the SQL PI administration agent and enable the PI extension for the MySQL agent again

Note: This script to check prerequisites is only run when the PI extension is enabled for the MySQL agent. After PI has been added, the user can return to agent status and change the Database Name back to the original "mysql" value.

STATUS

This issue was logged as defect FOG-9587 and has been fixed in Foglight Cloud and the 8.0.0.10 and higher releases of the MySQL cartridge.