Bug #91891 Workbench 8.0.12 no longer allows LOAD DATA LOCAL INFILE
Submitted: 3 Aug 2018 14:02 Modified: 11 Nov 2018 2:55
Reporter: David Webb Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S2 (Serious)
Version: OS:Microsoft Windows (10 Pro)
Assigned to: CPU Architecture:Any

[3 Aug 2018 14:02] David Webb
Description:
I upgraded from Workbench 6.3.10 to 8.0.12 without making any change to the server, which is MySQL 8.0.11. I can no longer run a LOAD DATA LOCAL INFILE command via Workbench. It returns the following error:

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

I checked my system variables, and it still shows:
LOCAL_INFILE=ON
So that is not the problem. It should allow the command.

Next, I upgraded the server from 8.0.11 to 8.0.12, but this did not make any difference. My temporary workaround is to put the file on the server and then run Workbench on the server with "LOAD DATA INFILE" (without the "LOCAL" option).

How to repeat:
See steps in the description.
[4 Aug 2018 13:48] Akash Popat
Also occurs on Mac OS High Sierra. Doing "mysql --local-infile tableName" works as it should. Doesn't work only in work bench.
[5 Aug 2018 20:28] MySQL Verification Team
If you run the below command on WorkBench SQL Editor which result you get?

show variables like '%INFILE%';
[6 Aug 2018 10:13] Akash Popat
@Miguel It shows 
local_infile	ON

What is should show.
[6 Aug 2018 12:38] Chiranjeevi Battula
Hello Akash Popat,

Thank you for the bug report.
This is most likely duplicate of Bug #72220, please see Bug #72220

Thanks,
Chiranjeevi.
[6 Aug 2018 12:47] Akash Popat
Hi Chiranjeevi Battula

It is not a duplicate of that because this issue is being faced only after upgrading MySQL to version 8.0.12 and not earlier versions.
[6 Aug 2018 14:08] Chiranjeevi Battula
Hello Akash Popat,

Thank you for the feedback.
Verified this behavior on MySQL Workbench in 8.0.12 version.

Thanks,
Chiranjeevi.
[6 Aug 2018 14:11] Chiranjeevi Battula
Screenshot

Attachment: Bug_91891.PNG (image/png, text), 39.47 KiB.

[16 Oct 2018 21:30] MySQL Verification Team
https://bugs.mysql.com/bug.php?id=92805 marked as duplicate of this one.
[4 Nov 2018 14:41] David Webb
I've just installed the upgrade to Workbench 8.0.13 and am disappointed to see that this bug has still not been fixed.
[10 Nov 2018 0:31] Nathaniel Jue
Having the same problem here. I can actually fix the issue for the server by creating an adapted my.cnf file and using LOAD DATA LOCAL INFILE on command line, but can't resolve with Workbench, even though if I query local_infile variable it shows it listed as "ON". Definitely seems like a Workbench specific issue, but no idea how to fit it through that software. Big kick the shins as I discovered while try to teach a class on how to use MySQL after majority of class had just updated to latest versions. Anyone able to resolve this issue?
[10 Nov 2018 0:33] Nathaniel Jue
Also, affected both Windows and MacOS version of Workbench.
[10 Nov 2018 1:25] Catherine S
I have temporarily fixed this by several steps. However, I am also teaching a class using MySQL Workbench and hope the bug can be fixed soon in a future release.

Here is what I teach my students to do:

Steps for Mac:
1. Create a my.cnf file with the following statements:
[client]
port		= 3306
[mysqld]
port		= 3306
secure_file_priv=''
local-infile = 1
2. Set the my.cnf file as the default configuration file
3. Restart the MySQL server
4. Try the following statements in MySQL Workbench:
SHOW VARIABLES LIKE "local_infile";  //Should be ON
SHOW VARIABLES LIKE "secure_file_priv";  //Should have no values (not NULL but blank)
5. Put the files you want to upload in the /tmp folder
6. LOAD DATA without the LOCAL keyword (i.e., LOAD DATA INFILE 'your_file' INTO TABLE table_name ...)

On Windows (a little bit easier):
1. Run the following statement in MySQL Workbench:
SET GLOBAL local_infile = 'ON';
2. Run the following statement and make sure it shows ON:
SHOW VARIABLES LIKE "local_infile";
3. SHOW VARIABLES LIKE "secure_file_priv"; //Then you'll get the default path, usually C:\ProgramData\MySQL\MySQL Server 8.0\Uploads
4. Put your files into the folder shown in step 3
5. LOAD DATA without the LOCAL keyword (i.e., LOAD DATA INFILE 'your_file' INTO TABLE table_name ...)
[10 Nov 2018 20:33] Nathaniel Jue
Catherine, thanks for the suggestion. I had come up with a similar solution for my previous comment, but hadn't put the data files in the /tmp directory. Will test with students next week. Where did you put the my.cnf file? I found that putting it in ~/.my.cnf worked better than other spots, but I wasn't sure why.
[10 Nov 2018 21:03] Catherine S
Hi Nathaniel,
1. I put my files under /tmp because I faced authorization issues sometime.
2. On Mac, I put my.cnf under /etc/, but I guess it can be put anywhere because we can direct to the file using the MySQL server preference pane.
[11 Nov 2018 2:55] David Webb
The "workaround" described here appears to relate to situations in which the student is running MySQL server on his/her own machine and using Workbench on that machine to "LOAD DATA INFILE", i.e. from a /tmp directory on the same machine, not using "LOCAL".

By comparison, I need to use LOCAL because the client (Workbench) is on a different PC to the server. I want to LOAD DATA LOCAL, that is, from a file on the client host machine up to the server host machine. As the manual states:

"If LOCAL is specified, the file is read by the client program on the client host and sent to the server"

https://dev.mysql.com/doc/refman/8.0/en/load-data.html
[1 Dec 2018 23:19] Tia Sawhney
I am also trying to set this up for students (class is this week) and am frustrated beyond belief.  I am on Windows 10, mySQL Workbench 8.0.13.  Secure_file_priv is null.  Local_infile is on.  Yet I cannot load data from a data directory or C:\Windows\Temp, with or without the LOCAL command.
[2 Dec 2018 0:13] Tia Sawhney
As follow-up to my previous comment: I can load residing in any directory using the command prompt, specifying LOCAL INFILE. The exact same code that works in the command prompt, however, fails in WorkBench. (ErrorCode: 1148.  The used command is not allowed with this MySQL version.)

The gotcha for the command prompt is that the LOAD...INTO... statement needs to be on a separate command line than the FIELDS... statement.  It can be further parsed, but copy and pasting the load statement as a single command line does not work.
[2 Dec 2018 1:02] Tia Sawhney
The last comment should have started with:  I can load *data* residing...

More information:  Using WorkBench I can dump my database (which contains one table) to a self-contained .SQL file, residing in a directory of my choice on my local machine. When I then drop the database and import the dump file, the import creates the database and the table, but then fails to load data into the table.  The error message is "ERROR 1148 (42000) at line 27: The used command is not allowed with this MySQL version".
[20 Dec 2018 22:03] Tianyu Wei
i'm encountering the same problem... this is quite a critical bug :( can somebody take notice of this bug?
[6 Feb 2019 14:04] Horacio Nesman
Hi Caherine S,

I am going through the same problem using Mac and I have a question regarding one of the steps you detailed:

5. Put the files you want to upload in the /tmp folder

Where is that /tmp folder you mention? Where shall I look it for?

Thanks,
Horacio
[6 Feb 2019 16:30] Catherine S
Hi Horacio Nesman´╝î

Right click on the Finder. Then click on "Go to Folder". Type in /tmp.

Catherine
[6 Feb 2019 19:56] Joe Kelly
This bug is still present on version 8.0.15.  This is a critical bug, someone fix this please!!!
[6 Feb 2019 23:25] Horacio Nesman
Hi Catherine,

Thanks for your answer, I followed all the steps you listed

Unfortunately I am still having the same problem:

Error Code: 13. Can't get stat of '/usr/local/mysql/data/estudio-gastos/data-3.csv' (OS errno 2 - No such file or directory)

I tried to copy the file into the mentioned folder using Terminal (since using Finder I don't know why I don't have access to the folder 'usr/local/mysql/data/' but it Terminal says that the directory 'usr/local/mysql/data/estudio-gastos/' doesn't exist... 

BTW, the folder 'estudio-gastos' it's the name of the .dbo that I am using to create my tables. 

Thanks,
Horacio
[7 Feb 2019 3:37] Catherine S
Hi Horacio,

I don't quite get your situation, but have you put the csv file under /tmp and had a try? (in this case, the path would be "/tmp/data-3.csv")

Catherine
[27 Mar 2019 14:19] Ruihong Jiang
Hi, Catherine,

I'm using MySQL on Windows 10. I follow your 5-step solution but another error comes out: 
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement.

Do you know how to fix this issue?

Thanks,

Ruihong
[30 Mar 2019 14:02] Catherine S
Hi Ruihong,

Did you put your files into C:\ProgramData\MySQL\MySQL Server 8.0\Uploads?

Catherine
[30 Mar 2019 23:08] Ruihong Jiang
Hi Catherine,

Thank you for your reply.

I do put my file under in the 'Uploads' file. 

If I load my CSV file as:
      LOAD DATA INFILE 'my.file.csv' INTO TABLE `table1`,
the above error comes up.

If I load my CSV file as:
      LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\my.file.csv' INTO TABLE `table1`,
then the code can be executed but no data is added into 'table1'.

Ruihong
[31 Mar 2019 3:20] Catherine S
Hi Ruihong,

Definitely, you need to use the latter one. But it's weird that it can be executed. Because it should be 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/my.file.csv' (slash instead of backslash). 

Anyway, if the statement can be executed without any error messages, then probably there are issues related to the file. More information is needed to help regarding your case.

Best,
Jingyi
[4 Apr 2019 22:35] Thomas Stieve
Has this been fixed? This is a serious error.
[11 May 2019 14:44] Mor Sagmon
I am getting the same error message, but not by using the Workbench.

I'm sending the LOAD DATA LOCAL INFILE through the MySQL ODBC Connector 8.0 UNICODE Driver (from my application) and getting this error against a MySQL 8.0.11 Server. Using the ANSI Driver yields the same error.

SHOW GLOBAL VARIABLES LIKE 'local_infile'; = returns local_infile = ON.

I have also put the loose-local-infile=1 in the [client] section of my.ini file on my Windows 10 machine (same machine for the client and the server).

Frustrating...
[11 May 2019 15:10] Mor Sagmon
Found a way to get it to work when connecting through the ODBC Driver.
I added the ENABLE_LOCAL_INFILE=1 as a parameter in the connection string and it works fine.
[28 May 2019 14:00] H Thakur
This frustrating bug continues to hinder me for the last six months. A workaround exists but I hate workarounds! 

On a Mac, you need to drop the LOCAL and put the file you are trying to upload in the /tmp directory. Then the upload succeeds. But why o why does it not work from other folders? How does removing the LOCAL or using /tmp directory more secure?! 

Fix it, Oracle! Please.
[5 Jun 2019 17:41] Jan Euklid
Same problem here. Still not solved in Workbench 8.0.16!

Please solve this problem. I use it for a school class with BYOD (macOS, Windows) and we would like to connect to a remote mysql database server. We still have to use MySQL Workbench 6.3 (where the same "load data local infile ..."-command works flawlessly) till this problem is solved.

Please solve this bug!
[6 Jun 2019 20:41] Michael Lynott
Note to followers of this bug:
I have only been able to work around by 
noting the setting 'secure_file_priv' which on my server is
'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\'

Then, following this note in the Ref Man:
"Input File Name, Location, and Content Interpretation
The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes."

You may not use the path text as specified in the variable.  You must
change that text to either replace the  single backslash with two backslashes:
C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\ 
or a forward slash:
C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/

I suspect that this will persist even after the 'local_infile' setting  issue is resolved.

(I add my voice to those who have appealed for a fix. I too was embarrassed while teaching a class.)
[6 Jun 2019 20:41] Michael Lynott
Note to followers of this bug:
I have only been able to work around by 
noting the setting 'secure_file_priv' which on my server is
'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\'

Then, following this note in the Ref Man:
"Input File Name, Location, and Content Interpretation
The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes."

You may not use the path text as specified in the variable.  You must
change that text to either replace the  single backslash with two backslashes:
C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\ 
or a forward slash:
C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/

I suspect that this will persist even after the 'local_infile' setting  issue is resolved.

(I add my voice to those who have appealed for a fix. I too was embarrassed while teaching a class.)
[27 Jun 2019 1:49] Joaquin Pinto
I am also having this problem.

I've already checked, local-infile is ON, and I've attempted using the my.conf configuration.
[19 Jul 2019 7:12] Kojo DeGraft-Hanson
STILL an issue, almost a year later. I just downloaded HeidiSQL for my import. It's stupid, but I guess I'll just hack it by using both til the team here gets their ish together...
[6 Aug 2019 11:17] Md.Intekhabul Hafiz
I'm amazed that it still remains unresolved!
[19 Sep 2019 18:13] Stephen Woodbury
Hello all,

Tinkering with MySQL seriously for the first time, read through all the comments and workarounds, smart stuff. 

Still an issue though. How come something so critical isn't resolved after more than a year? 

I don't know too much about MySQL, but it seems to me that batch loading data would be a crucial function to anybody trying to use MySQL realistically. How else would someone batch load a bunch of data into their database?
[23 Sep 2019 17:00] MySQL Verification Team
Both the server and the client can restrict use of the LOCAL keyword for LOAD DATA and LOAD XML. The same error message is produced if either one blocks it.

To cause the server to permit access, set the local_infile variable. This can be done at runtime or at startup, as shown in the comments above.

To cause Workbench to permit access, edit the connection (click the wrench icon by the MySQL Connections, or right-click on a particular connection and choose Edit Connection...). On the Advanced tab, in the "Others:" box, add the line OPT_LOCAL_INFILE=1
[4 Oct 2019 17:18] Aaron forman
i have a great suggestion for people... rollback workbench to version 6... no more issues on this...
[15 Oct 2019 20:41] Brian Cashman
The comment by  [23 Sep 17:00] Scott Noyes fixed it for me. Edit the connection and add OPT_LOCAL_INFILE=1 in the Others: box in the Advanced tab.
[18 Feb 2020 21:11] c walker
adding OPT_LOCAL_INFILE=1 to the others section of advanced in managing connection does not work.
[1 Apr 2020 16:29] Emilie Besnard
Hi,

I only have used MySQL for a month and it was 5 years ago. I am now getting back to it.

I installed MySQL on a MacOS Catalina computer (Version 10.15.4) choosing the Strong Password Encryption option (not legacy password encryption). I can open mysql, create databases and tables but  I can not upload any file in a table. The issue seems to  "secure_file_priv"= NULL
I changed the variable local_infile to 'ON' but it does not help. I tried to create and modify the configuration file (etc/my.cnf) according to what I could find online including on this post, but nothing that I tried have worked. 

Do you have a similar issue? Could anyone help me on this issue? Thank you!

Emilie
[14 Jul 2020 22:15] Derrick Pope
adding this in the advanced tab fixed the issue for me 

OPT_LOCAL_INFILE=1

Thank you Brian Cashman
[15 Jul 2020 13:02] Jan Euklid
This trick solves the problem for me and all my students:

"[15 Oct 2019 20:41] Brian Cashman
The comment by  [23 Sep 17:00] Scott Noyes fixed it for me. Edit the connection and add OPT_LOCAL_INFILE=1 in the Others: box in the Advanced tab."

Thank yoooouuuu Brian!!!
[15 Jul 2020 13:02] Jan Euklid
This trick solves the problem for me and all my students:

"[15 Oct 2019 20:41] Brian Cashman
The comment by  [23 Sep 17:00] Scott Noyes fixed it for me. Edit the connection and add OPT_LOCAL_INFILE=1 in the Others: box in the Advanced tab."

Thank yoooouuuu Brian!!!
[6 Apr 1:07] SAMUEL ADENIGBA
I can not LOAD DATA LOCAL INFILE command via Workbench. It returns the following error:
	Error Code: 3948. Loading local data is disabled; this must be enabled on both the client and server sides	0.000 sec

then I ran this code to confirm if my local_infile is on or off

show variables like '%INFILE%';

then I got this:

local_infile off

pls how can I put it on?
[6 Apr 10:09] Jan Euklid
@ SAMUEL ADENIGBA

This solved the problem for us (if my.cnf on the server is setup correctly):
1. Start MySQL Workbench
2. Click on the tool icon just after the "MySQL Connections" title.
3. Select the connection you'd like to change in the left box
4. Click on the Advanced tab in the right box
5. Add the following line to the bottom of the text (if there is any) in the "Others" textbox:
OPT_LOCAL_INFILE=1
6. Close the window
7. Click on the tile to connect to your DB as usual.
Have fun!
[7 Apr 1:12] SAMUEL ADENIGBA
error outpute

Attachment: SQL_error.png (image/png, text), 70.61 KiB.

[7 Apr 1:17] SAMUEL ADENIGBA
Thanks team, first problem sorted, the issue I have now is running  SQL script generated from a python script, I kept on getting an error message 

"[WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Users\\saden\\AppData\\Local\\Temp\\tmpk3cc_hmv.cnf'"

what can I do please