• Register
0 votes
216 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

Please log in or register to answer this question.

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 3 views
3 views
Problem: ERROR 1148: The used command is not allowed with this MySQL version Thanks
asked Mar 20 ummesalma 29.2k points
0 votes
1 answer 9 views
9 views
Problem: Tried mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION; Getting ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ... Getting ERROR 1410 (42000): You are not allowed to create a user with GRANT MySQL (8.0.11.0) username/password is root/root.
asked 5 days ago anika11 32.2k points
0 votes
1 answer 66 views
66 views
Problem: cannot create a json value from a string with character set 'binary'.
asked Feb 15 Ethan ross 2.7k points
1 vote
1 answer 59 views
59 views
Problem: I am getting the following error: Error 1064 (42000): you have an error in your sql syntax;
asked Apr 29 PkGuy 23.5k points
1 vote
1 answer 23 views
23 views
Problem: How to fix this error. Sqlstate[42000]: syntax error or access violation: 1064 you have an error in your sql syntax.
asked Apr 18 PkGuy 23.5k points
0 votes
1 answer 3 views
3 views
Problem: As I mentioned in my previous query, I'm having difficulties with my learning; I've tried searching the web but haven't been able to find adequate information; could you assist me in continuing my project?
asked Apr 1 rakib1 51.5k points
0 votes
1 answer 8 views
8 views
Problem: What am I purported to do with this problem? >Check the manual that corresponds to your mysql server version.
asked 1 day ago Ashok Dileep 2.5k points
0 votes
1 answer 4 views
4 views
Problem: How to convert a text field to a date/time field in Access 2010?
asked Apr 18 Humaira ahmed 50.7k points
0 votes
1 answer 8 views
8 views
Problem: For the following SQL query: SELECT COUNT (distinct first_name) from actor; I receive the following error message: "SELECT" is not valid at this position for this server version, expecting: '(', WITH I am a total newbie at SQL. How do I resolve this error? I put the exact same line at another PC with the exact same schema and it worked fine.
asked Apr 26 munim01 21k points
1 vote
1 answer 9 views
9 views
Problem: Can somebody help me with this “Cara mengatasi host is not allowed to connect to this mysql server”?
asked May 1 Raj Tyagi 14.9k points