Error 3948 42000 loading local data is disabled

MySQL the used command is not allowed error loading local file fix

Posted on Nov 12, 2021

Learn how to fix ‘The used command is not allowed with this MySQL version error’ when loading a local file

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:

When I run the LOAD DATA LOCAL INFILE statement, MySQL throws the following error:

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:

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:

In my case, the local_infile variable is still OFF , so I need to use the SET statement to turn it ON as follows:

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:

Now you should be able to execute the LOAD DATA with local file statement as shown below:

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! 👍

Level up your programming skills

I’m sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I’ll send new stuff straight into your inbox!

About

Nathan Sebhastian is a software engineer with a passion for writing tech tutorials.
Learn JavaScript and other web development technology concepts through easy-to-understand explanations written in plain English.

Источник

Русские Блоги

MySQL (WIN10) Ошибка импорта данных, код ошибки 3948 Ultimate Solution 2020-12-17

MySQL версия:
Операционная система: Windows 10
Ошибка исходной код:

1. Почему этот вопрос возникает:
   Это связано с тем, что новой версии MySQL запрещена загружать локальные файлы по умолчанию, поэтому для новичков впервые импортируют данные, эта проблема будет столкнулась.
2. После сброса параметров и настроек перезапустите Workbench MySQL, а затем данные не будут происходить после того, как данные не будут происходить;

Решение:
Сначала: откройте разрешения на импорт локального файла:

Во -вторых, в базе данных MySQL Workbench -Menus.

Откройте новый интерфейс для настройки следующим образом:

Кейс:
Операция данных введена в застроенной таблице следующим образом:

Интеллектуальная рекомендация

Реализация оценки приложения iOS

Есть два способа получить оценку приложения: перейти в App Store для оценки и оценка в приложении. 1. Перейдите в App Store, чтобы оценить ps: appid можно запросить в iTunes Connect 2. Встроенная оцен.

JS функциональное программирование (е)

Давайте рассмотрим простой пример, чтобы проиллюстрировать, как используется Reduce. Первый параметр Reduce — это то, что мы принимаем массив arrayOfNums, а второй параметр — функцию. Эта функция прин.

PWN_JarvisOJ_Level1

Nc первый Затем мы смотрим на декомпиляцию ida Перед «Hello, World! N» есть уязвимая_функция, проверьте эту функцию после ввода Видно, что только что появившийся странный адрес является пе.

Установка и развертывание Kubernetes

На самом деле, я опубликовал статью в этом разделе давным -давно, но она не достаточно подробно, и уровень не является ясным. Когда я развернулся сегодня, я увидел его достаточно (хотя это было успешн.

На стороне многопроцессорного сервера — (2) *

Обработка сигнала Родительский процесс часто очень занят, поэтому вы не можете просто вызвать функцию waitpid, чтобы дождаться завершения дочернего процесса. Затем обсудите решение. Обратитесь .

Источник

Official Support Articles

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

• Qlik Community
• :
• Support
• :
• Support
• :
• Knowledge
• :
• Support Articles
• :
• How to resolve MySQL error with Qlik Replicate: Lo.

• Subscribe to RSS Feed
• Mark as New
• Mark as Read
• Bookmark
• Subscribe
• Printer Friendly Page
• Report Inappropriate Content

How to resolve MySQL error with Qlik Replicate: Loading local data is disabled this must be enabled on both the client and server sides

• Mark as New
• Bookmark
• Subscribe
• Mute
• Subscribe to RSS Feed
• Permalink
• Print
• Report Inappropriate Content

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

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.

Источник

Jon Adams, OCP, OCI Architect and DBA, MCTS, OCI Cloud DBA, AWS Cloud Practitioner

Loading CSV data files into MySQL 8 – manual method

In my earlier MySQL 8 post here, I detailed the process for creating a MySQL 8 test environment. If you have the MySQL 8 environment or some other newly-created MySQL 8 environment, you probably need to create some data in it now. I wanted to load a decent amount of table data and aside from creating a simulated data set, loading a CSV file seemed to be a convenient method. I chose to load a census data file in CSV format from https://data.census.gov/cedsci/. This data if freely available, sizeable, and lends itself well to analytical queries later on.

Reviewing the process to load the data, many of you will notice the similarities between this process and the Oracle database external file loading process. However, there is a gotcha that I encountered in MySQL that I will outline here.

First, you need to review your CSV data file to design and create a target table in MySQL. I won’t cover the basics of starting the mysqld service or logging into the MySQL database, but will only relay the commands I used to created my target table:

After creating the target table, I copied the CSV data file to a location on my MySQL database server and ensured that it was readable by all parties.

I then tried to load the CSV data file, but encountered the gotcha.

Through trial and error, I found this setting is the default and that this next process was the easiest method to accomplish changing this setting:

• Make a backup of the MySQL configuration file. In my case (RHEL7 and MySQL 8 Community Edition, this file is /etc/my.cnf). I recommend this step before editing any configuration file.
• Add local_infile = 1 to the [mysqld] section of the /etc/my.cnf file and restart the mysqld service.
• Log into mysql and execute the following command to verify the changed setting.

• Now the kicker. If you logged into MySQL using mysqlsh, you need to exit and login using mysql because you will need to use a command line argument that is not accepted by mysqlsh.
• Here is the log of my session including the load command and results. Keep in mind that you only need to use the IGNORE 1 ROWS clause if there is a header line in the CSV data file.

If the results of the load process show warnings or errors, you can use the SHOW WARNINGS or SHOW ERRORS command to display the warning or errors messages. Use the limit option to keep the number of warnings at a manageable level if there are hundreds or thousands.

You may be concerned that you are changing the default settings of MySQL that prevent the loading of local data files, but this process only works if you alter the default settings in the configuration file AND use the local_infile settings when logging into MySQL.

If you prefer a graphical method for this process, you can also load data into tables using the MySQL Workbench. The workbench includes many useful options including performance monitoring, import and export of data, querying and script execution, and data modeling.

There is also a graphical interface for loading data files into MySQL called phpMyAdmin which is browser-based and requires the installation and configuration of PHP and HTTPD (Apache).

Источник

Support for LOAD DATA LOCAL INFILE #110

Is LOAD DATA LOCAL INFILE supported?

If LOAD DATA LOCAL INFILE is executed the following error is received:

local_infile is enabled on the server.

The text was updated successfully, but these errors were encountered:

Hi there, sorry for my late reply.

It sounds like you are trying to execute a command, but the server does not support the command. As I can see, the MariaDB server version should be too low, which version are you using?

MariaDB server supports it, it works with MariaDB Connector/J.
In the meantime I checked r2dbc-mysql source code, and LOAD DATA LOCAL INFILE is definitely not supported.

I tried to implement it myself, and send in a pull request, however, I got stuck.
The process of handling LOAD DATA LOCAL INFILE is this:
https://mariadb.com/kb/en/packet_local_infile/

That means that once driver decodes the server response, it should start sending the file content of file specified in the server response, followed by empty packet.
I figured that decoding happens in dev.miku.r2dbc.mysql.message.server.ServerMessageDecoder, and decoded message is handled in dev.miku.r2dbc.mysql.client.MessageDuplexCodec.
I added the decoding part, however got stuck on how to send back the file content, since I don’t have any experience with Netty, and apparently no other commands work in two stages, so I don’t have any examples on how to do it.

I reckon dev.miku.r2dbc.mysql.client.MessageDuplexCodec should be changed something like this:

In private void handleDecoded(ChannelHandlerContext ctx, ServerMessage msg)

If you could give me any pointers how to send the file back to server, I’d be happy to try creating a pull request.

Источник

Я не понимаю ответы, которые другие давали на подобные вопросы, за исключением самых очевидных, таких как приведенный ниже:

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)

Под этим я подразумеваю точный код. Я был бы очень признателен, если бы кто-то мог шаг за шагом объяснить мне, что мне нужно сделать, чтобы включить локальные данные на стороне «клиента» и «сервера». Кажется, что я включил локальные данные на стороне клиента, но я не знаю, какие инструкции мне нужно дать моему компьютеру, чтобы включить «сторону сервера». Я совсем не разбираюсь в технологиях, и я просто хочу быть в состоянии добраться до точки, где данные были загружены в 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)
;

Я просто хочу иметь возможность импортировать файл .csv в MySQL с помощью оболочки командной строки.

SQLloadПри утверждении могут возникнуть следующие ошибки:

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on

решение:

1. Измените значение глобальных переменных:

mysql> SET GLOBAL local_infile=1;
Query OK, 0 rows affected (0.00 sec)

Вы можете увидеть после успеха настройки:

mysql> select @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.01 sec)

--или

mysql> SHOW GLOBAL VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.06 sec)

1. Выйдите из текущего сервера MySQL:

mysql> quit
Bye

1. Общая система локальной системной системы зарегистрирована в MySQL.

mysql -Local -infile = 1 -u root -p здесь добавьте свой собственный пароль MySQL (без места)

1. Используйте базу данных и используйте ееloadСинологические данные импорта:

mysql> use menagerie
Database changed
mysql> load data local infile 'Абсолютный путь вашего файла t' into table pet;
Query OK, 8 rows affected, 7 warnings (0.00 sec)

Ошибка успешно разрешена.

Ссылаться на:

https://stackoverflow.com/questions/59993844/error-loading-local-data-is-disabled-this-must-be-enabled-on-both-the-client