Failing to restore dev database
Closed this issue · 7 comments
Whilst trying to restore / reset the database to https://github.com/unee-t/bz-database/blob/dev.unee-t.com/db%20snapshots/unee-t_BZDb_clean_current.sql
https://media.dev.unee-t.com/2019-02-01/super.mp4
[hendry@t480s db snapshots]$ mysql -h auroradb.dev.unee-t.com -P 3306 -u root --password=$PASSWORD bugzilla < unee-t_BZDb_clean_current.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 3516: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Just tried as the bugzilla user. Same outcome:
[hendry@t480s db snapshots]$ mysql -h auroradb.dev.unee-t.com -P 3306 -u $(ssm uneet-dev MYSQL_USER) --password=$(ssm uneet-dev MYSQL_PASSWORD) bugzilla < unee-t_BZDb_clean_current.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000) at line 3516: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
I'm not sure how the database users are setup, but neither root of the mysql user (bugzilla) seems to have all the privileges.
I seem unable to update the privs. Perhaps there is a new Aurora parameter I need to toggle...
log_bin_trust_function_creators is already true, so I am not sure where this Unknown column 'ERROR (RDS): SUPER PRIVILEGE CANNOT BE GRANTED OR MAINTAINED' in 'field list' comes from.
I have raised an AWS issue about this https://console.aws.amazon.com/support/cases#/5745178081/en
@franck-boullier not sure you saw the support response, but in the schema you asked me to restore there is a user unee_t_root being setup.
Proposed solution is to remove that user.
From Slack chat, @franck-boullier says the unee_t_root is in fact required for triggers amongst other things. However since I am not quite sure how to create the user, and schema restore doesn't seem to setup this user, I have opened #106 which this bug effectively depends on since we are not removing the user as the AWS solution suggested.