• Register
0 votes
139 views

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.

7 5 2
3,870 points

2 Answers

0 votes

Solution :

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;
9 7 4
38,600 points
0 votes

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';
  1. 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)

 

10 6 4
31,120 points

Related questions

0 votes
1 answer 68 views
68 views
Problem : I am very new to the Linux but want to set up my MySQL databases on the Amazon ec2 instance. I have followed some directions I found about resetting a user login pass by using a --skip-grant-tables option of the MySQL. Now I want to add my user ... HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement How can I turn above option off?
asked Jan 30 jwilliam 3.9k points
0 votes
1 answer 251 views
251 views
Problem : I am facing a strange MySQL error may be it is related to the database's read-only flag. The Web application that is using the MySQL is running on Debian 7.9. It was running well but suddenly started attempting to access the application powered website ... - SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement
asked Nov 20, 2019 peterlaw 6.9k points
0 votes
1 answer 137 views
137 views
Problem : I get mysql error help the mysql server is running with the --secure-file-priv option so it cannot execute this statement
asked Nov 7, 2019 peterlaw 6.9k points
0 votes
1 answer 49 views
49 views
Problem: I am interested in learning MySQL. Currently I am facing the following error if I tried to add the index to my table using the MySQL Workbench: “Error Code: 2013. Lost connection to MySQL server during query.” I also noticed that above error appears only when I try to run long query. I want to know the away to increase my timeout value?
asked Jul 27 Raphael Pacheco 4.9k points
0 votes
1 answer 2.1K views
2.1K views
Problem : Following is my issue: I tried to install mysqlclient like below : C:\Users\alecxe>pip install mysqlclient Collecting mysqlclient Using cached https://files.pythonhosted.org/packages/ec/fd/83329b9d3e14f7344d1 cb31f128e6dbba70c5975c9e57896815dbb1988ad/mysqlclient-1.3.13.tar.gz Installed ... beginner to this and would be very happy if any input as to how to go about fixing above error.
asked Dec 27, 2019 alecxe 7.5k points