blocks|key|699555|text|在设置客户端连接时，可以将其指定为附加选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|699556|mysql+-u+myuser+-p+--local-infile+somedatabase|code-block|syntax|javascript|699557|这是因为这个特性打开了一个安全漏洞。因此，您必须以显式的方式启用它，以防您真的想使用它。|699558|客户端和服务器都应该启用本地文件选项。否则，它不会为服务器端服务器上的文件启用work.To，将以下内容添加到my.cnf配置文件中：|offset|length|style|CODE|699559|loose-local-infile+=+1|699560|entityMap^0|0|0|0|1J|6|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|V|8|@$K|W|L|X|M|N]]|9|@]|A|$]]|$1|O|3|P|5|D|7|Y|8|@]|9|@]|A|$E|F]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

You can specify that as an additional option when setting up your client connection: 

<pre><code>mysql -u myuser -p --local-infile somedatabase
</code></pre>

This is because that feature opens a security hole. So you have to enable it in an explicit manner in case you really want to use it. 

Both client and server should enable the local-file option. Otherwise it doesn't work.To enable it for files on the server side server add following to the <code>my.cnf</code> configuration file: 

<pre><code>loose-local-infile = 1
</code></pre>

blocks|key|3095904|text|http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3095905|将其放在my.cnf中--+[client]部分应该已经存在了(如果您不太关心安全性)。|style|CODE|3095906|[client]
loose-local-infile=1|code-block|syntax|javascript|3095907|entityMap|0|LINK|mutability|MUTABLE|url^0|0|1N|0|0|E|8|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@$A|U|B|V|1|W]]|C|$]]|$1|D|3|E|5|6|7|X|8|@$A|Y|B|Z|F|G]]|9|@]|C|$]]|$1|H|3|I|5|J|7|10|8|@]|9|@]|C|$K|L]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|4]]]]

<a href="http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html</a>

Put this in my.cnf - the <code>[client]</code> section should already be there
(if you're not too concerned about security).

<pre><code>[client]
loose-local-infile=1
</code></pre>

blocks|key|699672|text|所有这些并没有在我全新的Ubuntu15.04上为我解决这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|699673|我删除了LOCAL并得到了以下命令：|offset|length|style|CODE|699674|LOAD+DATA
INFILE+A.txt
INTO+DB
LINES+TERMINATED+BY+'%7C';|code-block|syntax|javascript|699675|但后来我得到了：|699676|MySQL+said:+File+'A.txt'+not+found+(Errcode:+13+-+Permission+denied)|699677|这让我找到了纳尔逊对另一个关于堆栈溢出的问题的回答，它帮我解决了这个问题！|699678|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/4215231/load-data-infile-error-code-13#answer-13677611^0|0|4|5|0|0|0|0|6|J|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@$D|12|E|13|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|17|8|@]|9|@$D|18|E|19|1|1A]]|A|$]]|$1|S|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

All of this didn't solve it for me on my brand new Ubuntu 15.04.

I removed the <code>LOCAL</code> and got this command:

<pre><code>LOAD DATA
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';
</code></pre>

but then I got: 

<pre><code>MySQL said: File 'A.txt' not found (Errcode: 13 - Permission denied)
</code></pre>

That led me to <a href="https://stackoverflow.com/questions/4215231/load-data-infile-error-code-13#answer-13677611">this answer from Nelson to another question on stackoverflow</a> which solved the issue for me!

blocks|key|1775591|text|我找到答案了，这里。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1775592|这是因为服务器变量local_infile被设置为FALSE\0。请参考文档。|style|CODE|BOLD|1775593|您可以通过执行以下操作来验证：|1775594|SHOW+VARIABLES+LIKE+'local_infile';|code-block|syntax|javascript|1775595|如果您拥有超级特权，则可以通过执行以下操作来启用它(而无需使用新配置重新启动服务器)：|1775596|SET+GLOBAL+local_infile+=+1;|1775597|entityMap|0|LINK|mutability|MUTABLE|url|https://bugs.mysql.com/bug.php?id=72220|1|https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_local_infile^0|7|2|0|0|9|C|P|7|10|2|1|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@$A|14|B|15|1|16]]|C|$]]|$1|D|3|E|5|6|7|17|8|@$A|18|B|19|F|G]|$A|1A|B|1B|F|H]]|9|@$A|1C|B|1D|1|1E]]|C|$]]|$1|I|3|J|5|6|7|1F|8|@]|9|@]|C|$]]|$1|K|3|L|5|M|7|1G|8|@]|9|@]|C|$N|O]]|$1|P|3|Q|5|6|7|1H|8|@]|9|@]|C|$]]|$1|R|3|S|5|M|7|1I|8|@]|9|@]|C|$N|O]]|$1|T|3|-4|5|6|7|1J|8|@]|9|@]|C|$]]]|U|$V|$5|W|X|Y|C|$Z|10]]|11|$5|W|X|Y|C|$Z|12]]]]

I find the answer <a href="https://bugs.mysql.com/bug.php?id=72220" rel="noreferrer">here</a>.

It's because the server variable <code>local_infile</code> is set to FALSE|0. Refer from the <a href="https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_local_infile" rel="noreferrer">document</a>.

You can verify by executing: 

<pre><code>SHOW VARIABLES LIKE 'local_infile';
</code></pre>

If you have SUPER privilege you can enable it (without restarting server with a new configuration) by executing:

<pre><code>SET GLOBAL local_infile = 1;
</code></pre>

blocks|key|3096015|text|带有默认SpringBoot连接器的MySQL+2.1|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096016|要解决这个问题，请按照说明|3096017|1.设置全局local_infile+=+1；|3096018|若要设置此全局变量，请按照MySQL文档https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html中提供的说明进行操作。|offset|length|3096019|2.更改MySQL连接器字符串|3096020|jdbc:mysql://localhost:3306/xxxx?useSSL=false&useUnicode=yes&characterEncoding=UTF-8&allowLoadLocalInfile=true示例:+allowLoadLocalInfile=true|3096021|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html^0|0|0|0|K|1O|0|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|W|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Y|8|@]|9|@$H|Z|I|10|1|11]]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|O|$P|$5|Q|R|S|A|$T|U]]]]

SpringBoot 2.1 with Default MySQL connector

To Solve this please follow the instructions

<h2>1. SET GLOBAL local_infile = 1;</h2>

to set this global variable please follow the instruction provided in MySQL documentation
<a href="https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html" rel="noreferrer">https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html</a>

<h2>2. Change the MySQL Connector String</h2>

allowLoadLocalInfile=true
Example :
jdbc:mysql://localhost:3306/xxxx?useSSL=false&amp;useUnicode=yes&amp;characterEncoding=UTF-8&amp;allowLoadLocalInfile=true

blocks|key|699780|text|参考MySQL+8.0参考手册--+6.1.6本地负载数据的安全性问题|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|699781|在服务器端，运行|699782|mysql.server+start+--local-infile|code-block|syntax|javascript|699783|在客户端，运行|699784|mysql+--local-infile+database_name+-u+username+-p|699785|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html^0|I|H|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]]|C|$]]|$1|D|3|E|5|6|7|10|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|11|8|@]|9|@]|C|$I|J]]|$1|K|3|L|5|6|7|12|8|@]|9|@]|C|$]]|$1|M|3|N|5|H|7|13|8|@]|9|@]|C|$I|J]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|C|$]]]|P|$Q|$5|R|S|T|C|$U|V]]]]

Refer to MySQL 8.0 Reference Manual -- <a href="https://dev.mysql.com/doc/refman/8.0/en/load-data-local.html" rel="nofollow noreferrer">6.1.6 Security Issues with LOAD DATA LOCAL</a>

On the server side, run

<pre><code>mysql.server start --local-infile
</code></pre>

On the client side, run

<pre><code>mysql --local-infile database_name -u username -p
</code></pre>

blocks|key|699813|text|上面的答案是正确的。请先在MySQL+CLI中直接检查。如果这解决了那里的问题，您可能想让它在Python3中工作，只需将它作为参数传递给MySQLdb.connect。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|699814|self.connection+=+MySQLdb.connect(
++++++++++++++++++++host=host,+user=settings_DB.db_config['USER'],
++++++++++++++++++++port=port,+passwd=settings_DB.db_config['PASSWORD'],+
++++++++++++++++++++db=settings_DB.db_config['NAME'],
++++++++++++++++++++local_infile=True)|code-block|syntax|javascript|699815|entityMap^0|1X|F|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

The top answers are correct. Please check them direct in MySQL CLI first. If this fixes the problem there, you may want to have it working in Python3 just pass it to the <code>MySQLdb.connect</code>as parameter

<pre><code>self.connection = MySQLdb.connect(
 host=host, user=settings_DB.db_config['USER'],
 port=port, passwd=settings_DB.db_config['PASSWORD'], 
 db=settings_DB.db_config['NAME'],
 local_infile=True)
</code></pre>

blocks|key|1775733|text|只是加了一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775734|在allow_local_infile=True+Ver8.0.16中找到另一个转世，它现在需要allow_local_infile=True，否则您将看到上面的错误。在早期版本中工作过。|offset|length|style|BOLD|1775735|conn+=+mysql.connector.connect(host=host,+user=user,+passwd=passwd,+database=database,+allow_local_infile=True)|code-block|syntax|javascript|1775736|entityMap^0|0|1|N|1C|N|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Just little addition.

Found another reincarnation in mysql.connector ver 8.0.16
It now requires allow_local_infile=True or you will see the above error.
Worked in prior versions.

<pre><code>conn = mysql.connector.connect(host=host, user=user, passwd=passwd, database=database, allow_local_infile=True)
</code></pre>

blocks|key|3096197|text|在AWS+MySQL+8.0RDS中导入CSV文件时，我也遇到了同样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096198|mysql>将数据本地INFILE+'/tmp/your.csv‘加载到以’‘结束的表test.demo2字段中，由'\n’忽略1行结束的“”行括起来；
错误1148+(42000)：此MySQL版本不允许使用所使用的命令|blockquote|3096199|1)检查local_infile参数|offset|length|style|CODE|3096200|mysql>+SHOW+VARIABLES+LIKE+'local_infile';|code-block|syntax|javascript|3096201|2)从客户端注销并使用以下参数重新登录。|3096202|mysql+-u+root+-p+-h+rdsendpoint+--local-infile=1|3096203|3)运行相同的命令|3096204|mysql>+LOAD+DATA+LOCAL+INFILE+'/tmp/your.csv'+INTO+TABLE+test.demo2+FIELDS+TERMINATED+BY+','+ENCLOSED+BY+'"'+LINES+TERMINATED+BY+'\n'+IGNORE+1+LINES;


Query+OK,+300+rows+affected+(0.01+sec)

Records:+300++Deleted:+0++Skipped:+0++Warnings:+0|3096205|entityMap^0|0|0|4|C|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@]|A|$]]|$1|E|3|F|5|6|7|11|8|@$G|12|H|13|I|J]]|9|@]|A|$]]|$1|K|3|L|5|M|7|14|8|@]|9|@]|A|$N|O]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|M|7|16|8|@]|9|@]|A|$N|O]]|$1|T|3|U|5|6|7|17|8|@]|9|@]|A|$]]|$1|V|3|W|5|M|7|18|8|@]|9|@]|A|$N|O]]|$1|X|3|-4|5|6|7|19|8|@]|9|@]|A|$]]]|Y|$]]

I had the same issue while importing the CSV file in AWS MySQL 8.0 RDS.

<blockquote>
 mysql> LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;
 
 ERROR 1148 (42000): The used command is not allowed with this MySQL version
</blockquote>

1) Check for <code>local_infile</code> parameter

<pre><code>mysql&gt; SHOW VARIABLES LIKE 'local_infile';
</code></pre>

2) Log out from client and re-login using below parameter.

<pre><code>mysql -u root -p -h rdsendpoint --local-infile=1
</code></pre>

3) Run the same command

<pre><code>mysql&gt; LOAD DATA LOCAL INFILE '/tmp/your.csv' INTO TABLE test.demo2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;


Query OK, 300 rows affected (0.01 sec)

Records: 300 Deleted: 0 Skipped: 0 Warnings: 0
</code></pre>

blocks|key|3096237|text|在使用docker1加载数据时，我得到了这个错误。在我遵循下面这些步骤之后，解决方案就奏效了。最初，我创建了数据库和表datavault和fdata。当我试图导入data2时，我得到了error3。然后我做了：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3096238|SET+GLOBAL+local_infile+=+1;|unordered-list-item|3096239|使用SHOW+VARIABLES+LIKE+'local_infile';确认|3096240|然后我重新启动mysql会话：mysql+-P+3306+-u+required+--local-infile=1+-p，有关用户创建，请参见4。|3096241|我重新创建了我的表，因为这解决了我的问题：|BOLD|3096242|use+datavault;|3096243|drop+table+fdata;|3096244|CREATE+TABLE+fdata+(fID+INT,+NAME+VARCHAR(64),+LASTNAME+VARCHAR(64),+EMAIL+VARCHAR(128),+GENDER+VARCHAR(12),+IPADDRESS+VARCHAR(40));|3096245|3096246|​|3096247|最后，我使用2导入了数据。|3096248|为了完整起见，我想补充的是，我是通过docker+exec+-it+testdb+sh在容器中运行mysql版本的。mysql版本是MySQLVer8.0.17，用于Linuxonx86_64+(MySQL+)。--这也是用mysql.exe+Ver+14.14++5.7.14对Win64+(x86_64)进行了测试，后者是来自WAMP64的mysql的另一个版本。所使用的相关命令在5中列出。|3096249|1个docker+run+--name+testdb+-v+//c/Users/C/Downloads/data/csv-data/:/var/data+-p+3306+-e+MYSQL_ROOT_PASSWORD=password+-d+mysql:latest|3096250|2个load+data+local+infile+'/var/data/mockdata.csv'+into+table+fdata+fields+terminated+by+','+enclosed+by+''+lines+terminated+by+'\n'+IGNORE+1+ROWS;|3096251|3错误1148+(42000)：此MySQL版本不允许使用所使用的命令|3096252|4.所需的客户端是使用以下方法创建的：|3096253|CREATE+USER+'required'@'%25'+IDENTIFIED+BY+'password';|3096254|GRANT+ALL+PRIVILEGES+ON+*+.+*+TO+'required'@'%25';|3096255|FLUSH+PRIVILEGES;|3096256|如果遇到这一错误：Authentication+plugin+‘caching_sha2_password’+cannot+be+loaded，您可能需要这行这一错误：Authentication+plugin+‘caching_sha2_password’+cannot+be+loaded|3096257|使用来自WAMP64的mysql的5个命令：|3096258|mysql+-urequired+-ppassword+-P+32775+-h+192.168.99.100+--local-infile=1将端口映射到由docker+ps+-a描述的主机，并且主机ip是使用docker-machine+ip获得的(这取决于操作系统，也可能取决于Docker版本)。|3096259|创建数据库datavault2和表fdata，如前所述|3096260|load+data+local+infile+'c:/Users/C/Downloads/data/csv-data/mockdata.csv'+into+table+fdata+fields+terminated+by+','+enclosed+by+''+lines+terminated+by+'\n';|3096261|为了我的记录，在我之前创建了datavault3和fdata：mysql+-urequired+-ppassword+-P+32775+-h+192.168.99.100+--local-infile+datavault3+-e+"LOAD+DATA+LOCAL+INFILE+'c:/Users/C/Downloads/data/csv-data/mockdata.csv'++REPLACE+INTO+TABLE+fdata++FIELDS+TERMINATED+BY+','+LINES+TERMINATED+BY+'\n'+IGNORE+1+ROWS"之后，加载文件的另一个选择就可以工作了，并且它成功地加载了在运行select+*+from+fdata+limit+10;之后轻松检查过的数据。|3096262|entityMap|0|LINK|mutability|MUTABLE|url|https://itnext.io/install-mysql-in-20mins-by-using-docker-3751819e7815|1^0|1N|9|1X|5|0|0|S|0|2|Z|0|F|19|0|1|5|1|0|E|1|0|H|1|0|3O|1|0|0|0|I|P|2Y|Q|0|2|3M|0|2|40|0|1|N|0|0|0|1G|0|0|1C|0|0|H|0|9|1Q|2C|1Q|4|4|0|27|4|1|0|0|0|1Z|26|C|2X|H|3X|6|0|5|A|H|5|0|0|4B|0|E|A|P|5|V|6X|8O|T|0^^$0|@$1|2|3|4|5|6|7|21|8|@$9|22|A|23|B|C]|$9|24|A|25|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|26|8|@$9|27|A|28|B|C]]|D|@]|E|$]]|$1|I|3|J|5|H|7|29|8|@$9|2A|A|2B|B|C]]|D|@]|E|$]]|$1|K|3|L|5|H|7|2C|8|@$9|2D|A|2E|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|2F|8|@$9|2G|A|2H|B|O]]|D|@]|E|$]]|$1|P|3|Q|5|H|7|2I|8|@$9|2J|A|2K|B|C]]|D|@]|E|$]]|$1|R|3|S|5|H|7|2L|8|@$9|2M|A|2N|B|C]]|D|@]|E|$]]|$1|T|3|U|5|H|7|2O|8|@$9|2P|A|2Q|B|C]]|D|@]|E|$]]|$1|V|3|-4|5|6|7|2R|8|@]|D|@]|E|$]]|$1|W|3|X|5|6|7|2S|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|H|7|2T|8|@]|D|@]|E|$]]|$1|10|3|11|5|6|7|2U|8|@$9|2V|A|2W|B|C]|$9|2X|A|2Y|B|O]]|D|@]|E|$]]|$1|12|3|13|5|6|7|2Z|8|@$9|30|A|31|B|C]]|D|@]|E|$]]|$1|14|3|15|5|6|7|32|8|@$9|33|A|34|B|C]]|D|@]|E|$]]|$1|16|3|17|5|6|7|35|8|@$9|36|A|37|B|O]]|D|@]|E|$]]|$1|18|3|19|5|6|7|38|8|@]|D|@]|E|$]]|$1|1A|3|1B|5|H|7|39|8|@$9|3A|A|3B|B|C]]|D|@]|E|$]]|$1|1C|3|1D|5|H|7|3C|8|@$9|3D|A|3E|B|C]]|D|@]|E|$]]|$1|1E|3|1F|5|H|7|3F|8|@$9|3G|A|3H|B|C]]|D|@]|E|$]]|$1|1G|3|1H|5|H|7|3I|8|@$9|3J|A|3K|B|C]|$9|3L|A|3M|B|C]]|D|@$9|3N|A|3O|1|3P]|$9|3Q|A|3R|1|3S]]|E|$]]|$1|1I|3|1J|5|6|7|3T|8|@]|D|@]|E|$]]|$1|1K|3|1L|5|H|7|3U|8|@$9|3V|A|3W|B|C]|$9|3X|A|3Y|B|C]|$9|3Z|A|40|B|C]|$9|41|A|42|B|O]]|D|@]|E|$]]|$1|1M|3|1N|5|H|7|43|8|@$9|44|A|45|B|O]|$9|46|A|47|B|O]]|D|@]|E|$]]|$1|1O|3|1P|5|H|7|48|8|@$9|49|A|4A|B|C]]|D|@]|E|$]]|$1|1Q|3|1R|5|H|7|4B|8|@$9|4C|A|4D|B|O]|$9|4E|A|4F|B|O]|$9|4G|A|4H|B|C]|$9|4I|A|4J|B|C]]|D|@]|E|$]]|$1|1S|3|-4|5|6|7|4K|8|@]|D|@]|E|$]]]|1T|$1U|$5|1V|1W|1X|E|$1Y|1Z]]|20|$5|1V|1W|1X|E|$1Y|1Z]]]]

I got this error while loading data when using docker[1]. The solution worked after I followed these next steps. Initially, I created the database and table <code>datavault</code> and <code>fdata</code>. When I tried to import the data[2], I got the error[3]. Then I did:

<ul>
<li><code>SET GLOBAL local_infile = 1;</code></li>
<li>Confirm using <code>SHOW VARIABLES LIKE 'local_infile';</code></li>
<li>Then I restarted my mysql session: <code>mysql -P 3306 -u required --local-infile=1 -p</code>, see [4] for user creation.</li>
<li>I recreated my table as this solved my problem: 

<ul>
<li><code>use datavault;</code> </li>
<li><code>drop table fdata;</code></li>
<li><code>CREATE TABLE fdata (fID INT, NAME VARCHAR(64), LASTNAME VARCHAR(64), EMAIL VARCHAR(128), GENDER VARCHAR(12), IPADDRESS VARCHAR(40));</code></li>
</ul></li>
<li>Finally I imported the data using [2]. </li>
</ul>

For completeness, I would add I was running the mysql version inside the container via <code>docker exec -it testdb sh</code>. The mysql version was mysql Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL). This was also tested with mysql.exe Ver 14.14 Distrib 5.7.14, for Win64 (x86_64) which was another version of mysql from WAMP64. The associated commands used are listed in [5].

<hr>

[1] <code>docker run --name testdb -v //c/Users/C/Downloads/data/csv-data/:/var/data -p 3306 -e MYSQL_ROOT_PASSWORD=password -d mysql:latest</code>

[2] <code>load data local infile '/var/data/mockdata.csv' into table fdata fields terminated by ',' enclosed by '' lines terminated by '\n' IGNORE 1 ROWS;</code>

[3] ERROR 1148 (42000): The used command is not allowed with this MySQL version

[4] The required client was created using:

<ul>
<li><code>CREATE USER 'required'@'%' IDENTIFIED BY 'password';</code></li>
<li><code>GRANT ALL PRIVILEGES ON * . * TO 'required'@'%';</code></li>
<li><code>FLUSH PRIVILEGES;</code></li>
<li>You might need this line <code>ALTER USER 'required'@'%' IDENTIFIED WITH mysql_native_password BY 'password';</code> if you run into <a href="https://itnext.io/install-mysql-in-20mins-by-using-docker-3751819e7815" rel="nofollow noreferrer">this error</a>: <code>Authentication plugin ‘caching_sha2_password’ cannot be loaded</code></li>
</ul>

[5] Commands using mysql from WAMP64:

<ul>
<li><code>mysql -urequired -ppassword -P 32775 -h 192.168.99.100 --local-infile=1</code> where the port is thee mapped port into the host as described by <code>docker ps -a</code> and the host ip was optained using <code>docker-machine ip</code> (This depends on OS and possibly Docker version).</li>
<li>Create database datavault2 and table fdata as described above</li>
<li><code>load data local infile 'c:/Users/C/Downloads/data/csv-data/mockdata.csv' into table fdata fields terminated by ',' enclosed by '' lines terminated by '\n';</code></li>
<li>For my record, this other alternative to load the file worked after I have previously created datavault3 and fdata: <code>mysql -urequired -ppassword -P 32775 -h 192.168.99.100 --local-infile datavault3 -e "LOAD DATA LOCAL INFILE 'c:/Users/C/Downloads/data/csv-data/mockdata.csv' REPLACE INTO TABLE fdata FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS"</code> and it successfully loaded the data easily checked after running <code>select * from fdata limit 10;</code>.</li>
</ul>

blocks|key|1775881|text|如果您正在使用Java8或%2B版本、JDBC和MySql8并面对此问题，请尝试如下：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1775882|将参数添加到连接字符串：|1775883|jdbc:mysql://localhost:3306/tempDB?allowLoadLocalInfile=true|code-block|syntax|javascript|1775884|同时，设置|1775885|local_infile+=1|blockquote|1775886|在my.cnf文件中。|1775887|最新版本的mysql-java-连接器可能不允许直接连接到本地文件。因此，通过这个参数，您可以启用它。这对我有用。|1775888|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|V|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|W|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|Z|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|S|$]]

If you are using Java8 or + version, JDBC and MySql8 and facing this issue then try this:

Add parameter to connection string:

<pre><code>jdbc:mysql://localhost:3306/tempDB?allowLoadLocalInfile=true
</code></pre>

Also, set 

<blockquote>
 local_infile = 1
</blockquote>

in my.cnf file. 

The latest version of mysql-java-connector might be wont allow directly to connect to local file. So with this parameter, you can able to enable it. This works for me.

blocks|key|3096329|text|也是为了解决这个问题，试图使用.csv上的MySQL+Workbench从我的本地机器将数据上传到AWS实例中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3096330|我需要添加的是在:+Connection+>+Advanced+>Other中添加OPT_LOCAL_INFILE=1。需要设置注释上限。|CODE|3096331|我找到了AWS开发人员论坛中的PeterMag的答案。|3096332|欲了解更多信息：|3096333|SHOW+VARIABLES+LIKE+'local_infile';已经返回了ON，查询是：|3096334|LOAD+DATA+LOCAL+INFILE+'filepath/file.csv'+
++++INTO+TABLE+`table_name`
++++FIELDS+TERMINATED+BY+','+
++++ENCLOSED+BY+'"'
++++LINES+TERMINATED+BY+'\n'
++++IGNORE+1+ROWS;|code-block|syntax|javascript|3096335|从上面引用的应答源复制：|3096336|显然，这是MYSQL+V8.X中的一个bug。除了这个线程前面显示的配置之外，您还需要按如下方式更改Workbench中的MYSQL连接：|blockquote|3096337|转到MYSQL的欢迎页面，该页面显示所有连接|ordered-list-item|3096338|选择(小扳手图标)|3096339|选择您的连接|3096340|选择高级选项卡|3096341|在“其他”框中，添加OPT_LOCAL_INFILE=1|3096342|现在，我可以在MYSQL上使用LOAD数据本地INFILE查询。似乎不需要File_priv权限。|3096343|entityMap|0|LINK|mutability|MUTABLE|url|https://forums.aws.amazon.com/thread.jspa?messageID=942113#jive-message-942113^0|L|F|0|15|I|0|4|J|0|0|0|0|Z|14|2|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1J|8|@$9|1K|A|1L|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1M|8|@$9|1N|A|1O|B|H]]|D|@]|E|$]]|$1|I|3|J|5|6|7|1P|8|@]|D|@$9|1Q|A|1R|1|1S]]|E|$]]|$1|K|3|L|5|6|7|1T|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|1U|8|@$9|1V|A|1W|B|H]|$9|1X|A|1Y|B|H]]|D|@]|E|$]]|$1|O|3|P|5|Q|7|1Z|8|@]|D|@]|E|$R|S]]|$1|T|3|U|5|6|7|20|8|@]|D|@]|E|$]]|$1|V|3|W|5|X|7|21|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|10|7|22|8|@]|D|@]|E|$]]|$1|11|3|12|5|10|7|23|8|@]|D|@]|E|$]]|$1|13|3|14|5|10|7|24|8|@]|D|@]|E|$]]|$1|15|3|16|5|10|7|25|8|@]|D|@]|E|$]]|$1|17|3|18|5|10|7|26|8|@]|D|@]|E|$]]|$1|19|3|1A|5|6|7|27|8|@]|D|@]|E|$]]|$1|1B|3|-4|5|6|7|28|8|@]|D|@]|E|$]]]|1C|$1D|$5|1E|1F|1G|E|$1H|1I]]]]

Also struggled with this issue, trying to upload .csv data into AWS RDS instance from my local machine using MySQL Workbench on Windows.

The addition I needed was adding <code>OPT_LOCAL_INFILE=1</code> in: Connection > Advanced > Others. Note CAPS was required.

I found this answer by <a href="https://forums.aws.amazon.com/thread.jspa?messageID=942113#jive-message-942113" rel="noreferrer">PeterMag in AWS Developer Forums</a>.

<hr>

For further info:

<code>SHOW VARIABLES LIKE 'local_infile';</code> already returned <code>ON</code>
and the query was:

<pre><code>LOAD DATA LOCAL INFILE 'filepath/file.csv' 
 INTO TABLE `table_name`
 FIELDS TERMINATED BY ',' 
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 IGNORE 1 ROWS;
</code></pre>

Copying from the answer source referenced above:

<blockquote>
 Apparently this is a bug in MYSQL Workbench V8.X. In addition to the
 configurations shown earlier in this thread, you also need to change
 the MYSQL Connection in Workbench as follows:
 
 <ol>
 <li>Go to the Welcome page of MYSQL which displays all your connections</li>
 <li>Select Manage Server Connections (the little spanner icon)</li>
 <li>Select your connection</li>
 <li>Select Advanced tab</li>
 <li>In the Others box, add OPT_LOCAL_INFILE=1</li>
 </ol>
 
 Now I can use the LOAD DATA LOCAL INFILE query on MYSQL RDS. It seems
 that the File_priv permission is not required.*
</blockquote>

blocks|key|3096373|text|我在Windows10Pro上的MySQL工作台版本8.031上也面临着同样的问题，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3096374|我更改了Edit+Connection+-->Advanced+Go在编辑others下面|offset|length|style|CODE|3096375|增加以下一行：|3096376|OPT_LOCAL_INFILE=1|code-block|syntax|javascript|3096377|然后试着运行查询。|3096378|entityMap^0|0|4|R|11|6|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@$D|U|E|V|F|G]|$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|R|$]]

I am faced same issue on MySQL Workbench Version 8.031 on Windows 10 pro machine,
And I changed the <code>Edit Connection --&gt;Advanced</code> Go below Edit <code>others</code>
Add below line :
<pre><code>OPT_LOCAL_INFILE=1
</code></pre>
And try run the query.

I am trying to load data into mysql database using

<pre><code>LOAD DATA LOCAL
INFILE A.txt
INTO DB
LINES TERMINATED BY '|';
</code></pre>

the topic of this question is the response I get. I understand the local data offloading is off by default and I have to enable it using a the command <code>local-infile=1</code> but I do not know where to place this command.

ERROR 1148: The used command is not allowed with this MySQL version

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正在尝试使用LOAD DATA LOCALINFILE A.txtINTO DBLINES TERMINATED BY '|';这个问题的主题是我得到的回答。我知道默认情况下本地数据卸载是关闭的，我必须使用命令local-infile=1来启用它，但是我不知道把这个命令放在哪里。

问错误1148:此MySQL版本不允许使用使用的命令
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问错误1148:此MySQL版本不允许使用使用的命令EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问错误1148:此MySQL版本不允许使用使用的命令
EN