search

unee-t / bz-database

Scripts and schema for the bz database so we can build the BZ FE
GNU Affero General Public License v3.0
0 stars 2 forks source link

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

Open kaihendry opened 5 years ago

kaihendry commented 5 years ago

Need the ability to restore the SQL from a backup in the DEV environment.

Could be related https://github.com/unee-t/bz-database/issues/127

When using https://github.com/unee-t/bugzilla-customisation/blob/master/backup/restore.sh

I'm seemingly unable to restore the SQL as root!?

kaihendry commented 5 years ago

Hitting this with https://media.dev.unee-t.com/2019-09-13/demo-baseline-2019-09-13.sql

[hendry@t480s backup]$ ./restore.sh -d demo-baseline-2019-09-13.sql
DEMO
Restoring demo-baseline-2019-09-13.sql to bugzilla-cluster-1.cluster-c2waklyujftr.ap-southeast-1.rds.amazonaws.com
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 447: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
kaihendry commented 5 years ago

From https://console.aws.amazon.com/support/home#/case/?displayId=5745178081&language=en

I understand that you already tried to make the user with super user privileges but the restoration of dump file still fails with the below error.

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

I reviewed the dump file provided by you and I see that this dump file is different from what was used earlier. This dump file fails with the above error at line '7770'. On checking the file, I found out that this line has a DEFINER with the user "bugzilla".

I would like to inform you that DEFINER means the procedure or the trigger can be only defined with the specified user. After going through the dump file, I can see that there are triggers that has DEFINER as "unee_t_root" and as well as "bugzilla". When the dump file has multiple triggers with different definers, Super User Privileges is required to restore such a dump file. But as this is not possible in RDS, all the triggers in a single dump file must have the same user as a definer, and the same user must be used while restoration.

In other words, since you have used the user "unee_t_root" to restore, the dump restoration is successful until it encountered a trigger with a different definer than "unee_t_root" (in this case "bugzilla").