Menu
We are using MySQL 8.0.* and .csv file for the importing data into Amazon RDS. We are executing this command from the app server command line.
Error:
mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
Command:
mysqlimport --local --compress --columns='col1,col2,col3,col4' -h dbhost -u dbusername -pdbpassword dbname --fields-terminated-by='|' file_path/table_name.csv
We have already provided DBA permission to DB user.
965
What can be done to resolve Error 1227? Restore the database as a user with super privilege. Restore the database as the definer user if possible. Edit the backup file by either removing the DEFINER= statement from the backup file, or replace the definer values with CURRENT_USER.
Log in to the Amazon RDS dashboard. Select the “Instances” menu item and then select the MariaDB or Aurora RDS instance you wish to modify. From the “Instance Actions” menu, click the “Modify” option. On the resulting page, enter a new password in the “Settings -> New Password” field.
AWS has introduced IAM authentication for RDS with SQL and PSQL. This method allows you to connect to the DB with a authentication token generated with the help of your IAM policy attached to a role or user.
As error suggests, the user you are running import command not having permissions SESSION_VARIABLES_ADMIN.
You could setup it like below.
GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'user'@'%';
OR
GRANT SESSION_VARIABLES_ADMIN ON *.* TO 'user'@'specific-host';
It should resolve the issue.
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
