Error 'LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.' when using the S3ToMySqlOperator Apache Airflow operator

Question

I am trying to use the Apache Airflow S3ToMySqlOperator operator (documentation) to insert contents of a certain S3 file to a AWS RDS SQL database. The code I use for this looks like this:

s3_most_recent_file_to_sql = S3ToMySqlOperator(
   s3_source_key='s3://housing-project-airflow/house-links/filtered-links-most-recent/filtered_most_recent.csv',
   mysql_table='housing_project_db.city_info',
   mysql_duplicate_key_handling='REPLACE',
   task_id='s3_most_recent_file_to_sql',
   aws_conn_id='aws_s3_connection',
   mysql_conn_id='sql_conn_id',
   dag=funda_scraper_dag)

The 'mysql_conn_id' has as the 'login' in the connection the AWS RDS admin account. Now when running this in a DAG, I get the following error code:

MySQLdb._exceptions.OperationalError: (2068, 'LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.')

Now my guess is that the admin account doesn't have the permissions to load any files into the given database/table. This is confirmed when I check the admin user privileges in DBeaver; the 'file' privilege is not selected.

So now I'm trying to add this FILE privilege for the admin account, but I can't get it done.

What I've tried is to connect to the RDS SQL server with: mysql -h housing-project-db.ckvznkgqispo.eu-west-2.rds.amazonaws.com -P 3306 -u admin -p, after which I run the command:

GRANT FILE ON mysql.* TO 'admin'@'housing-project-db.ckvznkgqispo.eu-west-2.rds.amazonaws.com' IDENTIFIED BY 'mysecretpassword’;, after which I get

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'mysecretpassword’' at line 1

When I search for others who've had this problem they offer this as the solution, so I don't get why I'm getting a syntax error at all... Am using MySQL server version 8.0.

Answer 1

it's a bit late for you, but others may try to enable allow_local_infile . please check Airflow documentation here. Basically, you add allow_local_infile parameter to Extras section in you MySQL Airflow connection details.