[FIX] SQL Error (1227): Access denied; You Need the SUPER Privilege for this Operation in Amazon RDS

by
SQL Error (1227): Access denied; You Need the SUPER Privilege for this Operation in Amazon RDS
When you’re trying to execute a command related to privileges and you get an error like SQL Error (1227): Access denied; you need the SUPER privilege for this operation. Like in our case it was for SET GLOBAL group_concat_max_len = 10240; and the MYSQL served us with the above-said error. Such error comes because the privileges are limited in MYSQL as it is a managed environment.

[FIX] 1227 Access denied; You Need the SUPER Privilege for this Operation in Amazon RDS

In order to fix this issue, you’ll need to leverage DB parameter groups. To modify parameter values in a customer-created DB parameter group, you can’t change the parameter values in a default DB parameter group. Changes to parameters in a customer-created DB parameter group are applied to all DB instances that are associated with the DB parameter group. SQL Error (1227): Access denied; You Need the SUPER Privilege for this Operation in Amazon RDS
  • Changes to some parameters are applied to the DB instance immediately without a reboot. Changes to other parameters are applied only after the DB instance is rebooted.
  • The RDS console shows the status of the DB parameter group associated with a DB instance on the Configuration tab. For example, if the DB instance isn’t using the latest changes to its associated DB parameter group, the RDS console shows the DB parameter group with a status of pending-reboot.
  • To apply the latest parameter changes to that DB instance, manually reboot the DB instance.
To modify a DB parameter group, use the AWS CLI modify-db-parameter-group command with the following required parameters:
  • --db-parameter-group-name
  • --parameters
Like the following command modifies the max_connections and max_allowed_packet values in the DB parameter group named mydbparametergroup. For example, to run this command in: Windows 
aws rds modify-db-parameter-group ^
    --db-parameter-group-name mydbparametergroup ^
    --parameters "ParameterName=max_connections,ParameterValue=250,ApplyMethod=immediate" ^
                 "ParameterName=max_allowed_packet,ParameterValue=1024,ApplyMethod=immediate"
Linux, macOS, or UNIX 
aws rds modify-db-parameter-group \
    --db-parameter-group-name mydbparametergroup \
    --parameters "ParameterName=max_connections,ParameterValue=250,ApplyMethod=immediate" \
                 "ParameterName=max_allowed_packet,ParameterValue=1024,ApplyMethod=immediate"
The command outputs the result like the following: 
DBPARAMETERGROUP  mydbparametergroup
To fix the issue we were facing, we used the following given command as the option is a dynamic and should not require a reboot of the instance to apply, like static options: 
rds modify-db-parameter-group mydbparametergroup --parameters "name=group_concat_max_len,value=10240,method=immediate"

You can also modify a DB Parameter from the console via the following steps:

  • Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
  • In the navigation pane, choose Parameter groups.
  • In the list, choose the parameter group that you want to modify.
  • For Parameter group actions, choose Edit.
  • Change the values of the parameters that you want to modify. You can scroll through the parameters using the arrow keys at the top right of the dialog box.
  • You can’t change values in a default parameter group.
  • Choose Save changes.

Leave a Comment