Error 1148 (42000): the used command is not allowed with this mysql version

Question

Problem :

I am trying to load my data into mysql database using below code

LOAD DATA LOCAL
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';

A topic of my question is related to response I get. I understand a local data offloading is off by default and I have to enable it using following command local-infile=1 but I do not know how to execute this command.

Answer 1

You can always specify that as the additional option when you are setting up your client connection as shown below :

mysql -u myuser -p --local-infile somedatabase

This is because that feature opens the security hole. So you have to enable it in the explicit manner in case you really need to use it.

Both the client and server should enable a local-file option. Otherwise it does not work for you. To enable it for the files on a server side server needs to add following to your my.cnf configuration file:

loose-local-infile = 1

OR

This is because a server variable local_infile is set to the FALSE|0. Refer from a document.

You can verify by executing below command:

SHOW VARIABLES LIKE 'local_infile';

If you have the SUPER privilege you can enable it (without restarting the server with the new configuration) by executing below command:

SET GLOBAL local_infile = 1;

Answer 2

You can seclude that as an additional option at the time setting up your client connection:

mysql -u myuser -p --local-infile somedatabase

This is since that feature opens a security hole. Hence you have to enable it in an explicit manner in case you truly want to use it.

Both client and server must enable the local-file option. Else it doesn't perform.To enable it for files on the server side server include following to the my.cnf configuration file:

loose-local-infile = 1

It's since the server variable local_infile is set to FALSE|0. Refer from the document.

You can confirm by executing:

SHOW VARIABLES LIKE 'local_infile';

In case you have SUPER privilege you can enable it (without restarting server with a fresh configuration) by executing:

SET GLOBAL local_infile = 1;

Place this in my.cnf - the [client] section must menwhile be there (in case you're not too worried about security).

[client]
loose-local-infile=1

I removed the LOCAL and obtain this command:

LOAD DATA
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';

However then I obtain:

MySQL said: File 'A.txt' not found (Errcode: 13 - Permission denied)

mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;

ERROR 1148 (42000): The employed command is not approved with this MySQL version

1) Inspect for local_infile parameter

mysql> SHOW VARIABLES LIKE 'local_infile';

2. Log out from client and re-login employing below parameter.

mysql -u root -p -h rdsendpoint --local-infile=1

3) Run the similar command

mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;


Query OK, 300 rows affected (0.01 sec)

Records: 300  Deleted: 0  Skipped: 0  Warnings: 0

only little addition.

Figured out another reincarnation in mysql.connector ver 8.0.16 It presently needs allow_local_infile=True or you will view the above error. performed in earlier versions.

conn = mysql.connector.connect(host=host, user=user, passwd=passwd, database=database, allow_local_infile=True)