ERROR 1045 (28000) on AWS RDS mysql LOAD DATA INFILE

I'm using MySQL 8.0.16 on AWS RDS. When I run SQL command:

mysql> LOAD DATA INFILE 't1.csv'  INTO TABLE t1 FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)

Running mysqlimport from Shell:

$ mysqlimport --local --compress  -u admin -pXXXXXXX -h HOST.rds.amazonaws.com DB --verbose --lines-terminated-by="\n" --fields-terminated-by=, --fields-enclosed-by='"' t1.csv
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
Connecting to HOST.rds.amazonaws.com
Selecting database empresas
Loading data from LOCAL file: t1.csv into t1
empresas.cnaes: Records: 1209  Deleted: 0  Skipped: 0  Warnings: 1
Disconnecting from HOST.rds.amazonaws.com empresas

$ mysql -u admin -pXXXXXX-h HOST.rds.amazonaws.com DB -e "select count(*) from t1"   
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+
| count(*) |
+----------+
|        0 |
+----------+

I'd tried:

parameter ‘log_bin_trust_function_creators’ = 1
Change master password

ref: https://forums.aws.amazon.com/message.jspa?messageID=902265

Summary:

I had Error 1045 when I'd try to import data from mysql prompt, and I had no error when I'd tried to import with mysqlimport utility, but mysqlimport doesn't load no data.

Someone knows wha's happening?

Thank you

Solution

tl;dr: use this:

mysql [connect-options] --local-infile --execute "LOAD DATA LOCAL INFILE 'foo.csv' ...;"

LOAD DATA INFILE is used to load data from a file that is located on the MySQL server, while LOAD DATA LOCAL INFILE is used to load data from a file located on the client machine (where the mysql CLI) is running.

Because RDS is a managed service that gives you no access to the server where the service is running, LOAD DATA INFILE isn't possible, so the LOCAL variant is needed.

An important detail about LOAD DATA LOCAL INFILE is that all of the work is still done by the server, not the client, and -- behind the scenes -- it actually works exactly the same way LOAD DATA INFILE works, by processing a file that's actually located on the server... but what is different is how that file came to find itself on the server... that file is a temporary file that the client streamed to the server for this command to use. (This means for loading large files with LOCAL, sufficient disk space is still required on the server for both the original file and the tablespace storage of the generated rows.)

Fundamentally, the mysql CLI is a program that establishes a socket-based connection to the server process and provides a shell for typing (or piping) SQL statements, sending them individually to the server for execution, and unpacking any returned results... so LOAD DATA LOCAL INFILE -- a SQL statement -- requires a curious interplay of client and server that does not work the way intuition might suggest. It actually works something like this:

(console) mysql> LOAD DATA LOCAL INFILE 'foo.csv' ...;
(socket) (client) "Hey, server, run this query: LOAD DATA LOCAL INFILE 'foo.csv' ...
(socket) (server) "Okay, client, I parsed that query without problems, so now I need you to start streaming me your local file 'foo.csv'.
(socket) (client) "Okay, server, here is that raw file..."

So... yikes, the server is asking the client to stream a file, whose name is specified by the server. Clearly, there are multiple possible exploits here if the server code is malicious or the client is executing untrusted queries.

This is why the mysql CLI has the --local-infile option. Without this option, the client code won't hand over the file requested by the server, the server discovers this, and returns an error.

According to the docs, before MySQL Server 8.0, it's the generic error you encountered:

ERROR 1148 (42000): The used command is not allowed with this MySQL version.

Starting in Server 8.0, it should be more specific:

ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side

It isn't clear why RDS for MySQL 8.0 is returning the old error.