Solution:
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';
- 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)