MySQL LOAD_FILE() 加载空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18069054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL LOAD_FILE() loads null values
提问by arete
I'm attempting to load a directory of xml files into a database table as blobs. Each row would contain a corresponding blob file. When loading files via a python script into the table, values are inserted as null. This is also the case when running an equivalent command in the mysql command line.
我正在尝试将 xml 文件目录作为 blob 加载到数据库表中。每行将包含一个相应的 blob 文件。通过 python 脚本将文件加载到表中时，值插入为空。在 mysql 命令行中运行等效命令时也是这种情况。
At one point I was able to insert values after changing some folder permissions to mysql, but due to needed scripting privileges I had to modify ownership of the directory /var/lib/mysql/foo, thus values are being reinserted as null effectively breaking the script I wrote. I cannot remember the necessary change of directory permissions to do so.
在某一时刻，我能够在将某些文件夹权限更改为 mysql 后插入值，但由于需要脚本权限，我不得不修改目录 /var/lib/mysql/foo 的所有权，因此值被重新插入为 null 有效地破坏了我写的脚本。我不记得这样做的必要目录权限更改。
The following are discussions of the topic:
以下是对该主题的讨论：
http://bugs.mysql.com/bug.php?id=38403
http://bugs.mysql.com/bug.php?id=38403
along with
随着
回答by Julien Lamarche
Make sure:
确保：
- there is execute permission on the parent directory
- The FILE privilege must is explicily granted. (GRANT FILE on .TO user@localhost)
- You have flushed privileges
- You have logged out and logged back in
- 父目录有执行权限
- 必须明确授予 FILE 特权。(GRANT FILE on .TO user@localhost)
- 您已刷新权限
- 您已注销并重新登录
Example of permission on parent dir:
父目录权限示例：
mysql> \! ls -ld `dirname /home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg`
drwxrwxr--. 2 jlam jlam 4096 May 12 14:22 /home/jlam/code/projectName/doc/filesForTesting/images
mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/image
Test01.jpg'));
+-------------------------------------------------------------------------------------------------------------+
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg')) |
+-------------------------------------------------------------------------------------------------------------+
| NULL |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> \! chmod o+x /home/jlam/code/projectName/doc/filesForTesting/images
mysql> \! ls -ld `dirname /home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg`
drwxrwxr-x. 2 jlam jlam 4096 May 12 14:22 /home/jlam/code/projectName/doc/filesForTesting/images
mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| FFD8FFE1001845786966000049492A00080000000000000000000000FFEC00114475636B7900010004000000500000FFE10407687474703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F003C3F787061636B657420626567696E3D22EFBBBF222069643D2257354D304D7043656869487
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example of user privileges:
用户权限示例：
16:38:09 (getImages) ~/code/projectName/doc/filesForTesting/images$ mysql -u eventCal -p eventCal
Enter password:
mysql> show grants;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for eventCal@localhost |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'eventCal'@'localhost' IDENTIFIED BY PASSWORD '*xxxx' |
| GRANT ALL PRIVILEGES ON `tmp`.* TO 'eventCal'@'localhost' |
| GRANT ALL PRIVILEGES ON `eventCalTesting`.* TO 'eventCal'@'localhost' |
| GRANT ALL PRIVILEGES ON `eventCal`.* TO 'eventCal'@'localhost' |
| GRANT ALL PRIVILEGES ON `eventCal_categoryMigration`.* TO 'eventCal'@'localhost' |
+-----------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+-------------------------------------------------------------------------------------------------------------+
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg')) |
+-------------------------------------------------------------------------------------------------------------+
| NULL |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
In other root session:
在其他根会话中：
mysql> grant file ON *.* to eventCal@localhost; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
Back in user session, I still can't load the file
回到用户会话，我仍然无法加载文件
mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+-------------------------------------------------------------------------------------------------------------+
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg')) |
+-------------------------------------------------------------------------------------------------------------+
| NULL |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
.....But if I log out and back in:
.....但是如果我退出并重新登录：
mysql> exit
Bye
16:40:14 (getImages) ~/code/projectName/doc/filesForTesting/images$ mysql -u eventCal -p eventCal
Enter password:
mysql> select hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| hex(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| FFD8FFE1001845786966000049492A00080000000000000000000000FFEC00114475636B7900010004000000500000FFE10407687474703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F003C3F787061636B657420626567696E3D22EFBBBF222069643D2257354D304D7043656869487
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
回答by serge3ling
I copied my file to the location where MySQL has access to.
To know the location I used:
我将我的文件复制到MySQL 可以访问的位置。
要知道我使用的位置：
select @@secure_file_priv;
and it gave me /var/lib/mysql-files/.
它给了我/var/lib/mysql-files/。
Nothing else worked: neither turning off apparmor, nor changing ownership and permissions, nor merely granting the fileprivilege. So I rolled back most of that but the right directory still works. In my case.
其他什么都不起作用：既不是关闭 apparmor，也不是更改所有权和权限，也不是仅仅授予file特权。所以我回滚了大部分，但正确的目录仍然有效。就我而言。
My source is Raymond Nijland, here: https://dba.stackexchange.com/questions/190380/load-file-producing-null
我的消息来源是 Raymond Nijland，在这里：https://dba.stackexchange.com/questions/190380/load-file-production-null
回答by Acubo
I have lost some time with this problem. In my case, the problem had to do with secure_file_privvariable defined in "my.ini".
我在这个问题上浪费了一些时间。就我而言，问题与“my.ini”中定义的secure_file_priv变量有关。
From MySQL documentation:
来自 MySQL 文档：
If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory
如果secure_file_priv 系统变量设置为非空目录名，则要加载的文件必须位于该目录中
So, there are two ways:
所以，有两种方法：
1)remove this option 2) use the default folder for uploading files ("C:\ProgramData\MySQL\MySQL Server 5.7\Uploads"). I also had to use double slash in Windows.
1) 删除此选项 2) 使用默认文件夹上传文件（“C:\ProgramData\MySQL\MySQL Server 5.7\Uploads”）。我还必须在 Windows 中使用双斜杠。
回答by arete
In order for load file to work make sure that all permissions are granted for the MySQL owner and group.
为了使加载文件工作，请确保为 MySQL 所有者和组授予所有权限。
chown mysql:mysql /var/lib/mysql/foo/*
chmod go+rw /var/lib/mysql/foo/*
回答by Sawssen BENNACEUR
I copied the image in /var/lib/mysql/images( after creating images folder) and it worked!
我将图像复制到/var/lib/mysql/images（创建图像文件夹后）并且它起作用了！
回答by Arturo Seijas
It could be due to the SO security constraits. In fact, I was able to solve the problem by disabling the MySQL profile in AppArmour
这可能是由于 SO 安全限制。事实上，我能够通过禁用AppArmour 中的 MySQL 配置文件来解决问题
The procedure I followed was to check whether there was a profile loaded for mysql
我遵循的程序是检查是否有为 mysql 加载的配置文件
sudo aa-status
and then disabling it
然后禁用它
sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld
https://www.cyberciti.biz/faq/ubuntu-linux-howto-disable-apparmor-commands/
https://www.cyberciti.biz/faq/ubuntu-linux-howto-disable-apparmor-commands/
回答by Frank Salinas
Acubo solved my issue with the Null Blob inserts. In the MySql my.ini file, the secure-file-priv was set to a specific folder that I do not use, that is why it ignored my path in the LOAD_FILE. Acubo said to make sure the secure-file-priv setting is set to empty. It will then use the hardcoded path in the LOAD_FILE statement.
Acubo 用 Null Blob 插入解决了我的问题。在 MySql my.ini 文件中，secure-file-priv 设置为我不使用的特定文件夹，这就是它忽略我在 LOAD_FILE 中的路径的原因。Acubo 说要确保将 secure-file-priv 设置设置为空。然后它将使用 LOAD_FILE 语句中的硬编码路径。
my.ini
我的.ini
Secure File Priv. Modified by Frank Salinas 12/11/2019 secure-file-priv must null in order to query directories other than the hard coded directory
安全文件隐私 由 Frank Salinas 修改 12/11/2019 secure-file-priv must null 以便查询硬编码目录以外的目录
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
secure-file-priv=""
回答by r0m4n
I had tried all the other answers here. After quite a bit of trial and error discovered that my issue was that secure-file-privwas not set to anything at all. When I set this value to a directory path in my.conf and ensured the permissions were set correctly, files could finally be accessed
我在这里尝试了所有其他答案。经过相当多的反复试验后发现我的问题secure-file-priv是根本没有设置任何内容。当我将此值设置为 my.conf 中的目录路径并确保正确设置权限时，最终可以访问文件
add to my.conf:
添加到我的.conf：
secure_file_priv="/test/"
secure_file_priv="/test/"
I think this answer is specific to mysql running on mac
我认为这个答案特定于在 mac 上运行的 mysql
