| Bug #38403 | LOAD_FILE() function works as expected only on Windows | ||
|---|---|---|---|
| Submitted: | 28 Jul 2008 12:42 | Modified: | 18 Aug 2008 13:34 |
| Reporter: | Harald Groven | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
| Version: | 5.1.24-rc-1 | OS: | Linux (Ubuntu 8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | file, fileimport, import, loadfile | ||
[28 Jul 2008 19:45]
Valeriy Kravchuk
Thank you for a problem report. Please, send/upload the problematic file.
[29 Jul 2008 9:25]
Harald Groven
This behavior is independent of which file is being imported. ANY file from the file system fails being imported by LOAD_FILE() I also tried the queries on Mac OS 10.4 with a chmod 777ed file in /Users/Shared directory. Same NULL result as on Linux.
[29 Jul 2008 9:50]
Harald Groven
LOAD_FILE() on Mysql 5.0 worked fine on SUSE linux.
[29 Jul 2008 12:22]
Valeriy Kravchuk
OK, so, is it Ubuntu-specific bug? I can not repeat it on my SuSE:
openxs@suse:/home2/openxs/dbs/5.0> ls -l ~/.bashrc
-rw-r--r-- 1 openxs users 1294 2005-12-23 15:58 /home/openxs/.bashrc
openxs@suse:/home2/openxs/dbs/5.0> cat ~/.bashrc
# Sample .bashrc for SuSE Linux
# Copyright (c) SuSE GmbH Nuernberg
...
#export PILOTPORT=/dev/pilot
#export PILOTRATE=115200
test -s ~/.alias && . ~/.alias || true
openxs@suse:/home2/openxs/dbs/5.0> bin/mysqld_safe &
[1] 16184
openxs@suse:/home2/openxs/dbs/5.0> Starting mysqld daemon with databases from /home2/openxs/dbs/5.0/var
openxs@suse:/home2/openxs/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.68-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select load_file('/home/openxs/.bashrc')\G
*************************** 1. row ***************************
load_file('/home/openxs/.bashrc'): # Sample .bashrc for SuSE Linux
# Copyright (c) SuSE GmbH Nuernberg
...
#export PILOTPORT=/dev/pilot
#export PILOTRATE=115200
test -s ~/.alias && . ~/.alias || true
1 row in set (0.01 sec)
mysql>
Can you, please, try to do same on your Ubuntu 8?
[29 Jul 2008 13:00]
Harald Groven
SELECT LOAD_FILE('/home/myUserName/test.txt') # returns NULL
Not necessarily a Ubuntu bug, it may be a 5.1 spesific bug.
So far, LOAD_FILE() has succeeded on both two 5.0 servers, while failing on two different 5.1 servers.
[29 Jul 2008 14:41]
Valeriy Kravchuk
What exact 5.1.x version(s) do you use? So far I see 5.0 in "Version" field...
[29 Jul 2008 14:43]
Valeriy Kravchuk
Same machine, same file, recent 5.1:
openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.28-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select load_file('/home/openxs/.bashrc')\G
*************************** 1. row ***************************
load_file('/home/openxs/.bashrc'): # Sample .bashrc for SuSE Linux
# Copyright (c) SuSE GmbH Nuernberg
...
#export PILOTPORT=/dev/pilot
#export PILOTRATE=115200
test -s ~/.alias && . ~/.alias || true
1 row in set (0.04 sec)
mysql>
[30 Jul 2008 10:54]
Harald Groven
Created a file, test.txt with some ASCII text. First, move to / and make 777 to check if it is a directory/permissions problem:
harald@harald-laptop:~$ sudo cp /home/harald/test.txt /test.txt
harald@harald-laptop:~$ sudo chmod 777 /test.txt
harald@harald-laptop:~$ mysql -uroot -p -Dtest
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18043
Server version: 5.1.24-rc-1 (Debian)
mysql> SELECT LOAD_FILE('/test.txt');
+------------------------+
| LOAD_FILE('/test.txt') |
+------------------------+
| NULL |
+------------------------+
1 row in set (0,00 sec)
mysql> SELECT version();
+-------------+
| version() |
+-------------+
| 5.1.24-rc-1 |
+-------------+
1 row in set (0,00 sec)
mysql>
[31 Jul 2008 5:09]
Valeriy Kravchuk
Please, send the results of:
cat /test.txt
and
SELECT LOAD_FILE('/home/harald/test.txt');
[31 Jul 2008 7:26]
Harald Groven
harald@harald-laptop:~$ cd /
harald@harald-laptop:/$ ls -l grep test.txt
-rwxrwxrwx 1 root root 9 2008-07-31 09:14 test.txt
harald@harald-laptop:/$ cat test.txt
Hi Mysql
harald@harald-laptop:/$ mysql -uroot -p -Dtest
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 226045
Server version: 5.1.24-rc-1 (Debian)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT LOAD_FILE('/test.txt') ;
ERROR 1300 (HY000): Invalid utf8 character string: '�LOAD_FILE'
mysql> SELECT CONVERT(LOAD_FILE('/test.txt') USING utf8) ;
+--------------------------------------------+
| CONVERT(LOAD_FILE('/test.txt') USING utf8) |
+--------------------------------------------+
| NULL |
+--------------------------------------------+
1 row in set (0,09 sec)
mysql> SELECT LOAD_FILE('/test.txt') ;
+------------------------+
| LOAD_FILE('/test.txt') |
+------------------------+
| NULL |
+------------------------+
1 row in set (0,00 sec)
mysql>
[18 Aug 2008 13:05]
nils petersohn
The caller of this function must have the MySQL FILE privilege !
[18 Aug 2008 13:34]
Harald Groven
it's not the FILE privilege, since I'm root...
[18 Oct 2008 11:56]
Chuan Xu
I met the similar problem on my ubuntu 8 as well. However, my mysql is a 5.0 server. mysql> select version(); +--------------------+ | version() | +--------------------+ | 5.0.51a-3ubuntu5.1 | +--------------------+ 1 row in set (0.00 sec) It is weird that loading file from /tmp works fine, but no other directories.
[21 Oct 2008 13:07]
Chuan Xu
I finally figure out that this issue is caused by apparmor, which is a security service like selinux on ubuntu. You can either stop it or change the configuration file /etc/apparmor.d/usr.sbin.mysqld.
[17 Feb 2010 10:15]
Tomasz Zdybal
I have same problem with LOAD_FILE on Archlinux / MySQL 5.1.42. So i think it's not apparmor problem.
[11 May 2010 16:33]
Dashamir Hoxha
For those that have problems with LOAD_FILE on Ubuntu, apparmor can definitely a problem (besides other problems). I had the same problem, stopped apparmor (/etc/init.d/apparmor stop), and everything worked OK.
[16 Jul 2012 18:55]
T H
The problem is because LOAD_FILE() will return null if the file you are reading isn't owned by the user 'mysql'. Even if the file is *readable* by the mysql user, if it isn't *owned* by mysql it isn't loaded. I would guess this is a bug in MySql, not in Ubuntu or AppArmor (I tried disabling it). I'm using MySQL 5.5.24 on Ubuntu 12.04. http://forums.mysql.com/read.php?10,558899,558899#msg-558899
[16 Jul 2012 19:53]
T H
I partially take back my previous comment. At least, I think ownership has something to do with it because I have never successfully loaded a file that wasn't owned by mysql. But it seems like the LOAD_FILE implementation is just plain buggy. Observe these commands on essentially identical files all owned by mysql:
mysql> UPDATE atable SET image = LOAD_FILE('/tmp/file1') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE atable SET image = LOAD_FILE('/tmp/file2') WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE atable SET image = LOAD_FILE('/tmp/file1') WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE atable SET image = LOAD_FILE('/tmp/file3') WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE atable SET image = LOAD_FILE('/tmp/file2') WHERE id = 1;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0
See how the last one didn't work even though it did literally a moment ago! There aren't even any warnings or errors. That is *definitely* a bug with MySQL.
[16 Jul 2012 19:57]
T H
Sorry for the splitting of comments, but this is a confusing and annoying issue. I have further discovered that if you try to update the row by loading a file in the same directory as the previously loaded file it says 0 rows changed, otherwise it says 1 row changed. (Sorry in the previous example I changed the folder names to make them simpler; files 2 and 3 were in a different folder to 1.) However, LOAD_FILE() still seems to return NULL in all cases. Is there another way to get binary data into MySQL? This is ridiculous.
[12 Feb 2013 18:08]
Rohit Kalhans
in and after 5.1.17 we have --secure-file-priv=path variable which allows you to set the path of the folder that can contain the file being loaded with LOAD_FILE() or LOAD DATA IN FILE command. I think (hopefully) this should solve your problems to great extent.
[11 Mar 2013 18:24]
Kevin Brammer
The secure-file-priv doesn't seem to solve the issue with CentOS 6.4 and MySQL 5.1.67. I checked max_allowed_packet against the filesize, gave mysql ownership of the file, and still can't get a BLOB into the database (returns NULL).
[11 Mar 2013 19:04]
MySQL Verification Team
Works fine on fedora 17, I ran mysqld as my user (sbester) with
--secure-file-priv=/tmp option
mysql> select load_file('/tmp/testing.txt');
+-------------------------------+
| load_file('/tmp/testing.txt') |
+-------------------------------+
| testing
|
+-------------------------------+
1 row in set (0.03 sec)
mysql> select version(), user(), current_user();
+---------------------------------------+--------+----------------+
| version() | user() | current_user() |
+---------------------------------------+--------+----------------+
| 5.5.29-enterprise-commercial-advanced | root@ | @ |
+---------------------------------------+--------+----------------+
1 row in set (0.01 sec)
[sbester@fc17 ~]$ ls -l /tmp/testing.txt
-rw-rw-r--. 1 sbester sbester 8 Mar 10 00:45 /tmp/testing.txt
[sbester@fc17 ~]$
Also, "changed" rows on an update will not increase unless the row actually changed. So, rather use SELECT instead up UPDATE to see the results of LOAD_FILE.
mysql> create table t(a int)engine=innodb;
Query OK, 0 rows affected (0.29 sec)
mysql> insert into t values (1);
Query OK, 1 row affected (0.07 sec)
mysql> update t set a=1;
Query OK, 0 rows affected (0.12 sec)
Rows matched: 1 Changed: 0 Warnings: 0
[31 Oct 2013 19:47]
Josh Castaneda
Did anyone solve this? This bug appears to be related, but I can't tell: http://bugs.mysql.com/bug.php?id=50373 Sergei says: I said that it doesn't work, not that it cannot be set to an empty string. Doesn't work means that the protection is not lifted. The code in the fix for Bug#43913 looks like: else if (opt_secure_file_priv) { char secure_file_real_path[FN_REFLEN]; (void) my_realpath(secure_file_real_path, opt_secure_file_priv, 0); if (strncmp(secure_file_real_path, name, strlen(secure_file_real_path))) which only works when opt_secure_file_priv == 0. When it's an empty string, it will be expanded and normalized and compared. That means that an empty string does not disable the check. The second bug in this bug fix: it only covers LOAD DATA. With --secure-file-priv="" we have mysql> load data infile '/etc/group' into table t; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement (which is incorrect, the as explained above), but mysql> select load_file('/etc/group'); ... 1 row in set (0.00 sec) which shows that the opt_secure_file_priv works differently for LOAD_FILE() and LOAD DATA.
[22 Nov 2017 23:50]
Steven Buehler
It is now almost a decade since this bug was submitted; it is STILL not fixed in Mac OS X (MySQL 5.7.20).
[1 Dec 2017 2:39]
Parfait Gasana
Same question here. Nearly a decade now. Is bug resolved? With MySQL 8 on Ubuntu 16.0, LOAD_FILE() does not work. I can effortlessly use LOAD DATA but cannot use LOAD_FILE() to import/update binary files like .jpg to a blob column. The function always returns NULL with no warning or error in console or log. I tried apparmor changes, file/folder ownership, read/write/execute access, etc. to no avail. Please advise and how to integrate any patches. Thanks.
[1 Dec 2017 3:40]
MySQL Verification Team
Can somebody please strace mysqld process to find what happens?
[8 Jul 2019 7:35]
JJ Ward
I'm having this problem as well running 8.0.13 on Fedora 29. LOAD DATA INFILE works like a charm, but LOAD_FILE() from the same directory fails for any file type. Tried chown/chmod, secure-file-priv="", setting max_allowed_packet high enough, etc. all mentioned here and yet it reads files as NULL without exception. Has there really been no update here to address? Any other tickets I can reference? Really do not want to change OS over this.
[20 Apr 2021 16:18]
Matthew Boehm
Here we are, 2021. MySQL 8. Still can't use this. CentOS Linux release 7.8.2003 (Core). Server version: 8.0.19-10
[root@ip-10-11-7-247 ~]# ls -lah mysqlbug.txt
-rw-r--r--. 1 mysql mysql 21 Apr 20 16:17 mysqlbug.txt
[root@ip-10-11-7-247 ~]# cat mysqlbug.txt
This is mysql 8 bug.
[root@ip-10-11-7-247 ~]# mysql -e "SELECT LOAD_FILE('/root/mysqlbug.txt');"
+------------------------------------------------------------------+
| LOAD_FILE('/root/mysqlbug.txt') |
+------------------------------------------------------------------+
| 0x |
+------------------------------------------------------------------+
Description: I regularly import files from the file system to mysql databases using the LOAD_FILE() function. It works fine on Windows, but when I tried to do the same on Linux no data is read. File paths seems to be valid, I'm logged on as root, so permissions should be ok, backslashes on win file paths and forward slashes in linux filpaths, but only Windows behave as expected. How to repeat: Win: mysql> CREATE TABLE files ( `filepath` varchar(200) NOT NULL, -- path to file in local filesystem `html` longtext -- a text field to store data ) ; Query OK, 0 rows affected (0.05 sec) mysql> mysql> INSERT INTO `files` (`filepath`, `html`) VALUES ('C:\\temp\\somefile.html', NULL); Query OK, 1 row affected (0.00 sec) mysql> mysql> UPDATE files SET html=LOAD_FILE(filepath) ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 LINUX me@my-laptop:~$ chmod 777 somefile.html me@my-laptop:~$ ls -l ... -rwxrwxrwx 1 me me 21 2008-07-14 00:46 somefile.html mysql> CREATE TABLE files ( `filepath` varchar(200) NOT NULL, `html` longtext ) ; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO `files` (`filepath`, `html`) VALUES ('~/somefile.html', NULL), ('/home/myusername/somefile.html', NULL) ; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> UPDATE files SET html=LOAD_FILE(filepath) ; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0 mysql> Result: No data imported to the column html :-( What's wrong here? Mysql, permission settings or Linux (Ubuntu) ? Suggested fix: I asked this as a Q on the forum a few weeks ago, but no one has found out if this is due to a mysql bug, or undocumented difference between platforms.