I don’t understand the responses that others have provided to similar questions except for the most obvious ones, such as the one below:
mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
1 row in set (0.01 sec)
By this I mean the exact code was provided. I’d greatly appreciate if someone could walk me through, step by step, what I need to do to enable local data on the «client» side and «server» side. It seems like I’ve enabled local data on the client side, but I don’t know what instructions I need to give my computer to enable the «server side». I’m not tech savvy at all, and I just want to be able to get to the point where the data has been uploaded into MySQL workbench.
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
CREATE TABLE toys (
uniq_id VARCHAR(1000),
product_name VARCHAR(1000),
manufacturer VARCHAR(1000),
price VARCHAR(1000),
number_available_in_stock VARCHAR (1000),
number_of_reviews INT,
number_of_answered_questions INT,
average_review_rating VARCHAR(1000),
amazon_category_and_sub_category VARCHAR(1000),
customers_who_bought_this_item_also_bought VARCHAR(1000),
description VARCHAR(1000),
product_information VARCHAR(1000),
product_description VARCHAR(1000),
items_customers_buy_after_viewing_this_item VARCHAR(1000),
customer_questions_and_answers VARCHAR(1000),
customer_reviews VARCHAR(1000),
sellers VARCHAR(1000)
);
LOAD DATA LOCAL INFILE ‘/Users/BruddaDave/Desktop/amazonsample.csv’ INTO TABLE toys
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘n’
IGNORE 1 LINES
(uniq_id, product_name, manufacturer, price, number_available_in_stock, number_of_reviews, number_of_answered_questions, average_review_rating, amazon_category_and_sub_category, customers_who_bought_this_item_also_bought, description, product_information, product_description, items_customers_buy_after_viewing_this_item, customer_questions_and_answers, customer_reviews, sellers)
;
I just want to be able to import a .csv file into MySQL using the command line shell.
Table of Contents
Ispirer Website
Ispirer Capabilities — MySQL Migration
Free Trial
Symptoms
Loading data into a table during the import to MySQL database can lead to the following message: “ERROR 3948 (42000) at line 1: Loading local data is disabled; this must be enabled on both the client and server sides”.
Cause
-
Local data load option is not set.
-
Loading data into a table is performed with LOCAL_INFILE variable set to OFF
Solutions
NOTE! In order to set the settings below, Expert Mode should be enabled: How to Use SQLWays Wizard in Expert Mode
1. To set local data load into MySQL database, you should switch on the “Import from client” and “Enable local load” options at “Advanced” Page.
2. To set variable LOCAL_INFILE to ON in MySQL database perform the command:
SET GLOBAL LOCAL_INFILE=TRUE;
For check:
SHOW GLOBAL VARIABLES LIKE 'LOCAL_INFILE';
If you have any other questions, please, feel free to contact our support team: support@ispirer.com
When trying to load a local file data to your MySQL table, you may see an error saying that The used command is not allowed with this MySQL version.
For example, I want to load an infile.txt file to a table named students with the following data:
Sarah Math 9
Christ English 7
Natalia Math 6
When I run the LOAD DATA LOCAL INFILE statement, MySQL throws the following error:
mysql> LOAD DATA LOCAL INFILE './infile.txt' INTO TABLE students;
ERROR 1148 (42000): The used command is not allowed with this MySQL version
This error happens because loading data from a local file has now been disabled by default.
Note that when you update to the latest MySQL version, the error message may have been changed to a more descriptive one as follows:
mysql> LOAD DATA LOCAL INFILE './infile.txt' INTO TABLE students;
ERROR 3948 (42000): Loading local data is disabled;
this must be enabled on both the client and server sides
Both errors can be resolved in the same way.
You need to allow local file loading by enabling the feature from both client and server sides. Let’s learn how to do that next.
Enabling local data load on MySQL server and client
First, you need to enable local data load from the server side by setting the local_infile global variable value to ON.
You can find the variable with the SHOW VARIABLES statement as shown below:
SHOW VARIABLES LIKE 'local_infile';
-- The response:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | OFF |
+---------------+-------+
In my case, the local_infile variable is still OFF, so I need to use the SET statement to turn it ON as follows:
SET GLOBAL local_infile = true;
Now local_infile should have the value ON in the MySQL server.
Next, you need to enable local data load from the client.
Exit your mysql command-line client and add the --local-infile option when you connect to the server as shown below:
mysql -uroot -proot --local-infile=1
Now you should be able to execute the LOAD DATA with local file statement as shown below:
mysql> LOAD DATA LOCAL INFILE './infile.txt' INTO TABLE students;
# response:
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
With that, new data should be stored in your MySQL table.
And that’s how you can resolve The used command is not allowed with this MySQL version error from your MySQL database server. Nice work! 👍
I am working on a python project with MySQL connectivity. I have MySQL as a shell, i.e.,I need to use mysqlsh command in the Terminal to use MySQL(I am using MacOS Mojave 10.14.6). I needed to use a csv file to store data taken from user and then transfer it to a MySQL database. After doing some research, I found the best way to do it was by giving theLOAD DATA LOCAL INFILE command to my_c.execute()(‘my_c‘ is the cursor I defined to use for Python-MySQL connectivity) in Python. However when I run the command:
my_c.execute('LOAD DATA LOCAL INFILE "/test1Desktop/Python/Practice/body.csv" INTO TABLE POQ COLUMNS TERMINATED BY "," ESCAPED BY '"' LINES TERMINATED BY 'n'IGNORE 1 LINES')
I get the following errors:
mysql.connector.errors.ProgrammingError: 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
During handling of the above exception, another exception occurred:
mysql.connector.errors.DatabaseError: LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.
Here is a screenshot of the code:
As both the files(python and the source file) are in the same directory, I tried removing the full path of the file, and just writing the file name; but that didn’t work either.
#The password has been removed.
Here is the screenshot of the error output:
I tried researching the errors, and many suggested to use the command show global variables like 'local_infile'; and set global local_infile=true; in MySQL. Sure the value changed from NULL to ON. But there was still no difference.
#Some solutions suggested to make changes to the ‘my.cnf’ file. Bit when I tried to find it, I found that it doesn’t exist.
#Some suggested to make changes to secure-file-priv in the file file /etc/mysql/mysql.conf.d/mysqld.cnf. But this file was also not found.
Can someone pls tell what is wrong and how to fix it? Pls give step-by-step solution as I am not very good with the in-depth technical details of it.
Other information:
- OS: MacOS Mojave(10.14.6)
- Python version: 3.9.2
- MySQL shell version: 8.0.23
If you need other information, do tell.
The following error is displayed when using MySQL as an endpoint in Qlik Replicate:
RetCode: SQL_ERROR SqlState: HY000 NativeError: 3948 Message: [MySQL][ODBC 5.3(w) Driver][mysqld-8.0.22-commercial]Loading local data is disabled; this must be enabled on both the client and server sides
Resolution
Set the variable local_infile to ON.
You can do this in the MySQL UI with this command:
SET GLOBAL local_infile=1;
Or in a command line with:
mysql> SET GLOBAL local_infile=1;
Environment
- Qlik Replicate
The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.