MySQL LOAD_FILE()加载空值 [英] MySQL LOAD_FILE() loads null values

问题描述

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

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.

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

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.

以下是对该主题的讨论:

The following are discussions of the topic:

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

以及

MySQL LOAD_FILE返回NULL

推荐答案

请确保:

• 父目录具有执行权限
• 必须明确授予FILE特权. (上的GRANT文件.到user @ localhost)
• 您拥有特权刷新
• 您已注销并重新登录

• 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

父目录权限的示例:

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
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

用户权限示例:

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)

在其他根会话中:

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
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

这篇关于MySQL LOAD_FILE()加载空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！