MySQL 加载数据文件错误代码：13

LOAD DATA INFILE Error Code : 13

提问by user292049

In my remote MySQL, when I try to execute this query, I am getting the MySQL Error Code : 13.

在我的远程 MySQL 中，当我尝试执行此查询时，我收到 MySQL 错误代码：13。

Query -

询问 -

LOAD DATA INFILE 
'/httpdocs/.../.../testFile.csv'
INTO TABLE table_temp
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r \n'
(sku, qty);

Error Code : 13 Can't get stat of '/httpdocs/.../.../testFile.csv' (Errcode: 2)

错误代码 ： 13 Can't get stat of '/httpdocs/.../.../testFile.csv' (Errcode: 2)

a. The database userlogin has all the grant priviliges.

一种。数据库用户登录具有所有授予权限。

CREATE USER 'userName'@'%' IDENTIFIED BY '************';

GRANT ALL PRIVILEGES ON * . * TO 'userName'@'%' IDENTIFIED BY '************' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `userName\_%` . * TO 'userName'@'%';

b. I have also set the file and folder permission to chmod 777 (rwxrwxrwx) using FTP Tool

湾 我还使用 FTP 工具将文件和文件夹权限设置为 chmod 777 (rwxrwxrwx)

回答by Nelson

I know that this post is old, but this still comes up in search results. I couldn't find the solution to this problem online, so I ended up figuring it out myself. If you're using Ubuntu, then there is a program called "Apparmor" that is preventing MySQL from seeing the file. Here's what you need to do if you want MySQL to be able to read files from the "tmp" directory:

我知道这篇文章很旧，但这仍然出现在搜索结果中。我在网上找不到这个问题的解决方案，所以我最终自己解决了这个问题。如果您使用的是 Ubuntu，那么有一个名为“Apparmor”的程序会阻止 MySQL 看到该文件。如果您希望 MySQL 能够从“tmp”目录读取文件，则需要执行以下操作：

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Once you are in the file, you're going to see a bunch of directories that MySQL can use. Add the line /tmp/** rwkto the file (I am not sure that it matters where, but here is a sample of where I put it):

进入该文件后，您将看到一组 MySQL 可以使用的目录。将该行添加/tmp/** rwk到文件中（我不确定它放在哪里很重要，但这是我放置它的位置的示例）：

  /etc/mysql/*.pem r,

  /etc/mysql/conf.d/ r,

  /etc/mysql/conf.d/* r,

  /etc/mysql/*.cnf r,

  /usr/lib/mysql/plugin/ r,

  /usr/lib/mysql/plugin/*.so* mr,

  /usr/sbin/mysqld mr,

  /usr/share/mysql/** r,

  /var/log/mysql.log rw,

  /var/log/mysql.err rw,

  /var/lib/mysql/ r,

  /var/lib/mysql/** rwk,


  /tmp/** rwk,


  /var/log/mysql/ r,

  /var/log/mysql/* rw,

  /var/run/mysqld/mysqld.pid w,

  /var/run/mysqld/mysqld.sock w,

  /run/mysqld/mysqld.pid w,

  /run/mysqld/mysqld.sock w,

Now all you need to do is reload Apparmor:

现在您需要做的就是重新加载 Apparmor：

sudo /etc/init.d/apparmor reload

Note I used "vim", but substitute that with whatever your favorite text editor is that you know how to use.

注意我使用了“vim”，但是用你最喜欢的、你知道如何使用的文本编辑器来代替它。

回答by Matthias Munz

Adding the keyword 'LOCAL' to my query worked for me:

将关键字“LOCAL”添加到我的查询中对我有用：

LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name

A detailed description of the keyword can be found here.

可以在此处找到该关键字的详细说明。

回答by JonVD

This is normally a file access permissions issue but I see your already addressing that in point b, but it's worth going over just in case. Another option is to use LOAD DATA LOCAL INFILE which gets past a lot of these issues of file access permissions. To use this method though you need to copy the file locally (in the mysql folder is best) first. ?If LOCAL is specified, the file is read by the client program on the client host and sent to the server.

这通常是一个文件访问权限问题，但我看到您已经在 b 点解决了这个问题，但为了以防万一，值得一读。另一种选择是使用 LOAD DATA LOCAL INFILE ，它解决了许多文件访问权限问题。要使用此方法，您需要先将文件复制到本地（最好在 mysql 文件夹中）。?如果指定了LOCAL，则该文件由客户端主机上的客户端程序读取并发送到服务器。

Insufficient Directory Permissions

The error in this example has resulted because the file you are trying to import is not in a directory which is readable by the user the MySql server is running as. Note that all the parent directories of the directory the is in need to be readable by the MySql user for this to work. Saving the file to /tmp will usually work as this is usually readable (and writable) by all users. The error code number is 13. Source

目录权限不足

此示例中的错误是因为您尝试导入的文件不在运行 MySql 服务器的用户可读的目录中。请注意，MySql 用户需要读取该目录的所有父目录才能使其工作。将文件保存到 /tmp 通常会起作用，因为这通常可以被所有用户读取（和写入）。错误代码编号为 13。 来源

EDIT:

编辑：

Remember you will need permissions on not just the folder the holds the file but also the upper directories.

请记住，您不仅需要对保存文件的文件夹的权限，还需要对上层目录的权限。

Example from thispost on the MySql Forums.

MySql 论坛上这篇文章的示例。

If your file was contained within the following strucutre: /tmp/imports/site1/data.file

如果您的文件包含在以下结构中：/tmp/imports/site1/data.file

you would need (I think, 755 worked) r+x for 'other' on these directories:

您需要（我认为，755 工作）r+x 用于这些目录上的“其他”：

/tmp

/tmp

/tmp/imports

/tmp/进口

As well as the main two:

除了主要的两个：

/tmp/imports/site1

/tmp/imports/site1

/tmp/imports/site1/data.file

/tmp/imports/site1/data.file

You need the file and directory to be world-readable. Apologies if you've already tried this method but it may be worth retracing your steps, never hurts to double check.

您需要文件和目录是世界可读的。抱歉，如果您已经尝试过这种方法，但可能值得回顾一下您的步骤，仔细检查一下也无妨。

回答by Ankit Sharma

There is one property in mysql configuration file under section [mysqld] with name - tmpdir

在 [mysqld] 部分下的 mysql 配置文件中有一个属性，名称为 - tmpdir

for example:

例如：

tmpdir = c:/temp (Windows) or tmpdir = /tmp (Linux)

and LOAD DATA INFILEcommand can perform read and write on this location only.

和LOAD DATA INFILE命令只能在这个位置上执行读写操作。

so if you put your file at that specified location then LOAD DATA INFILEcan read/write any file easily.

因此，如果您将文件放在该指定位置，则LOAD DATA INFILE可以轻松读取/写入任何文件。

One more solution

另一种解决方案

we can import data by following command too

我们也可以通过以下命令导入数据

load data local infile

In this case there is no need to move file to tmpdir, you can give the absolute path of file, but to execute this command, you need to change one flag value. The flag is

在这种情况下不需要将文件移动到 tmpdir，你可以给出文件的绝对路径，但是要执行这个命令，你需要改变一个标志值。国旗是

--local-infile

you can change its value by command prompt while getting access of mysql

您可以在访问 mysql 的同时通过命令提示符更改其值

mysql -u username -p --local-infile=1

Cheers

干杯

回答by Flem

I use Ubuntu 12.04, I had to create the table, then do one of these:

我使用 Ubuntu 12.04，我必须创建表，然后执行以下操作之一：

Using local gives an error (mysql 5.5):

使用 local 会出现错误（mysql 5.5）：

> LOAD DATA LOCAL INFILE "file.csv" INTO table inverter FIELDS TERMINATED BY ',';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

LOAD DATA INFILE command is disabled by default for security reasons, re-enable it here and it should work: https://stackoverflow.com/a/16286112/445131

出于安全原因，默认情况下禁用 LOAD DATA INFILE 命令，请在此处重新启用它，它应该可以工作：https: //stackoverflow.com/a/16286112/445131

You can import the csv-file using mysqlimport:

您可以使用 mysqlimport 导入 csv 文件：

mysqlimport -u root -p --fields-terminated-by=',' --local dbname tablename.csv

Note the csv-file must have same name before the extension as the table.

请注意，csv 文件在扩展名之前必须与表具有相同的名称。

回答by Avnish Mehta

Error 13 is nothing but the permission issues. Even i had the same issueand was unable to load data to mysql table and then resolved the issue myself.

错误 13 只不过是权限问题。即使我也有同样的问题，无法将数据加载到 mysql 表，然后自己解决了这个问题。

Here's the solution:

这是解决方案：

Bydefault the

默认情况下

--local-infile is set to value 0

--local-infile 设置为值 0

, inorder to use LOAD DATA LOCAL INFILE it must be enabled.

, 为了使用LOAD DATA LOCAL INFILE 必须启用它。

So Start mySQLlike this :

所以像这样启动mySQL：

mysql -u username -p --local-infile

mysql -u 用户名 -p --local-infile

This will make LOCAL INFILE enabled during startup and thus there wont be any issues using it !

这将使 LOCAL INFILE 在启动期间启用，因此使用它不会有任何问题！

回答by bryant1410

If you are using Fedora/RHEL/CentOO you can disable temporarily SELinux:

如果您使用 Fedora/RHEL/CentOO，您可以暂时禁用 SELinux：

setenforce 0

Load your data and then enable it back:

加载您的数据，然后重新启用它：

setenforce 1

回答by solo

I had a lot of trouble to fix this, there are two things to do :

我在解决这个问题时遇到了很多麻烦，有两件事要做：

• update file /etc/mysql/my.cnfwith local-infile =1at twolocations after [mysql]paragraphs (this allows to use LOCAL INFILE sql command).

• update via sudo gedit /etc/apparmor.d/usr.sbin.mysqldso apparmor will allow you to write into the special files of /var/www/by adding the lines:

• 更新文件/etc/mysql/my.cnf与local-infile =1在2点的位置后[mysql]段（这允许使用LOCAL INFILE SQL命令）。

• 通过sudo gedit /etc/apparmor.d/usr.sbin.mysqldso apparmor更新将允许您/var/www/通过添加以下行来写入特殊文件：

/name of your directory/ r,

/name of your directory/ r,

/name of your directory/* rw,

/name of your directory/* rw,

The name of your directory can be as /var/www/toto/(this allows to use the directory where your files are located).

您的目录的名称可以是/var/www/toto/（这允许使用您的文件所在的目录）。

回答by marcz2007

I too have struggled with this problem over the past few days and I too turned to stack overflow for answers.

在过去的几天里，我也一直在努力解决这个问题，我也转向堆栈溢出寻求答案。

However, no one seems to mention the simplest way to import data into MySQL is actually through their very own import data wizard tool!!

但是，似乎没有人提到将数据导入 MySQL 的最简单方法实际上是通过他们自己的导入数据向导工具！！

Just right-click on the table and it comes up there.

只需右键单击表格，它就会出现在那里。

None of the above answers helped me, so just use this if you're stuck! :)

以上答案都没有帮助我，所以如果你被卡住了，就用这个吧！:)

回答by Arnaud Bouchot

for Ubuntu users

对于 Ubuntu 用户

I'm running mysql 5.6.28 on Ubuntu 15.10 and I just ran into the exact same problem, I had all the necessary flags in my.cnf tmpdir = /tmp local-infile=1 restarted mysql and I would still get LOAD DATA INFILE Error Code : 13

我在 Ubuntu 15.10 上运行 mysql 5.6.28 并且我遇到了完全相同的问题，我在 my.cnf 中有所有必要的标志 tmpdir = /tmp local-infile=1 重新启动了 mysql，我仍然会得到 LOAD DATA INFILE错误代码：13

Just like Nelson mentionned the issue was "apparmor", sort of patronising mysql about permissions, I then found the solution thanks to thisquick & easy tutorial.

就像 Nelson 提到的问题是“apparmor”，有点光顾 mysql 的权限，然后我找到了解决方案，这要归功于这个快速而简单的教程。

basically, assuming your tmp dir would be /tmp:

基本上，假设您的 tmp 目录是/tmp：

Add new tmpdir entries to /etc/apparmor.d/local/usr.sbin.mysqld

将新的 tmpdir 条目添加到 /etc/apparmor.d/local/usr.sbin.mysqld

sudo nano /etc/apparmor.d/local/usr.sbin.mysqld

*add this

*添加这个

/tmp/ r,
/mnt/foo/tmp/** rw,

Reload AppArmor

重新加载 AppArmor

sudo service apparmor reload

Restart MySQL

重启 MySQL

sudo service mysql restart

I hope that'll help few Ubuntu-ers

我希望这会帮助少数 Ubuntu 用户