What is wrong in the command?

Question

ERROR 1148 (42000): The used command is not allowed with this MySQL version.
The above error appears when the load data query execute. I was unable to detect what is wrong in the query.
Please help. Thanks.

Create table SQL

DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
    field1  char(8) NOT NULL,
    field2  varchar(10) NOT NULL,
    field3  varchar(4) NOT NULL,
    field4  varchar(100),
    field5  text,
    field6  char(8),
    field7  char(1)
) CHARACTER SET UTF8MB4;

Load table sql

load data local infile 'E:/insert/table1.RRF' into table table1 fields terminated by '|' lines terminated by '\r\n'
(@field1,@field2,@field2,@field4,@field5,@field6,@field7)
SET field1 = @field1,
field2 = @field2,
field2 = @field2,
field4 = NULLIF(@field4,''),
field5 = NULLIF(@field5,''),
field6 = NULLIF(@field6,''),
field7 = NULLIF(@field7,'');

Edit 1: Variable of local_infile in MySQL is set to ON

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

Does this answer your question? [ERROR 1148: The used command is not allowed with this MySQL version](https://stackoverflow.com/questions/18437689/error-1148-the-used-command-is-not-allowed-with-this-mysql-version) — Kanad, Mar 06 '20 at 05:25

score 0 · Accepted Answer · answered Mar 06 '20 at 04:59

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

In general this means that secure_file_priv server setting is disabled (set to NULL) or set to some definite directory (not empty string).

If it is disabled then LOAD DATA INFILE is not available until this setting enabling (which needs server restart).

If it is set to some directory then loaded file must be posessed in this directory strongly.

load data local infile 'E:/insert/table1.RRF'

If E: is local device/volume then LOCAL makes no sense.

LOCAL keyword means "copy remote file to local temporary folder before loading" - so it makes sense only for remote files.

It is applicable only if secure_file_priv is enabled and set to empty string.

Thanks for the clarification! Adding `[mysqld] secure-file-priv = "" ` in to my.ini will set the secure_file_priv to disable and the load data works. Also, remove local from the queries. — Zi Xian Tan, Mar 06 '20 at 05:33

What is wrong in the command?

1 Answers1