Background

During the usage of the database, you need to import data by batch regularly. In data import of MySQL database by batch, you may use
"LOAD DATA" command to realize the rapid import of massive data.

Failure phenomenon

In the use of the "LOAD DATA" command, you can see the following error information:

• When importing data by batch via "LOAD DATA LOCAL INFILE" command, this error information is shown：

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

• When importing data by batch via "LOAD DATA INFILE" command, the following shows this error information:

  ERROR 1045 (28000): Access denied for user 'xxxx'@'%' (using password: YES)

Analysis of cause

The first error results from the parameter setting:

• MySQL server parameter: "local_infile" is usable for controlling the MySQL Server: whether or not it allows import of data files from the client by using "LOAD DATA LOCAL INFILE" command.
• MySQL client parameter: "--local-infile" is usable for controlling the MySQL Client: whether or not it allows import of data files from the client by using "LOAD DATA LOCAL INFILE" command.

The second error results from mismatching between command and account privilege.:

• "LOAD DATA LOCAL INFILE" command may be used for import of data files from the client, and you can use an account with common write privilege.
• "LOAD DATA INFILE" command is usable for the import of data files from the server. You need to use an account with the "FILE" privilege. For the sake of security protection, recommend you not to lift the control over FILE privilege.

Solutions

• Solution for the first error: make sure that the parameter "--local_infile" is enabled when login to the MySQL client, and make sure that the parameter "local_infile" is enabled at the MySQL Server.
• Solution for the second error: add "LOCAL" keyword when using "LOAD DATA" command.

Case review

1. Create a test table

   CREATE TABLE tb_01 (
   id int(11) NOT NULL AUTO_INCREMENT,
   age int(11) NOT NULL DEFAULT '0',
   name varchar(64) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
   ) ENGINE=InnoDB;

2. Build test data

   Age	Name
   15	Zhangsan
   28	Lisi
   21	Wangwu
   11	Zhaoliu
   33	Dingyi

3. Test results

• Scenario 1: normal case

  Execute the following SQL command:

  LOAD DATA LOCAL INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n' (age,name);

  Output the result:

• Scenario 2: disable parameter "--local-infile" at MySQL client

  Log in to MySQL client:

  bin/mysql -h HOST -P PORT -u USER -p PASSWORD --local-infile=0

  Execute the following SQL command:

  LOAD DATA LOCAL INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (age,name);

  Output the result:

• Scenario 3: disable parameter "local-infile" at MySQL server

Execute the following SQL command:

LOAD DATA LOCAL INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (age,name);

Output the result:

• Scenario 4: remove "LOCAL" keyword

  Execute the following SQL command:

  LOAD DATA INFILE 'testdata.csv' INTO TABLE tb_01 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (age, name);

  Output the result:

Conclusions & suggestions

• In Baidu AI Cloud RDS, the "local_infile "parameter is enabled by default. If you need to limit the usage of the "LOAD DATA" command, you may disable this parameter on the【Parameter Settings 】page of the RDS console.
• When using "LOAD DATA" command, you need to add "LOCAL" keyword, avoiding error information in execution.