Menu
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
414
For the mysql client, local data loading capability is determined by the default compiled into the MySQL client library. To disable or enable it explicitly, use the --local-infile=0 or --local-infile[=1] option. For the mysqlimport client, local data loading is not used by default.
Launch the MySQL Workbench from the desktop. Click the Local instance MySQL80 button, and click Connect to begin the configuration process. When prompted, enter in the MySQL server root password which was created during the MySQL installation process. Optionally, you may check the Save password in vault check box.
This allows you to load files from the client's local file system into the database. In the event that you don't want to permit this operation (such as for security reasons), you can disable the LOAD DATA LOCAL INFILE statement on either the server or the client.
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 withSHOW GLOBAL VARIABLES LIKE 'local_infile';. Alternatively, edit mysql's config to includelocal_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.
162
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.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
