mysql - MySQL LOAD_FILE()加载空值

Question

我正在尝试将xml文件的目录作为blob加载到数据库表中。每行将包含一个对应的Blob文件。通过python脚本将文件加载到表中时，值将插入为null。在mysql命令行中运行等效命令时，也是如此。

有一次，我可以在将一些文件夹权限更改为mysql后插入值，但是由于需要脚本权限，我不得不修改目录/var/lib/mysql/foo的所有权，因此值被重新插入为null，有效地打破了我写的脚本。我不记得要进行必要的目录权限更改。

以下是对该主题的讨论:

http://bugs.mysql.com/bug.php?id=38403

随着

MySQL LOAD_FILE returning NULL

Answer 1

确保:

在父目录

上具有执行权限

必须明确授予FILE特权。 (位于。TO user @ localhost上的GRANT FILE)

您已刷新特权

您已注销并重新登录

父目录权限的示例:

mysql>\! ls -ld`dirname/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg`
drwxrwxr--。 2 jlam jlam 4096 5月12日14:22/home/jlam/code/projectName/doc/filesForTesting/images

mysql>选择十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/image

Test01.jpg'));
+ ------------------------------------------------- -------------------------------------------------- ---------- +
|十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))|
+ ------------------------------------------------- -------------------------------------------------- ---------- +
| NULL |
+ ------------------------------------------------- -------------------------------------------------- ---------- +
设置1行(0.00秒)

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 5月12日14:22/home/jlam/code/projectName/doc/filesForTesting/images
mysql>选择十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------
|十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------
| FFD8FFE1001845786966000049492A00080000000000000000000000FFEC00114475636B7900010004000000500000FFE10407687474703A2F2F6E732E61646F62652E636F6D2F786​​1702F312E302F003C3F787​​061636B657420626567696E3D22EFBBBF222069643D2257354869
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------

用户权限示例:

16:38:09(getImages)〜/code/projectName/doc/filesForTesting/images $ mysql -u eventCal -p eventCal
输入密码:

mysql>显示授权；
+ ------------------------------------------------- -------------------------------------------------- -------------- +
|授予eventCal @ localhost |
+ ------------------------------------------------- -------------------------------------------------- -------------- +
| *。*到“eventCal” @“localhost”上的授权使用已由密码“* xxxx”标识
|将所有特权授予tmp。*至'eventCal'@'localhost'|
|将所有特权授予`eventCalTesting`。*到'eventCal'@'localhost'|
|将所有特权授予`eventCal`。*到'eventCal'@'localhost'|
|将所有特权授予`eventCal_categoryMigration`。*至'eventCal'@'localhost'|
+ ------------------------------------------------- -------------------------------------------------- -------------- +
设置5行(0.00秒)

mysql>选择十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+ ------------------------------------------------- -------------------------------------------------- ---------- +
|十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))|
+ ------------------------------------------------- -------------------------------------------------- ---------- +
| NULL |
+ ------------------------------------------------- -------------------------------------------------- ---------- +
设置1行(0.00秒)

在其他根 session 中:

mysql>将文件ON *。*授予eventCal @ localhost;
查询正常，受影响的0行(0.00秒)

mysql>刷新权限；
查询正常，受影响的0行(0.00秒)

在用户 session 中，我仍然无法加载文件

mysql>选择十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+ ------------------------------------------------- -------------------------------------------------- ---------- +
|十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))|
+ ------------------------------------------------- -------------------------------------------------- ---------- +
| NULL |
+ ------------------------------------------------- -------------------------------------------------- ---------- +
设置1行(0.00秒)

.....但是如果我注销并重新登录:

mysql>退出
再见

16:40:14(getImages)〜/code/projectName/doc/filesForTesting/images $ mysql -u eventCal -p eventCal
输入密码:

mysql>选择十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'));
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------
|十六进制(LOAD_FILE('/home/jlam/code/projectName/doc/filesForTesting/images/imageTest01.jpg'))
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------
| FFD8FFE1001845786966000049492A00080000000000000000000000FFEC00114475636B7900010004000000500000FFE10407687474703A2F2F6E732E61646F62652E636F6D2F786​​1702F312E302F003C3F787​​061636B657420626567696E3D22EFBBBF222069643D2257354869
+ ------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------