2

I am using the following lines of code:

LOAD DATA LOCAL INFILE 'CA_DRU_proj_2010-2060.csv'
INTO TABLE pop_proj
FIELDS TERMINATED BY ' , '
ENCLOSED BY ' " '
LINES TERMINATED BY ' \n '
IGNORE 1 LINES;

The error I am receiving is:

Error Code: 1148. The used command is not allowed with this MySQL version

My version of mysql is version 8.0.16

Improve this question
4
  • Just out of curiosity, how did you check that the MySQL version is 8.0.16? I know I have had issues in the past where the version being run on my server is different than the one on my local and had a miscommunication between environments when running a query.. Commented Dec 28, 2020 at 21:46
  • 1
    @AaronMorefield I just clicked the "MySQL Workbench" in the upper toolbar. Then I clicked on "About MySQL Workbench" and it displayed my version in a pop-up. Commented Dec 28, 2020 at 21:56
  • That tells you the version of MySQL Workbench, not the version of the server. Try running the SQL query: SELECT @@version; - that will tell you the version of the server. Commented Dec 28, 2020 at 23:34
  • (1) What is the reason to use LOAD DATA LOCAL INFILE when the file is local already? (2) You do not specify the directory where the file is posessed in - how MySQL must determine its location? (*) Remove LOCAL, put the file into @@secure_file_priv directory, and specify complete pathname in the statement. Commented Dec 29, 2020 at 5:16

1 Answer 1

Reset to default
3

You need both the server and the client to enable local-infile import.

The server needs to enable local_infile=1. You can put this in your my.cnf options file, or you can enable it dynamically on a running server with SET GLOBAL local_infile=1;

You also need to tell the client to allow it. Do this by starting the client:

mysql --local-infile=1 ...

Since MySQL 8.0.19, the error message is more clear:

ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side

See also:

Update: If you use MySQL Workbench instead of the mysql command line client, you can enable local-infile in the options on the Advanced tab of your connection profile. See highlighted line below:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I ran the line you had suggested above SET GLOBAL local_infile=1; And I while it run smoothly- when I went back to run my initial line of code; I received the same error as earlier.
Did you understand the part where I mentioned that both client and server must have options set to enable local infile? These options are set separately. Unless you do both, it's not enough.
Ah I see. I was able to enable it dynamically for the server, but I am still unsure on how to enable for the client. Thank you for all of your continued help.
I don't know which client you're using.
How can I check to see which client I am using? I apologize for the confusion, this is my first time learning SQL.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.