blocks|key|3275774|text|我也遇到了这个问题。我必须将LOCAL添加到我的SQL语句中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|CODE|entityRanges|data|3275775|例如，这给出了权限问题：|3275776|LOAD+DATA+INFILE+'{$file}'+INTO+TABLE+{$table}|code-block|syntax|javascript|3275777|将LOCAL添加到您的语句中，权限问题就会消失。如下所示：|3275778|LOAD+DATA+LOCAL+INFILE+'{$file}'+INTO+TABLE+{$table}|3275779|entityMap^0|E|5|E|5|0|0|0|1|5|1|5|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|D]]|E|@]|F|$]]|$1|G|3|H|5|6|7|Y|8|@]|E|@]|F|$]]|$1|I|3|J|5|K|7|Z|8|@]|E|@]|F|$L|M]]|$1|N|3|O|5|6|7|10|8|@$9|11|A|12|B|C]|$9|13|A|14|B|D]]|E|@]|F|$]]|$1|P|3|Q|5|K|7|15|8|@]|E|@]|F|$L|M]]|$1|R|3|-4|5|6|7|16|8|@]|E|@]|F|$]]]|S|$]]

I just ran into this issue as well. I had to add <code>LOCAL</code> to my SQL statement.

For example, this gives the permission problem: 

<pre class="lang-sql prettyprint-override"><code>LOAD DATA INFILE '{$file}' INTO TABLE {$table}
</code></pre>

Add <code>LOCAL</code> to your statement and the permissions issue should go away. Like so: 

<pre class="lang-sql prettyprint-override"><code>LOAD DATA LOCAL INFILE '{$file}' INTO TABLE {$table}
</code></pre>

blocks|key|3275793|text|我遇到了这个问题。我到处找，也没有找到满意的答案。我将我的搜索结果总结如下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3275794|拒绝访问错误可能意味着：|3275795|3275796|'user'@'localhost‘没有file权限(GRANT+FILE+on+*.*+to+user@'localhost')；或者，|unordered-list-item|offset|length|style|CODE|3275797|您尝试加载的文件在运行mysql服务器的计算机上不存在(如果使用load+DATA+INFILE)；或者，|3275798|您尝试加载的文件在本地计算机上不存在(如果使用LOAD+DATA+LOCAL+INFILE)；或者，|3275799|您尝试加载的文件不是完全可读的(您需要文件和所有父目录都是完全可读的:chmod755目录；和chmod744+file.dat)|3275800|3275801|entityMap^0|0|0|0|R|11|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|E|3|F|5|G|7|X|8|@$H|Y|I|Z|J|K]]|9|@]|A|$]]|$1|L|3|M|5|G|7|10|8|@]|9|@]|A|$]]|$1|N|3|O|5|G|7|11|8|@]|9|@]|A|$]]|$1|P|3|Q|5|G|7|12|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

I had this problem. I searched around and did not find a satisfactory answer. I summarise below the results of my searches. 

The access denied error could mean that:

<ul>
<li>'user'@'localhost' does not have the FILE privilege (<code>GRANT FILE on *.* to user@'localhost'</code>); or,</li>
<li>the file you are trying to load does not exist on the machine running mysql server (if using LOAD DATA INFILE); or,</li>
<li>the file you are trying to load does not exist on your local machine (if using LOAD DATA LOCAL INFILE); or,</li>
<li>the file you are trying to load is not world readable (you need the file and all parent directories to be world-readable: chmod 755 directory; and, chmod 744 file.dat)</li>
</ul>

blocks|key|3275812|text|尝试使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3275813|load+data+local+infile+'home/data.txt'+into+table+customer;|code-block|syntax|javascript|3275814|这应该是可行的。它在我的案例中起作用了。|3275815|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try using this command:

<pre><code>load data local infile 'home/data.txt' into table customer;
</code></pre>

This should work. It worked in my case.

blocks|key|3275770|text|确保您的MySQL用户具有FILE权限。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3275771|如果您使用的是共享虚拟主机，则有可能会被您的虚拟主机提供商阻止。|3275772|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Ensure your MySQL user has the FILE privilege granted.

If you are on shared web hosting, there is a chance this is blocked by your hosting provider.

blocks|key|319795|text|如果您使用命令行，我找到了一个简单的方法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319796|以mysql+-u[username]+-p[password]+--local-infile身份登录|offset|length|style|CODE|319797|然后是SET+GLOBAL+local_infile+=+1;|319798|通过use+[db_name]选择您的数据库|319799|最后是LOAD+DATA+LOCAL+INFILE+'C:\\Users\\shant\\Downloads\\data-1573708892247.csv'+INTO+TABLE+visitors_final_test+FIELDS+TERMINATED+BY+','LINES+TERMINATED+BY+'\r+\n'+IGNORE+1+LINES;|319800|entityMap^0|0|1|1A|0|3|S|0|2|D|0|3|4V|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@$D|R|E|S|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@$D|U|E|V|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|W|8|@$D|X|E|Y|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|Z|8|@$D|10|E|11|F|G]]|9|@]|A|$]]|$1|N|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|O|$]]

I found easy one if you are using command line 

Login as<code>mysql -u[username] -p[password] --local-infile</code>

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

select your database by <code>use [db_name]</code>

and finally <code>LOAD DATA LOCAL INFILE 'C:\\Users\\shant\\Downloads\\data-1573708892247.csv' INTO TABLE visitors_final_test FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;</code>

blocks|key|319734|text|来自Lyon的字符串给了我一个非常好的提示:在Windows上，我们需要使用slahes而不是反斜杠。下面的代码适用于我：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319735|++++File+tempFile+=+File.createTempFile(tableName,+".csv");
++++FileUtils.copyInputStreamToFile(data,+tempFile);

++++JdbcTemplate+template+=+new+JdbcTemplate(dataSource);
++++String+path+=+tempFile.getAbsolutePath().replace('\\',+'/');
++++int+rows+=+template.update(MessageFormat
++++++++++++.format("LOAD+DATA+LOCAL+INFILE+''{0}''+INTO+TABLE+{1}+FIELDS+TERMINATED+BY+'',''",
++++++++++++++++++++path,+tableName));
++++logger.info("imported+{}+rows+into+{}",+rows,+tableName);

++++tempFile.delete();|code-block|syntax|javascript|319736|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The string from Lyon gave me a very good tip: On Windows, we need to use slahes and not backslashes. This code works for me:

<pre><code> File tempFile = File.createTempFile(tableName, ".csv");
 FileUtils.copyInputStreamToFile(data, tempFile);

 JdbcTemplate template = new JdbcTemplate(dataSource);
 String path = tempFile.getAbsolutePath().replace('\\', '/');
 int rows = template.update(MessageFormat
 .format("LOAD DATA LOCAL INFILE ''{0}'' INTO TABLE {1} FIELDS TERMINATED BY '',''",
 path, tableName));
 logger.info("imported {} rows into {}", rows, tableName);

 tempFile.delete();
</code></pre>

blocks|key|3062922|text|我遇到了同样的问题，并通过以下步骤解决了它：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3062923|我的自定义mysql+user|3062924|deactivate+load_infile变量的|unordered-list-item|3062925|激活load_infile变量|3062926|大文件权限(我的文件由was服务器上传到/tmp文件夹，该文件夹当然不是myslq+user|3062927|deactivate的安全目录|3062928|3062929|3062930|关于这第三点，您可以参考：https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv|offset|length|3062931|BR|3062932|广告|3062933|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv^0|0|0|0|0|0|0|0|0|D|2K|0|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|17|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|18|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|19|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1C|8|@]|9|@$Q|1D|R|1E|1|1F]]|A|$]]|$1|S|3|T|5|6|7|1G|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1H|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

I ran into the same issue, and solve it by folowing those steps :

<ul>
<li>activate load_infile variable</li>
<li>grand file permission to my custom mysql user</li>
<li>deactivate secure_file_priv variable (my file was uploaded by the webserver to the /tmp folder which is of course not the secured directory of myslq /var/lib/mysql-file)</li>
</ul>

For this 3rd point, you can refer to : <a href="https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv</a>

BR,

AD

blocks|key|314774|text|这也发生在我身上，尽管我遵循了Yamir在他的帖子中描述的所有步骤，但我无法让它工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|314775|该文件位于/tmp/test.csv中，权限为777。MySQL用户有文件权限，我的MySQL版本不允许本地选项，所以我被卡住了。|314776|最后，我能够通过运行以下命令来解决这个问题：|314777|sudo+chown+mysql:mysql+/tmp/test.csv|code-block|syntax|javascript|314778|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This happened to me as well and despite having followed all the steps described by Yamir in his post I couldn't make it work.

The file was in /tmp/test.csv with 777 permissions. The MySQL user had file permissions, LOCAL option was not allowed by my MySQL version, so I was stuck.

Finally I was able to solve the problem by running:

<pre><code>sudo chown mysql:mysql /tmp/test.csv
</code></pre>

blocks|key|3275875|text|我发现加载MySQL表可以快速而轻松(我使用的是python+/+Django模型管理器脚本)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3275876|1)创建包含所有列VARCHAR(n)+NULL的表，例如：|3275877|mysql>+CREATE+TABLE+cw_well2(+api+VARCHAR(10)+NULL,api_county+VARCHAR(3)+NULL);|offset|length|style|CODE|3275878| 2)从csv中移除头部(第一行)，然后加载(如果忘记了本地，会得到“#1045-拒绝访问用户‘user+'user'@'localhost’(using+password:+YES)”)：|3275879|mysql>+LOAD+DATA+LOCAL+INFILE+"/home/magula6/cogswatch2/well2.csv"+INTO+TABLE+cw_well2+FIELDS+TERMINATED+BY+','+LINES+TERMINATED+BY+'\n'+ +  ->+;+Query+OK,+119426+rows+affected,+19962+warnings +(3.41+sec)|3275880| 3)修改列：|3275881|mysql>+ALTER+TABLE+cw_well2+CHANGE+spud_date+spud_date+DATE;|3275882|mysql>+ALTER+TABLE+cw_well2+CHANGE+latitude+latitude+FLOAT;|3275883|瞧！|3275884|entityMap^0|0|0|0|27|0|0|0|5O|0|0|0|1O|0|0|1N|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Y|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Z|8|@$F|10|G|11|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|12|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|13|8|@$F|14|G|15|H|I]]|9|@]|A|$]]|$1|N|3|O|5|6|7|16|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|17|8|@$F|18|G|19|H|I]]|9|@]|A|$]]|$1|R|3|S|5|6|7|1A|8|@$F|1B|G|1C|H|I]]|9|@]|A|$]]|$1|T|3|U|5|6|7|1D|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]]|W|$]]

I discovered loading MySQL tables can be fast and painless (I was using python / Django model manager scripts):

1) create table with all columns VARCHAR(n) NULL e.g.:

<code>mysql&gt; CREATE TABLE cw_well2( api VARCHAR(10) NULL,api_county VARCHAR(3) NULL);</code>

 
2) remove headers (first line) from csv, then load (if you forget the LOCAL, you’ll get “#1045 - Access denied for user 'user'@'localhost' (using password: YES)”):

<code>mysql&gt; LOAD DATA LOCAL INFILE "/home/magula6/cogswatch2/well2.csv" INTO TABLE cw_well2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
    -&gt; ;
Query OK, 119426 rows affected, 19962 warnings  (3.41 sec)</code>

 
3) alter columns:

<code>mysql&gt; ALTER TABLE cw_well2 CHANGE spud_date spud_date DATE;</code> 

<code>mysql&gt; ALTER TABLE cw_well2 CHANGE latitude latitude FLOAT;</code>

voilà!

blocks|key|319810|text|如果您在MySQL工作台上尝试此操作，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|319811|转到connections+->+edit+connection+->选择advanced选项卡|319812|并在“Other”文本字段中添加OPT_LOCAL_INFILE=1。|offset|length|style|CODE|319813|现在重新启动连接并尝试。|319814|​|319815|📷|atomic|319816|319817|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/d7635889c829e1c2b132892c48a71c1f.png|imageAlt^0|0|0|G|I|0|0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|12|8|@$F|13|G|14|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|16|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|17|8|@]|9|@$F|18|G|19|1|1A]]|A|$]]|$1|Q|3|M|5|6|7|1B|8|@]|9|@]|A|$]]|$1|R|3|-4|5|6|7|1C|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y|Z|-4]]]]

If you are trying this on MySQL Workbench,
Go to connections -&gt; edit connection -&gt; select advanced tab
and add <code>OPT_LOCAL_INFILE=1</code> in the 'Others' text field.
Now restart the connection and try.
<a href="https://i.stack.imgur.com/3DGls.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/3DGls.png" alt="enter image description here" /></a>

blocks|key|3062865|text|这可能意味着您为'user'@'localhost'提供的密码不正确。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3062866|entityMap^0|8|I|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

It probably means that the password you supplied for <code>'user'@'localhost'</code> is incorrect.

I use MySQL queries all the time in PHP, but when I try 

<pre class="lang-sql prettyprint-override"><code>LOAD DATA INFILE
</code></pre>

I get the following error 

<blockquote>
 #1045 - Access denied for user 'user'@'localhost' (using password: YES)
</blockquote>

Does anyone know what this means?

access denied for load data infile in MySQL

虚拟主机

数据库

服务器

Windows

Django

MySQL

我在PHP中一直使用MySQL查询，但是当我尝试LOAD DATA INFILE我得到以下错误 #1045 -拒绝用户' user '@'localhost‘的访问(使用密码:是)有人知道这是什么意思吗？

问拒绝访问MySQL中的load data infile
EN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拒绝访问MySQL中的load data infileEN

回答 11

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拒绝访问MySQL中的load data infile
EN