By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Can't use mysqlimport since MySQL 8 upgrade due to insufficient privileges

1

Since upgrading my RDS instance to MySQL 8 I am no longer able to use mysqlimport to import external data from .csv files. When running mysqlimport it returns the following 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

It is, however, impossible to assign any of these privileges. The RDS User Guide instructions for mysqlimport provide instructions for setting the SESSION_VARIABLES_ADMIN privilege for MySQL version 8.0.15 and higher by running the

GRANT SESSION_VARIABLES_ADMIN ON *.* TO *user*;

command, but this results in the following error:

ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation ... and RDS does not support the GRANT OPTION privilege.

  • Aarohi
    16 days ago

    Hi Jared, did you find a solution to this problem?

  • Jared
    16 days ago

    I did not find a solution to this, but was able to reformat the .csv files into .sql format and import them using the mysql command line. This was not at all optimal, but the only way I could figure out how to import flat data.

Topics
Database
Tags
MySQL
Language
English
Jared
asked 4 months ago343 views
1 Answer
-1

Hello.

Please use the following command to set the privilege "ROLE_ADMIN" to the database administrative user (such as the root user), and then try setting "SESSION_VARIABLES_ADMIN".

GRANT ROLE_ADMIN on *.* to root
profile picture
EXPERT
Riku_Kobayashi
answered 4 months ago
  • Jared
    4 months ago

    While this ROLE_ADMIN command succeeds when running it under the admin user, subsequently trying to set the SESSION_VARIABLES_ADMIN privilege results in the same error as above: "ERROR 1227 (42000): Access denied; you need (at least one of) the GRANT OPTION privilege(s) for this operation".

  • Riku_Kobayashi EXPERT
    4 months ago

    By the way, is the database user used by "mysqlimport" the correct database administrator user (root user, etc.)?

  • Jared
    4 months ago

    Yes, I've tried running these commands with both the admin user and a separate user both of which I've tried assigning the appropriate privileges without success. I even reset the admin user password in the RDS console - which resets its privileges to the default.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content