Connect with local-infile Option in MySql Workbench

Question

Is it possible to connect with the local-infile Option in MySql Workbench? Like this:

mysql -h 10.152.xx.xx -u USER -p --local-infile MyDatabase

The reason is, that LOAD DATA LOCAL INFILE seems to work only with this option. Even if

SHOW GLOBAL VARIABLES LIKE 'local_infile'; 

returns

'local_infile', 'ON'

LOAD DATA LOCAL INFILE yields

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

Answer 1

This not working is a verified bug since Workbench 8.0.12. Solution from the bug's comments:

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 with SET GLOBAL local_infile = 1; or check it with SHOW GLOBAL VARIABLES LIKE 'local_infile';. Alternatively, edit mysql's config to include local_infile=1.

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

I just ran into this problem and setting the variable as well as the MySQL Workbench config solved it.

Answer 2

If you are using MySQL Workbench 8.0.12 (more recent encountering of this issue), this bug might explain,

https://bugs.mysql.com/bug.php?id=91891

I just upgraded from MySQLWorkbench 6.x to 8.0.12, and the LOCAL INFILE broke with "Error code 1148. The used command is not allowed with the MySQL version." without any other changes (GLOBAL variable local_infile is ON).

P.S. I also tried with the local_infile=1 parameter in the Advanced preferences, and it does not resolve the issue. For the time being, I rolled back to the older version of workbench till the bug is fixed.