要約

When importing data to an Azure/Amazon RDS for MySQL DB instance using mysqldump, you may receive an error similar to the following:

ERROR 1227 (42000) at line xxxxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

環境

• Confluence 7.11.0+
• Amazon/Azure MySQL
  
  

診断

• Make sure log_bin_trust_function_creators value has been set correctly on Azure and AWS per this KB
• Make sure you have followed this KB to dump the MySQL database if your confluence version is 7.11+

• Checking the DB dump at line xxxxxx 

  sed -n 'xxxxxx,+10p' DB_dump_name

  If you see the output lines similar to the following, this KB applies to you: 

  /!50003 CREATE/ /!50017 DEFINER=`confluence`@`localhost`/ /*!50003 TRIGGER denormalised_content_trigger_on_insert
  AFTER INSERT
  ON CONTENT FOR EACH ROW
  sp: BEGIN
  DECLARE isServiceDisabled BOOL DEFAULT TRUE;
  CALL content_procedure_for_denormalised_permissions(isServiceDisabled);
  IF (isServiceDisabled) THEN
  LEAVE sp;
  END IF;

原因

Definer errors are triggered when MySQL attempts to create an object under a database user, and that database user doesn't exist on the destination database. This usually happened when you are trying to migrate the on-perms MySQL database to Azure/Amazon RDS. When MySQL attempts to create a user for localhost, which is not permitted for Azure/Amazon RDS. This is because Amazon/Azure RDS doesn't have superuser privileges.

ソリューション

Rename the definer users to the current user and host:

sed -i -e 's/DEFINER=`confluence`@`localhost`/DEFINER=`confluenceazure`@`%`/g' dump.sql

You can remote access Azure/Amazon RDS to verify the local DB admin account: 

show grants;